Optimizing for write performance

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

Based on the test case described in the section "Intel ICC-compiled MySQL", the table below shows the changes in the test execution time when various configuration settings were applied. Each test was run 5 times and the averate execution time is shown. We started with the default MySQL configuration and one by one applied the settings shown below. Finally, we compared the end result with the GCC version of MySQL with the same settings.

Configuration
Execution time

Default configuration
9.18 sec

innodb_flush_log_at_trx_commit = 0
8.45 sec

innodb_buffer_pool_size = 700M
7.63 sec

innodb_additional_mem_pool_size=50M
6.54 sec

key_buffer = 1000M
6.59 sec

innodb_log_file_size = 175
6.50 sec

innodb_log_buffer_size=8M
6.53 sec

GCC version of MySQL
7.70 sec


With the above settings, the performance improved from 9.18 seconds to 6.50 seconds, an increase of 2.68 seconds (29%). Therefore we can see that these optimization settings are good for write-intensive operations in MySQL.

InnoDB transactions
When you are running eZ Publish in an environment where you want to use transactions, you must use the InnoDB storage engine in MySQL. The MyISAM storage engine is the default, but does not support transactions. (A "transaction" means that you can group a number of queries to the database and revert all of them if one fails, thus ensuring the integrity of the data.) InnoDB is more efficient than MyISAM when you have a write-intensive environment. Pure read operations are slower than with MyISAM, but when you have combinations of reads and writes InnoDB performs well.

MySQL has recently added a new storage engine type (called Falcon) that is supposed to be better than InnoDB for transaction-based web applications. We have not tested this engine and therefore cannot confirm the performance enhancement.

Disable flush transaction on commit
When using InnoDB, by default MySQL flushes data to disk when transactions are commited. This means that each transaction is flushed to disk when it occurs. This provides data security in case the database server crashes.

The default behaviour can be overridden with the following setting:

innodb_flush_log_at_trx_commit = 0
This setting makes MySQL flush the transaction cache every second instead of after each commit. This means transactions are not flushed to disk the moment they happen. While this improves performance, you must decide whether the risk of losing data due to a server crash is acceptable.

InnoDB buffer pool size
The InnoDB buffer pool caches table data and indexes. The larger the size of the buffer pool, the more data can be cached and the less disk I/O used. The InnoDB memory buffer pool in MySQL is by default quite low and should be made as large as 70% of the available memory. ("Available memory" means the memory not used by any other application or by another buffer in MySQL.) We increase this to 700MB to increase performance.

innodb_buffer_pool_size = 700M
InnoDB additional mem pool size
The InnoDB additional mem pool is the buffer used to store internal data structures. The more tables in the database, the more memory is required. If the additional mem pool size is not large enough to store data about the InnoDB tables, MySQL will use system memory and will write warnings to the error log.

innodb_additional_mem_pool_size = 50M
Key buffer
The key buffer is a memory cache of the indexes in a MySQL database. A large key buffer means that more indexes fit in memory and thus there is a faster execution of queries using indexes. We increase this to 500MB; the default is 16MB.

key_buffer = 500M
Log buffer size
The log buffer stores the transactions in memory before they are flushed to disk. By making the log buffer size larger, MySQL can wait longer before flushing the transaction log to disk and therefore use less disk I/O. The size recommended by MySQL is between 1MB and 8MB. We used 8MB for our test, which actually made MySQL a bit slower compared to the 1MB default. Therefore, we recommend somewhere in between, for example 4MB.

innodb_log_buffer_size = 4M
« Optimizing for read performance DNS and filesystem optimizations »


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

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.