“This is the 14th day of my participation in the August More Text Challenge. For details, see: August More Text Challenge.”

1. Optimize philosophy

1.1 Optimization is risky

1.2 Optimization scope

  • Storage, host, and OS:

    • Stability of host architecture
    • I/O planning and configuration
    • Swap
    • OS kernel parameters
    • Network problems

  • Application: Index, Lock, session

    • Application stability and performance
    • SQL Statement performance
    • Serial access resource
    • Poor session management performance

  • Database optimization: memory, database design, parameters

    • memory
    • Database structure (physical & logical)
    • The instance configuration

1.3 Evaluation of optimization effect

2. Optimize the tool

2.1 system layer

  • CPU
    • Calculation (main)
    • Scheduling (times)
  • IO
    • The input
    • The output
  • MEM
    • The cache
    • The buffer

2.1.1 the top command

Cpu (s) : 0.1% of the us, 0.1% sy, 0.0% ni, 99.9% id, wa, 0.0%, 0.0% hi si, 0.0% 0.0% of stCopy the code
  • id: Indicates the proportion of idle CPU time slices
  • us: The slice of time consumed by the user program
  • sy: The slice of CPU time spent on kernel work
    • Reasons for being too high
      • Bugs in the kernel itself
      • Concurrent high
      • The lock
  • wa: The percentage of CPU waiting time slices

Press 1 to check whether each CPU is running evenly

Sy Cpu0:0.3% of the us, 0.3%, 0.0% ni, 99.3% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st Cpu1: Sy us 2.7%, 0.7%, 0.0% ni, 96.7% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st Cpu2: Sy us 0.0%, 0.0%, 0.0% ni, 100.0% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st Cpu3: Sy us 1.0%, 1.3%, 0.0% ni, 97.6% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st, issue: Sy us 0.0%, 0.0%, 0.0% ni, 100.0% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st Cpu5: Sy us 0.0%, 0.0%, 0.0% ni, 100.0% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st Cpu6: Sy us 0.7%, 0.7%, 0.0% ni, 98.7% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st Cpu7: Sy us 0.7%, 0.0%, 0.0% ni, 99.3% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st Cpu8: Sy us 0.0%, 0.0%, 0.0% ni, 100.0% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st Cpu9: Sy us 0.0%, 0.0%, 0.0% ni, 100.0% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% st Cpu10: Sy us 0.0%, 0.0%, 0.0% ni, 100.0% id, wa, 0.0%, 0.0% hi 0.0% si, 0.0% stCopy the code

2.1.2 iostat command

iostat -dk 1
Copy the code

2.1.3 the vmstat command

vmstat 1
Copy the code

3. Optimize ideas

3.1 Hosts, Storage Devices, and Networks

3.1.1 host

  • Real hardware (PC Server) : DELL R series, Huawei, Inspur, HP, Lenovo
  • Cloud products: ECS, database RDS, DRDS
  • IBM minicomputer P6 570 595 P7 720 750 780 P8

3.1.2 CPU Based on the database type

  • OLTP
  • OLAP
  • IO intensive: Online systems, OLTP is mainly IO intensive services, high concurrency
  • CPU intensive: Data analysis data processing, OLAP, CPU intensive, requires CPU high computing power (I series, IBM Power series)
  • CPU intensive: I series, high frequency, few cores
  • IO intensive: E series (Xeon), relatively low frequency, a large number of cores

3.1.3 memory

Recommended 2-3 times the number of CPU cores (ECC)

3.1.4 Disk selection

SATA-III SAS Fc SSD (SATA) Battery Backup Unit (BBU) of the RAID card of the HOST PCI-E SSD Flash is disabled

3.1.5 storage

Select storage devices and configure RAID levels (RAID5, RAID10, and Hot spare disks) based on data types.

  • R0: Striping for high performance
  • R1: Mirroring is safe
  • R5: check + striping, high security + high read performance, low write performance (suitable for more read and less write)
  • R10: High security and performance, at least four disks, waste half of the space (high IO requirements)

