Wednesday, December 2, 2015

performance - MySQL gradually slows down until server reboot




The performance of pages on my site that access my mysql database slows down over time until I reboot my server. (Static pages are not affected.) Restarting mysqld itself doesn't help, but rebooting the entire server helps. I've tried various MySQL performance tweaks and enabled caching, none of which helped.



It's initially very fast, but after a few days becomes much slower, and it eventually becomes unbearably slow. Any mysql operations that I run from the command line are perfectly fine, including connecting to mysql, connecting to a database, and running queries. The slow queries log doesn't show anything abnormal. But any page on my site that connects with mysql runs very slowly.



If I reboot my server, everything runs fast again, until it slows down again.



my.cnf:



max_connections = 500

query_cache_size = 10M
tmp_table_size = 16M
max_heap_table_size = 16M
thread_cache_size = 4
innodb_buffer_pool_size = 7G
symbolic-links=0


top, server up 3 days:




top-3days



top, server up 5 mins after reboot:
top-5min



What are some steps I should take to isolate and fix this problem?


Answer



Looks like an OSSEC firewall process was the culprit - it took up too much CPU and bogged everything down. When that was reconfigured everything ran smoothly again.


No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...