Big brother beep beep

We’ve written a lot about MySQL, such as index optimization, database locking, master-slave replication, and more. Show processList shows the session status of all database connections. Help us to see the running status of each SQL thread, whether it is running properly, sleeping, or something else.

Show the processlist profile

grammar

Different users can only view their own data. If you want to view all data, please use the administrator to query

show processlist;
Copy the code

The result field description is displayed

  • id

    SQL ID. This ID can be used when you need to kill the SQL process

  • User

    Current Connected User

  • Host

    Owning IP address and port

  • db

    The database name

  • command

    The connection status is usually sleep, query, or connect. If an SQL statement is in the Query state and the time is long, it indicates that there is a problem

  • time

    Duration of connection status, in seconds (s)

  • State (Key analysis)

    The state of the current SQL statement is an important parameter for optimization

  • info

    Displays the SQL statement currently executed

The state,

State is an important field in optimization and can give us a lot of information about the current state of the SQL thread to help us locate analysis problems. Some common fields for state are listed below.

  • state

    Description: Indicates that the resource is not released. If the database is connected through the connection pool, state should be stable in range. If a large number of SQL requests forget to close the database connection, a large number of connection requests will be blocked, and the database will hang.

  • checking table

    Explanation: checking the data table, this operation is automatic

  • closing tables

    Description: Indicates that you are flushing the changed data from the table to disk, and then closing the used table, which is a quick operation.

    Optimization suggestion: If this process is slow, you need to check whether the disk is full, or the disk is doing a lot of I/O operations, etc

  • connect out

    Description: In master/slave replication, the slave server is connecting to the master server

  • creating tmp table

    Explanation: Creating a temporary table to temporarily store query results

  • copying to tmp table on disk

    When the order by, group by, or JOIN query is used, temporary table data will be created. When the data is too large, temporary table data in memory will be stored on the hard disk.

    Optimization suggestions: 1. Optimize indexes to minimize the creation of temporary tables. Two: optimize SQL statement logic, you can use Java code to achieve part of the time-consuming SQL logic. Tmp_table_size and max_HEAP_table_size can be adjusted to increase the size of temporary tables in memory.

  • flushing tables

    Wait for another thread to close the database table while performing the refresh table

  • killed

    Explanation: If a kill request is sent to a thread, the thread will check the kill flag and discard the next kill request. MySQL checks for the kill flag in each main loop, but in some cases the thread may take a short time to die. If the thread is locked by another thread, the kill request takes effect as soon as the lock is released.

  • sending data

    Explanation: This field is very misleading literally, most people think it is just sending data to the client, but it is actually collecting + sending. If a column is not in the index, then MySQL needs to fetch the data from the row where the id resides and return the data to the client.

  • sorting for group / order

    SQL statement uses group and order for sorting

    Optimization suggestion: If temporary table creation or in-file sorting occurs, optimize indexes when it is time-consuming

  • Waiting for net / reading from net / writing to net

    Description: Mainly describes the network status, such as a large number of database network connection status and traffic

    Optimization suggestion: For example, when external traffic attacks the database, the network bandwidth is occupied and a large number of connection requests are sent to the database, causing the database to crash. Therefore, you are advised to defend against traffic attacks.

  • locked

    SQL is locked, such as table locks, row locks, gap locks, etc.

    Optimization suggestion: Use indexes correctly to prevent index failures and upgrade to table locks. Use InnoDB search engine, not MyISam.

  • Opening tables

    An SQL thread is attempting to open a table, which would normally be fast, but if someone finishes before an ALTER TABLE or lock TABLE statement, other threads cannot open the table.

  • Waiting for tables

    The thread is notified that the data table structure has been modified and needs to reopen the data table to get the new structure. Then, in order to reopen the table, you must wait until all other threads close the table.

    This notification is generated when: FLUSH TABLES TBL_NAME, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

  • System lock

    Explanation: Waiting to acquire an external system lock. If you are not currently running multiple mysqld servers simultaneously requesting the same table, you can disable external system locking by adding the –skip-external-locking parameter. This parameter is turned off by default.

conclusion

We can according to the analysis of the SQL statement to state of the state, where is the problem, combined with brother talked about before database lock, index optimization, show Profiles, and so on optimization method, a comprehensive analysis, brother can only tell you the theory knowledge, the theory of knowledge to learn first, combined with theoretical knowledge in the practical work is analyzed.

IT brother

A big factory to do advanced Java development program ape

Follow wechat public account: IT elder brother

Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc

Reply: Resume template, you can get 100 beautiful resumes

Reply: Java learning route, you can get the latest and most complete a learning roadmap

Re: Java ebooks, get 13 must-read books for top programmers