Long initialization after zabbix server restart

[nvitaly007]

Hello,

I probably missing something well known but:

I have zabbix server with 5 proxies, when by some reason zabbix server get down for some time, it takes really long time (hours) after server start untill whole system start working.

I can see on mysql server this task:

| zabbix | Query | 1890 | Sending data | select max(id) from history_text where id between 0 and 99999999999999

Any way to speed it up ?

Thank you

[Pada]

Hi,

What versions of Zabbix server & MySQL are you running?

That looks like a very poor query. I would simply drop the whole "where" clause.

Could you also show the indexes on that table, because that query really shouldn't run THAT long! On my DB it finished very quickly:

mysql> select max(id) from history_text where id between 0 and 99999999999999;
+----------+
| max(id)  |
+----------+
| 72461418 |
+----------+
1 row in set (0.00 sec)

Here's how mine looks in Zabbix 1.8.11:

mysql> show indexes from history_text;
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| history_text |          0 | PRIMARY        |            1 | id          | A         |    12814699 |     NULL | NULL   |      | BTREE      |         |               |
| history_text |          0 | history_text_2 |            1 | itemid      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| history_text |          0 | history_text_2 |            2 | id          | A         |    12814699 |     NULL | NULL   |      | BTREE      |         |               |
| history_text |          1 | history_text_1 |            1 | itemid      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| history_text |          1 | history_text_1 |            2 | clock       | A         |    12814699 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.11 sec)

Also, you can run the following MySQL command to check if the table is being locked:

SHOW OPEN TABLES WHERE `table` LIKE 'history_text';

[nvitaly007]

Zabbix Zabbix 2.0.5
MySQL: 5.5.27

mysql> explain select max(id) from history_text where id between 0 and 99999999999999;
+----+-------------+--------------+-------+---------------+----------------+---------+------+-----------+--------------------------+
| id | select_type | table        | type  | possible_keys | key            | key_len | ref  | rows      | Extra                    |
+----+-------------+--------------+-------+---------------+----------------+---------+------+-----------+--------------------------+
|  1 | SIMPLE      | history_text | index | NULL          | history_text_1 | 12      | NULL | 160596378 | Using where; Using index |
+----+-------------+--------------+-------+---------------+----------------+---------+------+-----------+--------------------------+
1 row in set (0.00 sec)

mysql> show indexes from history_text;
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| history_text |          0 | PRIMARY        |            1 | itemid      | A         |   161712209 |     NULL | NULL   |      | BTREE      |         |               |
| history_text |          0 | PRIMARY        |            2 | id          | A         |   161712209 |     NULL | NULL   |      | BTREE      |         |               |
| history_text |          0 | PRIMARY        |            3 | clock       | A         |   161712209 |     NULL | NULL   |      | BTREE      |         |               |
| history_text |          1 | history_text_1 |            1 | itemid      | A         |   161712209 |     NULL | NULL   |      | BTREE      |         |               |
| history_text |          1 | history_text_1 |            2 | clock       | A         |   161712209 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (10.90 sec)

I am going to create index for just "id" field and see how it help.

Thank you for idea.

[Pada]

I find that PRIMARY key index of yours very strange!
Seeing that the primary key isn't JUST the ID column, it would definitely result in that query taking that long with 160 million rows!

Like my DB with Zabbix 2.1.5 uses the exact same indexes as with 1.8.11:

mysql> show indexes from history_text;
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| history_text |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| history_text |          0 | history_text_2 |            1 | itemid      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| history_text |          0 | history_text_2 |            2 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| history_text |          1 | history_text_1 |            1 | itemid      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| history_text |          1 | history_text_1 |            2 | clock       | A         |           0 |     NULL | NULL   |      | BTREE      |         |

Perhaps one of the developers should have a look into this issue.

Did you by any chance do an upgrade from another version(s), or did you do a clean install to 2.0.5?
Also, did you modify some of the indexes yourself before you had this issue?