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

[mschedrin]

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)

Вы что издеваетесь?

between 800800000000000 and 099999999999999999

Надо указать от *НУЛЯ* до 099999999999999999.

[ugh]

не вопрос)

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 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            | 104598 | 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)

[mschedrin]

Вот это уже интересно. Какая версия mysql у Вас стоит? Движок базы innodb?

[ugh]

mysql 5.0.77
иннодб конечно

а покажите:
show index from hosts;
show index from items;
?

[mschedrin]

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         |        2743 |     NULL | NULL   |      | BTREE      |         | 
| hosts |          1 | hosts_1  |            1 | host         | A         |        2743 |     NULL | NULL   |      | BTREE      |         | 
| hosts |          1 | hosts_2  |            1 | status       | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
| hosts |          1 | hosts_3  |            1 | proxy_hostid | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.02 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         |       83499 |     NULL | NULL   |      | BTREE      |         | 
| items |          0 | items_1  |            1 | hostid      | A         |        5566 |     NULL | NULL   |      | BTREE      |         | 
| items |          0 | items_1  |            2 | key_        | A         |       83499 |     NULL | NULL   |      | BTREE      |         | 
| items |          1 | items_3  |            1 | status      | A         |           4 |     NULL | NULL   |      | BTREE      |         | 
| items |          1 | items_4  |            1 | templateid  | A         |        7590 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

Чего-то не хватает?

[ugh]

всего хватает
я не знаю почему такой план запроса

создал сейчас тестовую базу, засунул туда 250 хостов, привязал по 150 неактивных итемов (мог кстати и не добавлять, план для исходной вроде бы и так такой)

получил примерно тоже что и у вас:

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 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           |  129 | Using temporary; Using filesort |
|  1 | SIMPLE      | i     | ref  | PRIMARY,items_1,items_3 | items_1 | 8       | zabbix.h.hostid |   98 | Using where                     |
+----+-------------+-------+------+-------------------------+---------+---------+-----------------+------+---------------------------------+
2 rows in set (0.00 sec)

добавил в запрос: FORCE INDEX(PRIMARY)
получил

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 FORCE INDEX(PRIMARY),hosts h FORCE INDEX(PRIMARY) where i.hostid=h.hostid and h.status in (0) and i.status in (0,3) and i.itemid between 000000000000000 and 099999999999999999 order by i.itemid;
+----+-------------+-------+--------+---------------+---------+---------+-----------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+-------+-------------+
|  1 | SIMPLE      | i     | range  | PRIMARY       | PRIMARY | 8       | NULL            | 23866 | Using where |
|  1 | SIMPLE      | h     | eq_ref | PRIMARY       | PRIMARY | 8       | zabbix.i.hostid |     1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+-------+-------------+
2 rows in set (0.00 sec)

попробуйте.. если поможет, тогда в исходниках можно будет поправить и радоваться

ага... завтра попробую покапать еще почему так, но знаний у меня маловато к сожалению

[mschedrin]

Да, с вашим запросом у меня тоже всё хорошо отрабатывает. Разобраться почему так у меня тоже знаний маловато, к сожалению. Исходники заббикса видимо придется править самостоятельно, на открытый мною баг никто пока внимание не обращает.

[ugh]

src/libs/zbxdbcache/dbconfig.c