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 Mon, 11 Apr 2016 01:37:25 GMT


ASF GitHub Bot commented on CLOUDSTACK-9340:

Github user rafaelweingartner commented on a diff in the pull request:
    --- Diff: setup/db/db/schema-481to490.sql ---
    @@ -413,3 +413,89 @@ VIEW `user_vm_view` AS
     -- Add property
     INSERT INTO `cloud`.`configuration` (`category`, `instance`, `component`, `name`, `description`,
`default_value`, `updated`, `scope`, `is_dynamic`) VALUES ('Advanced', 'DEFAULT', 'CapacityManager',
'', 'Exclude cluster from storage operations', 'false',
now(), 'Cluster', '1');
    +-- Added in CLOUDSTACK-9340: General DB optimization, 4 cases:
    +----- 1) Incorrect PRIMARY key
    +ALTER TABLE `cloud`.`ovs_tunnel_network` 
    +ADD PRIMARY KEY (`id`),
    +DROP INDEX `id` ,
    +ADD UNIQUE INDEX `i_to_from_network_id` (`to` ASC, `from` ASC, `network_id` ASC);
    +----- 2) Duplicate PRIMARY KEY
    +ALTER TABLE `cloud`.`user_vm` DROP INDEX `id_2` ,DROP INDEX `id` ;
    --- End diff --
    Here you remove duplicated primary keys.
    You are removing indexes and not primary keys per se, right?
    I see you removing two indexes, does that mean that the table has another one? A third
    Because if you remove the ID field index, if we use a select by that field, it would cause
a full table scan, right?

> 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