Skip to content
This repository has been archived by the owner. It is now read-only.

[dev.icinga.com #3018] Redundant indexes in MySQL schema. #1087

Closed
icinga-migration opened this issue Aug 25, 2012 · 7 comments
Closed

[dev.icinga.com #3018] Redundant indexes in MySQL schema. #1087

icinga-migration opened this issue Aug 25, 2012 · 7 comments
Labels
Milestone

Comments

@icinga-migration
Copy link
Member

@icinga-migration icinga-migration commented Aug 25, 2012

This issue has been migrated from Redmine: https://dev.icinga.com/issues/3018

Created by robe on 2012-08-25 14:47:28 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2012-09-23 09:57:47 +00:00)
Target Version: 1.8
Last Update: 2014-12-08 14:37:49 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

Attachments

Changesets

2012-09-16 15:35:05 +00:00 by mfriedrich be502d8

idoutils: fix redundant indexes in MySQL schema (thx Michael Renner) #3018 - MF

refs #3018
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Aug 25, 2012

Updated by robe on 2012-08-25 15:01:05 +00:00

In #1173 the default type for all PRIMARY KEY columns was changed to serial. This causes MySQL to create two indexes over the same column - one for the serial column type and one for enforcing the PRIMARY KEY.

mysql> show create table icinga_hoststatus \G
       Table: icinga_hoststatus
Create Table: CREATE TABLE `icinga_hoststatus` (
  `hoststatus_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
[..]
  PRIMARY KEY (`hoststatus_id`),
  UNIQUE KEY `hoststatus_id` (`hoststatus_id`),
[..]
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Current host status information'

mysql> show index from icinga_hoststatus \G
*************************** 1. row ***************************
        Table: icinga_hoststatus
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: hoststatus_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: icinga_hoststatus
   Non_unique: 0
     Key_name: hoststatus_id
 Seq_in_index: 1
  Column_name: hoststatus_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
[..]

This causes wasted disk space and increased IOPS when writing to the tables in question. Please revert all serial PRIMARY KEY columns to

bigint(20) unsigned NOT NULL AUTO_INCREMENT

and keep the PRIMARY KEY in place.

See http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html for SERIAL and BIGINT documentation and http://dev.mysql.com/doc/refman/5.5/en/create-table.html for the PRIMARY KEY implications.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Aug 26, 2012

Updated by mfriedrich on 2012-08-26 00:08:57 +00:00

  • Status changed from New to Assigned
  • Assigned to set to robe
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Sep 16, 2012

Updated by robe on 2012-09-16 14:17:12 +00:00

  • Assigned to changed from robe to mfriedrich

Patch forwarded to MichaelF

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Sep 16, 2012

Updated by mfriedrich on 2012-09-16 14:30:24 +00:00

  • File added mysql_index.patch
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Sep 16, 2012

Updated by mfriedrich on 2012-09-16 15:40:05 +00:00

  • Status changed from Assigned to 7
  • Done % changed from 0 to 100

up in my mfriedrich/ido branch - thanks Michael for the patch.

dropping indexes might take a bit, so if users complain that the script takes too long, just tell them the url to this issue (or check what the upgrade script does).

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Sep 23, 2012

Updated by mfriedrich on 2012-09-23 09:57:47 +00:00

  • Status changed from 7 to Resolved
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Dec 8, 2014

Updated by mfriedrich on 2014-12-08 14:37:49 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category changed from 112 to IDOUtils
  • Icinga Version changed from 1 to 1
  • OS Version set to any
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
1 participant
You can’t perform that action at this time.