Add the line in the mysqld section of the my.cnf file.
Our recommendation will, in fact, cache transactional changes so that database modifications are flushed once per second, as opposed to a ‘per transaction’ basis; see InnoDB Startup Options and System Variables , for more information. Finally, on a dedicated MySQL server, you should set the innodb_buffer_pool_size, as large as possible, leaving approximately 40% free memory for the operating system; see below:
MySQLReport is a useful tool to evaluate the performance of MySQL. To tune MySQL, edit these values in the mysqld section of /etc/mysql/my.cnf (OS dependent) and restart mysql.
Good starting values for a reasonably sized database server of 2-4GB memory are:
table_cache = 768 (check tables opened/sec in mysqlreport)
query_cache_size = 16M (this should not be any higher due to limitations in mysql – see this post )
key_buffer = 256M
innodb_buffer_pool_size = 1024M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_autoinc_lock_mode=0 # Required for replication with MySQL 5.1 or later
max_allowed_packet = 16M
binlog_format = ‘MIXED’ # when using binary logs in replication
max_connections = 150
tmp_table_size = 64M # To allow each connection to sort tables in memory. Maximum possible is max_connections x tmp_table_size
max_heap_table_size = 64M # Set to the same as tmp_table_size
You can see the current values with mysqladmin variables.
Note: the recommendations for MySQL server variables depend on your system and what other services run on it, so you have to exercise judgment when changing your system. Make sure that you do not over-commit resources to MySQL because if it causes the server to go into swapping, this will reduce the performance of MySQL.
Note: the crashed tables check may take a while to run. More information about the innodb parameters are on the mysql documentation site.
General Hints for Performance Tuning
Check iostat -x 5. This gives I/O statistics per disk. You could have a low overall I/O wait time, but it could be due to a single disk being used 100% of the time.
For maximum I/O, you should stripe the disks so that all disks are being utilized.
You should use separate disks for data files and index files – this improves read and write times.
You should use a fast disk for redo logs.
We have also seen improvements if innodb_flush_log_at_trx_commit is set to 0, although it is possible to lose up to 1 second of data in the event of a disk failure.