Optimization Philosophy

• Why optimize?
  From the perspective of business requirements, it has been proved that the optimization results are often opposite to the expected values. Optimization is risky, and you need to be careful!

• Risk of optimization?

  1. Optimization is not always for a simple environment, but also a complex system that has been put into production;
  2. Optimization means have great risks, but they can't realize or foresee them;
  3. Any technology can solve a problem, but there must be a risk of bringing a problem;
  4. For optimization, it is fruitful to control the problems brought by solving the problems within an acceptable range;
  5. Maintaining the status quo or worse is an optimization failure!
  6. Stability and business sustainability are often more important than performance!
  7. Optimization inevitably involves change, and change has risks!
  8. Optimization makes performance better, maintained and worse. It is an equal probability event!
  9. The database administrator should not only take the risk of optimization, but everyone should share the optimization results!
  10. So optimization is driven by business needs!

• Who participates in optimization?

  1. Database Administrator
  2. Business unit representative
  3. Application Architect
  4. Application Designer
  5. Application Developer
  6. Hardware and system administrator
  7. Storage administrator
     

• Optimization direction?

  1. Security optimization → business continuity
  2. Performance optimization → business efficiency

• Scope and idea of optimization?
  Decoupling & slicing & self healing

  1. Storage, host, and operating system:
          Host architecture stability
          I/O planning and configuration
         Swap
          OS kernel parameters
          Network problems
  2. Application: (Index, lock, session)
          Application stability and performance
          SQL statement performance
          Serial access resource
          Poor performance session management
  3. Database optimization: (memory, database design, parameters)
          Memory
          Database structure (physical & logical)
          Instance configuration
     

Optimization tools

Operating system layer

top - 13:37:57 up 3 min,  1 user,  load average: 0.52, 0.74, 0.33Tasks: 160 total,   2 running, 158 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.7 sy,  0.0 ni, 99.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 stKiB Mem :  2895196 total,  1848656 free,   615172 used,   431368 buff/cache
KiB Swap:  2097148 total,  2097148 free,        0 used.  2115012 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                   710 root      20   0  295376   5200   3960 S  0.3  0.2   0:00.32 vmtoolsd                                               
  8679 mysql     20   0 1123976 182680  11168 S  0.3  6.3   0:01.75 mysqld                                                   8725 root      20   0  162100   2268   1580 R  0.3  0.1   0:00.12 top                                                    
     1 root      20   0  125772   4196   2604 S  0.0  0.1   0:01.68 systemd                                                     2 root      20   0       0      0      0 S  0.0  0.0   0:00.00 kthreadd                                               
     3 root      20   0       0      0      0 S  0.0  0.0   0:00.00 kworker/0:0     
...             

• CPU

  1. %Cpu(s): there may be CPUs. One CPU accounts for 100% at most and two CPUs account for 200% at most
  2. us: percentage of CPU time occupied by user programs (the higher the percentage, the better. Try to control it at 90%)
  3. sy: percentage of CPU time occupied by system programs (reason for high sys: virus in the system, lock problem)
  4. ni: percentage of CPU time occupied by user programs with changed priority
  5. id: CPU idle percentage
  6. wa: percentage of CPU time waiting for Io. If it exceeds 10%, it indicates a problem (reasons for high wa: lock, IO[raid, excessive striping], index)
  7. hi: percentage of CPU time occupied by hard interrupt
  8. si: percentage of CPU time occupied by soft interrupt

  Multi CPU monitoring: mainly judge whether the multi-core CPU is fully utilized. For example, a single CPU is busy and other CPU is idle. For MySQL, it may be caused by unreasonable concurrency parameters.

• MEM

  1. Total: total memory size
  2. Free: free memory size
  3. Used: used memory size
  4. Buffer / cache: buffer and cache (write buffer, read cache)

• SWAP

  1. Total: total swap partition size
  2. Free: free swap partition size
  3. Used: used swap partition size
  4. Available MEM: available memory size (combined with tmpfs)

  Swap is used when the remaining memory is 30%, that is, swap is used when the memory is 70%.

  [root@db01 ~]# cat /proc/sys/vm/swappiness 
  30
  

  For MySQL, it is recommended to set swap to 0 (that is, swap will not be used until memory is used up), because using swap will affect IO performance.

  # Temporary modification
  [root@db01 ~]# echo 0 >/proc/sys/vm/swappiness
  # Permanent modification
  [root@db01 ~]# vim /etc/sysctl.conf
  vm.swappiness=0
  [root@db01 ~]# sysctl -p 
  

