Full stack prerequisite--mysql performance Tuning

For the full stack, database skills are indispensable, relational databases or NoSQL, memory-based database or partial disk storage database, object storage database or graph database ... , but the first essential skill should be MySQL. From the rise of lamp, to the advent of mariadb, and even the advent of PG, skilled MySQL skills are very useful.

MySQL database technology is also a lot of aspects, here is only the necessary performance tuning, respected performance tuning from the bottom up, mainly including the operating environment, configuration parameters, SQL performance, and system architecture design tuning.

Operational environment Tuning

This is the Linux world, MySQL running environment tuning is often done with the Linux kernel tuning. Of course, for the cloud service RDS also has a certain reference role.

Adjust the linux default IO scheduling algorithm.

The overall goal of the IO Scheduler is to keep the head moving in one direction, moving to the opposite direction, which is exactly the elevator model in real life, so the IO Scheduler is also called the Elevator (elevator), and the corresponding algorithm is called the elevator algorithm. There are several types of elevator algorithms for IO scheduling in Linux, one called as (anticipatory), one called CFQ (Complete Fairness queueing), and one called Deadline, There is also a name NoOp (No operation).

IO has a large impact on the database, the Linux default IO scheduling algorithm is CFQ, need to be modified to deadline, if the SSD or PCIE-SSD device, need to be modified to NoOp, you can use the following two ways to modify.

1, online dynamic modification, restart failure.

echo “deadline” > /sys/block/sda/queue/scheduler

2, modify the/etc/grub.conf, the permanent effect.
Modify the/etc/grub.conf configuration file to add a configuration to the kernel line, for example:
elevator=deadline

The main concern elevator this parameter, set the kernel, the need to restart the system to take effect.

disabling NUMA attributes

The NUMA of a new generation of architectures is not suitable for running databases, NUMA is for the purpose of improving memory utilization, but it may result in one CPU's memory remaining, the other is not enough, the problem of swap occurs, so it is generally recommended to close or modify NUMA scheduling.

1. Modify/etc/grub.conf to close Numa and take effect after reboot.


numa=off

2, modify the/etc/init.d/mysql or Mysqld_safe script, set the NUMA scheduling mechanism when starting the mysqld process, such as numactl –interleave=all .

Modify swappiness Settings

Swappiness is a kernel parameter of Linux that controls the strategy of swapping out physical memory. It allows a value of a percentage, the minimum is 0, the maximum is 100, and the default value of the change is 60. What is the effect of this setting value?

Vm.swappiness set to 0 means that using as little as possible swap,100 means swapping inactive memory pages to swap or releasing the cache. Inactive memory means that the program maps, but "long-time" memory is not used. We can use Vmstat to see how much inactive memory is in the system.

# vmstat -a 1

This value is recommended to set to 1, set the method as follows, add a line in the/etc/sysctl.conf file.
vm.swappiness = 1

Expand File Descriptors

This is a frequently modified parameter, and the highly concurrent program will be modified.

1, dynamic modification, restart failure, only use root, and the current session is valid.


ulimit -n 51200

2, modify the configuration file, permanent effect.
Adding in the/etc/security/limits.conf configuration file

* hard nofile 51200

* soft nofile 51200

Session-oriented process file descriptor changes are slightly different, the changes in the cloud are slightly differences, you can see the same "open too many files"

Optimizes file system mount parameters.

For file systems, it is best to use EXT4 if there is no special requirement.

The file system mount parameter is 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.

The primary purpose of barrier is to ensure the security of disk write data, but it degrades performance. If there is a battery backup power such as a BBU to ensure that the control card is not instantaneous power-down, then this function can be assured to close boldly.

Configuration parameter Tuning

Configuration parameter tuning in MY.CNF depends on the business, load, or hardware, requiring special adjustments in slow and fast disks, high concurrency, and write-intensive loads.

Basic Configuration

query_cache_size
Query cache is a well-known bottleneck, even in a few concurrent cases. It's best to deactivate at first, set query_cache_size = 0, and use other methods to speed up the query: Optimize the index, increase the copy spread load, or enable additional caches (such as memcache or Redis). If you have enabled query cache and have not found any problems, query cache may be useful. If you want to deactivate it, you have to be careful.

innodb_buffer_pool_size

The buffer pool is where the data and index caches are: the higher the value, the better, which guarantees that you will use memory instead of the hard disk for most of the read operations. Typical values are 5-6GB (8GB memory), 20-25GB (32GB memory), 100-120GB (128GB memory).

innodb_log_file_size

The redo log is used to ensure that the write operation is fast and reliable and recovers when it crashes. After MySQL 5.5, the performance of crash recovery has been greatly improved, and can have high write performance and crash recovery performance at the same time. In MySQL 5.6 can be raised to more than 4GB. If your application needs to write data frequently, you can start by turning it into 4G.

max_connections
A major drawback after the Max_connection value is set higher (for example, 1000 or higher) is that it becomes unresponsive when the server is running 1000 or higher active transactions. Using a connection pool in your application or using a process pool in MySQL can help solve this problem.

Back_log
The number of connections required for MySQL to be available. When the primary MySQL thread gets very many connection requests in a very short time, this works, and then the main thread takes some time to check the connection and start a new thread. Back_log indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. Only if you expect to have a lot of connections in a short period of time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections.

InnoDB Configuration

innodb_file_per_table

