Why would restarting MySQL make my site faster?

Question

hey all, my site started dragging lately, the queries taking exceptionally longer than I would expect with properly tuned indexes. I just restarted the mysql server after 31 days uptime and every query is now substantially faster and the whole site renders 3-4 times faster.

Would there be anything that jumps out at you as to why this may have been? Improper settings on my.cnf perhaps? Any ideas as to what I can start looking at to try and pinpoint why?

thanks

updated note: I have a 16GB dedicated db box and mysql runs at about 71% of memory after a week or so.

Answer 1

Try to execute show processlist;, maybe there are some long lasting threads that were not killed for some reason.

Similarly execute SHOW SESSION STATUS LIKE 'Created%'; to check if mysql hasn't created to many temporary tables.

Server restart automatically closes all open temp tables and kills threads, so the application might run quicker.

Answer 2

Do you have temporary table(s) that might not be getting cleared/collected?

Answer 3

I would suggest using MySQL Enterprise for analysis purposes. It comes with a 30 day trial. We just used it. We got alerts such as :

CRITICAL Alert - Table Scans Excessive

The target server does not appear to be using indexes efficiently.

CRITICAL Alert - Connection Usage Excessive

CRITICAL Alert - CPU Usage Excessive

WARNING Alert - MyISAM Key Cache Has Sub-Optimal Hit Rate

Just something to explore!