After re-evaluated PHP opcode caching used on my web server, I go further to tune MySQL performance. I've been seeing mysqld hogging 1 of the cores on the Althon 64 X2 constantly for no good reason, that really bother me.

I started with running a MySQL benchmark test to collect a baseline. At the same time reading the on-line manual to get familiar features and area for optimization.

Then I run mysqltuner to see what need to be optimzed. After followed the recommendations, mysqld no longer hogs the CPU continuously. In fact, my web benchmark improve almost 40% (now load time ~0.260 sec). Yeah!



However, there are still work left to do. sqltuner founds that my application (which one?) is not closing connection properly, and join queries might be able to improve further. In addition, MySQL manual has some good advice on how to make better database design and performs faster query (here and here).

Furthermore, one of the tunings done is enable the build-in query cache. I think there is further improvement if I deploy a memory object cache like memcached, it minimizes the overhead of querying MySQL. However, coding change is needed. I will be looking at implement it into NucleusCMS in the future.

I've been using eaccelerator to cache PHP opcode and improve my web server performance for a while. The main reason I didn't pick other solution back then was the ease of deployment; To make upgrade and maintenance easy, I usually only use Fedora pre-packaged software. Recently, I bumped into article on APC, and found it might be a better solution. Fortunately, Fedora now included it as standard. So, I decided to give it a try.

In order to benchmark the web server performance, I wrote a simple PHP script to poll a website with CURL. It might not be the most accurate, but all I need is a baseline to measure the average load time.

After some testing, here's result:
no acceleration: ~0.550 sec
eaccelerator 32Mb cache: ~0.475 sec
eaccelerator 64Mb cache: ~0.455 sec
APC 32Mb cache: ~0.417 sec
APC 64Mb cache: ~0.413 sec

APC is also come with a control panel:



From the performance point of view, any opcode cache helps server performance as expected. APC, from the result, has a edge over eaccelerator. In addition, APC has a more informative control panel. APC also a part of PECL, which is semi-standard to PHP.

Therefore, I switched to APC.

BTW, I also use my benchmark script on Google.... its load time is a wicked 0.112 sec. I guess I still have a long way to go.