Shulou(Shulou.com)06/01 Report--

The following brings you about the performance of mysql how to optimize the content, I believe you must have read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I believe you will gain something after reading how to optimize the performance of mysql.

File system:

Optimize file system mount parameters: the file system mount parameters are modified in the / etc/fstab file and take effect when you restart. Noatime does not record the access time, and nodiratime does not record the access time of the directory. Barrier=0, which means to turn off the barrier function. Where nobarrier is specific to the xfs file system, and the ext4 file system does not have this parameter.

Expand the file descriptor:

1. Dynamic modification, restart failure, can only use root, and the current session is valid: ulimit-n 65535

2. Modify the configuration file, take effect permanently, and add the following to the / etc/security/limits.conf configuration file:

Soft nofile 65535

Soft nproc 65535

Hard nofile 65535

Hard nproc 65535

Sync_binlog:

Sync_binlog=0, when a transaction is committed, MySQL does not do disk synchronization instructions such as fsync to refresh the information in binlog_cache to disk, but let Filesystem decide when to synchronize, or synchronize to disk after the cache is full.

Sync_binlog=n, after every n transaction commits, MySQL will issue a disk synchronization instruction such as fsync to force the data in binlog_cache to be written to disk.

Innodb_flush_log_at_trx_commit:

If innodb_flush_log_at_trx_commit is set to 0 innodb_flush_log_at_trx_commit log buffer will be written to log file once a second, and the flush (brush to disk) operation of log file will occur at the same time. In this mode, writing to disk is not actively triggered when the transaction is committed.

If innodb_flush_log_at_trx_commit is set to 1, MySQL will write log buffer data to log file and flush (flush to disk) each time the transaction commits.

If innodb_flush_log_at_trx_commit is set to 2, MySQL writes log buffer data to log file. But the flush (flush to disk) operation does not happen at the same time. In this mode, MySQL performs a flush (flush to disk) operation once a second.

It is strongly recommended that you close query cache. Set query_cache_size = 0 and query_cache_type = 0 through the configuration file.

Distributed optimization

Sub-database sub-table:

It can also be divided into two categories:

The main contents are as follows: (1) the way of table splitting is realized by using the logic of the front-end application code. This is more intrusive to the application, but the process of data processing logic is in your own hands, and there are exceptions that can be located independently.

(2) it is realized by middleware, and data slicing is realized by mycat and cobar which are commonly used at present.

Read-write separation:

Generally implemented through database middleware, commonly used middleware such as maxscale, mycat, cobar, altas, etc.

For the above about how to optimize the performance of mysql, do you think it is what you want? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.