Wednesday, January 25, 2012

Why the phpMyAdmin Status Monitor is awesome

So today I checked out my vServer performance using the phpMyAdmin Status Monitor that I have coded last summer, because a user notified me that a certain webpage doesn't load.
I couldn't figure out the problem as the site loaded normally for me and the server wasn't overloaded, however somehow I ended up hammering on the refresh button in the server tab to check what MySQL processes were running because it was fun and interesting.


So then one process did appear that was currently sorting the results of a query. It looked like a candidate for optimization. So I threw it into the query analyzer.



My assumption turned out to be correct. This query is used to determine when the visitor last visited the website, and it took 0.3 seconds to execute. It happens to be run on every page visit on of my websites.

So, being already in phpMyAdmin, I head over to the table in question and add an index for this field.

ALTER TABLE `cc_counter` ADD INDEX ( `ip` )

Now lets run this query again:


Well that sounds much better doesn't it! From 300ms to 0.8ms. Around 375 times faster now. While we're at it, we might as well write this query correctly.


As we only require the newest visit it is enough find MAX(time) of the visitor in question. Another 40% faster, which results in a speedup of around 525 times compared to the original.

So we not only saved ~0.3 seconds of loading time per visit but also reduced the required CPU time allowing greater workload on the server.

If you are running whatever self written code on your servers or webspace, get the new phpMyAdmin 3.5, check out your database load and optimize your queries. It's very gratifying work ;-)



P.S.: I'm aware that this might be an exceptional case, since a well designed database layout would have proper indexes from the very start. Nonetheless the Status Monitor is a great tool for surgical improvements on bad code like this one. Also in projects that slowly grow over time the chances are not too low to miss out on creating an index where it would be needed.

5 comments:

  1. Cool, ant idea why clicking the tabs in the top picture here does not have any effect on a install? No errors are thrown, it just stays on the server tab. Tried with all major browswers without luck

    ReplyDelete
    Replies
    1. Do you happen to have Javascript disabled? Otherwise can you check if there's any errors in the Javascript console? (e.g. in Firefox its available at Tools->Web developer->Web console; in Chrome its Wrench->Tools->Javascript console)

      Delete
  2. I'm like, Fuck, why didn't I event know this amazing tool exist!

    ReplyDelete