• IO

  1. High IO and high CPU us are normal phenomena;
  2. CPU us is high and IO is low. MySQL does not add, delete, modify or query. It may be stored procedures, functions, sorting, grouping and multi table connection;
  3. CPU wa and sys are high, IO is low: IO has a problem (there is a high probability of too much lock waiting);
  4. IOPS: the maximum number of IOS that can occur on the disk per second (the factory set value of the hard disk). Because frequent small transactions lead to high IOPS and reach the threshold, new IO cannot be generated if the IO throughput does not exceed the maximum IO throughput → there is a problem with the storage planning.

Database instance layer

• show status: View database status
• show variables: view system variables
• show index: view the index
• show processlist: View connection threads
• show slave status: view the master-slave replication status
• show engine innodb status: view the storage engine status
• desc /explain: view the execution plan
• slowlog: tool log

Extended class depth optimization

• information_schema
• performance_schema
• sys
• pt series tools
• mysqlslap
• sysbench

Optimization ideas

Hardware optimization

• host

  1. Real hardware (PC Server): DELL R series, Huawei, Inspur, HP, Lenovo
  2. Cloud products: ECS, RDS, DRDS
  3. IBM minicomputers: P6, 570, 595, P7, 720, 750, 780, P8 (for Oracle)

• CPU

  1. IO intensive: online system. OLTP is mainly IO intensive business with high concurrency
  2. IO intensive: E series (Xeon), with relatively low dominant frequency and large number of cores
  3. CPU intensive: data analysis, data processing, OLAP, CPU intensive, requiring high CPU computing power (I series, IBM power Series)
  4. CPU intensive: I series, with high dominant frequency and few cores

• Memory: 2-3 times the number of CPU cores (ECC) is recommended

• Disk: SATA-III, SAS, Fc, SSD (SATA), pci-e, SSD, Flash
              Note: the BBU(Battery Backup Unit) of the host RAID card is turned off

• Storage: select different storage devices and configure reasonable RAID levels (RAID5, RAID10 and hot spare) according to different types of stored data

  1. RAID 0: striped, high performance
  2. RAID 1: mirroring, security
  3. RAID 5: verification + striping, high security + high performance (read), low write performance (suitable for more reads and less writes)
  4. RAID 10: high security + performance, at least four disks, wasting half of the space (high IO requirements)

• network

  1. Hardware bought (single card, single port)
  2. Network card binding, switch stacking

system optimization

• SWAP adjustment

  # Temporary modification
  [root@db01 ~]# echo 0 >/proc/sys/vm/swappiness
  # Permanent modification
  [root@db01 ~]# vim /etc/sysctl.conf
  vm.swappiness=0
  [root@db01 ~]# sysctl -p 
  

  The swappness parameter determines whether Linux prefers to use swap or release the file system cache. In the case of tight memory, the lower the swappiness value, the more likely it is to free the file system cache. Of course, this parameter can only reduce the probability of using swap, and can not prevent Linux from using swap.

  Modify MySQL configuration parameter innodb_flush_method, open O_DIRECT mode. In this case, InnoDB's buffer pool will directly bypass the file system cache to access the disk, but the redo log will still use the file system cache. It should be noted that redo log is in overwrite mode, and it will not take up too much even if the cache of the file system is used.

• IO scheduling policy

  # centos 7 defaults to deadline
  [root@db01 ~]# cat /sys/block/sda/queue/scheduler
  noop [deadline] cfq 
  

  CentOS 6 is modified to deadline

  # Temporarily modified to deadline(centos6)
  [root@db01 ~]# echo deadline >/sys/block/sda/queue/scheduler 
  # Permanently changed to deadline(centos6)
  [root@db01 ~]# vi /boot/grub/grub.conf
  kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
  

  IO: plan all the following problems in advance to reduce the difficulty of MySQL optimization

  1. RAID
  2. NO LVM
  3. EXT4 or XFS
  4. SSD
  5. IO scheduling policy

Application optimization

1. Development process specification, standard
2. Avoid business logic errors and lock contention
3. Reduce bad SQL (no index, complex logic, cutting large transactions)

MySQL parameter optimization

MySQL parameter optimization details

Max_connections √

• brief introduction
  The maximum number of connections of MySQL. If the server has a large number of concurrent requests, you can increase this value. Of course, this is based on the fact that the machine can support it. Because if there are more and more connections, MySQL will provide a buffer for each connection, which will cost more memory. Therefore, you need to adjust this value appropriately, and you can't increase the setting value casually.

