Shulou(Shulou.com)05/31 Report--

What are the troubleshooting skills for MySQL performance emergencies? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problems. Through this article, I hope you can solve this problem.

After years of practical experience, some materials have been sorted out, and the troubleshooting skills of MySQL performance emergencies in Linux environment have been shared to you.

As a DBA in the face of sudden performance problems, whether there is a helpless, do not know how to start the experience. In fact, the performance problem lies in storage, operating system, application, database and so on.

Performance analysis problems are not as difficult as you might think, and when you learn about some commonly used Linux system commands and MySQL basic troubleshooting commands, all problem points can be located.

First, the last Linux performance tool graph, the author of Brendan D. Gregg dynamic tracking tool DTrace.

It's a little complicated, and you don't have to care too much, as long as you understand the following common commands and analysis points, you can determine the vast majority of performance problems.

Common performance collection tools for Linux platform foundations:

1. Process monitoring of top-Linux system

Top command is a commonly used performance analysis tool under Linux, which can display the resource consumption of each process in the system in real time, similar to the task manager of Windows. And it is also a tool often used by Linux system administrators to monitor system performance. The Top command periodically displays all running and actual running and updates to the list, showing CPU usage, memory usage, swap memory, cache size, buffer size, process control, users, and more commands. It also shows running processes with excessive memory and CPU usage.

2. Vmstat-Virtual memory statistics

The vmstat command is used to display more information about virtual memory, kernel threads, disks, system processes, Imax O modules, interrupts, CPU active status, and so on.

3. Lsof-Open the file list

The lsof command is available on many Linux/Unix systems, mainly displaying open files and processes in the form of lists. Open files mainly include disk files, network sockets, pipes, devices and processes. This command makes it easy to see which files are in use.

4. Tcpdump-Network packet Analyzer

Tcpdump is the most widely used command line network packet analysis, which completely intercepts the packets transmitted in the network to provide analysis. It supports filtering against network layer, protocol, host, network or port and provides and, or, not and other logic statements to help you get rid of useless information.

The package can be parsed by the tcpdump command, or saved as a file with the suffix pcap, which can be viewed by software such as wireshark.

3. Netstat-Network Statistics

The netstat command is a command-line tool that monitors the statistical interface of incoming and outgoing network packets. It is a very useful tool for many system administrators to monitor network performance and solve network-related problems.

4. Iostat-input / output statistics

Iostat is a simple tool for collecting and displaying system input and output storage device statistics. This tool is often used to find performance problems with storage devices, including devices, local disks, such as NFS remote disks.

In addition to the above, there are other tools commonly used in Linux, such as sar,htop, IPTraf, iotop, iftop, iptraf and so on.

MySQL Common performance Emergency Analysis commands:

1. SHOW PROCESSLIST;-all threads running on the current MySQL database

2. INNODB_TRX;-all currently running transactions

# # all currently running transactions and specific statements

3. INNODB_LOCKS;-the lock that currently appears

# # statement information of locks occurring in the current transaction

4. INNODB_LOCK_WAITS;-the correspondence of lock waiting

# # correspondence of Lock waiting

5. SHOW OPEN TABLES where In_use > 0;-currently open table

To see which tables are in use, the In_use column indicates how many threads are using a table, and Name_locked indicates whether the table name is locked, which usually occurs when the Drop or Rename command manipulates the table. So this command does not help answer our common questions: whether a table currently has a deadlock, who owns the lock on the table, and so on.

Please pay attention to the key parts below.

6. SHOW ENGINE INNODB STATUS\ G;-Innodb status

Display in addition to a large number of internal information, the output is more complex and difficult to understand, the output contains some average statistics, these averages are the statistics since the last output result was generated.

The specific analysis is as follows:

① .Header

This section simply prints, the time of the output, and the interval since the last output.

② .BACKGROUND THREAD

3. SEMAPHORES semaphore

The OS WAIT ARRAY INFO operating system waits for the information of the array, which is an array of slots. How many times does the innodb wait for the operating system?

Retention Statistics (reservation count) shows the frequency of innodb allocation slots

Signal count (signal count) measures how often a thread gets a signal through an array.

The RW-shared spins:# line shows the counters for read-write shared locks

The RW-excl spins:# line shows read-write counters with exclusive locks.

The RW-sx spins:# line shows the shared exclusive lock counter

* Note: 5.7.2 adds a new type of read-write lock called SX shared exclusive lock

The owner of the lock can read any data in the table, and if the X lock can be obtained on the corresponding row, the row can be modified.

4. TRANSACTIONS

Contains statistics for Innodb transactions (transactions) as well as a list of currently active transactions.

Transaction id: this ID is a system variable that increases every time a new transaction is generated.

Purge done: the transaction ID that is purge in progress. You can see how transactions are not lagging behind by purge by looking at the difference between transaction id and transaction done ID.

History listlength: records the number of unpurged transactions in the undo spaces.

5. FILE I/O

Shows the status of I _ Helper thread, including some statistics

Pending operations, log of pending and fsync () call of buffer pool thread

399 OS file: the line shows the number of reads, writes, and fsync () calls.

0.00 reads/s. Statistics per second are displayed

Note: "aio" means "Asynchronous I aio O (Asynchronous I Pot O)."

6. INSERT BUFFER AND ADAPTIVE HASH INDEX

Some information about Ibuf:insertbuffer, including free list, segment size

Hash table: shows some information about hash table * one line shows how many hash searches per second, as well as non-hash searches

7. LOG

Log sequence number stands for lsn in redo log buffer

Log flushed up to stands for lsn in redo log file

Pages flushed up to represents the lsn of the oldest dirty page in the buffer pool

Last checkpoint at refers to the variable value of fil_page_lsn when the most recent physical page was flushed to disk.

8. BUFFER POOL AND MEMORY

Current memory usage status

Pages read ahead: shows the number of linear and random pre-reads per second

Note: InnoDB provides two ways of pre-reading, one is Linear read ahead, which is controlled by the parameter innodb_read_ahead_threshold. When you continuously read threshold page of one extent, it will trigger the pre-read of 64 page of the next extent. The other is Random read-ahead, which is controlled by the parameter innodb_random_read_ahead. When you continuously read a set number of page, it will trigger to read the remaining page of that extent. The read-ahead function of InnoDB is done asynchronously using background threads.

9. ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue: shows how many threads are in the Innodb kernel

Read views open inside InnoDB: shows how many read view have been opened. A read view is the MVCC "snapshot" of consistency guarantee.

Note: innodb multiple version concurrency (MVCC) determines the database snapshot for consistent reading through read view, and the read view of innodb determines whether a record can be seen.

Under the RC isolation level, every SELECT will get the read view of *.

Under the RR isolation level, the read view is created when there are * SELECT requests in the transaction.

7. SHOW STATUS LIKE 'innodb_row_lock_%';-Lock performance status

View current lock performance status

The explanation is as follows:

Innodb_row_lock_current_waits: number of locks currently waiting

Innodb_row_lock_time: the total length of time since the system was booted and locked

Innodb_row_lock_time_avg: average lock time per time

Innodb_row_lock_time_max: maximum lock time

Innodb_row_lock_waits: total number of times the system has been locked since it was started

8. SQL statement EXPLAIN;-query optimizer

EXPLAIN execution plan section, skip

As a DBA, problem troubleshooting skills are the core skills that every engineer needs to master.

After reading the above, have you mastered the troubleshooting skills of MySQL performance emergencies? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.