MySQL performance optimization

File system:

Optimize file System Mount parameters: File system mount parameters are modified in the/etc/fstab file, and the restart time takes effect. Noatime indicates that no access time is recorded, and Nodiratime does not log the access time of the directory. Barrier=0, which indicates that the barrier feature is turned off. Where Nobarrier is unique to the XFS file system, the Ext4 file system does not have this parameter.

To enlarge a file descriptor:

1, dynamic modification, restart failure, can only use root, and the current session valid : Ulimit-n 65535

2.Modify the configuration file for permanent entryIn/etc/security/limits.conf in the configuration file:          

Soft Nofile 65535
Soft Nproc 65535
Hard Nofile 65535
Hard Nproc 65535

Sync_binlog:

Sync_binlog=0, when the transaction commits, MySQL does not do fsync such as the disk synchronization instructions to refresh Binlog_cache information to disk, and let filesystem decide when to synchronize, or cache full after the synchronization to disk.

Sync_binlog=n, after every n transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk.

Innodb_flush_log_at_trx_commit:

If Innodb_flush_log_at_trx_commit is set to 0,log buffer, it will be written to log file once per second, and the flush (brush to disk) of the log file Operation at the same time. In this mode, the operation to write to the disk is not actively triggered when the transaction commits.
If Innodb_flush_log_at_trx_commit is set to 1, MySQL writes log buffer data to log file each time the transaction commits, and flush (swipe to disk).
If Innodb_flush_log_at_trx_commit is set to 2, MySQL writes log buffer data to log file each time the transaction commits. But the flush (brushed to disk) operation does not work at the same time. In this mode, MySQL executes the flush (swipe to disk) operation once per second.

It is strongly recommended that you close query cache. configuration file Settings query_cache_size = 0, Query_cache_type = 0.

Distributed optimization

Sub-database sub-table:

This can also be divided into 2 categories:

(1) The way of the table partition is realized by the way of the front-end application code logic. This is very intrusive to the application, but the process of data processing logic is controlled in its own hands, there are anomalies can be self-positioning.

(2) through the way of middleware, the current commonly used mycat, Cobar to achieve data fragmentation.

Read/write Separation:

Generally through the way of database middleware, commonly used middleware such as: Maxscale, Mycat, Cobar, Altas, etc.

MySQL performance optimization