• see

  # View maximum connections
  mysql> show variables like 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 151   |
  +-----------------+-------+
  1 row in set (0.00 sec)
  
  # Maximum number of connections since startup
  mysql> show status like 'Max_used_connections';
  +----------------------+-------+
  | Variable_name        | Value |
  +----------------------+-------+
  | Max_used_connections | 3     |
  +----------------------+-------+
  1 row in set (0.04 sec)
  

• judge

  1. When you open the database, you can temporarily set a large test value;
  2. Observe show status like 'Max'_ used_ connections’; Change;
  3. If max_used_connections and Max_ If the connections are the same, then it is max_ The connections setting is too low or exceeds the load limit of the server. If it is less than 10%, it is max_connections setting is too large.

• to configure

  # Temporary modification
  mysql> set GLOBAL max_connections=1024;
  
  # Permanent modification
  [root@db01 ~]# vim /etc/my.cnf 
  Max_connections=1024
  

back_log

• brief introduction
  The number of connections that mysql can temporarily store. When the main mysql thread gets a lot of connection requests in a very short time, it will work. If the mysql connection data reaches max_ During connections, new requests will be stored in the stack, waiting for a connection to release resources, the number of pushed stacks and the back_log, if the number of waiting connections exceeds back_log, will not be granted connection resources.
  back_ The log value indicates how many requests can be stored in the stack in a short time before mysql temporarily stops answering new requests. It needs to be added only when many connections are expected in a short time.
• judge

  mysql> show full processlist;
  

  When you find a large number of processes to be connected, you need to increase the back_log or increase Max_ The value of connections.
• to configure

  [root@db01 ~]# vim /etc/my.cnf 
  back_log=1024
  

wait_timeout & interactive_timeout

• brief introduction

  1. wait_timeout: the number of seconds mysql has to wait before closing a non interactive connection

     mysql> show variables like 'wait_timeout';
     +---------------+-------+
     | Variable_name | Value |
     +---------------+-------+
     | wait_timeout  | 28800 |
     +---------------+-------+
     1 row in set (0.26 sec)
     

  2. interactive_timeout: refers to the number of seconds mysql needs to wait before closing an interactive connection

     mysql> show variables like 'interactive_timeout';
     +---------------------+-------+
     | Variable_name       | Value |
     +---------------------+-------+
     | interactive_timeout | 28800 |
     +---------------------+-------+
     1 row in set (0.02 sec)
     

  For example, mysql management on the terminal uses instant interactive connection. At this time, if there is no operation, the time exceeds interactive_ The time set by time will be automatically disconnected (the default is 28800, which can be tuned to 7200).

• proposal
  wait_timeout: if the setting is too small, the connection will close quickly, so that some persistent connections will not work. If the setting is too large, it will easily cause the connection to open for too long. You can see a lot of connections when you show processlist. Generally, you want to wait_timeout as low as possible.

  Usually wait_ The timeout setting is small and interactive_timeout should communicate with application developers whether there are many long connection applications (long connection applications: in order not to recycle and allocate resources repeatedly and reduce additional overhead). If necessary, this value can not be adjusted. In addition, parameters outside the class can be used to compensate.

• to configure

  wait_timeout=60
  interactive_timeout=1200
  

key_buffer_size √

• brief introduction
  key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially index reading.

  mysql> show variables like "key_buffer_size%";
  +-----------------+---------+
  | Variable_name   | Value   |
  +-----------------+---------+
  | key_buffer_size | 8388608 |
  +-----------------+---------+
  1 row in set (0.58 sec)
  

  1. This parameter is related to the index of MyISAM table;
  2. This parameter is related to the creation of temporary tables (multi table link, subquery, union). Temporary tables can be created in two ways:
     1. In memory → key_buffer_size
     2. Hard disk → ibdata1 (MySQL 5.6) ibtmp1 (MySQL 5.7)

