MySQL высокое использование ЦП/времени запроса во время параллельных вставок
Я просто устанавливаю новый сервер для большой базы данных MySQL приблизительно 70 ГБ. Эта база данных регулярно пишется автоматическими процессами, которые должны записать так быстро, как они могут свои данные. Прежде чем у нас был сервер с SSD на 120 ГБ, но мы переключились на жесткий диск, потому что объем данных становится больше и больше.
Проблема состоит в том, что, когда процессы работают, пики нагрузки ЦП больше чем к 150% и операции записи становятся смертельно медленными...
Сервер имеет 4 ядра - 8 потоков ЦП, 32 ГБ RAM и жесткого диска на 2x2 ТБ с RAID-контроллером LSI 2108 (RAID 1). MariaDB 10.0 является единственным сервером, работающим на этой машине. ОС является Ubuntu 14.04, недавно установленная. Это имеет ведомый немного менее мощный сервер, вот почему я включил бинарные журналы.
Я настроил установку 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... У Вас есть какая-либо идея, куда проблема могла возникнуть? Спасибо :)
Редактирование: образец processlist, в то время как мой ЦП пронзает к 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 причиной узкого места. Возможно, потоки просто блокируются, что приводит к длительному времени выполнения и высокой загрузке ЦП.
Кроме того, включение кеша запросов может помочь, например, переключение query_cache_type на ON и установка query_cache_size на 1G.
Если это поможет, вы также можете попробуйте поиграть с его количеством, потому что разные значения могут дать разный результат, даже правило «больше - лучше» иногда не применяется.