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

[mschedrin]

Проверил, все индексы на месте.
Вспомнил, что у меня есть ряд скриптов, которые работают с базой напрямую. Возможно они что-то выполняют некорректно, после чего база начинает плохо работать. Завтра попробую в этом разобраться.

[mschedrin]

Нашел тяжелые запросы, все они выглядят вот так:

# Time: 100421 14:03:32
# User@Host: zabbix[zabbix] @ localhost []
# Query_time: 101  Lock_time: 0  Rows_sent: 83371  Rows_examined: 253038
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 000000000000000 and 099999999999999 order by i.itemid;

Такой запрос заставляет mysql создать два временных файла размером по почти 500Мб. Естественно он выполняется очень долго. Вот так выглядят файлы:

#sql_111b1_0.MYD                       |489723K
#sql_111b1_1.MYD                       |489723K|

Explain:

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 000000000000000 and 099999999999999 order by i.itemid;
+----+-------------+-------+------+-------------------------+---------+---------+-----------------+------+---------------------------------+
| id | select_type | table | type | possible_keys           | key     | key_len | ref             | rows | Extra                           |
+----+-------------+-------+------+-------------------------+---------+---------+-----------------+------+---------------------------------+
|  1 | SIMPLE      | h     | ref  | PRIMARY,hosts_2         | hosts_2 | 4       | const           | 1173 | Using temporary; Using filesort | 
|  1 | SIMPLE      | i     | ref  | PRIMARY,items_1,items_3 | items_1 | 8       | zabbix.h.hostid |   14 | Using where                     | 
+----+-------------+-------+------+-------------------------+---------+---------+-----------------+------+---------------------------------+
2 rows in set (0.00 sec)

Если отключить order by то temporary не используется и запрос выполняется быстро.

[den_crane]

1 	SIMPLE 	h 	ref 	PRIMARY,hosts_2,hosts_4 	hosts_2 	4 	const 	106 	Using index; Using temporary; Using filesort
1 	SIMPLE 	i 	ref 	PRIMARY,items_1,items_3,items_test2 	items_1 	8 	zabbix.h.hostid 	15 	Using where

меняю h.status in (0) на h.status+0 in (0) и filesort исчезает

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	i 	range 	PRIMARY,items_1,items_3,items_test2 	PRIMARY 	8 	NULL 	1957 	Using where
1 	SIMPLE 	h 	eq_ref 	PRIMARY 	PRIMARY 	8 	zabbix.i.hostid 	1 	Using where

[mschedrin]

1 	SIMPLE 	h 	ref 	PRIMARY,hosts_2,hosts_4 	hosts_2 	4 	const 	106 	Using index; Using temporary; Using filesort
1 	SIMPLE 	i 	ref 	PRIMARY,items_1,items_3,items_test2 	items_1 	8 	zabbix.h.hostid 	15 	Using where

меняю h.status in (0) на h.status+0 in (0) и filesort исчезает

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	i 	range 	PRIMARY,items_1,items_3,items_test2 	PRIMARY 	8 	NULL 	1957 	Using where
1 	SIMPLE 	h 	eq_ref 	PRIMARY 	PRIMARY 	8 	zabbix.i.hostid 	1 	Using where

Во-первых, это костыль
Во-вторых, где это поменять? Я пока не знаю zabbix_server этот запрос инициирует или веб интерфейс?

[den_crane]

Во-первых, это костыль
Во-вторых, где это поменять?

оптимизатор у мускуля тупой не понимает что h.status in (0) это практически вся таблица, переходите на postgre

[mschedrin]

оптимизатор у мускуля тупой не понимает что h.status in (0) это практически вся таблица, переходите на postgre

Вы баг на этот счет не открывали?
Вы знаете где в исходиках можно поменять этот запрос? Или вы просто перешли на postgre?

[den_crane]

Вы баг на этот счет не открывали?

нет.

Вы знаете где в исходиках можно поменять этот запрос?

не знаю, обычно я ищу grep-м, минут 10-ть уходит на это.

Или вы просто перешли на postgre?

я собирался, даже тестировал переход, но довел до состояния cpu idle 98%, и как-то мне лень стало дергаться.

[ugh]

а почему у меня все хорошо?
почему план запроса разный?
хостов/итемов ведь сравнимое количество

[mschedrin]

а почему у меня все хорошо?
почему план запроса разный?
хостов/итемов ведь сравнимое количество

А почему вы думаете, что всё хорошо?
Попробуйте выполнить подобный запрос:

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 000000000000000 and 099999999999999 order by i.itemid;

Сколько времени он будет выполняться?
Что скажет explain для этого запроса?

[ugh]

пост номер 31
чудно вы, вашу же ветку читаете)))

[mschedrin]

пост номер 31
чудно вы, вашу же ветку читаете)))

У Вас другой запрос, у меня between от нуля начинается, а у вас:

... between 800800000010001 and 099999999999999999 order by i.itemid;

[mschedrin]

Открыл баг: https://support.zabbix.com/browse/ZBX-2363

[ugh]

... between 800800000010001 and 099999999999999999 order by i.itemid;
не суть, ид итема меньше 800800000010001 все равно нет)))

[mschedrin]

... between 800800000010001 and 099999999999999999 order by i.itemid;
не суть, ид итема меньше 800800000010001 все равно нет)))

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            |    1 | 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> 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 0 and 099999999999999999 order by i.itemid;             +----+-------------+-------+------+-------------------------+---------+---------+-----------------+------+---------------------------------+
| id | select_type | table | type | possible_keys           | key     | key_len | ref             | rows | Extra                           |
+----+-------------+-------+------+-------------------------+---------+---------+-----------------+------+---------------------------------+
|  1 | SIMPLE      | h     | ref  | PRIMARY,hosts_2         | hosts_2 | 4       | const           | 1663 | Using temporary; Using filesort | 
|  1 | SIMPLE      | i     | ref  | PRIMARY,items_1,items_3 | items_1 | 8       | zabbix.h.hostid |   11 | Using where                     | 
+----+-------------+-------+------+-------------------------+---------+---------+-----------------+------+---------------------------------+
2 rows in set (0.00 sec)

Видите разницу?

[ugh]

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 800800000000000 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            | 87704 | Using where |
|  1 | SIMPLE      | h     | eq_ref | PRIMARY,hosts_2         | PRIMARY | 8       | zabbix.i.hostid |     1 | Using where |
+----+-------------+-------+--------+-------------------------+---------+---------+-----------------+-------+-------------+
2 rows in set (0.01 sec)