3.1.6 network

1. Purchased hardware (single-card and single-port) 2. Bonded network adapters (bonding) and stacked switches

The above problems should be avoided in advance.

3.2 Operating System optimization

3.2.1 Swap adjustment

Echo 0 >/proc/sys/vm/swappiness

Add vm.swappiness=0 (permanent) to /etc/sysctl.conf

sysctl -p

This parameter determines whether Linux tends to use swap or to free the filesystem cache. When memory is tight, the lower the value, the more likely it is to release the file system cache.

Of course, this argument can only reduce the probability of using swap, but it does not prevent Linux from using swap.

Change the MySQL configuration parameter innodb_flush_method to enable O_DIRECT mode. In this case, InnoDB’s buffer pool will bypass the file system cache to access the disk. However, the file system cache is still used in the redo log. It is important to note that the Redo log is overwritten, so the file system cache is not used as much.

3.2.2 I/O Scheduling Policies

Centos 7 defaults to deadline

cat /sys/block/sda/queue/scheduler

Temporary changed to deadline (centos6) : echo, deadline > / sys/block/sda/queue/scheduler vi/boot/grub/grub. Conf

Change to the following:

Kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline RHGB quiet

  • IO:
    • raid
    • no lvm
    • Corruption or XFS
    • ssd
    • I/O Scheduling Policy

Planning all of these issues ahead of time makes it easier to optimize MySQL.

4. Optimize details

4.1 Parameter Optimization

4.1.1 Max_connections

1. Introduction

Mysql maximum number of connections, if the amount of concurrent requests the server is bigger, can raise the value, of course, this is to set up under the condition of the machine can support, because if there are more and more the number of connections, Mysql will provide a buffer for each connection, will cost the more memory, so need proper adjust the value, can’t literally to improve set value.

2. Judgment Basis

show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
show status like 'Max_used_connections';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Max_used_connections | 101   |
    +----------------------+-------+
Copy the code

3. Example of the modification mode

vim /etc/my.cnf
Max_connections=1024
Copy the code

Supplement:

  1. When the database is started, we can temporarily set a large test value
  2. To observe theshow status like 'Max_used_connections';change
  3. ifmax_used_connections 跟 max_connectionsSame, so it’s going to bemax_connectionsThe server load limit is too low or too high.

4.1.2 back_log

1. Introduction

The number of connections that can be temporarily stored by mysql. This is used when the main mysql thread receives a large number of connection requests in a short period of time. If the connection data reaches max_connections, new requests will be stored on the stack, waiting for a connection to release resources. If the number of waiting connections exceeds the back_log, no connection resource will be granted.

The back_log value indicates how many requests can be placed on the stack in a short period of time before mysql temporarily stops answering new requests, and only needs to be added if many connections are expected in a short period of time.

2. Judgment Basis

show full processlist

When a large number of processes are found to be connected, you need to increase the back_log or max_connections value

3. Example of the modification mode

vim /etc/my.cnf
back_log=1024
Copy the code

4.1.3 wait_timeout and interactive_timeout

1. Introduction

  • Wait_timeout:
    • The number of seconds mysql must wait before closing a non-interactive connection
    • If the setting is too small, the connection will close quickly, rendering some persistent connections useless
  • Interactive_timeout:
    • This is the number of seconds that mysql must wait before closing an interactive connection. For example, if we are running mysql management on the terminal, using an instant interactive connection, if no operation time exceeds the interactive_time setting, the connection will be automatically disconnected. The default is 28800. The value can be set to 7200.

2. Setting suggestions

  • If the setting is too large, the connection may take too long to openshow processlistWhen you can see a lot of connections, generally hopewait_timeoutAs low as possible

3. Example of the modification mode

wait_timeout=60
interactive_timeout=1200
Copy the code

Long-connected applications, in order to avoid repeated recycling and allocation of resources, reduce the extra overhead. In general, we will set wait_timeout to be small, interactive_timeout whether there are many applications that have long links to communicate with the application developer. If he needs long links, then this value does not need to be adjusted. You can also compensate for this by using arguments outside the class.

