Optimizing for read performance

Optimizing for read performance
Bård Farstad
Friday 11 August 2006 11:33:00 pm

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:

query_cache_limit = 1M
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.

query_cache_size = 100M
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)
« MySQL configuration Optimizing for write performance »


--------------------------------------------------------------------------------

Digg! Del.icio.us Newsvine Reddit Technorati Furl


--------------------------------------------------------------------------------

Comments
data=writeback
eric smith
Wednesday 18 June 2008 8:53:50 am

You don't need to use Proxypass if you have multiple IPs ofcourse. ProxyPass however should work fine with eZ publish, atleast I remember it did in my test environment. However I only checked with different vhost names for the instances.
Article discussion on sdk-public@lists.ez.no
Jennifer Zickerman
Friday 02 February 2007 1:35:22 pm

This article is being discussed on the sdk-public@lists.ez.no mailing list: http://lists.ez.no/pipermail/sdk-public/2007-January/002550.html
data=writeback
Nils Meyer
Thursday 24 August 2006 4:57:10 pm

This option exists for the ext3 filesystem, i don't know wether it also exists for reiserfs.

From the kernel doc:
data=journal All data are committed into the journal prior
to being written into the main file system.

data=ordered (*) All data are forced directly out to the main file
system prior to its metadata being committed to
the journal.

data=writeback Data ordering is not preserved, data may be
written into the main file system after its
metadata has been committed to the journal.