Mysql

MySQL 並發插入期間的高 CPU/查詢時間使用率

  • October 17, 2014

我剛剛為大約 70GB 的大型 MySQL 數據庫設置了一個新伺服器。該數據庫由自動程序定期寫入,這些程序必須盡可能快地寫入數據。在我們擁有一台配備 120GB SSD 的伺服器之前,我們切換到 HDD,因為數據量越來越大。

問題是當程序執行時,CPU 峰值超過 150%,寫入操作變得非常緩慢……

該伺服器有一個 4 核 - 8 執行緒 CPU、32GB RAM 和 2x2TB 硬碟和一個 LSI 2108 RAID 控制器 (RAID 1)。MariaDB 10.0 是這台機器上執行的唯一伺服器。作業系統是全新安裝的 Ubuntu 14.04。它有一個從伺服器稍微不那麼強大,這就是我啟用 binlogs 的原因。

我像這樣調整了 InnoDB 設置:

query_cache_type        = OFF
tmp_table_size          = 1G
max_heap_table_size     = 1G
transaction-isolation   = READ-COMMITTED
binlog_format           = row
innodb_log_file_size    = 6G
innodb_buffer_pool_size = 24G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 300
innodb_io_capacity_max  = 400
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout= 240
innodb_use_fallocate = 1
innodb_random_read_ahead = 1
innodb_flush_neighbors = 0
innodb_checksum_algorithm = crc32
innodb_fast_shutdown    = 0
skip-innodb_doublewrite

在程序執行時,慢日誌充滿了這些行(突出顯示的查詢是隨機的,可以在任何表上插入、更新或刪除):

# User@Host: user_prod[user_prod] @ xxxxx [xxx.xxx.xxx.xxx]
# Thread_id: 177018  Schema: user_prod  QC_hit: No
# Query_time: 18.318539  Lock_time: 0.000026  Rows_sent: 0  Rows_examined: 1
SET timestamp=1413450644;
update `pages_objects` set `status_comments` = 'idle', `updated_at` = '2014-10-16 09:10:57' where `id` = '331667763652878';

我被卡住了,在Google上找不到任何幫助……你知道問題可能出在哪裡嗎?謝謝 :)

編輯:當我的 CPU 達到 250% 時的程序列表範例(見鬼!):

+--------+--------------+---------------------------------+--------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
| Id     | User         | Host                            | db           | Command | Time | State          | Info                                                                                                 | Progress |
+--------+--------------+---------------------------------+--------------+---------+------+----------------+--------------------------------    ----------------------------------------------------------------------+----------+
|    378 | user_prod | server.ip:46542 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|   2985 | user_prod | server.ip:60257 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|   4001 | user_prod | server.ip:38046 | user_prod | Execute |    0 | preparing      | select * from `pages_users` where `user_id` = '1247143319' and `page_id` = '169449309753828' limit 1 |    0.000 |
|   6533 | user_prod | server.ip:54548 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|   7582 | user_prod | server.ip:59995 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|  13179 | user_prod | server.ip:33221 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|  14624 | user_prod | server.ip:41004 | user_prod | Execute |    0 | Writing to net | select * from `pages_users` where `user_id` = '100000010909375' and `page_id` = '476930419093906' li |    0.000 |
|  54642 | user_prod | server.ip:45540 | user_prod | Execute |    0 | update         | insert into `pages_users` (`user_id`, `page_id`, `updated_at`, `created_at`) values ('1318873669', ' |    0.000 |
|  55244 | user_prod | server.ip:47407 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|  55426 | user_prod | server.ip:47983 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 107408 | user_prod | server.ip:57303 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204661 | user_prod | server.ip:45568 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204717 | user_prod | server.ip:51573 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204795 | user_prod | server.ip:52682 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204844 | user_prod | server.ip:53290 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204972 | user_prod | server.ip:54717 | user_prod | Sleep   |   20 |                | NULL                                                                                                 |    0.000 |
| 204999 | user_prod | server.ip:55069 | user_prod | Sleep   |   13 |                | NULL                                                                                                 |    0.000 |
| 205006 | user_prod | server.ip:55159 | user_prod | Sleep   |   11 |                | NULL                                                                                                 |    0.000 |
| 205020 | user_prod | server.ip:55377 | user_prod | Sleep   |    7 |                | NULL                                                                                                 |    0.000 |
| 205026 | user_prod | server.ip:55443 | user_prod | Sleep   |    5 |                | NULL                                                                                                 |    0.000 |
| 205028 | user_prod | server.ip:55524 | user_prod | Sleep   |    3 |                | NULL                                                                                                 |    0.000 |
| 205031 | user_prod | server.ip:55569 | user_prod | Sleep   |    2 |                | NULL                                                                                                 |    0.000 |
| 205032 | user_prod | server.ip:55573 | user_prod | Sleep   |    2 |                | NULL                                                                                                 |    0.000 |
| 205034 | user_prod | localhost                       | NULL         | Query   |    0 | init           | show processlist                                                                                     |    0.000 |
+--------+--------------+---------------------------------+--------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
24 rows in set (0.00 sec)

我會調查 table_open_cache 限制是否不是瓶頸原因。也許執行緒只是阻塞,導致執行時間長和 CPU 高。

此外,啟用查詢記憶體可能會有所幫助,例如將 query_cache_type 切換為 ON 並將 query_cache_size 設置為 1G。

如果這有幫助,您也可以嘗試使用它的數量,因為不同的值會產生不同的結果,即使“越多越好”的規則有時也不適用。

引用自:https://serverfault.com/questions/637461