4.1.4 key_buffer_size

1. Introduction

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing and, in particular, index reads

  1. This parameter is related to the index of the MyISAM table
  2. Temporary table creation (multi-table link, subquery, Union). When the above query statements occur, temporary tables need to be created and discarded after being used up
  3. There are two ways to create a temporary table:
    1. Memory — — — — — — — > key_buffer_size
    2. On disk ——->ibdata1(5.6), ibtmp1 (5.7)

2. Setting criteria

You can pass key_read_requests and KEY_reads until the KEY_baffer_size setting is reasonable.

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

mysql>
mysql> show status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 10    |
| Key_reads         | 2     |
+-------------------+-------+
2 rows in set (0.00 sec)
Copy the code

There are 10 index read requests in total, and 2 requests are not found in memory. Note: KEY_BUFFer_size only applies to myISAM tables, even if the internal temporary disk tables are myISAM tables and not myISAM tables.

You can use the check status value created_tmp_disk_tables to find out:

mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)
Copy the code

Generally, Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables); Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)

Or the difference over a period of time to determine the temporary table utilization based on memory. Therefore, we will pay more attention to whether Created_tmp_disk_tables is too much to determine whether the current server health is good or bad. Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) is controlled within 5%-10%

Here’s an example:

When you call mysqldump to back up data, you can do the following:

180322 17:39:33 7 Connect root@localhost on 7 Query /*! 40100 SET @@SQL_MODE='' */ 7 Init DB guo 7 Query SHOW TABLES LIKE 'guo' 7 Query LOCK TABLES `guo` READ /*! 32311 LOCAL */ 7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 7 Query show create table `guo` 7 Query show fields from `guo`  7 Query show table status like 'guo' 7 Query SELECT /*! 40001 SQL_NO_CACHE */ * FROM `guo` 7 Query UNLOCK TABLES 7 QuitCopy the code

One step is to show fields from guo. From the execution plan of the Slow Query record, we know that it also produces ·Tmp_table_on_disk·.

Therefore, the above formula does not really reflect the utilization of temporary tables in mysql. In some cases, Tmp_table_on_disk is generated. There is no need to worry about ·Created_tmp_disk_tables·, so there is no need to pay too much attention to it, but if it is too large, take a good look at what queries your server is executing.

3. Configuration method

key_buffer_size=64M
Copy the code

4.1.5 max_connect_errors

Max_connect_errors is a security-related counter in mysql that prevents too many failed client attempts to prevent password cracking and other conditions. If the number of attempts exceeds the specified number, the mysql server will block connection requests to host. Until the mysql server restarts or the flush hosts command is used to clear the information about this host.

The value of max_CONNECt_errors is not very performance-related.

Modify the /etc/my. CNF file and add the following content under [mysqld]

max_connect_errors=2000

4.1.6 sort_buffer_size

1. Introduction:

Each thread that needs to sort is allocated a buffer of this size. Increasing this value accelerates

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

2. Configuration basis

Sort_Buffer_Size is not always better, as it is a connection-level parameter, and too large a setting + high concurrency may deplete system memory resources. For example, 500 connections will consume 500* sort_BUFFer_size (2M) =1G of memory

3. Configuration method

Modify the /etc/my.cnf file and add the following to the [mysqld] : sort_buffer_size=1M

4.1.7 max_allowed_packet

1. Introduction:

Mysql limits the size of packets that the server can accept based on the configuration file.

2. Configuration Basis:

Sometimes large inserts and updates will be limited by the max_allowed_packet parameter, resulting in a write or update failure. Larger values are 1GB and must be set in multiples of 1024

3. Configuration method:

max_allowed_packet=32M

4.1.8 thread_cache_size

1. Introduction

