在并发插入期间MySQL高CPU /查询时间使用率

我刚刚为一个大约70GB的大型MySQL数据库安装了一个新的服务器。 该数据库定期通过自动进程编写,必须尽可能快地写入数据。 之前我们有一个120GB的SSD服务器,但我们切换到硬盘,因为数据量越来越大。

问题是,当进程运行时,CPU高达150%以上,写操作变得非常慢…

该服务器有一个4核心 – 8线程CPU,32GB内存和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。

此外,启用查询caching可能会有所帮助,如将query_cache_type切换为ON并将query_cache_size设置为1G。

如果有帮助的话,你也可以试着用它来玩,因为不同的价值可以给出不同的结果,即使“越多越好”规则有时不适用。