cloudstack-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
Date Mon, 11 Apr 2016 01:37:25 GMT

    [ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234398#comment-15234398
] 

ASF GitHub Bot commented on CLOUDSTACK-9340:
--------------------------------------------

Github user rafaelweingartner commented on a diff in the pull request:

    https://github.com/apache/cloudstack/pull/1466#discussion_r59146860
  
    --- Diff: setup/db/db/schema-481to490.sql ---
    @@ -413,3 +413,89 @@ VIEW `user_vm_view` AS
     
     -- Add cluster.storage.operations.exclude property
     INSERT INTO `cloud`.`configuration` (`category`, `instance`, `component`, `name`, `description`,
`default_value`, `updated`, `scope`, `is_dynamic`) VALUES ('Advanced', 'DEFAULT', 'CapacityManager',
'cluster.storage.operations.exclude', '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` 
    +DROP PRIMARY KEY,
    +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
one?
    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: https://issues.apache.org/jira/browse/CLOUDSTACK-9340
>             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
(v6.3.4#6332)

Mime
View raw message