Server thread cache, this value can be reused to save in the cache, the number of threads when disconnected, so the client thread will be put into the cache in response to the next customer rather than destroy (premise is the maximum number of caching), if the thread to be request, the request will be read from the cache, if the cache is empty or is a new request, The thread will then be recreated, and if there are many new threads, increasing this value can improve system performance.

2. Configuration basis

You can see what this variable does by comparing variables in the Connections and Threads_created states. The configuration rules are as follows: set 1GB memory to 8. Set 2GB memory to 16. Set 3GB memory to 32.

The server’s threads that process this client will be cached in response to the next client instead of being destroyed (if the number of cached threads is not reached).

The number of connections attempted to connect to MySQL, whether or not the connection was successful

mysql>  show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 2     |
| Threads_created   | 4783  |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
Copy the code
  • Threads_cached: represents the number of idle threads in the thread cache at this time.
  • Threads_connected: represents the number of existing connections. Since each connection requires one thread, it can also be viewed as the number of threads currently in use.
  • Threads_created: represents the number of threads that have been created since the last service start, if foundThreads_createdIf the value is too large, it indicates that the MySQL server has been creating threads, which consumes CPU SYS resources, and can be added to the configuration file appropriatelythread_cache_sizeValue.
  • Threads_running: represents the number of currently active (non-sleeping) threads. Does not represent the number of threads in use, sometimes the connection is established, but the connection is in sleep state.

3. Configuration method:

thread_cache_size=32
Copy the code

finishing

Threads_created: Typically during the architecture design phase, a test value is set and stress tested.

Combine this with zabbix monitoring to see how this state changes over time.

If Threads_created levels off over a period of time, the corresponding parameter setting is OK.

If it continues to grow steeply, or if there are a lot of peaks, then continue to increase the size of this value, as long as the system resources are sufficient (memory)

4.1.9 Innodb_buffer_pool_size (Very Important)

1. Introduction

For InnoDB tables, Innodb_buffer_pool_size is what KEY_buffer_size is for MyISAM tables.

2. Configuration Basis:

InnoDB uses this parameter to specify the size of memory to buffer data and indexes.

For a separate MySQL database server, this value can be set to a maximum of 80% of the physical memory. In general, it is recommended not to exceed 70% of the physical memory.

How to check if it is enough:

mysql> show engine innodb status\G
Total large memory allocated 137428992
Dictionary memory allocated 160964
Buffer pool size   8192
Free buffers       7747
Database pages     445
Old database pages 0
Modified db pages  0
Pending reads      0
Copy the code

The Buffer pool size refers to the number of pages in memory, and the Database Pages refers to the number of pages in use. Free buffers refers to the number of pages that are left, and if it is very small, you need to set this value higher. However, if the value exceeds 80% of the physical memory, you need to add a memory bar.

3. Configuration method

innodb_buffer_pool_size=2048M
Copy the code

4.1.10 innodb_flush_log_at_trx_commit

1. Introduction

It mainly controls the time point when InnoDB writes the data from the log buffer to the log file and flusits the disk. The values are 0, 1 and 2 respectively.

  • 0: indicates that log writing is not performed when the transaction is committed, but is performed every secondlog bufferWrite data in the log file and flush the disk once.
  • 1: Each transaction commit causes the redo log file to be written and flushed, ensuring ACID in the transaction.
  • 2: Each transaction commit causes a write to the log file, but the disk flush is completed once per second.

2. Configuration basis

The actual test found that this value has a great impact on the speed of data insertion. When set to 2, it only takes 2 seconds to insert 10000 records, when set to 0, it only takes 1 second, and when set to 1, it takes 229 seconds. Therefore, the MySQL manual also recommends that you try to combine the insert operations into a single transaction, which can greatly increase speed.

According to the official MySQL documentation, this value can be set to 0 or 2, allowing for the risk of losing some of the most recent transactions.

3. Configuration method

innodb_flush_log_at_trx_commit=1
Copy the code

A 1 in the double 1 standard

4.1.11 innodb_thread_concurrency

1. Introduction

This parameter is used to set the number of concurrent innoDB threads. The default value of 0 means that there is no limit.

