我刚刚为大约 70GB 的大型 MySQL 数据库设置了一台新服务器。该数据库定期由自动进程写入，这些进程必须尽可能快地写入数据。之前我们的服务器配备 120GB SSD，但由于数据量越来越大，我们改用 HDD。
问题是，当进程运行时，CPU 峰值会超过 150%，并且写入操作会变得非常慢......
该服务器有 4 核 - 8 线程 CPU、32GB RAM 和 2x2TB HDD，配有 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)
答案1
我会调查 table_open_cache 限制是否不是瓶颈原因。也许线程只是阻塞，导致执行时间长且 CPU 占用高。
此外，启用查询缓存可能会有帮助，例如将 query_cache_type 切换到 ON 并将 query_cache_size 设置为 1G。
如果有帮助的话，您也可以尝试玩它的数量，因为不同的值可以产生不同的结果，甚至“越多越好”的规则有时也不适用。