mysql 100%占用的解決

2024-07-24 12:43:29

字體：大中小

來源：轉載

供稿：網友

早上客戶反應，其網站無法訪問，無限轉圈

上服務器，查看磁盤空間df -h，內存使用率free -m，網絡流量iftop均正常

然后使用top查看時，發現mysql的cpu使用率上升到200%。

解決過程回放

進入mysql

查看正在執行的sql

mysql> use information_schema;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from PROCESSLIST where info is not null;+-----+------+-----------+--------------------+---------+------+-----------+--------------------------------------------------+| ID  | USER | HOST      | DB                 | COMMAND | TIME | STATE     | INFO                                             |+-----+------+-----------+--------------------+---------+------+-----------+--------------------------------------------------+| 291 | root | localhost | information_schema | Query   |    0 | executing | select * from PROCESSLIST where info is not null |+-----+------+-----------+--------------------+---------+------+-----------+--------------------------------------------------+1 row in set (0.00 sec)mysql>

并沒有發現有任何的異樣，沒有出現鎖表狀況

然后查看tmp_table_size的大小

mysql> show variables like '%table_size%';+---------------------+-----------+| Variable_name       | Value     |+---------------------+-----------+| max_heap_table_size | 16777216 || tmp_table_size      | 16777216 |+---------------------+-----------+2 rows in set (0.00 sec)

確認兩個值大小均為16M(安裝的是mariadb 5.5)

查看free -m還有4G大小的內存，此處顯得過小，將其一個值提升到500M，一個值提升至200M

[root@iZbp16s0cap5fnfk6bjvw1Z ~]# grep -v ^# /etc/my.cnf | grep -v ^$[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socktmp_table_size=200Mmax_heap_table_size=500M

然后重啟mysql

發現top的中mysql的cpu占用率使用已經大大下降，已經恢復至20%左右

事后總結

mysql cpu占用率很高，很有可能是因為查詢時死表，或者說大量多表查詢，導致cpu飚高。

另外也有可能是因為tmp_table_size過大，超出了mysql的內存大小使用設定，mysql會將一些table寫入到磁盤中，這樣也會大大引起cpu的使用率增大

在select * from PROCESSLIST where info is not null中沒有發現異樣時，即可以推斷另外一種的可能。

在mysql的官方文檔中是這樣寫的

Storage Engines Used for Temporary TablesAn internal temporary table can be held in memory and processed by the MEMORY storage engine, or stored on disk and processed by the MyISAM storage engine.If an internal temporary table is created as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. This differs from MEMORY tables explicitly created with CREATE TABLE: For such tables, only the max_heap_table_size system variable determines how large the table is permitted to grow and there is no conversion to on-disk format.

翻譯過來的大意是，當tmp_table變得越來越大的時候，msql tmp_table使用內存最大值為tmp_table_size與max_heap_table_size兩者中較小的值。

而最后一句話特別的重要，當create table的時候(mysql臨時表使用內存肯定會增加)，max_heap_table_size才是決定臨時表能創建多少的值。

所以一般max_heap_table_size要大于tmp_table_size

mysql> show global status like "%created_tmp%";+-------------------------+-------+| Variable_name           | Value |+-------------------------+-------+| Created_tmp_disk_tables | 1654  || Created_tmp_files       | 6     || Created_tmp_tables      | 1791  |+-------------------------+-------+3 rows in set (0.00 sec)