[#ZBX-1583] zabbix DB schema contains duplicated indexes for some tables

Comment by Alexey Korepov [ 2010 Mar 17 ]

Same problem after upgrading from 1.6 to 1.8 version.
Which index is right and which I can remove?

Comment by Alexei Vladishev [ 2012 Feb 18 ]

Please can somebody explain what kind of problem we have here?

Comment by Alexei Vladishev [ 2012 Sep 06 ]

There is nothing to fix, closing.

Comment by Oleksii Zagorskyi [ 2012 Sep 07 ]

but ... that duplicated indexes still are in 2.0 DB !
I think they are redundant and we could remove them.

Sorry, REOPENED.

Comment by Alexei Vladishev [ 2012 Sep 07 ]

Oleksiy, what duplicate indexes? Please give me an example.

Comment by Oleksii Zagorskyi [ 2012 Sep 08 ]

In the schema.sql we have:

CREATE TABLE `history_str_sync` (
        `id`                     bigint unsigned                           NOT NULL auto_increment unique,
        `nodeid`                 integer                                   NOT NULL,
        `itemid`                 bigint unsigned                           NOT NULL,
        `clock`                  integer         DEFAULT '0'               NOT NULL,
        `value`                  varchar(255)    DEFAULT ''                NOT NULL,
        `ns`                     integer         DEFAULT '0'               NOT NULL,
        PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE INDEX `history_str_sync_1` ON `history_str_sync` (`nodeid`,`id`);

From 2.0.2 sources I've just created zabbix DB with the schema, then:

mysql> SHOW CREATE TABLE history_str_sync;
| history_str_sync | CREATE TABLE `history_str_sync` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `nodeid` int(11) NOT NULL,
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` varchar(255) NOT NULL DEFAULT '',
  `ns` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `history_str_sync_1` (`nodeid`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Note there:

PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)

Latest phpMyAdmin 3.5.0 shows a warning:

The indexes PRIMARY and id seem to be equal and one of them could possibly be removed.

If create a table without indexes, one of indexes will be created automatically
because of the "unique" option, which is required for "auto_increment" option:

mysql> SHOW CREATE TABLE history_str_sync;
| history_str_sync | CREATE TABLE `history_str_sync` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `nodeid` int(11) NOT NULL,
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` varchar(255) NOT NULL DEFAULT '',
  `ns` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Just in case - for a SQL command:

ALTER TABLE `history_str_sync` DROP PRIMARY KEY 
MySQL said:  
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

My box used in this test:

# mysql --version
mysql  Ver 14.14 Distrib 5.5.24, for debian-linux-gnu (x86_64) using readline 6.1

Note that options "auto_increment unique" we have only for those 6 tables.

I suppose schema generation should be improved and we should drop 3 unneeded indexes in scripts upgrade (at least for MySQL) for tables proxy_autoreg_host, proxy_dhistory, proxy_history

Other 3 tables will be dropped according to old-style DM removal.

Comment by Florian Gusinde [ 2013 Jun 01 ]

I think there are lots of strange indexes so take this:



    – zabbix.events

    – Key events_1 ends with a prefix of the clustered index
    – Key definitions:
    –   KEY `events_1` (`object`,`objectid`,`eventid`),
    –   PRIMARY KEY (`eventid`),
    – Column types:
    –      `object` int(11) not null default ’0′
    –      `objectid` bigint(20) unsigned not null default ’0′
    –      `eventid` bigint(20) unsigned not null
    – To shorten this duplicate clustered index, execute:
    ALTER TABLE `zabbix`.`events` DROP INDEX `events_1`, ADD INDEX `events_1` (`object`,`objectid`);
    – zabbix.history_log

    – Key history_log_2 ends with a prefix of the clustered index
    – Key definitions:
    –   UNIQUE KEY `history_log_2` (`itemid`,`id`),
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `itemid` bigint(20) unsigned not null
    –      `id` bigint(20) unsigned not null
    – To shorten this duplicate clustered index, execute:
    ALTER TABLE `zabbix`.`history_log` DROP INDEX `history_log_2`, ADD INDEX `history_log_2` (`itemid`);
    – zabbix.history_str_sync

    – id is a duplicate of PRIMARY
    – Key definitions:
    –   UNIQUE KEY `id` (`id`),
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `id` bigint(20) unsigned not null auto_increment
    – To remove this duplicate index, execute:
    ALTER TABLE `zabbix`.`history_str_sync` DROP INDEX `id`;

    – Key history_str_sync_1 ends with a prefix of the clustered index
    – Key definitions:
    –   KEY `history_str_sync_1` (`nodeid`,`id`)
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `nodeid` int(11) not null
    –      `id` bigint(20) unsigned not null auto_increment
    – To shorten this duplicate clustered index, execute:
    ALTER TABLE `zabbix`.`history_str_sync` DROP INDEX `history_str_sync_1`, ADD INDEX `history_str_sync_1` (`nodeid`);
    – zabbix.history_sync

    – id is a duplicate of PRIMARY
    – Key definitions:
    –   UNIQUE KEY `id` (`id`),
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `id` bigint(20) unsigned not null auto_increment
    – To remove this duplicate index, execute:
    ALTER TABLE `zabbix`.`history_sync` DROP INDEX `id`;

    – Key history_sync_1 ends with a prefix of the clustered index
    – Key definitions:
    –   KEY `history_sync_1` (`nodeid`,`id`)
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `nodeid` int(11) not null
    –      `id` bigint(20) unsigned not null auto_increment
    – To shorten this duplicate clustered index, execute:
    ALTER TABLE `zabbix`.`history_sync` DROP INDEX `history_sync_1`, ADD INDEX `history_sync_1` (`nodeid`);
    – zabbix.history_text

    – Key history_text_2 ends with a prefix of the clustered index
    – Key definitions:
    –   UNIQUE KEY `history_text_2` (`itemid`,`id`),
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `itemid` bigint(20) unsigned not null
    –      `id` bigint(20) unsigned not null
    – To shorten this duplicate clustered index, execute:
    ALTER TABLE `zabbix`.`history_text` DROP INDEX `history_text_2`, ADD INDEX `history_text_2` (`itemid`);
    – zabbix.history_uint_sync

    – id is a duplicate of PRIMARY
    – Key definitions:
    –   UNIQUE KEY `id` (`id`),
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `id` bigint(20) unsigned not null auto_increment
    – To remove this duplicate index, execute:
    ALTER TABLE `zabbix`.`history_uint_sync` DROP INDEX `id`;

    – Key history_uint_sync_1 ends with a prefix of the clustered index
    – Key definitions:
    –   KEY `history_uint_sync_1` (`nodeid`,`id`)
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `nodeid` int(11) not null
    –      `id` bigint(20) unsigned not null auto_increment
    – To shorten this duplicate clustered index, execute:
    ALTER TABLE `zabbix`.`history_uint_sync` DROP INDEX `history_uint_sync_1`, ADD INDEX `history_uint_sync_1` (`nodeid`);
    – zabbix.profiles

    – Key profiles_2 ends with a prefix of the clustered index
    – Key definitions:
    –   KEY `profiles_2` (`userid`,`profileid`),
    –   PRIMARY KEY (`profileid`),
    – Column types:
    –      `userid` bigint(20) unsigned not null
    –      `profileid` bigint(20) unsigned not null
    – To shorten this duplicate clustered index, execute:
    ALTER TABLE `zabbix`.`profiles` DROP INDEX `profiles_2`, ADD INDEX `profiles_2` (`userid`);
    – zabbix.proxy_autoreg_host

    – id is a duplicate of PRIMARY
    – Key definitions:
    –   UNIQUE KEY `id` (`id`),
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `id` bigint(20) unsigned not null auto_increment
    – To remove this duplicate index, execute:
    ALTER TABLE `zabbix`.`proxy_autoreg_host` DROP INDEX `id`;
    – zabbix.proxy_dhistory

    – id is a duplicate of PRIMARY
    – Key definitions:
    –   UNIQUE KEY `id` (`id`),
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `id` bigint(20) unsigned not null auto_increment
    – To remove this duplicate index, execute:
    ALTER TABLE `zabbix`.`proxy_dhistory` DROP INDEX `id`;
    – zabbix.proxy_history

    – id is a duplicate of PRIMARY
    – Key definitions:
    –   UNIQUE KEY `id` (`id`),
    –   PRIMARY KEY (`id`),
    – Column types:
    –      `id` bigint(20) unsigned not null auto_increment
    – To remove this duplicate index, execute:
    ALTER TABLE `zabbix`.`proxy_history` DROP INDEX `id`;
    – Summary of indexes

    – Size Duplicate Indexes   852
    – Total Duplicate Indexes  13
    – Total Indexes            389

Version: Zabbix server v2.0.6 (revision 35158) (22 April 2013)

Regards, Florian

Comment by Florian Gusinde [ 2013 Jun 01 ]

Also in Zabbix server v2.0.4rc1 (revision 31511) (03 October 2012):


-- ########################################################################
-- zabbix.events                                                           
-- ########################################################################

-- Key events_1 ends with a prefix of the clustered index
-- Key definitions:
--   KEY `events_1` (`object`,`objectid`,`eventid`),
--   PRIMARY KEY (`eventid`),
-- Column types:
--	  `object` int(11) not null default '0'
--	  `objectid` bigint(20) unsigned not null default '0'
--	  `eventid` bigint(20) unsigned not null
-- To shorten this duplicate clustered index, execute:
ALTER TABLE `zabbix`.`events` DROP INDEX `events_1`, ADD INDEX `events_1` (`object`,`objectid`);

-- ########################################################################
-- zabbix.history_log                                                      
-- ########################################################################

-- Key history_log_2 ends with a prefix of the clustered index
-- Key definitions:
--   UNIQUE KEY `history_log_2` (`itemid`,`id`),
--   PRIMARY KEY (`id`),
-- Column types:
--	  `itemid` bigint(20) unsigned not null
--	  `id` bigint(20) unsigned not null
-- To shorten this duplicate clustered index, execute:
ALTER TABLE `zabbix`.`history_log` DROP INDEX `history_log_2`, ADD INDEX `history_log_2` (`itemid`);

-- ########################################################################
-- zabbix.history_str_sync                                                 
-- ########################################################################

-- id is a duplicate of PRIMARY
-- Key definitions:
--   UNIQUE KEY `id` (`id`),
--   PRIMARY KEY (`id`),
-- Column types:
--	  `id` bigint(20) unsigned not null auto_increment
-- To remove this duplicate index, execute:
ALTER TABLE `zabbix`.`history_str_sync` DROP INDEX `id`;

-- Key history_str_sync_1 ends with a prefix of the clustered index
-- Key definitions:
--   KEY `history_str_sync_1` (`nodeid`,`id`)
--   PRIMARY KEY (`id`),
-- Column types:
--	  `nodeid` int(11) not null
--	  `id` bigint(20) unsigned not null auto_increment
-- To shorten this duplicate clustered index, execute:
ALTER TABLE `zabbix`.`history_str_sync` DROP INDEX `history_str_sync_1`, ADD INDEX `history_str_sync_1` (`nodeid`);

-- ########################################################################
-- zabbix.history_sync                                                     
-- ########################################################################

-- id is a duplicate of PRIMARY
-- Key definitions:
--   UNIQUE KEY `id` (`id`),
--   PRIMARY KEY (`id`),
-- Column types:
--	  `id` bigint(20) unsigned not null auto_increment
-- To remove this duplicate index, execute:
ALTER TABLE `zabbix`.`history_sync` DROP INDEX `id`;

-- Key history_sync_1 ends with a prefix of the clustered index
-- Key definitions:
--   KEY `history_sync_1` (`nodeid`,`id`)
--   PRIMARY KEY (`id`),
-- Column types:
--	  `nodeid` int(11) not null
--	  `id` bigint(20) unsigned not null auto_increment
-- To shorten this duplicate clustered index, execute:
ALTER TABLE `zabbix`.`history_sync` DROP INDEX `history_sync_1`, ADD INDEX `history_sync_1` (`nodeid`);

-- ########################################################################
-- zabbix.history_text                                                     
-- ########################################################################

-- Key history_text_2 ends with a prefix of the clustered index
-- Key definitions:
--   UNIQUE KEY `history_text_2` (`itemid`,`id`),
--   PRIMARY KEY (`id`),
-- Column types:
--	  `itemid` bigint(20) unsigned not null
--	  `id` bigint(20) unsigned not null
-- To shorten this duplicate clustered index, execute:
ALTER TABLE `zabbix`.`history_text` DROP INDEX `history_text_2`, ADD INDEX `history_text_2` (`itemid`);

-- ########################################################################
-- zabbix.history_uint_sync                                                
-- ########################################################################

-- id is a duplicate of PRIMARY
-- Key definitions:
--   UNIQUE KEY `id` (`id`),
--   PRIMARY KEY (`id`),
-- Column types:
--	  `id` bigint(20) unsigned not null auto_increment
-- To remove this duplicate index, execute:
ALTER TABLE `zabbix`.`history_uint_sync` DROP INDEX `id`;

-- Key history_uint_sync_1 ends with a prefix of the clustered index
-- Key definitions:
--   KEY `history_uint_sync_1` (`nodeid`,`id`)
--   PRIMARY KEY (`id`),
-- Column types:
--	  `nodeid` int(11) not null
--	  `id` bigint(20) unsigned not null auto_increment
-- To shorten this duplicate clustered index, execute:
ALTER TABLE `zabbix`.`history_uint_sync` DROP INDEX `history_uint_sync_1`, ADD INDEX `history_uint_sync_1` (`nodeid`);

-- ########################################################################
-- zabbix.profiles                                                         
-- ########################################################################

-- Key profiles_2 ends with a prefix of the clustered index
-- Key definitions:
--   KEY `profiles_2` (`userid`,`profileid`),
--   PRIMARY KEY (`profileid`),
-- Column types:
--	  `userid` bigint(20) unsigned not null
--	  `profileid` bigint(20) unsigned not null
-- To shorten this duplicate clustered index, execute:
ALTER TABLE `zabbix`.`profiles` DROP INDEX `profiles_2`, ADD INDEX `profiles_2` (`userid`);

-- ########################################################################
-- zabbix.proxy_autoreg_host                                               
-- ########################################################################

-- id is a duplicate of PRIMARY
-- Key definitions:
--   UNIQUE KEY `id` (`id`),
--   PRIMARY KEY (`id`),
-- Column types:
--	  `id` bigint(20) unsigned not null auto_increment
-- To remove this duplicate index, execute:
ALTER TABLE `zabbix`.`proxy_autoreg_host` DROP INDEX `id`;

-- ########################################################################
-- zabbix.proxy_dhistory                                                   
-- ########################################################################

-- id is a duplicate of PRIMARY
-- Key definitions:
--   UNIQUE KEY `id` (`id`),
--   PRIMARY KEY (`id`),
-- Column types:
--	  `id` bigint(20) unsigned not null auto_increment
-- To remove this duplicate index, execute:
ALTER TABLE `zabbix`.`proxy_dhistory` DROP INDEX `id`;

-- ########################################################################
-- zabbix.proxy_history                                                    
-- ########################################################################

-- id is a duplicate of PRIMARY
-- Key definitions:
--   UNIQUE KEY `id` (`id`),
--   PRIMARY KEY (`id`),
-- Column types:
--	  `id` bigint(20) unsigned not null auto_increment
-- To remove this duplicate index, execute:
ALTER TABLE `zabbix`.`proxy_history` DROP INDEX `id`;

-- ########################################################################
-- Summary of indexes                                                      
-- ########################################################################

-- Size Duplicate Indexes   123903432
-- Total Duplicate Indexes  13
-- Total Indexes            389

Comment by Igors Homjakovs (Inactive) [ 2013 Aug 14 ]

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-1583

Comment by Alexander Vladishev [ 2013 Aug 14 ]

Successfully tested!

Please review my changes in r37824.

Comment by richlv [ 2013 Aug 14 ]

(1) let's document in whatsnew which features should work a bit faster and use less db space now

igorsh https://www.zabbix.com/documentation/2.2/manual/introduction/whatsnew220

RESOLVED

sasha CLOSED

<richlv> well, crap. i wrote a lengthy comment with suggested description for whatsnew, but issue was closed before i managed to save it, and i lost it all :/
the added note is very vague and does not explain anything about features that would be affected. looking at the diff, this affects the following tables :

*_sync
proxy_history
proxy_dhistory
proxy_autoreg_host

as such we could say that :
"Redundant indexes were removed in several Zabbix MySQL database tables. This should improve performance and slightly reduce database size for MySQL users in these cases:

child nodes in distributed mode
Zabbix proxy value collection, network discovery and active agent autoregistration data pocessing"

does that sound about right ?

igorsh Rich, What's new was fixed as you had suggested.

RESOLVED

<richlv> maartinjsh improved it further -> CLOSED

Comment by Alexander Vladishev [ 2013 Aug 14 ]

Florian,

I created a new issue about redundant suffixes in clustered indexes. In will be fixed under ZBX-6892.

Thank you.

Comment by Igors Homjakovs (Inactive) [ 2013 Aug 14 ]

Fixed in 2.1.2 (trunk) r37832

[ZBX-1583] zabbix DB schema contains duplicated indexes for some tables Created: 2009 Dec 21 Updated: 2017 May 30 Resolved: 2013 Aug 16
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Server (S)
Affects Version/s:	1.8, 2.0.2
Fix Version/s:	2.2.0

[ZBX-1583] zabbix DB schema contains duplicated indexes for some tables Created: 2009 Dec 21 Updated: 2017 May 30 Resolved: 2013 Aug 16