Primary key indexes on history and history_uint

[davidbey]

I've been migrating one zabbix server installation and I've found a so big database (mysql).
First thing I've done was optimize tables, but big ones (history and history_uint) with something about 110465752 rows failed the process.
So I've created a similar table with autonumeric primary key (last column) and imported one dump

Not only was able to optimize the table, it is so fast compared with the one without primary key. So I want to propose to include primary keys on those tables.


MariaDB [zabbix]> desc history;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| itemid | bigint(20) unsigned | NO | MUL | NULL | |
| clock | int(11) | NO | | 0 | |
| value | double(16,4) | NO | | 0.0000 | |
| ns | int(11) | NO | | 0 | |
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)

MariaDB [zabbix]> optimize table history;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history | optimize | status | OK |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (11 min 40.757 sec)

MariaDB [zabbix]> show index from history;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| history | 0 | PRIMARY | 1 | id | A | 110465752 | NULL | NULL | | BTREE | | |
| history | 1 | history_1 | 1 | itemid | A | 293791 | NULL | NULL | | BTREE | | |
| history | 1 | history_1 | 2 | clock | A | 110465752 | NULL | NULL | | BTREE | | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.002 sec)

MariaDB [zabbix]> select count(*) from history;
+-----------+
| count(*) |
+-----------+
| 110804374 |
+-----------+
1 row in set (1 min 3.972 sec)

MariaDB [zabbix]> drop table history;
Query OK, 0 rows affected (0.038 sec)

MariaDB [zabbix]> rename table historybak to history;
Query OK, 0 rows affected (0.029 sec)

MariaDB [zabbix]> select count(*) from history;
+-----------+
| count(*) |
+-----------+
| 110804374 |
+-----------+
1 row in set (3 min 7.416 sec)

[toodark]

I would like to upvote this post.
Having a primary key is quite important with the MySQL InnoDB engine, because data gets physically sorted by the primary key. This can drastically speed up reads and often writes when using HDDs

Instead of this create:

CREATE TABLE `history_uint` (
`itemid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`value` bigint unsigned DEFAULT '0' NOT NULL,
`ns` integer DEFAULT '0' NOT NULL
) ENGINE=InnoDB;
CREATE INDEX `history_uint_1` ON `history_uint` (`itemid`,`clock`);

similar could be more effective:

CREATE TABLE `history_uint` (
`itemid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`value` bigint unsigned DEFAULT '0' NOT NULL,
`ns` integer DEFAULT '0' NOT NULL,
PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB;

Besides that a lot of space could be spared, because this would create only a single index instead of the current two (auto generated primary key and, and the optional primary key)

Is there any reason why primary keys are not used here?

[Hamardaban]

And you create such a key in your database and see how it will work on big data.
There are reasons...

[cyber]

Forum is not for proposals. Please head to support.zabbix.com and submit a feature request.

https://support.zabbix.com/projects/ZBXNEXT/summary

[Saigets]

primary key will give unique constraint. what if you have received storm of simultaneous traps?