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).