This setting tells InnoDB whether the data and indexes of all tables need to be stored in a shared tablespace (innodb_file_per_table = OFF) or a single. ibd file (innodb_file_per_table = ON) for each table's data. One file per table allows you to reclaim disk space when you drop, truncate, or rebuild tables. This is also necessary for some advanced features, such as data compression. But it doesn't bring any performance gains. In MySQL 5.6, the default value for this property is on.

Innodb_flush_log_at_trx_commit

The default value is 1, which means that the InnoDB fully supports acid characteristics. This value is most appropriate when the focus is data security, such as on a primary node. However, for a system with slow disk (read-write), it can be costly because additional fsyncs are required each time the change is flush to the redo log. A value of 0 is faster, but some data may be lost when the system crashes, so it is only available for backup nodes.

Innodb_flush_method

This configuration determines how data and logs are written to the hard disk. In general, if you have a hardware RAID controller and its standalone cache is write-back and has a battery power-down protection, you should set the configuration to O_direct; otherwise, you should make it Fdatasync (the default) in most cases. Sysbench is a great tool to help you decide on this option.

innodb_log_buffer_size

This configuration determines the cache that is allocated for transactions that have not yet been executed. But if the transaction contains a binary large object or a large text field, look at the innodb_log_waits state variable, if it is not 0, increase the innodb_log_buffer_size.

Other configurations

Log_bin

If the database server acts as a backup node for the master node, it is necessary to turn on the binary log. Even if you have only one server, this is useful if you want to do data recovery based on point-in-time. Once the binary log is created, it is permanently saved. If you do not want to run out of disk space, you can use PURGE BINARY LOGS to purge old files, or set Expire_logs_days to specify how many days the log will be automatically cleared. Logging binary logs is not cost-free, so it is recommended to turn off this option if you do not need it on a copy node of a non-primary node.

Interactive_timeout

The number of seconds the server waits for an action on an interactive connection before shutting it down. An interactive customer is defined as a customer who uses the client_interactive option for Mysql_real_connect (). The default value is 28800, and it is recommended to change to 7200.

Table_open_cache

Every time MySQL opens a table, it reads some data into the Table_open_cache cache, and when MySQL does not find the appropriate information in this cache, it will go to the disk to read. Assuming that the system has 200 concurrent connections, you need to set this parameter to 200*n (N is the number of file descriptors required for each connection), and when the Table_open_cache is set to a large size, if the system cannot handle so many file descriptors, the client fails and the connection is not made.

Max_allowed_packet

The size of the packet that is accepted, and it is safe to increase the value of the variable because additional memory is allocated only when needed. For example, MYSQLD will allocate more memory only if you issue a long query or if mysqld must return a large result row. This variable takes a smaller default value as a precaution to capture error packets between the client and server and to ensure that memory overflow is not caused by accidental use of large packets
Skip_name_resolve

When a client connects to a database server, and when DNS is slow, the connection is slow. Therefore, we recommend that you turn off the skip_name_resolve option when you start the server without DNS lookups.

SQL Statement Tuning

In the application layer, a full-table scan can be easily distinguished by the combination of the PT tool and the slow query log.

Basic principles

• Avoid full table scan
• Build an index
• Try to avoid returning large amounts of data to the client, and if the amount of data is too large, you should consider whether the demand is reasonable
• Try to avoid large transaction operations and improve system concurrency
• Before you can use a cursor-based method or a temporary table method, you should look for a set-based solution to solve the problem, and the set-based approach is generally more efficient. Avoid using cursors as much as possible, because cursors are inefficient.

Tricks

about the condition after where

• Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.
• You should try to avoid using or in the WHERE clause to join the condition, consider using union instead of
• In and not in also to use caution, for continuous values, you can use between do not use in,exists instead of in
• Try to avoid expression and function manipulation of fields in the WHERE clause

About data types

• Use numeric fields as much as possible, and if fields with numeric information are not designed as character types, this can degrade query and connection performance and increase storage overhead.

• Use Varchar/nvarchar instead of Char/nchar as much as possible, because variable-length fields have small storage space, and for queries, the search efficiency in a relatively small field is clearly higher.

• It is best not to leave the database null, and to populate the database with not NULL as much as possible. Comments, descriptions, comments and the like can be set to NULL, others, preferably do not use NULL.

• Do not use anywhere select * from t , replace "*" with a specific field list, and do not return any fields that are not available.

about temporary Tables

• Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources. For one-time events, it is best to use an export table.

• When you create a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid causing a large number of logs to increase speed, and if the amount of data is small, create a table and insert it in order to mitigate the resources of the system tables.

• If a temporary table is used, TRUNCATE TABLE first and then drop table when all temporary tables are finally explicitly deleted, which avoids longer locking of the system tables.

About Indexes

• You should first consider establishing an index on the columns involved in where and order by.
• When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.
• Indexes are not as good as they are, and while indexes can improve the efficiency of the corresponding select, they also reduce the efficiency of insert and update, as it is possible to rebuild the index at INSERT or update, depending on the situation. The number of indexes on a table should not be more than 7, if too many you should consider whether some of the indexes that are not commonly used are necessary.

Database Schema Tuning

From the bottom to the application layer, eventually to the architecture layer, but the separation of business logic is bullying. The database architecture is also dependent on the business system, which is the key to serve the business system stably and flexibly. The direction of architecture tuning is:

• Table of Distinction
• Business Sub-Library
• Master-Slave synchronization and read-write separation
• Data caching
• Master-Slave hot standby and ha dual-live
• .....

~~本文是网络资源和个人经验总结，持续更新中

Full stack prerequisite--mysql performance Tuning