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.

Tuesday, January 3, 2012

Anego CMS Modules - Report 6

Due to the holidays this report has been slightly delayed ;-)
However, I'm happy to report that the gallery module is now mostly finished and only a number of smaller features are missing. Since the last report I've completed:
- Store/Restore image size and crop area
- All the server side code
- Default sizes from which the user can select from. This includes default sizes for the original image, the preview image and the crop area.
- Pictures can be deleted.
- There's now default sizes for the original and preview image which is a per-gallery setting.
- Drag&Drop image reordering is finally working too. I've had difficulties getting jQuery UI Sortable to work, the bug being wrong event bubbling.
- Added a very simple pagination for the blog (not related to the gallery module)
- A ton of bug fixing

The gallery module is now also being tested and running the gallery on my wife's website I was happy to hear that she is very pleased with the simplicity of setting up a gallery. Below is a screenshot of an image being edited.

Until the end of January the only things left to do are:
- There is one bug left to fix with image reordering
- Classic style file upload with file selection
- If there is time left, there could be some per-gallery-batch-image-resize so one could easily change all the preview and original image sizes.
- Write documentation.