Tuning MySQL for eZ Publish

This article is based on MySQL version 4.1. At the time of writing, MySQL version 5 is available; however, eZ Publish does not require the new functionality in MySQL 5 and, being optimized for 4.1, will run slightly faster with this version.

While MySQL is bundled with most Linux distributions, we recommend that you obtain and install an optimized distribution from MySQL. Distributions are available from the MySQL download page.

Intel ICC-compiled MySQL

If you are running on Intel-based hardware, we recommend that you use the distribution of MySQL that is compiled with the Intel ICC compiler, which can be downloaded from the URL shown above. This optmizes MySQL specifically for the Intel platform, which results in a performance gain (as shown below). Our tests show up to a 20% performance increase when using the MySQL ICC-compiled version compared to the standard GCC-compiled version.

When testing MySQL compiled with the ICC compiler, we tested with eZ Publish operations that use the database heavily. We wrote 15 articles to a database containing 35.000 articles and enabled delayed search indexing. The test was run 5 times; we recorded the total time it took the script to run and also the time it took specifically for the database operations.

The results from this test show that the ICC compiler boosts the performance of database transactions up to 20.2% on the most database-intensive eZ Publish operations. This is especially significant in a clustered environment where multiple webserver nodes are using the same database server for inserts. (See the article Clustering in eZ Publish 3.8 for more information about this configuration.)

You can also compile MySQL manually with the ICC compiler. This is a more complicated process and requires an ICC compiler license (which currently costs $399 USD). Instructions on how to compile MySQL can be found in the MySQL documentation.

Hardware configuration

When running MySQL (or any other software), performance is limited by the hardware on which the software is running. Therefore, it is important that you have the best possible hardware configuration for your site. The article Server Architecture for eZ Publish Hosting contains information about hardware configuration and scalability.

Generally speaking, the faster the hardware the better the MySQL performance (obviously). However, MySQL uses server resources differently than webserver nodes. The two most important factors (assuming that the server has a reasonably recent CPU and decent CPU speed) are:

• Memory
• Disk speed

With webserver nodes, an additional CPU will almost double page-serving capacity, and increasing the CPU speed by 10% will result in a performance increase of almost 10%. However, with a MySQL server the limitations are the performance of the disk and the available memory.

If you have a read-intensive site (that is, an eZ Publish site that serves a lot of pages) you should increase memory so it can be used for caching. If you have a write-intensive site (that is, an eZ Publish site where a lot of content gets added) you should increase the hard disk speed to handle more database inserts. The rotation speed of the hard drive is especially important. Faster disk rotation is better; at the time of writing, 15.000 RPM is the fastest rotation speed available. You could also install a RAID ("Redundant Array of Independent / Inexpensive Disks") to increase disk performance. When we performed mass object insertion on an eZ Publish site (several articles published per second), we were limited by the speed of the hard drive on the database server; there were both free memory and free CPU cycles.