Tuning MySQL for eZ Publish

For an eZ Publish site, there are basically two different optimization scenarios: optimizing for either read performance or write performance. This section describes optimizing for read performance; the next section focuses on write performance.

Optimizing read performance is a matter of changing buffer sizes to make use of the memory available on the server. There are basically two different types of buffers: those dedicated to individual connections and those used globally by all connections.

Key buffer

The key buffer stores database indexes in memory. This buffer should be large enough to hold all indexes used by eZ Publish. This should be in the range of hundreds of megabytes. Sites with large amounts of data require larger key buffers. To allocate a buffer of 500MB:

key_buffer = 500M

To find a suitable value for the key buffer, investigate the status variables key_read_requests and key_reads. The key_read_requests is the total number of key requests served from the cache while the key_reads shows the number of times MySQL had to access the filesystem to fetch the keys.

The lower the number of key_reads the better. The more memory you allocate to the key buffer the more requests will be served from the cache. There will always be some keys that need to be read from disk (for example when data changes), so the value will never be zero. By comparing the two values you see the hit ratio of your key buffer. The key_read_requests should be much larger than the key_reads. 99% cached requests is a good number to aim for in a read-intensive environment.

Table cache

The table cache tells MySQL how many tables it can have open at any one time. In SQL queries, several tables are typically joined. The rule of thumb is that you should multiply the maximum number of connections (described below) by the maximum number of tables used in joins. For example, if the maximum number of connections is set to 400, the table cache should be at least 400 * 10. The configuration setting below shows a table cache of 4000:

table_cache = 4000

Sort buffers

MySQL sorts query results before they are returned. The sort buffer is per connection, so you must multiply the size of the sort buffer by the maximum number of connections to predict the server memory requirements. In our case we use a 3MB sort buffer with 400 max connections, which can use a total of 1.2GB of memory.

sort_buffer_size = 3M

Max connections

MySQL has a limitation on the number of concurrent connections it can keep open. If you are using persistent connections in PHP, each process in Apache will keep a connection to MySQL open. This means that you need to set the number of max connections in MySQL to equal or greater than the number of Apache processes that can connect to the database. In a clustered environment, you must add up the processess on each webserver to determine the maximum. Setting sufficient max connections also ensures that users do not get errors about connecting to the MySQL database. The setting for 400 connections is shown below.

max_connections = 400

Query cache

MySQL is capable of caching the results of a query. The next time the same query is executed the result is immediately returned, as it is read from the cache rather than the database. For a read-intensive site, this can provide a significant performance improvement.

To enable the query cache, set the type to "1":

query_cache_type = 1

You can set the maximim size of each query that can be cached. If the query result is larger than the query cache limit, the results will not be cached. This is normally set to 1M:

<span>query_cache_limit = 1M</span>

The amount of memory globally available for query caches is set with the query cache size setting. This should be fairly large, and should be increased in size for large databases.

<span>query_cache_size = 100M</span>

To tune the query cache, use the show status command. This can be used to determine which settings need to be altered and to see the effect of alterations. The show status command will show you if the query cache is heavily in use and if you have free memory, which indicates whether the query cache buffer settings should be increased or decreased.

mysql> show status like "qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 34       |
| Qcache_free_memory      | 16466312 |
| Qcache_hits             | 1313227  |
| Qcache_inserts          | 78096    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 3328     |
| Qcache_queries_in_cache | 140      |
| Qcache_total_blocks     | 346      |
+-------------------------+----------+
8 rows in set (0.00 sec)