Top 5 MySQL settings I usually configure in a Single Instance

My goal on this post is to give newbies in MySQL an idea on the list of some of the most important settings they need to review after completing a MySQL Server installation.

Note: The recommendation here doesn't cover tuning and optimizing a MySQL Replication.

1. innodb_buffer_pool_size

This is the first important setting DBAs should look at and configure correctly. The innodb buffer pool is where data and indexes are cached. If dedicated server, I typically allocate atleast 75% of your Physical RAM, this is to give extra memory for the OS. If your Physical RAM is greater than 128GB, you can choose to maximize up to 90% since the remaining 10% of 128GB is more than enough for the OS.

2. innodb_log_file_size
this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. I typically set value to innodb_log_file_size = 512M. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

3. innodb_flush_log_at_trx_commit

You have three options in setting this parameter: 0,1,2.

The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.

With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash.

With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.

4. innodb_file_per_table
InnoDB tables, data and indexes, is stored in a separate .ibd file. A file per table allows you to reclaim disk space when truncating, dropping or rebuilding a table. Please note that it does not provide any performance benefit.

5. innodb_flush_method
This parameter defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput. For a performance boost and better I/O throughput I have decided to change innodb_flush_method to O_DIRECT. 

Please Note: Where InnoDB data and log files are located on a SAN, it has been found that setting innodb_flush_method to O_DIRECT can degrade performance of simple SELECT statements by a factor of three.