• judge
  Pass key_read_requests and keys_ Reads can determine the key_ baffer_ Whether the size setting is reasonable.

  # There are 10 index reading requests in total, and 5 requests directly read the index from the hard disk if they are not found in the memory.	
  mysql> show status like "key_read%";
  +-------------------+-------+
  | Variable_name     | Value |
  +-------------------+-------+
  | Key_read_requests | 10    |
  | Key_reads         | 5     |
  +-------------------+-------+
  2 rows in set (1.62 sec)
  

  Note: key_buffer_size only works for the myisam table. Even if the myisam table is not used, but the internal temporary disk table is the myisam table, this value should also be used.
  You can use the check status value created_tmp_disk_tables learned that:

  mysql> show status like "created_tmp%";
  +-------------------------+-------+
  | Variable_name           | Value |
  +-------------------------+-------+
  | Created_tmp_disk_tables | 0     |   # Number of temporary tables created on disk
  | Created_tmp_files       | 6     |   # Temporary file (temporarily ignored)
  | Created_tmp_tables      | 1     |   # Number of temporary tables created in memory
  +-------------------------+-------+
  3 rows in set (0.10 sec)
  

  Usually used to

  Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) memory creation ratio

  Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) disk creation ratio (controlled within 5% - 10%)

  Or calculate the difference in each period to judge the memory based temporary table utilization. Therefore, we will pay more attention to created_ tmp_ disk_ Whether there are too many tables, so as to determine the health status of the current server.

Note: temporary tables will be used when mysqldump.

• to configure

  key_buffer_size=64M
  

query_cache_size

• brief introduction
  Query cache is called QC for short. Using query buffer, mysql stores the query results in the buffer. In the future, for the same select statement (case sensitive), the results will be read directly from the buffer.
       MySQL 5.7 turns off this parameter by default;
       MySQL 8.0 discards this parameter.

  Storage method:

  select * from t1 where id=10;

  1. Set select * from t1 where id=10; Perform hash operation to calculate a string of hash values, which is called SQL_ID；
  2. Store the contents of the table returned by the storage engine + SQLID into the query cache

  Usage:

  1. When an SQL is executed, perform hash operation to obtain SQLID and find query cache;
  2. If there is in the cache, the data row will be returned directly. If not, the original SQL execution process will be followed.

  If an sql query starts with select, the mysql server will try to use the query cache for it.
  Note: as long as the difference between two sql statements is even one character (the case of columns is different; one more space, etc.), the two sql statements will use a different cache.

• judge

  mysql> show status like "%Qcache%";
  +-------------------------+---------+
  | Variable_name           | Value   |
  +-------------------------+---------+
  | Qcache_free_blocks      | 1       |
  | Qcache_free_memory      | 1031832 |
  | Qcache_hits             | 0       |
  | Qcache_inserts          | 0       |
  | Qcache_lowmem_prunes    | 0       |
  | Qcache_not_cached       | 9       |
  | Qcache_queries_in_cache | 0       |
  | Qcache_total_blocks     | 1       |
  +-------------------------+---------+
  8 rows in set (0.02 sec)
  

  1. Qcache_free_blocks: the number of adjacent memory blocks in the cache. If the value is large, it indicates that there are too many memory fragments in the Query Cache. FLUSH QUERY CACHE will defragment the fragments in the cache to get a free block.
     Note: when a table is updated, its associated cache blocks will be free. However, the block may still exist in the queue, except at the end of the queue. You can use the FLUSH QUERY CACHE statement to empty free blocks

  2. Qcache_free_memory: the current remaining memory size in the Query Cache. Through this parameter, you can accurately observe whether the Query Cache memory size in the current system is sufficient and whether it needs to be increased or decreased.

  3. Qcache_hits: indicates the number of hits to the cache. We can mainly use this value to verify the effect of our query cache. The larger the number, the better the cache effect.

  4. Qcache_inserts: indicates the number of misses and then inserts, which means that the new SQL request is not found in the cache and has to execute query processing. After query processing, insert the result into the query cache. The more times this happens, the less the query cache is applied, and the effect is not ideal. Of course, after the system is started, the query cache is empty, which is normal.

  5. Qcache_ lowmem_ Prunes: how many queries are cleared out of QueryCache due to insufficient memory. Through the combination of "Qcache_lowmem_prunes" and "Qcache_free_memory", we can more clearly understand whether the memory size of Query Cache in our system is really enough and whether Query is replaced frequently due to insufficient memory. This figure is best seen over a long period of time; If this number is growing, it means that the fragmentation may be very serious or there may be little memory. (the above free_blocks and free_memory can tell which situation it belongs to)

  6. Qcache_not_cached: the number of queries that are not suitable for caching, usually because these queries are not SELECT statements or use functions such as now().

  7. Qcache_queries_in_cache: the number of queries cached in the current Query Cache;

  8. Qcache_total_blocks: the number of block s in the current Query Cache;.
     Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
                      90/        10000                         0                            90

  If the hits ratio is too low, you can turn off the query cache and use redis to cache the database.

  1. Qcache_free_blocks to judge fragments
  2. Qcache_free_memory + Qcache_lowmem_prunes to determine memory
  3. Qcache_ How many hits hit the Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)

