Last week we started receiving monitoring alerts about the server load being to high for our real-time monitoring system application, that is gathering events from multiple monitoring systems and show’s them at a lovely dashboard. To get the real-time effect on a web application you really have a couple of options for getting the data show to the user. Lately there are web sockets and server sent events, but a while back the only method was long polling. So this means running a AJAX request every X seconds to see if there is anything new to show to the user.
Anyway, the test system has been running for a while now and the database of events is growing rapidly. When we started receiving alerts about the CPU usage (CPU usage was 10 average) we started to check history graphs of the CPU usage. It seemed to grow exponentially with growing of data. I then started with profiling MySQL database and came to a conclusion that database is probably not very well optimized.
I started the optimization process by downloading and running a couple of scripts like MySQL Tuner Script, MySQL Tuning Primer Script,… The first script proved it self to do the job very well. The first thing I noticed after running the script and correcting some MySQL parameters was, that there is a awful lot of opened connections. So the logical next step was to rewrite my application to use permanent MySQL connections. That helped a lot and the CPU load has decreased to 1 average, which is still very high for only one instance of the app running. So I started to check what else is using a lot of CPU. I noticed the counter for JOINs that are not using indexed columns and the number was extremely high and growing fast. So then I went back to all my stored procedures that the app uses and start checking all the JOINs and put an index on every field that is used in JOINs. After doing that the CPU load has decreased to the normal values and the app started to run great.
After a week or so the load increased heavily again and then it was time to give up all hopes and dreams and update MySQL on a stable 5.5.28 which finally brought order to caos and improved our query and overall MySQL database performance.
Also the new phpMyAdmin has proved it self very usefull in determing the database preformance issues. You can find it for download here.