И снова про производительность. Zabbix 1.8.2 и mysql innodb

[ugh]

кстати
14к хостов
160к итемов всего, 39к итемов активных

время выполнения:
39366 rows in set (1.02 sec)

mysql> explain select i.itemid,i.hostid,h.proxy_hostid,i.type,i.data_type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.snmp_port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.delay_flex,i.trapper_hosts,i.logtimefmt,i.params,i.status,i.authtype,i.username,i.password,i.publickey,i.privatekey from items i,hosts h where i.hostid=h.hostid and h.status in (0) and i.status in (0,3) and i.itemid between 800800000010001 and 099999999999999999 order by i.itemid;
+----+-------------+-------+--------+-------------------------+---------+---------+-----------------+-------+-------------+
| id | select_type | table | type   | possible_keys           | key     | key_len | ref             | rows  | Extra       |
+----+-------------+-------+--------+-------------------------+---------+---------+-----------------+-------+-------------+
|  1 | SIMPLE      | i     | range  | PRIMARY,items_1,items_3 | PRIMARY | 8       | NULL            | 92572 | Using where |
|  1 | SIMPLE      | h     | eq_ref | PRIMARY,hosts_2         | PRIMARY | 8       | zabbix.i.hostid |     1 | Using where |
+----+-------------+-------+--------+-------------------------+---------+---------+-----------------+-------+-------------+
2 rows in set (0.00 sec)

индексы:

mysql> show index from hosts;
+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| hosts |          0 | PRIMARY   |            1 | hostid       | A         |       14687 |     NULL | NULL   |      | BTREE      |         |
| hosts |          1 | hosts_1   |            1 | host         | A         |       14687 |     NULL | NULL   |      | BTREE      |         |
| hosts |          1 | hosts_2   |            1 | status       | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| hosts |          1 | hosts_3   |            1 | proxy_hostid | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| hosts |          1 | host_test |            1 | ip           | A         |       14687 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

mysql> show index from items;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| items |          0 | PRIMARY  |            1 | itemid      | A         |      168786 |     NULL | NULL   |      | BTREE      |         |
| items |          0 | items_1  |            1 | hostid      | A         |        5626 |     NULL | NULL   |      | BTREE      |         |
| items |          0 | items_1  |            2 | key_        | A         |      168786 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | items_3  |            1 | status      | A         |           5 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | items_4  |            1 | templateid  | A         |        5114 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

[den_crane]

Количество хостов 2860, Количество items: 81918
На мой взгляд не так уж и много. Историю сократил до жесточайшего минимума.
У меня всё еще есть сомнения, правильно ли настроен мой mysql, возможно всё-таки дело в нем?

а покажите конфиги мускуля и заббикса?

в общем-то интересны:
innodb_buffer_pool_size = 400M
innodb_additional_mem_pool_size = 64M
key_buffer_size = 150M
innodb_log_file_size = 150M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2

у заббикса StartPollers=50 и остальные Start*

А процессоры какие у сервера? И сколько?

А вы не пробовали придушить многопоточность:
innodb-thread-concurrency=2
может у вас типа http://bugs.mysql.com/bug.php?id=15815

[mschedrin]

для группы активировать "Режим отладки"
добавить юзера в группу
сверху справа будет

...Профиль|[b]Отладка[/b]|Выйти из системы...

Спасибо, полезная функция.

[mschedrin]

кстати
14к хостов
160к итемов всего, 39к итемов активных

время выполнения:
39366 rows in set (1.02 sec)

mysql> explain select i.itemid,i.hostid,h.proxy_hostid,i.type,i.data_type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.snmp_port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.delay_flex,i.trapper_hosts,i.logtimefmt,i.params,i.status,i.authtype,i.username,i.password,i.publickey,i.privatekey from items i,hosts h where i.hostid=h.hostid and h.status in (0) and i.status in (0,3) and i.itemid between 800800000010001 and 099999999999999999 order by i.itemid;
+----+-------------+-------+--------+-------------------------+---------+---------+-----------------+-------+-------------+
| id | select_type | table | type   | possible_keys           | key     | key_len | ref             | rows  | Extra       |
+----+-------------+-------+--------+-------------------------+---------+---------+-----------------+-------+-------------+
|  1 | SIMPLE      | i     | range  | PRIMARY,items_1,items_3 | PRIMARY | 8       | NULL            | 92572 | Using where |
|  1 | SIMPLE      | h     | eq_ref | PRIMARY,hosts_2         | PRIMARY | 8       | zabbix.i.hostid |     1 | Using where |
+----+-------------+-------+--------+-------------------------+---------+---------+-----------------+-------+-------------+
2 rows in set (0.00 sec)

индексы:

mysql> show index from hosts;
+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| hosts |          0 | PRIMARY   |            1 | hostid       | A         |       14687 |     NULL | NULL   |      | BTREE      |         |
| hosts |          1 | hosts_1   |            1 | host         | A         |       14687 |     NULL | NULL   |      | BTREE      |         |
| hosts |          1 | hosts_2   |            1 | status       | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| hosts |          1 | hosts_3   |            1 | proxy_hostid | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| hosts |          1 | host_test |            1 | ip           | A         |       14687 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

