[
https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234400#comment-15234400
]
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_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"
> 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)
