MySQL improve performance large tables

I am new to MySQl. Please excuse some wrong terminologies. I have a question about indexing and splitting tables in MySQL. I am working on a web server. There are 3 very large tables that are frequently queried. The tables and their fields are as follows:

Alignment: ali_id, chain1_id, chain2_id .....; Seed: seed_id, ali_id, .....; Fragment: seed_id .......

Seed contains one or more records for a given Alignment. Fragment contains one or more records for Seed.

The size of the tables is Alignment - 8.3GB, Seed - 26GB and Fragment - 127GB

These tables have very large indexes: Alignment has index for chain1_id, chain2_id, ali_id, (chain1_id and chain2_id) and (chain2_id and chain1_id). As a result size of index file itself is 27G which is roughly 3 time size of the Alignment table.

Is this appropriate?

Also since the table size is becoming very large, the server keeps on crashing. Is it a good idea to split up the tables into smaller ones. I am confused because I am not sure if multiple select statements will also slow down the server.

开发者_如何学运维Thank you.

Regards, Amit.

Tables were already created by someone else.I guess they were as follows:

create Table Alignment (
 ali_id int(11) PRIMARY KEY, 
 chain_id1 int(11), 
 chain_id2 int(11), 
 param_id smallint(6), 
 date datetime); 

create Table Seed(
 seed_id int(11), 
 Ne smallint(5), 
 rmsd float, 
 ali_id int(11), 
 identics smallint(6)); 

* Seed has 5 more fields, all are smallint(6)

create table Fragment (
 start1 smallint(6), 
 start2 smallint(6), 
 len smallint(6), 
 seed_id bigint(20));

There query that takes long time is:

select a.chain_id2, s.Ne, s.rmsd, s.zN, s.ali_id, s.identics, s.positives, s.nFrg, s.cMatch, s.cont1, s.cont2, s.bMatch, s.back1, s.back2, s.seed_id 

from Alignment AS a, Chain AS c, Seed AS s 

WHERE (a.chain_id1 = c.chain_id) and a.ali_id = s.ali_id and c.pdb_chain = "$pdb_here" and s.zN > $ZLIM;

The number of hits may vary from 100-2000

It is run on an apache server on a Linux machine witn Intel Quad Core @2.5 GHz with 4 GB RAM.

partitioning the table would be the obvious place to start

I might have assumed that your db has not been optimized. In MySQL, you can rebuild indexes, you can optimize table, you can execute a few of the table maintenance statements [1]

[1]http://dev.mysql.com/doc/refman/5.1/en/table-maintenance-sql.html

The next step, seeing that your data might be exponentially growing.
Take a look at http://www.mysql.com/products/cluster/, see if you can prep yourself for that too.

Don't worry about the size of the tables. It should not cause any problems if you make sure the indexes are properly set up. Additionally, you want to make sure that any information that is redundant is stored into a separate database. You can easily retrieve it through a JOIN query.

If your server keeps on crashing, it is either not capable enough to handle the load (with databases that big, I assume that you also have quite a visitor's load) or you might have some hardware issues (like a faulty HDD, for example). Also, your queries might need some optimizing. Slow queries can cause your table to be locked for extended amounts of time, putting new queries on hold....and well, any following scenario is not good.