2. Configuration basis

There are also some suggestions for using Innodb_thread_Concurrency concurrency on the doc page:

  1. If the number of concurrent user threads in a workload is less than 64, this is recommendedinnodb_thread_concurrency=0;
  2. If the workload is consistently heavy or even peaks occasionally, you are advised to set it firstinnodb_thread_concurrency=128For example, suppose the system usually has 40 to 50 users, but regularly increases to 60,70, or even 200. You’ll find that performance is stable at 80 concurrent users, and deteriorates above that. In this case, the recommended settinginnodb_thread_concurrencySet the parameter to 80 to avoid affecting performance.
  3. If you don’t want InnoDB to use more virtual cpus than user threads use (say 20 virtual cpus), it is recommended to set thisinnodb_thread_concurrencyFor this value (or lower, depending on performance), if your goal is to isolate MySQL from other applications, you might consider binding mysqld processes to a dedicated virtual CPU. Note, however, that this binding can result in suboptimal hardware utilization if the myslqd process is not always very busy. In this case, you might set the virtual CPU bound to the mysqld process to allow other applications to use part or all of the virtual CPU.
  4. In some cases, the bestinnodb_thread_concurrencyThe parameter Settings can be smaller than the number of virtual cpus.
  5. Regular monitoring and analysis of the system may be necessary for changes in load, users, or working environmentinnodb_thread_concurrencyParameter Settings are adjusted.

128 —–> top cpu

Setting standards:

  1. The CPU usage in the system is uneven

top

  1. The current number of connections has not reached a peak
show status like 'threads_%';
show processlist;
Copy the code

3. Configuration method:

innodb_thread_concurrency=8
Copy the code

Methods:

  1. Look at top to see how much each CPU is loaded
  2. If the number of cpus is not equal, set the parameter to the number of cpus, and then increase (double) this value
  3. Keep watching the top state until it is more uniform, indicating that it is in place.

4.1.12 innodb_log_buffer_size

This parameter determines the memory size, in M, used by some log files. Larger buffers improve performance, and for larger transactions, the cache size can be increased.

innodb_log_buffer_size=128M
Copy the code

Setting basis:

  1. Large transactions: Stored procedure CALL
  2. Many affairs

View the current committed transactions:

mysql> show status like '%commit%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Com_commit     | 0     |
| Com_xa_commit  | 0     |
| Handler_commit | 0     |
+----------------+-------+
3 rows in set (0.09 sec)
Copy the code

If Com_commit is too large, increase innodb_log_buffer_size.

You can also look at the IO and adjust it if the IO is very frequent (high).

4.1.13 innodb_log_file_size = 100 m

Set ib_logfile0 and ib_logfile1

This parameter determines the size of the data log file, in M. A larger setting can improve performance.

innodb_log_file_size = 100M
Copy the code

4.1.14 innodb_log_files_in_group

To improve performance, MySQL can write log files to multiple files in a circular manner. The recommended value is 3.

4.1.15 binary log

log-bin=/data/mysql-bin

binlog_cache_size = 2M The memory allocated for each session is used to store binary log cache during the transaction process to improve the efficiency of bin-log recording. If there are no large transactions and DML is not very frequent, it can be set to small. If there are large transactions and many DML operations, it can be set to large. The former suggestion is --1M, the latter suggestion is: namely 2--4M
max_binlog_cache_size = 8M # specifies the maximum cache size that binlog can use
max_binlog_size= 512M If the current log size reaches max_binlog_size, a new binary log will be created automatically. You cannot set this variable to be greater than 1GB or less than 4096 bytes. The default value is 1GB. When importing large-capacity SQL files, you are advised to disable THE SQL_log_bin function. Otherwise, the hard disk will fail. You are advised to periodically delete the SQL files.

expire_logs_days = 7 # specifies when mysql will clear expired logs. Number of days for binary logs to be automatically deleted. The default value is 0, indicating that the system is not automatically deleted.

log-bin=/data/mysql-bin

