cloudstack-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <>
Subject [jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
Date Sun, 10 Apr 2016 14:36:25 GMT


ASF GitHub Bot commented on CLOUDSTACK-9340:

Github user nvazquez commented on the pull request:
    @bhaisaab Most of the indexes being dropped are duplicate. In create-schema.sql quite
a lot of tables are created like this:
    CREATE TABLE `cloud`.`version` (
    `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id',
    `version` char(40) NOT NULL UNIQUE COMMENT 'version',
    `updated` datetime NOT NULL COMMENT 'Date this version table was updated',
    `step` char(32) NOT NULL COMMENT 'Step in the upgrade to this version',
    PRIMARY KEY (`id`),
    INDEX `i_version__version`(`version`)
    When column ID is marked as UNIQUE MySQL creates an unique index ID. Then below a primary
key ID is defined. Primary key in its nature already guarantees uniqueness so it is safe to
drop index ID leaving only primary key to remain.
    There are more than 50 tables like this plus 3 more tables where unique index based on
ID column was created twice. For example for domain_router table one duplicate index was created
initially in create-schema.sql and another duplicate was generated in schema-442to450.sql
when the following line was executed:
    ALTER TABLE `cloud`.`domain_router` MODIFY `id` bigint unsigned AUTO_INCREMENT UNIQUE

> General DB Optimization
> -----------------------
>                 Key: CLOUDSTACK-9340
>                 URL:
>             Project: CloudStack
>          Issue Type: Improvement
>      Security Level: Public(Anyone can view this level - this is the default.) 
>    Affects Versions: 4.9.0
>            Reporter: Nicolas Vazquez
>              Labels: database
>             Fix For: 4.9.0
> h2. General DB Optimization
> In some production environments there were being experimented delays in most of the jobs.
A search for DB optimization was taken and some deficiencies were discovered, we can group
them in 4 groups:
> * Incorrect PRIMARY key
> * Duplicate PRIMARY KEY
> * Missing indexes (Add indexes to avoid full table scans)
> * Some views query (Change view to improve account retrieval speed)

This message was sent by Atlassian JIRA

View raw message