mysql> show index from items;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| items |          0 | PRIMARY  |            1 | itemid      | A         |      168786 |     NULL | NULL   |      | BTREE      |         |
| items |          0 | items_1  |            1 | hostid      | A         |        5626 |     NULL | NULL   |      | BTREE      |         |
| items |          0 | items_1  |            2 | key_        | A         |      168786 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | items_3  |            1 | status      | A         |           5 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | items_4  |            1 | templateid  | A         |        5114 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

Интересно, у меня в таблице hosts нет индекса ip. Вы сами его добавляли или он должен быть по умолчанию?

[mschedrin]

а покажите конфиги мускуля и заббикса?

в общем-то интересны:
innodb_buffer_pool_size = 400M
innodb_additional_mem_pool_size = 64M
key_buffer_size = 150M
innodb_log_file_size = 150M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2

У меня так:

innodb_buffer_pool_size = 1500M
innodb_additional_mem_pool_size = 1048576
key_buffer_size = 8384512
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2

у заббикса StartPollers=50 и остальные Start*

StartPollers=10
StartIPMIPollers=0
StartPollersUnreachable=1
StartTrappers=5
StartPingers=20
StartDiscoverers=20

А процессоры какие у сервера? И сколько?

Один четырехядерный: Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz

А вы не пробовали придушить многопоточность:
innodb-thread-concurrency=2
может у вас типа http://bugs.mysql.com/bug.php?id=15815

Не пробовал, сейчас стоит значение 8.

Сегодня добавил еще 2Гб оперативной памяти. Собираюсь увеличить innodb_buffer_pool. Еще хочу проверить все индексы в таблицах, вдруг действительно что-то потерялось.

[ugh]

остался со старых времен
по совету den_crane добавлял))) помогло тогда кстати)

но в нашем случае он все равно не используется

[den_crane]

У меня так:

startpollers=10

для начала 100 поставьте. У вас очередь сразу уменьшится.

[mschedrin]

для начала 100 поставьте. У вас очередь сразу уменьшится.

Поставил. Сейчас буду смотреть что стало с очередью. Однако проблему с БД это вряд ли решит. В tmp все еще появляются временные файлы mysql по несколько сотен мегабайт.

[eugene.istomin]

Попробуйте c tmpfs

В tmp все еще появляются временные файлы mysql по несколько сотен мегабайт.

Скорее всего, проблема в том, что временные файлы создаются на диске.
Попробуйте c tmpfs
Например, здесь написано как

Пару месяцев назад битрикс валил БД, после переноса временных файлов mysql на tmpfs всё выровнялось.

[mschedrin]

Скорее всего, проблема в том, что временные файлы создаются на диске.
Попробуйте c tmpfs
Например, здесь написано как

Пару месяцев назад битрикс валил БД, после переноса временных файлов mysql на tmpfs всё выровнялось.

Уже пробовал такое. Делал ramdisk на 512 МБ. Не помогло, т.к. mysql пытался писать туда файлы значительно большего размера. Хочу понять почему так происходит. Мне кажется, такого быть не должно в нормальной ситстеме

[den_crane]

Поставил. Сейчас буду смотреть что стало с очередью. Однако проблему с БД это вряд ли решит. В tmp все еще появляются временные файлы mysql по несколько сотен мегабайт.

в том explain что вы показали нет страшного перечисления. Наверно в реальной жизни у вас запрос выполняется начиная с hosts, а временная таблица на диске создается типа для order by.

[den_crane]

Пару месяцев назад битрикс валил БД, после переноса временных файлов mysql на tmpfs всё выровнялось.

это все костыли, не должно быть у базы заббикса никаких временных файлов.

[mschedrin]

в том explain что вы показали нет страшного перечисления. Наверно в реальной жизни у вас запрос выполняется начиная с hosts, а временная таблица на диске создается типа для order by.

Каким образом отловить запросы, которые создают временные таблиц такого размера? Мне пока методика не очень понятна.

Из тяжелых запросов, которые удавалось поймать, временная таблица действительно использовалась для OrderBy.

[den_crane]

Каким образом отловить запросы, которые создают временные таблиц такого размера? Мне пока методика не очень понятна.

я думаю они у вас уже в slow queries.log

Из тяжелых запросов, которые удавалось поймать, временная таблица действительно использовалась для OrderBy. .

В том конкретном запросе я думаю стоит заменить h.hostid IN (перечисление хостов) на h.hostid+0 IN (перечисление хостов)

[mschedrin]

я думаю они у вас уже в slow queries.log

В том конкретном запросе я думаю стоит заменить h.hostid IN (перечисление хостов) на h.hostid+0 IN (перечисление хостов)

В slow queries попадает слишком много других запросов, отфильтровать их оттуда только те что используют tmp проблематично. Попробую что-нибудь придумать.