MySQL benchmark with SysBench (II)

Let's carry on the preceding article about MySQL benchmark with SysBench by putting into action the above scenario.

For the first case, I am going to use the values recommended through the first four articles that I wrote about MySQL optimization. So according to them, the values used will be as follows. Let's call this first settings A.

root@ubuntu-server:~# vim /etc/mysql/my.cnf
...
key_buffer              = 32M
thread_cache_size       = 512
table_cache             = 512
table_definition_cache  = 512
open_files_limit        = 1024
tmp_table_size          = 64M
max_heap_table_size     = 32M
query_cache_limit       = 4M
query_cache_size        = 256M
query_cache_type        = 1
innodb_buffer_pool_size = 1280M

For the second configuration, called B, I am going to set by means of the parameter innodb-flush-log-at-trx-commit, when the log buffer will be written out to the log file and the flush to disk operation will be effected.

root@ubuntu-server:~# vim /etc/mysql/my.cnf
...
innodb-flush-log-at-trx-commit = 0

For the third combination (C), a new parameter will be added: innodb_buffer_pool_instances. With this option, it is possible to define the number of regions that the InnoDB area will be divided into.

root@ubuntu-server:~# vim /etc/mysql/my.cnf
...
innodb_buffer_pool_instances = 2

And finally, the size for the log file will be increased by means of innodb_log_file_size, and in this way, the graph D will be turned out (in order to be able to apply this change, first up you have to stop MySQL and remove any existing log file).

root@ubuntu-server:~# vim /etc/mysql/my.cnf
...
innodb_log_file_size = 256M

root@ubuntu-server:~# rm -rf /var/lib/mysql/ib_logfile*

Below you can observe the graphs generated for the different cases.

As you can appreciate in the figure, the best improvements are achieved when the size of the log file is increased from its default value (5M) to 256M. The option C, that is, to separate the InnoDB buffer into two regions gets worse in respect of the previous alternative (B).