Я просто установил новый сервер для большой базы данных MySQL размером около 70 ГБ. Эта база данных регулярно записывается автоматическими процессами, которые должны записывать свои данные как можно быстрее. Раньше у нас был сервер с SSD на 120 ГБ, но мы перешли на HDD, потому что объем данных становится все больше и больше.
Проблема в том, что когда процессы запущены, загрузка ЦП превышает 150%, а операции записи становятся смертельно медленными ...
Сервер имеет 4 ядра - 8-поточный ЦП, 32 ГБ ОЗУ и 2 жестких диска по 2 ТБ с 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 ... Вы хоть представляете, где может возникнуть проблема? Спасибо :)
Изменить: образец списка процессов, когда мой процессор загружается до 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.
Если это поможет, вы также можете попробовать поиграть с его количеством, потому что разные значения могут дать разный результат, даже правило «больше - лучше» иногда не применяется.