Лавинообразный коллапс БД при запросах в

[smith]

Добрый день.
В последнее время стали происходить ежедневные коллапсы с системой, в частности из за БД и отработки ею некоторых запросов. Тюнинг базы по основным параметрам с помощью mysqltuner результата не дал, также как и смена версии марии с 5.5 на 10.
Буду признателен за помощь и/или консультацию по сабжу)

Начальные данные:
Zabbix server 2.2.3, используются zabbix-прокси.
Выделенный сервер БД – MariaDB-10, 2х8 Xeon, raid10 - 4 HDD по 10к, 64 RAM, включено партиционирование, временной диск вынесен в память. Количество хостов/элементов – 4900/354755, количество одновременно активных пользователей ~ 22.

Интересно то, что проблема такого коллапса замечена при отработке запроса одной группы пользователей (43):

SELECT t.* FROM triggers t WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='43'
WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0)
AND t.lastchange>'1400134755' AND t.priority BETWEEN '0' AND '5' AND t.value IN ('0','1') AND t.flags IN ('0','4') ORDER BY t.lastchange DESC LIMIT 30 OFFSET 0;

Эксплейн выполнения в аттачах -1й рисунок.

При его выполнении идет лавинообразное нарастание процессов mariadb Copying to tmp table (в количестве ~ до 200 шт.) с временем выполнения до 3000 сек и более. При таком раскладе память и процессоры в топе и краш БД (2й и 3й графики).

Кусок конфига MariaDB:

# -----------------------------------------------------------------------------------------
max_connections = 256
max_connect_errors = 1000

connect_timeout = 10
#wait_timeout = 600
#interactive_timeout = 600

max_allowed_packet = 64M

sort_buffer_size = 4M
join_buffer_size = 64M

tmp_table_size = 64M
max_heap_table_size = 1024M

# ---------- Thread settings --------------------------------------------------------------
thread_cache_size = 16
thread_concurrency = 8

# -----------------------------------------------------------------------------------------
# * Query Cache Configuration
query_cache_limit = 8M
query_cache_size = 128M

# -----------------------------------------------------------------------------------------
# * InnoDB
default_storage_engine = InnoDB

# you can't just change log file size, requires special procedure
innodb_log_file_size = 256M
innodb_log_files_in_group = 2

innodb_buffer_pool_size = 28G

innodb_log_buffer_size = 16M
innodb_file_per_table = 1
event_scheduler = 1

innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120

innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT

[ugh]

Неблагодарное это дело, с оптимизатором воевать.
Как показала практика, на каком-то этапе, когда появляется определенное количество итемов и триггеров, фронтэнд начинает вести себя довольно непредсказуемо.
Мне кажется что в таком случае, надо начинать смотреть в сторону некого логического шардинга и писать свою обвязку к базе с нужными функционалом.
ИМХО, вы победите этот коллапс чем-нибудь вроде "force index" или оптимизацией таблички, но надо понимать что скорее всего оно повторится.

К сожалению не знаю как ведет себя фронтэнд с постгресом на таких объемах. Может стоит попробовать?

[smith]

Неблагодарное это дело, с оптимизатором воевать.
Как показала практика, на каком-то этапе, когда появляется определенное количество итемов и триггеров, фронтэнд начинает вести себя довольно непредсказуемо.
Мне кажется что в таком случае, надо начинать смотреть в сторону некого логического шардинга и писать свою обвязку к базе с нужными функционалом.
ИМХО, вы победите этот коллапс чем-нибудь вроде "force index" или оптимизацией таблички, но надо понимать что скорее всего оно повторится.

К сожалению не знаю как ведет себя фронтэнд с постгресом на таких объемах. Может стоит попробовать?

Спасибо за рекомендации.