Posted: 2011-08-07 06:27:56|
I am having trouble with MySQL restarts. They are incredibly slow. I do restart only once every month or so when some config changes occur and restart is done Sunday only when we have the lowest use of MySQL. MySQL has aprox 10 databases each ranging from 20 to 900 Mb and each having a maximum of 2,5 million rows.
Now what happens is: I issue a STOP or a RESTART to MySQL, active queries - 1 or 2 and it takes 5-10 minutes to stop MySQL. Starting it takes seconds. Servers have dedicated Fusion IO disk and 4 in RAID1 15,000 rpm SCSI disks dedicated to MySQL only. 48 core AMD processor and server load is below 0.4 at that time with 48 Gb of RAM (4 Gb free and most of used RAM is cache).
Due to the reason that it takes so long to restart - server service monitoring script checks every 10 minutes MySQL and issues a restart if MySQL service is unresponsive. I assume that sometimes it may happen that a restart is issued twice. And sometimes it happens that MySQL tables get corrupted or marked as crashed after restart.
I have found this thread but it is talking about InnoDB and we do not use it (except for Exim mail server and running the commands suggested show 0 most of the time)
I am stuck on this and no idea where to turn. Anyone experienced the same and found a solution?
Posted: 2011-08-18 17:04:36|
Well, any MySQL restarts in 5 seconds when it was recently restarted (say within last 10-15 min).
Now taking 5- 15 minutes just to restart MySQL (without killing it) - can mean just one - your query cache is way too large, too fragmented.
And you do not need MySQL DBA and pay several thousand for 4 hrs job to fix this. All you need is tuning-primer and mysqltuner scripts to get the whole picture.
Now say we have two different database boxes - one with 1000 queries per second and the other with 200 queries per second - they both can use the same setting and will perform fine.
I assume that your query cache is over 512-768 Mb and lowering it below 512 Mb would be a good start. 384 Mb for larger ones and 256 Mb for average ones.
Having query cache under 512 Mb will allow you to restart MySQL in less than 1 minute.
We were having huge query cache consumption by "empty" entries. We had default query_cache_min_res_unit = 4 K and lowering it to query_cache_min_res_unit = 2 K has slashed query cache memory consuption by exactly half. And further more - it eliminated query cache prunes (from 2
million an hour down to 0)
You may slightly increase read_rnd_buffer_size to 10 Mb as well as tmp_table_size to 192 Mb and table_cache to 4096 if you have high traffic. It may lower the number of slow queries in your slow query log.
Your max_connections should never be over 500 as this number requires 10 Gb or RAM. You shoudl loook at the number of connections max usage over the period of 2 weeks and set max_connections value 2-3 times that. So if your peak time usage is 100 connections - make max_connections setting between 200 and 300 and you'll be fine. But that will require 5-7 Gb of RAM for mysql alone.
It seems that query cache above 128 Mb (on a powerfull box 256-384 Mb) is bad for performance. If you see that you need more than that - start looking if you have many small queries and lower query_cache_min_res_unit setting. If you have many large queries - consider using memcache for them. Cache all of your website pages to memcache for several hours as well no matter how dynamic they are - this may save you a lot!
You should be seeing query cache hit % in the high 80-90%, or else consider using NO_SQL_CACHE for teh queries that are rare or change often (for example various searches) as query cache efficiency will lower if your queries are not identical every time. Do not cache large queries either if they are not complex to retrieve. in mysql command line do FLUSH QUERY CACHE from time to time to defragment the cache and speed it up. OPTIMIZE your tables every day or two to get rid of their overhead (if they are written often).
Now to get a picture of your mysql speed:
show global status like 'Qc%';
and you will see the numbers coming.
Then for all queries ever inserted into the cache since you started the server:
%queries still in cache = 100 * Qcache_queries_in_cache / Qcache_inserts
If you have more than 5% of queries in cache after the server has been running a long time, your doing really well!
%queries removed because data changed = 100 * (Qcache_inserts - Qcache_lowmem_prunes - Qcache_queries_in_cache) / Qcache_inserts
If removals are near 100% you can reduce the cache size and let something else make better use of the memory because you are not using query cache - it is changing too often and you may consider using NO_SQL_CACHE on some queries.
%queries removed because cache was full = 100 * Qcache_lowmem_prunes / Qcache_inserts
If the this % is high, increase the cache size (but see warning about having it over 128 Mb and over 512 Mb