MySQL CPU 100% high solution

Little scum · Posted on 10/11/2018 11:20:31 AM

Open the server task manager and find that the CPU process has always been 99%, and the view is occupied by the mysql process, which has been high.

Login is visible.

The output of the processlist command shows which threads are running, and you can check the current running status of the database.

1. Go to the mysql/bin directory and enter mysqladmin processlist;
2. Start mysql and enter show processlist.
If you have SUPER permission, you can see all threads, otherwise, you can only see the threads of your account.

The meaning and purpose of each column

id: An identifier
user: displays the current user, if it is not root, this command will only display SQL statements within your permissions.
host: shows which IP this statement is coming from which port
db: Displays the database that the process is currently connected to.
command: Displays the commands executed by the current connection, usually sleep, query, and connect.
time: The time this state lasts, in seconds.
state: Displays the status of the sql statement that uses the current connection, but only a certain state in the execution of the statement, a sql statement, has been queried, for example, it may need to go through the copying to tmp table, Sorting result, Sending data, etc
info: Displays this SQL statement, because the length is limited, so the long SQL statement is not fully displayed, but it is an important basis for judging the problem statement.

It is found that there are two SQL query statements that take the longest time and have not been executed for more than 1000 seconds, and the SQL statements we copied from the info field are a joint query, as follows:

select cast(count(*) as SIGNED) as col_0_0_ from `RatedPassenger` ratedpasse0_, `BaseInfoCompany` baseinfoco1_, `OrderMatch` ordermatch2_ where baseinfoco1_. CompanyId=ratedpasse0_. CompanyId and ordermatch2_. OrderId=ratedpasse0_. OrderId

We kill the process of these two query statements

Login is visible.

After waiting for 10 seconds, I found that the CPU had dropped a lot, but it was still very high, and I will continue to query the reason!

For the join joint query statement, add an index to the associated field or modify the program query code.
Create an index SQL statement

Login is visible.

End

Little scum · Posted on 10/11/2018 1:03:02 PM

The most critical of this command is the state column, and the states listed in mysql are mainly as follows:

Checking table
  Checking the datasheet (this is automatic).
  Closing tables
  The data modified in the table is being flushed to disk, and the table that has been used up is being closed. This is a quick operation, if not, you should check if the disk space is full or if the disk is under load.
  Connect Out
  The replication slave server is connecting to the primary server.
  Copying to tmp table on disk
  Because the temporary result set is greater than tmp_table_size, the temporary table is being converted from in-memory to disk storage to save memory.
  Creating tmp table
  A temporary table is being created to hold some of the query results.
  deleting from main table
  The server is performing the first part of a multi-table deletion and has just deleted the first table.
  deleting from reference tables
  The server is performing the second part of multi-table deletion, which is deleting records from other tables.
  Flushing tables
  Doing the FLUSH TABLES, waiting for other threads to close the datatable.
  Killed
  If a kill request is sent to a thread, the thread will check the kill flag and abandon the next kill request. MySQL checks for kill flags in each main loop, although in some cases the thread may die for a short period of time. If the thread is locked by another thread, the kill request will take effect immediately when the lock is released.
  Locked
  Locked by other queries.
  Sending data
  The record of the SELECT query is being processed while the results are being sent to the client.
  Sorting for group
  Sorting for GROUP BY.
  Sorting for order
  Sorting is being done for ORDER BY.
  Opening tables
  This process should be quick unless interfered with by other factors. For example, a data table cannot be opened by another thread until the ALTER TABLE or LOCK TABLE statement is executed. Trying to open a table.
  Removing duplicates
  A SELECT DISTINCT query is being executed, but MySQL is unable to optimize the duplicate records in the previous stage. Therefore, MySQL needs to remove the duplicate records again before sending the results to the client.
  Reopen table
  A lock on a table is obtained, but it must be modified after the table structure is modified. Already released the lock, closed the datasheet, and is trying to reopen the datatable.
  Repair by sorting
  The fix instruction is sorting to create an index.
  Repair with keycache
  The fix directive is using the index cache to create new indexes one by one. It will be slower than Repair by sorting.
  Searching rows for update
  Eligible records are being identified for updates. It must be done before the UPDATE modifies the relevant record.
  Sleeping
  Waiting for the client to send a new request.
  System lock
  Waiting to obtain an external system lock. If there are no mysqld servers running at the same time requesting the same table, external system locks can be prohibited 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
  is waiting for GET_LOCK().
  Waiting for tables
  The thread was notified that the datasheet structure had been modified and needed to be reopened to get the new structure. Then, in order to reopen the data table, you must wait until all other threads close the table. This notification occurs in the following cases: 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 inserts and is waiting for a new request.
  Most states correspond to fast operations, as long as a thread stays in the same state for a few seconds, then there may be a problem that needs to be checked.

Little scum · Posted on 3/1/2019 10:26:49 AM

Official documentation link:https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

Little scum · Posted on 3/1/2019 10:34:13 AM

Login is visible.

It takes 10 seconds to query a piece of data.

[Source] MySQL CPU 100% high solution

Related Posts

Sections viewed