• to configure

  mysql> show variables like '%query_cache%' ;
  +------------------------------+---------+
  | Variable_name                | Value   |
  +------------------------------+---------+
  | have_query_cache             | YES     |
  | query_cache_limit            | 1048576 |
  | query_cache_min_res_unit     | 4096    |
  | query_cache_size             | 1048576 |
  | query_cache_type             | OFF     |
  | query_cache_wlock_invalidate | OFF     |
  +------------------------------+---------+
  6 rows in set (0.00 sec)
  

  Configuration Description: query_cache_type off means no queries are cached

  Field interpretation:

  1. query_cache_limit: queries larger than this size will not be cached
  2. query_cache_min_res_unit: the minimum size of the cache block, query_ cache_ min_ res_ The configuration of unit is a "double-edged sword". The default is 4KB. A large setting value is good for big data queries, but if your queries are small data queries, it is easy to cause memory fragmentation and waste.
  3. query_cache_size: query cache size (Note: the minimum unit of QC storage is 1024byte, so if you set a value that is not a multiple of 1024, this value will be rounded to the value closest to the current value equal to the multiple of 1024.)
  4. query_cache_type: cache type, which determines what kind of query to cache. Note that this value cannot be set arbitrarily, but must be set to a number. The optional items and descriptions are as follows:
     If it is set to 0, the cache is useless at all, which is equivalent to disabling;
     If set to 1, all results will be cached unless the select statement uses SQL_NO_CACHE disables query caching;
     If it is set to 2, it is only cached in the select statement through SQL_CACHE specifies the query to cache.

  Modify / etc/my.cnf

  query_cache_size=128M
  query_cache_type=1
  

max_connect_errors

• brief introduction
  max_connect_errors is a security related counter value in mysql. It is responsible for preventing too many failed clients from brutally cracking passwords. When the specified number of times is exceeded, the mysql server will prohibit the connection request of the host until the mysql server is restarted or the relevant information of the host is cleared through the flush hosts command. max_ connect_ The value of errors has little to do with performance.
• to configure

  max_connect_errors=2000
  

sort_buffer_size

• brief introduction
  Sort is assigned to each thread that needs to be sorted_ buffer_ Size sets the size of a buffer (consuming memory).

  1. ORDER BY
  2. GROUP BY
  3. distinct
  4. union

• judge
  Sort_ Buffer_ The larger the size, the better. Because it is a connection level parameter, too large setting + high concurrency may exhaust the system memory resources.

• to configure

  sort_buffer_size=1M
  

max_allowed_packet √

• brief introduction
  mysql Max according to the configuration file_ allowed_ Packet limits the packet size accepted by the server.
• judge
  Sometimes large inserts and updates are affected by max_ allowed_ The packet parameter is limited, resulting in write or update failure. The larger value is 1GB. A multiple of 1024 must be set.
• to configure

  max_allowed_packet=32M
  

join_buffer_size

• brief introduction
  The size of the cache used for inter table Association, and sort_ buffer_ Like size, the allocated memory corresponding to this parameter is also exclusive to each connection.
• proposal
  Optimize SQL as much as possible, and the effect is obvious. Optimization method: add an index to the on condition column. At least there should be a MUL index.

thread_cache_size √

• brief introduction
  The number of server thread caches. This value indicates the number of threads saved in the cache that can be reused. When the connection is disconnected, the client's thread will be put into the cache to respond to the next client instead of being destroyed (provided that the maximum number of caches is not reached). If the thread is re requested, the request will be read from the cache. If the cache is empty or a new request, the thread will be re created. If there are many new threads, increasing this value can improve the system performance.

• judge
  By comparing Connections and threads_ You can see the function of the created state variable.
  Setting rules: 1GB memory is configured as 8, 2GB memory is configured as 16, 3GB memory is configured as 32, 4GB or higher, and a larger number of threads can be configured.

  mysql> show status like 'threads_%';
  +-------------------+-------+
  | Variable_name     | Value |
  +-------------------+-------+
  | Threads_cached    | 1     |
  | Threads_connected | 2     |
  | Threads_created   | 3     |
  | Threads_running   | 2     |
  +-------------------+-------+
  4 rows in set (0.27 sec)
  

  1. Threads_cached: how many free threads are in the thread cache at this moment.
  2. Threads_connected: the number of currently established connections (because a connection requires one thread, it can also be regarded as the number of threads currently used).
  3. Threads_created: the number of threads created since the last service startup. If threads are found_ If the created value is too large, it indicates that the MySQL server has been creating threads, which also consumes CPU sys resources. You can appropriately increase the thread in the configuration file_ cache_ Size value.
  4. Threads_running: the number of currently active (non sleep) threads. It does not represent the number of threads in use. Sometimes the connection has been established, but the connection is in sleep state.

