cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rafaelweingartner <...@git.apache.org>
Subject [GitHub] cloudstack pull request: CLOUDSTACK-9340: General DB Optimization
Date Mon, 11 Apr 2016 01:39:05 GMT
Github user rafaelweingartner commented on a diff in the pull request:

    https://github.com/apache/cloudstack/pull/1466#discussion_r59146919
  
    --- 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` ;
    +ALTER TABLE `cloud`.`domain_router` DROP INDEX `id_2` ,DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`vm_instance` DROP INDEX `id_2` ,DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`account_vlan_map` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`account_vnet_map` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`baremetal_rct` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`cluster` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`conditions` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`counter` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`data_center` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`dc_storage_network_ip_range` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`dedicated_resources` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`host_pod_ref` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`iam_group` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`iam_policy` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`iam_policy_permission` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`image_store_details` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`instance_group` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`netapp_lun` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`netapp_pool` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`netapp_volume` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`network_acl_item_cidrs` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`network_offerings` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`nic_secondary_ips` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`nics` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`op_ha_work` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`op_host` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`op_host_transfer` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`op_networks` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`op_nwgrp_work` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`op_vm_ruleset_log` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`op_vpc_distributed_router_sequence_no` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`pod_vlan_map` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`portable_ip_address` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`portable_ip_range` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`region` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`remote_access_vpn` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`snapshot_details` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`snapshots` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`storage_pool` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`storage_pool_details` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`storage_pool_work` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`user_ip_address` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`user_ipv6_address` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`user_statistics` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`version` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`vlan` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`vm_disk_statistics` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`vm_snapshot_details` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`vm_work_job` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`vpc_gateways` DROP INDEX `id` ;
    +ALTER TABLE `cloud`.`vpn_users` DROP INDEX `id` ;
    +
    +----- 3) Missing indexes (Add indexes to avoid full table scans)
    --- End diff --
    
    did you execute some evaluation to see which fields were being most used to create an
index for them?
    
    For instance, why did you create an index for the field called "type" of table "op_it_work"


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

Mime
View raw message