MySQL5.6 Basic Optimization Configuration

With the improvement of a large number of default options, MySQL 5.6 has significantly fewer options than the previous version needs tuning. In this article I will describe the configuration items that need to be optimized.

InnoDB settings

1.innodb_buffer_pool_size  --The default value is 128M. This is the primary optimization option because it specifies how much memory InnoDB uses to load data and indexes (data+indexes). For dedicated MySQL servers, it is recommended that you specify the range of 50-80% for physical memory. For example, a machine with 64GB of physical memory, the cache pool should be set to about 50GB.  
Setting this value to a greater extent can be risky, such as not having enough free memory left to the operating system and some MySQL subsystems (subsystem) that depend on the filesystem cache. Includes binary log (binary logs), INNODB transaction log (transaction logs), and so on.

2.innodb_log_file_size  --The default value is 48M. Systems with high write throughput need to increase this value to allow background checkpoint activity to smooth writes over a longer period of time, improving performance. It is safe to set this value to 4G below. Past practice has shown that the disadvantage of a log file is that it increases the repair time required for crashes, but this has been significantly improved in 5.5 and 5.6.

3.innodb_flush_method --The default value is Fdatasync. If you use a hardware RAID disk controller, you may need to set it to O_direct. This prevents the "double buffering" effect when reading the InnoDB buffer pool, or a 2 copy (copy) is formed between the file system cache and the InnoDB cache.
If you do not use a hardware RAID controller, or if you are using SAN storage, o_direct may cause performance degradation. The MySQL user manual and Bug #54306 explain this in detail.

4.innodb_flush_neighbors --The default value is 1. It should be set to 0 (disabled) on SSD storage because there is no performance benefit with sequential IO. This setting should also be disabled on some hardware that uses raid because logically contiguous blocks are not guaranteed to be contiguous on the physical disk.

5.innodb_io_capacity and Innodb_io_capacity_max  --These settings affect how many operations are performed in the background InnoDB per second. If you have a deep understanding of hardware performance, such as how many IO operations can be performed per second, it is advisable to use these features rather than leave it idle.

There is a good analogy:  if a flight is not sold for a single ticket-then it may be a good strategy for some of the people on the flight to take the flight, in case there is a bad weather in the back. That's when you have the opportunity to handle the background operation, To reduce competition with possible real-time operations later on.

  has a very simple calculation:  if each disk can read and write (IOPS) 200 times per second, RAID10 disk array ioPS with 10 disks theoretically = (10/2) * 200 = 1000. I said it was "very simple", is because RAID controllers often provide additional consolidation and effectively improve IOPS capabilities. For SSD disks, IOPS can easily reach thousands of.

  Setting these two values too large can be risky, and you certainly don't want the background operation to interfere with the performance of the foreground task IO operations. Past experience has shown that setting these two values too high can result in performance degradation due to the internal locks held by InnoDB (as I understand the information This has been greatly improved in MySQL5.6).

innodb_lru_scan_depth -The default value is 1024. This is a new option introduced in MySQL 5.6. Mark Callaghan offers some configuration recommendations. Simply put, if you increase the innodb_io_capacity value, you should increase the innodb_lru_scan_depth at the same time.

Copy (Replication)

If the server is to support master-slave replication or restore at a point in time, in this case we need:

1.log-bin --Enables binary logging. Binary logs are not accident-safe by default (not crash safe), but as I said in previous articles, I recommend that most users should target stability. In this case, you will also need to enable: Sync_binlog=1, Sync_relay_log=1, relay-log-info-repository=table and master-info-repository=table.

2.expire-logs-days --The default old log is retained. I recommend setting it to 1-10 days. Saving for a longer time is not much use, because recovering from a backup is much faster.

3.server-id --all servers in a master-slave replication system (replication topology) must have a unique server-id set.

4.binlog_format=row --modified to row-based replication. Another line-based copy I wrote recently describes the reason I really like it because it can improve performance by reducing resource locking. There are also two additional settings that need to be enabled: transaction-isolation=read-committed and Innodb_autoinc_lock_mode = 2.

Additional configuration (MISC)

1.TIMEZONE=GMT Set the time zone to GMT. An increasing number of system administrators recommend that all servers be set to Greenwich (GMT). I personally like this, because almost all of the business is now global. Setting your local time zone seems to be a bit arbitrary.

2.character-set-server=utf8mb4 and Collation-server=utf8mb4_general_ci as described in previous articles, UTF8 encoding is a better default option for new applications. You can also set Skip-character-set-client-handshake to ignore other character sets (Character-set) that your application wants to set.

3.max_connect_errors --todd Farmer wrote: "[This feature] provides no meaningful protection against brute-force access attacks." In fact, when setting skip-name-resolve, Max_connect_errors doesn't even work (see above).

Firewalls are a more appropriate solution, usually I will block the 3306 port, whether it is a public network or intranet port, only specific applications can access and connect to MySQL.
I usually set up max_connect_errors=100000 so that I can avoid any "dual configuration" and make sure it doesn't get in the way.

4.max-connections  --is 151. I've seen a lot of users set him up quite a bit, mostly between 300 and 1000.
  Inevitably this value is set to be larger, but I'm a little bit nervous that the 16-core machine has only about 2x~10x connection execution capability in the case of Io blocking.  
  You may wish that many open connections are idle and dormant. But if they are all active, a large number of new threads (Thread-thrash) may be created.
  If conditions allow, you can configure the application configuration tuning database connection pool (connection-pools) to solve this problem instead of opening and maintaining a large number of connections;  
  Of course those that do not use connection pooling (non-pooled ), open quickly, and it's also possible to close connected applications as quickly as possible after performing a task.  
  Another solution starting from 5.5 (some differences between MySQL Community Edition and Enterprise Edition) is to use the thread pool plug-in.

Summary (conclusion)

Assume that the MySQL server is configured as:
1.64GB Physical Memory
2. Hardware RAID controller (assuming IO up to IOPS per second)
3. Master-slave replication required (Replication)
4. New applications (eg. non-legacy systems)
5 A firewall protection
6. No authorization based on domain name (hostnames, hostname) is required
7. Globalization applications, and do not want to be fixed in a time zone.
8. Want the program to be reliable and stable (durable).

The configuration might look like the following:

Copy the Code code as follows:

# InnoDB Settings
innodb_buffer_pool_size=50g
innodb_log_file_size=2g
Innodb_flush_method=o_direct
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000

# Binary Log/replication
Log-bin
Sync_binlog=1
Sync_relay_log=1
Relay-log-info-repository=table
Master-info-repository=table
expire_logs_days=10
Binlog_format=row
transaction-isolation=read-committed
Innodb_autoinc_lock_mode = 2

# Other
Timezone=gmt
Character-set-server=utf8
Collation-server=utf8_general_ci
max-connect-errors=100000
max-connections=500

# Unique to this machine
Server-id=123

MySQL5.6 Basic Optimization Configuration