• to configure

  thread_cache_size=32
  

• summary

  1. Threads_created: generally, in the architecture design stage, set a test value for stress testing.
  2. Combined with zabbix monitoring, monitor the change of this state over a period of time.
  3. If within a period of time, Threads_created tends to be stable, indicating that the corresponding parameter setting is OK.
  4. If there is a steep growth or a large number of peaks, continue to increase the size of this value when the system resources are sufficient (memory).

innodb_buffer_pool_size √

• brief introduction
  For the InnoDB table, innodb_buffer_pool_size is equivalent to key_buffer_size works the same for the MyISAM table.

• judge
  InnoDB uses the memory size specified by this parameter to buffer data and indexes. For a separate MySQL database server, you can set the maximum value to 80% of the physical memory (generally, it is recommended not to exceed 70% of the physical memory).

• to configure

  innodb_buffer_pool_size=2048M
  

innodb_flush_log_at_trx_commit √

• brief introduction
  It mainly controls the time point when innodb writes the data in the log buffer to the log file and flush the disk. The values are 0, 1 and 2 respectively.
  1. 0 means that when the transaction is committed, the log is not written, but the data in the log buffer is written to the log file and flush ed to the disk every second;
  2. 1. Each transaction submission will cause redo log file writing and flush disk operations to ensure the ACID of the transaction;
  3. 2. Each transaction commit causes the action of writing to the log file, but the flush disk operation is completed every second.
• judge
  The actual test shows that this value has a great impact on the speed of inserting data. When it is set to 2, it takes only 2 seconds to insert 10000 records, when it is set to 0, it takes only 1 second, and when it is set to 1, it takes 229 seconds. Therefore, the MySQL manual also recommends merging the insert operation into one transaction as much as possible, which can greatly improve the speed.
  According to the official MySQL documentation, this value can be set to 0 or 2 on the premise of allowing the risk of losing some recent transactions.
• to configure
  One of the double 1 standards

  innodb_flush_log_at_trx_commit=1
  

innodb_thread_concurrency

• brief introduction
  This parameter is used to set the concurrent number of innodb threads. The default value is 0, which means there is no limit.

• judge
  Official recommendations:

  1. If the number of concurrent user threads in a workload is less than 64, it is recommended to set innodb_thread_concurrency=0；
  2. If the workload is always serious or even reaches the peak occasionally, it is recommended to set InnoDB first_ thread_ Concurrency = 128, and continuously reduce this parameter, 96, 80, 64, etc., until the number of threads that can provide the best performance is found;
  3. If you do not want InnoDB to use more virtual CPUs than user threads (such as 20 virtual CPUs), it is recommended to set InnoDB_ thread_ The concurrency parameter is this value (or lower, depending on the performance);
  4. If the goal is to isolate MySQL from other applications, consider binding the mysqld process to a proprietary virtual CPU. However, it should be noted that this binding may lead to non optimal hardware utilization when the myslqd process is not very busy. In this case, you may set the virtual CPU bound to the mysqld process to allow other applications to use part or all of the virtual CPU.
  5. In some cases, the optimal InnoDB_ thread_ The concurrency parameter can be set smaller than the number of virtual CPU s.
  6. Regularly detect and analyze the system. Changes in load, number of users or working environment may need to be made to InnoDB_ thread_ Adjust the setting of the concurrency parameter.

  Set standard:

  1. The cpu usage of the current system is uneven (top)
  2. Has the current number of connections reached its peak
     show status like 'threads_%';
     show processlist;

• to configure

  innodb_thread_concurrency=8
  

• method

  1. Check the top status and observe the load of each cpu;
  2. In case of unevenness, first set the parameter to the number of CPUs, and then increase (double);
  3. Observe the top state until it is relatively uniform.

innodb_log_buffer_size √

• brief introduction
  This parameter determines the amount of memory used by the log file, in M. Larger buffers can improve performance, and for larger transactions, you can increase the cache size.
• judge
  1. Big business
  2. Multi transaction
• to configure

  innodb_log_buffer_size=128M
  

innodb_log_file_size √

• brief introduction
  This parameter determines the size of the data log file in M. a larger setting can improve performance.
• to configure

  innodb_log_file_size=100M
  

innodb_log_files_in_group √

