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

This article introduces how to check the sql and commands when the number of mysql connections is full. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

1: log in to mysql using root

Mysql-uroot-paired movies *

2: view the number of mysql connections settings

Show variables like'% max_connections%'

3: modify the number of connections settings

Set GLOBAL max_connections=500

4: list the number of connections

Method 1: execute after logging in to mysql

Mysql-uroot-paired movies *

Show full processlist

Method 2: operating system execution

Mysqladmin-uroot-paired alarm * processlist

Method 3: query through dynamic view

SELECT *

FROM information_schema.processlist

WHERE user = 'me' and state IS NOT NULL

View the number of client connections:

Select substring_index (host,':',1), count (*) from information_schema. Processlist group by substring_index (host,':',1)

Show variables like'% innodb_thread_concurrency%'

The results are listed as follows:

Id:

Mysql thread id

Command:

Commands that show the execution of the current connection, such as sleep, query, connect

Time:

The duration of this state, in seconds.

Info column:

Display this sql statement, because the length is limited, so the long sql statement is not complete, but it is an important basis for judging the problem statement.

State column:

Statement execution A sql statement, such as a query, may need to go through states such as copying to tmp table,Sorting result,Sending data before it can be completed. The status is as follows:

Checking table

Checking the datasheet (this is automatic).

Closing tables

The modified data in the table is being flushed to disk and the table that has been used up is being closed. This is a quick operation, and if not, you should make sure that the disk space is full or that the disk is under a heavy load.

Connect Out

The replication slave server is connecting to the master server.

Copying to tmp table on disk

Because the temporary result set is larger than tmp_table_size, temporary tables are being converted from memory storage to disk storage to save memory.

Creating tmp table

Creating a temporary table to hold some of the query results.

Deleting from main table

The server is performing the first part of the multi-table deletion, and the first table has just been deleted.

Deleting from reference tables

The server is performing the second part of the multi-table deletion and is deleting records for other tables.

Flushing tables

Executing FLUSH TABLES, waiting for another thread to close the data table.

Killed

If a kill request is sent to a thread, the thread will check the kill flag bit and abandon the next kill request. MySQL checks the kill flag bit 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.

Locked

Locked by other queries.

Sending data

The record of the SELECT query is being processed and the results are being sent to the client.

Sorting for group

Sorting for GROUP BY.

Sorting for order

Sorting for ORDER BY.

Opening tables

This process should be very fast unless disturbed by other factors. For example, a data table cannot be opened by another thread until the execution of an ALTER TABLE or LOCK TABLE statement is complete. Trying to open a table.

Removing duplicates

A query in SELECT DISTINCT mode is being executed, but MySQL cannot optimize those duplicate records in the previous phase. Therefore, MySQL needs to remove the duplicate records again and then send the results to the client.

Reopen table

A lock on a table is acquired, but the lock cannot be acquired until the table structure has been modified. The lock has been released, the datasheet has been closed, and an attempt is being made to reopen the datasheet.

Repair by sorting

The repair directive is sorting to create an index.

Repair with keycache

The repair instruction is using the index cache to create new indexes one by one. It will be slower than Repair by sorting.

Searching rows for update

We are talking about finding qualified records for updating. It must be done before UPDATE modifies the relevant records.

Sleeping

Waiting for the client to send a new request.

System lock

Waiting to acquire an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can disable external system locks by adding the-- skip-external-locking parameter.

Upgrading lock

INSERT DELAYED is trying to get a lock table to insert a new record.

Updating

Searching for matching records and modifying them.

User Lock

Waiting for GET_LOCK ().

Waiting for tables

The thread is informed that the data table structure has been modified and needs to be reopened to get the new structure. Then, in order to reopen the table, you must wait until all other threads close the table. This notification occurs in the following situations: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

Waiting for handler insert

INSERT DELAYED has processed all pending insert operations and is waiting for a new request.

Most states correspond to fast operations, and as long as one thread stays in the same state for a few seconds, there may be a problem that needs to be checked.

5: check the connection status

Show status like'% below variable%'

Aborted_clients the number of connections that have been abandoned because the customer did not close the connection correctly.

The number of times Aborted_connects has attempted a connection to the MySQL server that has failed.

The number of times Connections attempted to connect to the MySQL server.

Created_tmp_tables the number of implicit temporary tables that have been created when the statement is executed.

The number of latency insertion processor threads being used by Delayed_insert_threads.

The number of rows written by Delayed_writes in INSERT DELAYED.

The number of rows written by Delayed_errors with INSERT DELAYED for which some errors (possibly duplicate key values) have occurred.

The number of times Flush_commands executed the FLUSH command.

The number of times Handler_delete requests to delete rows from a table.

The number of times Handler_read_first requests to read the first row in the table.

The Handler_read_key request number is based on the key reading line.

The number of times Handler_read_next requests to read a row based on a key.

The number of times Handler_read_rnd requests to read a row based on a fixed location.

The number of times Handler_update requests to update a row in the table.

The number of times Handler_write requests to insert a row into the table.

The number of blocks used by Key_blocks_used for keyword caching.

The number of times Key_read_requests requests to read a key value from the cache.

The number of times Key_reads physically reads a key value from disk.

Key_write_requests requests the number of times a keyword block is written to the cache.

The number of times Key_writes physically writes a key block to disk.

The maximum number of connections used simultaneously by Max_used_connections.

Not_flushed_key_blocks keyblocks that have been changed in the key cache but have not been emptied to disk.

The number of rows that Not_flushed_delayed_rows is waiting to write in the INSERT DELAY queue.

The number of Open_tables open tables.

The number of files opened by Open_files.

Number of Open_streams open streams (mainly used for logging)

The number of tables that Opened_tables has opened.

The number of queries sent by Questions to the server.

The number of queries that Slow_queries takes longer than long_query_time time.

The number of connections currently open by Threads_connected.

The number of threads that Threads_running is not sleeping.

How many seconds did the Uptime server work?

6: kill thread:

Kill id

Select concat ('kill','', id,';'), t.*

From information_schema.processlist t

Where user='xxxx'

7: use the linux command to see which pid uses ports

Netstat-anp | grep 3306

Freebsd system uses sockstat

About the number of mysql connections how to view sql and commands to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.