Re:MySQL and partitioning tables with millions of rows
by carbocation on Sunday January 31, 2010 @04:45PM
(#1091157)
Can you tell us the size of your query cache and your key cache, if they are enabled? If not, we should talk about enabling them. :-)
[ Parent
| on HN ]
Re:MySQL and partitioning tables with millions of rows
by chrismoos on Sunday January 31, 2010 @04:48PM
(#1091162)
They are rather low...I figured that would be good on a limited memory configuration (maybe not :( ).
query_cache_limit = 1M query_cache_size = 16M key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8
Re:MySQL and partitioning tables with millions of rows
by carbocation on Sunday January 31, 2010 @08:12PM
(#1091489)
OK, at least they're enabled :-) I'm not a DBA, but have hit IO issues with my MYISAM/MYSQL database many times myself.
1) Have you run Mysqltuner? If not, wget http://mysqltuner.com/mysqltuner.pl chmod to executable and execute it. I find it rather helpful.
2) Are you indexing every field that is part of any WHERE statement (at least for the big tables)?
3) Have you run EXPLAIN on the original query? (Suggested by someone else in this thread, and a critical question.)
4) Is the slow_query_log enabled, and are you sure about which query is causing the slowdown?
5) Have you tried tools like MySQL Administrator, which lets you monitor connections, threads, keys, queries, etc, in realtime?