• brief introduction
  To improve performance, MySQL can write log files to multiple files in a circular manner. Recommended setting is 3.
• to configure

  innodb_log_files_in_group=3
  

read_buffer_size

• brief introduction
  MySql read in buffer size. Requests for sequential scanning of tables will allocate a read in buffer, and MySql will allocate a memory buffer for it. If the sequential scan request for the table is very frequent and you think the frequent scan is too slow, you can improve its performance by increasing the value of the variable and the memory buffer size. And sort_ buffer_ Like size, the allocated memory corresponding to this parameter is also exclusive to each connection.
• to configure

  read_buffer_size=1M
  

read_rnd_buffer_size

• brief introduction
  Size of random read (query operation) buffer of MySQL. When rows are read in any order (for example, in sort order), a random read cache is allocated. During sorting query, MySQL will scan the buffer first to avoid disk search and improve query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySQL will release this buffer space for each customer connection, so you should set this value appropriately as far as possible to avoid excessive memory overhead.
  be careful:
  1. Sequential reading means that the required row data can be read sequentially according to the leaf node data of the index.
  2. Random reading refers to finding the actual row data according to the primary key in the auxiliary index leaf node. The data segments of the auxiliary index and the primary key are different, so the access method is random.
• to configure

  read_rnd_buffer_size=1M
  

bulk_insert_buffer_size

• brief introduction
  Batch insert data cache size can effectively improve the insertion efficiency. The default is 8M.
• to configure

  bulk_insert_buffer_size=8M
  

binary log √

log-bin=/data/mysql-bin
binlog_format=row 
binlog_cache_size=2M                # The memory allocated for each session is used to store the cache of binary logs during transactions, so as to improve the efficiency of recording bin logs. If there are no large transactions and dml is not very frequent, you can set it smaller. If there are many transactions and dml operations are frequent, you can appropriately increase it. The former suggests 0-1M, while the latter suggests 2-4M
max_binlog_cache_size=8M            # Maximum cache memory size that binlog can use
max_binlog_size=512M                # Specify the size of binlog log file. The default value is 1GB. If the current log size reaches max_binlog_size, a new binary log will be automatically created. When importing large sql files, it is recommended to close sql_log_bin, otherwise the hard disk cannot carry it, and it is recommended to delete it regularly.
expire_logs_days=7                  # Defines the time when mysql clears expired logs. The default value is 0.
# Double 1 standard (safety based control)
sync_binlog=1                  
innodb_flush_log_at_trx_commit=1

set sql_log_bin=0;
show status like 'com_%';

Safety parameters √

Innodb_flush_method=(O_DIRECT, fsync)

• fsync :
  1. When the data page needs to be persistent, first write the data to the OS buffer, and then the OS decides when to write to the disk;
  2. When the redo buffer needs persistence, first write the data to the OS buffer, and the OS decides when to write to the disk; But if InnoDB_ flush_ log_ at_ trx_ If commit = 1, the log is written directly to the disk every commit.
• Innodb_flush_method=O_DIRECT
  1. When the data page needs to be persisted, it is written directly to disk
  2. When the redo buffer needs persistence, first write the data to the OS buffer, and the OS decides when to write to the disk; But if InnoDB_ flush_ log_ at_ trx_ If commit = 1, the log is written directly to the disk every commit.

Safest mode

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

Maximum performance mode

innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

Generally speaking, it prefers the safety mode "double one standard"

innodb_flush_log_at_trx_commit=1     
sync_binlog=1                                   
innodb_flush_method=O_DIRECT

Parameter optimization results

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock  

Lock monitoring

Analog lock wait

