MySQL Configuration (the my. file)

my.cnf (linux)
or my.ini (windows)
/etc/mysql or /etc linux
c:/windows, just c: or mysql installation directory windows

innodb buffer_pool_instances
innodb_buffer_pool_dump_at_shutdown innodb_buffer_pool_load_at_startup

These are the settings that matter most when configuring your InnoDB/XtraDB engine.  There are a lot more available but most not worth mentioning as defaults are usually just fine.  This was all sourced directly from Muhammad Irfan’s blog post and as there would be little value in rewriting the stuff in my own words, I have just copied them here.

innodb_buffer_pool_size: InnoDB relies heavily on the buffer pool and should be set correctly, so be sure to allocate enough memory to it. Typically a good value is 70%-80% of available memory. More precisely, if you have RAM bigger than your dataset setting it bit larger should be appropriate with that keep in account of your database growth and re-adjust innodb buffer pool size accordingly. Further, there is improvement in code for InnoDB buffer scalability if you are using Percona Server 5.1 or Percona Server 5.5 You can read more about it here.

innodb_buffer_pool_instances: Multiple innodb buffer pools introduced in InnoDB 1.1 and MySQL 5.5. In MySQL 5.5 the default value for it was 1 which is changed to 8 as new default value in MySQL 5.6. Minimum innodb_buffer_pool_instances should be lie between 1 (minimum) & 64 (maximum). Enabling innodb_buffer_pool_instances is useful in highly concurrent workload as it may reduce contention of the global mutexes.

Dump/Restore Buffer Pool: This feature speed up restarts by saving and restoring the contents of the buffer pool. This feature is first introduced in Percona Server 5.5 you can read about it here. Also Vadim benchmark this feature You can read more about it in this post. Oracle MySQL also introduced it in version 5.6, To automatically dump the database at startup and shutdown set innodb_buffer_pool_dump_at_shutdown & innodb_buffer_pool_load_at_startup parameters to ON.

innodb_log_file_size: Large enough InnoDB transaction logs are crucial for good, stable write performance. But also larger log files means that recovery process will slower in case of crash. However this is not such big issue since great improvements in 5.5. Default value has been changed in MySQL 5.6 to 50 MB from 5 MB (old default), but it’s still too small size for many workloads. Also, in MySQL 5.6, if innodb_log_file_size is changed between restarts then MySQL will automatically resize the logs to match the new desired size during the startup process. Combined log file size is increased to almost 512 GB in MySQL 5.6 from 4 GB. To get the optimal logfile size please check this blog post.

innodb_log_buffer_size: Innodb writes changed data record into lt’s log buffer, which kept in memory and it saves disk I/O for large transactions as it not need to write the log of changes to disk before transaction commit. 4 MB – 8 MB is good start unless you write a lot of huge blobs.

innodb_flush_log_at_trx_commit: When innodb_flush_log_at_trx_commit is set to 1 the log buffer is flushed on every transaction commit to the log file on disk and provides maximum data integrity but it also has performance impact. Setting it to 2 means log buffer is flushed to OS file cache on every transaction commit. The implication of 2 is optimal and improve performance if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes.

innodb_thread_concurrency: With improvements to the InnoDB engine, it is recommended to allow the engine to control the concurrency by keeping it to default value (which is zero). If you see concurrency issues, you can tune this variable. A recommended value is 2 times the number of CPUs plus the number of disks. It’s dynamic variable means it can set without restarting MySQL server.

innodb_flush_method: DIRECT_IO relieves I/O pressure. Direct I/O is not cached, If it set to O_DIRECT avoids double buffering with buffer pool and filesystem cache. Given that you have hardware RAID controller and battery-backed write cache.

innodb_file_per_table: innodb_file_per_table is ON by default from MySQL 5.6. This is usually recommended as it avoids having a huge shared tablespace and as it allows you to reclaim space when you drop or truncate a table. Separate tablespace also benefits for Xtrabackup partial backup scheme.

Oh by the way…

Don’t cache in linux


vm.swappiness = 1-5

MySQL Optimization Tools my.cnf tuning


Percona Configuration Wizard

Perhaps the most commonly used my.cnf tuning tool available is MySQLTuner.  This program consists of a simple perl script.  If your server is not currently set up to run perl then you will need to do this.  It can be downloaded or in the case of Ubuntu server, installed directly using apt-get.  Make sure your server has been up and used as it typically is for at least 24 hours.  This use data is important to the script.  Upon running this simple program, a report will be generated suggesting my.cnf configuration changes.  Use this report with caution.  While it will effectively identify some problems and will take care of the capacity estimations you might normally calculate by hand, it’s advice could potentially degrade performance in some cases.  Make sure that you understand the changes you are making regardless of whether or not they have been suggested by an optimization tool.

Another interesting tool is the Percona configuration wizard.  This online tool generates a fairly optimized my.cnf file for your database based on your answers to a sequence of questions.  It could not get any easier than that 🙂