I had this table which contained an nXm relationship between two big tables.
PK_TABLE1 varchar(100)
PK_TABLE2 int
INFO1 varchar(30)
INFO2 date
INFO3 int
Unique key unq_t1_pk2(PK_TABLE1, PK_TABLE2)
Since it was growing very large as a single table, i divided it into multiple tables (100 tables) by breaking it horizontally based on PK_TABLE2.
Queries were running fine until a few days ago. Now the machine remains heavily loaded and the queries take too much time.
Each table has around 8,000,000 rows, 220 MB of .MYD file and around 200 MB of .MYI file.
My my.cnf looks something like this
–snip–
skip-locking
key_buffer = 1500M
back_log = 150
table_cache = 512
sort_buffer_size = 256K
read_buffer_size = 1M
max_connections = 1000
long_query_time = 5
thread_cache = 200
query_cache_size= 512M
query_cache_limit= 2M
–snip–
and queries are of the following form
mysql> explain SELECT * FROM BIG_TBL_39
WHERE PK_TABLE2
=‘171839’ and PK_TABLE1
in (binary ‘variable1’,binary ‘variable2’,binary ‘var3’,binary ‘var4’,binary ‘var5’,binary ‘var6’,binary ‘var7’,binary ‘var8’,binary ‘var9’,binary ‘var10’);
±—±------------±-----------±------±--------------±-- ---------±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-----------±------±--------------±-- ---------±--------±-----±-----±------------+
| 1 | SIMPLE | BIG_TBL_39 | range | unq_t1_pk2 | unq_t1_pk2 | 46 | NULL | 20 | Using where |
±—±------------±-----------±------±--------------±-- ---------±--------±-----±-----±------------+
1 row in set (0.04 sec)
Any suggestions ??
I am using mysql5.1.22 on a dedicated 6 GB machine with 2 Hyperthreaded Intel Xeon CPUs