• session1

  mysql> use pressure;
  Database changed
  
  mysql> UPDATE t_100w SET k1='av' WHERE id=10;
  Query OK, 1 row affected (8.93 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  

• session2

  mysql> use pressure;
  Database changed
  
  mysql> UPDATE  t_100w SET k1='az' WHERE id=10;
  Query OK, 1 row affected (6.79 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  

Monitor lock status

1. Check whether there is lock waiting: SHOW STATUS LIKE 'innodb_row_lock%’;

   mysql> SHOW  STATUS LIKE 'innodb_row_lock%';
   +-------------------------------+-------+
   | Variable_name                 | Value |
   +-------------------------------+-------+
   | Innodb_row_lock_current_waits | 1     |
   | Innodb_row_lock_time          | 15729 |
   | Innodb_row_lock_time_avg      | 3145  |
   | Innodb_row_lock_time_max      | 5221  |
   | Innodb_row_lock_waits         | 5     |
   +-------------------------------+-------+
   5 rows in set (0.37 sec)
   

2. View which transaction is waiting (blocking): SELECT * FROM information_schema.INNODB_TRX WHERE trx_state=‘LOCK WAIT’\G

   mysql> SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT'\G
   *************************** 1. row ***************************
                       trx_id: 10003066                                  # Transaction ID   
                    trx_state: LOCK WAIT                                 # Current transaction status 
   ...
          trx_mysql_thread_id: 1114                                      # Connection thread ID (show processlist)
                    trx_query: UPDATE  t_100w SET k1='az' WHERE id=10    # Currently blocked operations
   ...
   

Determine lock source

1. View lock source: SELECT * FROM sys.innodb_lock_waits\G

   mysql> SELECT * FROM sys.innodb_lock_waits\G
                   locked_table: `pressure`.`t_100w`                       # Table with lock
                 waiting_trx_id: 10003066                                  # Waiting transaction ID
                    waiting_pid: 1114                                      # Waiting thread ID
                blocking_trx_id: 10003069                                  # Transaction ID of lock source
                   blocking_pid: 5                                         # Thread number of lock source
   

2. View lock source thread_id (get SQL thread ID through connection thread ID): SELECT * FROM performance_schema.threads WHERE processlist_id=blocking_pid;

   mysql> SELECT * FROM performance_schema.threads WHERE processlist_id=5\G
   
             THREAD_ID: 30
   ...
   

3. View lock source SQL statement (obtain SQL statement through SQL thread ID)

   Currently executing SQL sentence: SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=30;
   Historical execution SQL sentence: SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=30;
   

   mysql> SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=30\G
   *************************** 1. row ***************************
                 THREAD_ID: 30
                  EVENT_ID: 60000102
              END_EVENT_ID: 60000102
                EVENT_NAME: statement/sql/update
                    SOURCE: socket_connection.cc:101
               TIMER_START: 16584453793783000
                 TIMER_END: 16596271775695000
                TIMER_WAIT: 11817981912000
                 LOCK_TIME: 165000000
                  SQL_TEXT: UPDATE t_100w SET k1='av' WHERE id=10
                    DIGEST: a53700ae3d083482445ba277e43f7eee
               DIGEST_TEXT: UPDATE `t_100w` SET `k1` = ? WHERE `id` = ? 
            CURRENT_SCHEMA: pressure
               OBJECT_TYPE: NULL
   

Deadlock monitoring

# Deadlock monitoring (only the last deadlock status can be viewed)
mysql> show engine innodb status\G

# Open deadlock log
mysql> show variables like '%deadlock%';

[root@db01 ~]# vim /etc/my.cnf 
innodb_print_all_deadlocks = 1  

Optimization project: lock monitoring and handling

• Background: hardware environment DELL R720, E series 16 core, 48G MEM SAS900G6, RAID10
              During routine inspection, it is found that the CPU pressure during 9-11 o'clock is very high (80-90%)

• Project responsibilities

  1. Through the top detailed investigation, it is found that the proportion of mysqld processes reaches 700-800%;
  2. A large number of CPU s are used as sys and wait, and us is in normal state;
  3. It is suspected that there is a problem with MySQL lock or SQL statement;
  4. After checking the slowlog and lock waiting, it is found that there are a large number of lock waiting and a small number of slow statements.
     (1) PT query dialog view slow logs
     (2) Check for lock waiting
               Case 1: more than 100 current_waits, which indicates that many locks are waiting at present
               Case 2: more than 1000 locks_ Waits, indicating that there have been many lock waits in history
  5. See which transaction is waiting (blocked)
  6. View lock source transaction information (who locked it)
  7. Thread of lock source found_ id
  8. SQL statement to find lock source
  9. After finding the statement, negotiate with the application developer
     (1) According to the developer's description, this sentence is caused by the suspension of transactions. Our suggestion is a temporary kill session to finally solve the problem.
     (2) After checking, developers find that the deadlock is caused by business logic problems, resulting in a large number of lock waiting
          The temporary solution is to kill the session blocking the transaction
          The final solution is to modify the business logic in the code

• Project results
  After troubleshooting, the number of locks waiting is reduced by 80%, which solves the problem of continuous CPU peak.

Lock monitoring related commands:

show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;

Optimize master-slave

MySQL 5.7 slave multi-threaded MTS

• Basic Requirements

  1. Version above 5.7
  2. GTID must be turned on
  3. binlog must be in row mode

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16      # cpu core count as standard