I’ve been facing problems with a query which I think is relatively well optimized but has been performing really slow as of late as the size of the table has increased to more than 1GB. The MyISAM table is updated every few seconds, only a few rows, and a different set of rows are selected every few seconds…would that cause a slowdown and if so, what is the solution?
MyISAM doesn’t have row-level locking like InnoDB, so concurrent reads and writes could slow you down. It’s hard to say without knowing more about your data, the query, and how your server is configured.
If the only thing that has really changed is the size of the table, and not your usage pattern or load, then you might just need to tune your database to deal with the larger data size. MySQL will do its best to speed things up by caching index and data pages, but when your data size gets too large, it will need to access disk more frequently. When you hit this threshold, your performance degrades noticeably.
You can tell if your index sizes are getting out of hand by looking at:
SHOW GLOBAL STATUS LIKE ‘Key_read_requests’;
SHOW GLOBAL STATUS LIKE ‘Key_reads’;
If the ratio of Key_reads:Key_read_requests is small, like say 1:50, things are working well. If it’s a lot higher, you can try increasing your key_buffer_size.
That’s a shot in the dark, but if you post a little more information, I’m sure people around here would be glad to help out.
The ratio of Key_reads:Key_read_requests is something like 1:2329
and key_buffer=128M in the my.cnf file, so should I increase that. I have 4GB ram on my server, if that info. helps.
The thing that has changed recently is that not only has the size of the table increased but also more users are accessing it simultaneously. Each user typically generates one complex select query and then after some time an update query and there are at least 30-40 users accessing the table simultaneously. It takes minutes sometime before any one person is able to progress on the page.
That’s a pretty good ratio (1:2329 is much much smaller than 1:50), so you don’t need to change the key buffer.
The next thing I would check in SHOW STATUS is Table_locks_waited vs. Table_locks_immediate. If you have a lot Table_locks_waited, then its likely a problem with read-write concurrency. In that case, you should probably switch to InnoDB.
When you SHOW PROCESSLIST and see that the queries are taking a long time, does it say they are locked? What does your EXPLAIN plan look like for the slow queries?
I thought 1:2329 is bigger than 1:50.
The status of table locks is as follows:
The query’s been as optimized as it can possibly be. The EXPLAIN shows the following:
select_type table type possible_keys key ref rows Extra
SIMPLE a ref_or_null sd,submission_date,siteid submission_date const 67587 Using where
SIMPLE c eq_ref PRIMARY PRIMARY inksmax_main.a.siteid 1 Using where
Heh, I just realized I replied to your other post as well.
That’s definitely a bigger Table_locks_waited than I would like, but I’ve never tried to have high read-write concurrency on a MyISAM table. If possible, you should test what the performance would be like if you switched to InnoDB. It will definitely take some time to rebuild a 1G table, especially with all of the indexes you have defined, but it should perform considerably better.