binlog_format=row

sync_binlog=1 # Double 1 standard (security-based control) : When is the binlog flushed to disk for each transaction commit

innodb_flush_log_at_trx_commit=1

set sql_log_bin=0;

show status like 'com_%';
Copy the code

4.1.16 Security parameters

Innodb_flush_method=(O_DIRECT, fsync)

  1. Innodb_flush_method = fsync:

    1. When the data page needs to be persisted, the data is first written to the OS buffer, and then the OS decides when to write the data to disk
    2. When redo Buffuer is persisted, data is first written to the OS buffer, and the OS decides when to write it to disk. But, ifinnodb_flush_log_at_trx_commit=1In this case, the log is directly written to disk every commit

  2. Innodb_flush_method = O_DIRECT:

    1. When a data page needs to be persisted, it is written directly to disk
    2. When redo Buffuer is persisted, data is first written to the OS buffer, and the OS decides when to write it to disk. But, ifinnodb_flush_log_at_trx_commit=1In this case, the log is directly written to disk every commit
  • Safest mode:
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
Copy the code
  • Maximum performance mode:
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
Copy the code

In general, we prefer to be safe. “Double one Standard” :

innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_flush_method=O_DIRECT
Copy the code

5. 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
Copy the code

Stress test again:

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose

6. Lock monitoring and handling

6.1 Lock wait simulation

  • Record Lock
  • Next Lock
  • GAP Lock

6.2 Monitoring the Lock Status

6.3 Lock Troubleshooting Process

6.3.1 Monitoring whether there is a lock wait

show status like 'innodb_row_lock%';
Copy the code

Focus:

  • Innodb_ ROW_LOCK_CURRENT_waits: How many lock waits there are currently
  • Innodb_row_lock_waits: Total number of lock waits that have occurred

6.3.2 Viewing Pending Transactions

select * from information_schema.INNODB_TRX where trx_state='LOCK WAIT';
Copy the code

6.3.3 Check the Lock source and who locks me

select * from sys.innodb_lock_waits;
Copy the code
  • Locked_table: Table that generates the lock wait
  • Locked_type: Lock type (Record, gap, nextlock)
  • Waiting_trx_id: ID of the transaction to be waited on
  • Waiting_pid: ID of the connection thread that is waiting for the transaction
  • Waiting_query: Transaction statement to wait for
  • Waiting_lock_mode: Type of wait lock (X, S)
  • Blocking_trx_id: Transaction ID of the lock source
  • Blocking_pid: Lock the transaction connection thread ID of the source
  • Sql_kill_blocking_connection: Solution

6.3.4 According to the PID of the lock source, zhao Dor locks the thread ID of the source SQL

select * from performance_schema.threads where processlist_id=1691;
Copy the code
  • Thread_id: indicates the ID of the thread executing the SQL
  • Name: indicates the thread name
  • Processlist_id: ID of the thread that received the returned data

6.3.5 Finding the SQL statement of the Lock source based on the SQL Thread ID of the Lock Source

select * from performance_schema.event_statements_current where thread_id=1716;
Copy the code
  • Thread_id: indicates the ID of a thread
  • Event_name: indicates the operation type
  • Sql_text: indicates the SQL statement

6.4 Deadlock monitoring

show engine innodb status\G
show variables like '%deadlock%';
Copy the code
vim /etc/my.cnf
innodb_print_all_deadlocks = 1
Copy the code

7. Master-slave optimization

Multithreaded MTS from library In versions later than 5.7 (forget the small version), GTID binlog must be enabled. Gtid_mode =ON ENFORCE_GTId_consistency =ON log_slave_updates=ON slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 master_info_repository=TABLE relay_log_info_repository=TABLE Relay_log_recovery = ON 5.7: Slave parallel-type=LOGICAL_CLOCK slave parallel-workers=8 CPU cores as standard CHANGE MASTER TO MASTER_HOST='10.0.0.128', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_AUTO_POSITION=1; start slave;Copy the code