Tuning MySQL for eZ Publish

DNS lag

When running MySQL in a clustered environment with several webservers accessing the database server(s), we have noticed that the time used to contact the DNS server can be significant. We found that every 100 requests or so produced a significant slowdown of approximately 5 seconds when benchmarking read performance.

After closer inspection we found that it is the MySQL database initalization in eZ Publish that takes time. Periodically there is a delay of about 5 seconds in the MySQL login. The solution is to enter DNS information in the /etc/hosts file on the database server. We add entries for all associated machines in the cluster:

192.168.0.1    giza           giza.ezcluster.ez.no
192.168.0.2    babylon        babylon.ezcluster.ez.no
192.168.0.3    olympia        olympia.ezcluster.ez.no
192.168.0.4    ephesus        ephesus.ezcluster.ez.no
192.168.0.5    rhodes         rhodes.ezcluster.ez.no
192.168.0.6    alexandria     alexandria.ezcluster.ez.no

After the file is saved, there is no longer any slowdown due to DNS requests and we get optimal speed for any number of requests.

Filesystem optmization: noatime

Mounting a filesystem with the noatime option disables the storage of information about file access. This information is normally not needed. This increases the performance of the filesystem as fewer disk writes need to be done.

We tested this method with an ext3 filesystem on SATA disks, generating 15 articles in one folder of an eZ Publish installation with 900.000 objects. We ran the test 3 times and recorded the average time:

• Before noatme: 1.333 sec MySQL queries
• After noatme: 1.262 sec MySQL queries

While this setting does not seem to generate a significant performance improvement (about a 5.3% increase in MySQL processing time), we only did the test with concurrency of 1. If you increase the number of concurrent users the improvement should be more noticeable, as there will be fewer writes to the filesystem.

Other filesystem optmizations

If you are running ReiserFS you can try applying the notail and data=writeback mount options. These options were not tested by us (because we were testing with an ext3 filesystem) but they should increase performance.

If you are running Linux kernel 2.6 or newer you can try changing the disk I/O schedulers. The deadline or cfg scheduling should provide best performance. This was not tested by us since our test environment was running on Debian Linux with version 2.4 kernel.