From 1259ed685535e246bf7c0ec8d3a3d330f7df9596 Mon Sep 17 00:00:00 2001 From: Justin Shepherd Date: Tue, 7 Aug 2012 03:12:28 +0000 Subject: Adding indexes to frequently joined database columns. Fixes bug 1006624 Change-Id: I0f1f6903a29cb8d3573987b88f62cd895a11c100 --- .../versions/118_add_indexes_to_agent_builds.py | 44 +++++++++ .../119_add_indexes_to_aggregate_metadata.py | 42 ++++++++ .../120_add_indexes_to_block_device_mapping.py | 71 ++++++++++++++ .../versions/121_add_indexes_to_bw_usage_cache.py | 44 +++++++++ .../versions/122_add_indexes_to_certificates.py | 59 ++++++++++++ .../versions/123_add_indexes_to_dns_domains.py | 44 +++++++++ .../versions/124_add_indexes_to_fixed_ips.py | 76 +++++++++++++++ .../versions/125_add_indexes_to_floating_ips.py | 68 +++++++++++++ .../versions/126_add_indexes_to_instance_faults.py | 44 +++++++++ ...127_add_indexes_to_instance_type_extra_specs.py | 44 +++++++++ .../versions/128_add_indexes_to_instances.py | 96 ++++++++++++++++++ .../versions/129_add_indexes_to_iscsi_targets.py | 57 +++++++++++ .../versions/130_add_indexes_to_key_pairs.py | 44 +++++++++ .../versions/131_add_indexes_to_networks.py | 107 +++++++++++++++++++++ 14 files changed, 840 insertions(+) create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/118_add_indexes_to_agent_builds.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/119_add_indexes_to_aggregate_metadata.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/120_add_indexes_to_block_device_mapping.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/121_add_indexes_to_bw_usage_cache.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/122_add_indexes_to_certificates.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/123_add_indexes_to_dns_domains.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/124_add_indexes_to_fixed_ips.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/125_add_indexes_to_floating_ips.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/126_add_indexes_to_instance_faults.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/127_add_indexes_to_instance_type_extra_specs.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/128_add_indexes_to_instances.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/129_add_indexes_to_iscsi_targets.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/130_add_indexes_to_key_pairs.py create mode 100644 nova/db/sqlalchemy/migrate_repo/versions/131_add_indexes_to_networks.py (limited to 'nova') diff --git a/nova/db/sqlalchemy/migrate_repo/versions/118_add_indexes_to_agent_builds.py b/nova/db/sqlalchemy/migrate_repo/versions/118_add_indexes_to_agent_builds.py new file mode 100644 index 000000000..23f7d3cdb --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/118_add_indexes_to_agent_builds.py @@ -0,0 +1,44 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + # Based on agent_build_get_by_triple + # from: nova/db/sqlalchemy/api.py + t = Table('agent_builds', meta, autoload=True) + i = Index('agent_builds_hypervisor_os_arch_idx', + t.c.hypervisor, t.c.os, t.c.architecture) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('agent_builds', meta, autoload=True) + i = Index('agent_builds_hypervisor_os_arch_idx', + t.c.hypervisor, t.c.os, t.c.architecture) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/119_add_indexes_to_aggregate_metadata.py b/nova/db/sqlalchemy/migrate_repo/versions/119_add_indexes_to_aggregate_metadata.py new file mode 100644 index 000000000..0e819a59d --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/119_add_indexes_to_aggregate_metadata.py @@ -0,0 +1,42 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + # Based on aggregate_metadata_get_item + # from: nova/db/sqlalchemy/api.py + t = Table('aggregate_metadata', meta, autoload=True) + i = Index('aggregate_metadata_key_idx', t.c.key) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('aggregate_metadata', meta, autoload=True) + i = Index('aggregate_metadata_key_idx', t.c.key) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/120_add_indexes_to_block_device_mapping.py b/nova/db/sqlalchemy/migrate_repo/versions/120_add_indexes_to_block_device_mapping.py new file mode 100644 index 000000000..432fd91a0 --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/120_add_indexes_to_block_device_mapping.py @@ -0,0 +1,71 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('block_device_mapping', meta, autoload=True) + + # Based on block_device_mapping_update_or_create + # from: nova/db/sqlalchemy/api.py + i = Index('block_device_mapping_instance_uuid_device_name_idx', + t.c.instance_uuid, t.c.device_name) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on block_device_mapping_update_or_create + # from: nova/db/sqlalchemy/api.py + i = Index( + 'block_device_mapping_instance_uuid_virtual_name_device_name_idx', + t.c.instance_uuid, t.c.virtual_name, t.c.device_name) + i.create(migrate_engine) + + # Based on block_device_mapping_destroy_by_instance_and_volume + # from: nova/db/sqlalchemy/api.py + i = Index('block_device_mapping_instance_uuid_volume_id_idx', + t.c.instance_uuid, t.c.volume_id) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('block_device_mapping', meta, autoload=True) + + i = Index('block_device_mapping_instance_uuid_device_name_idx', + t.c.instance_uuid, t.c.device_name) + i.drop(migrate_engine) + + i = Index( + 'block_device_mapping_instance_uuid_virtual_name_device_name_idx', + t.c.instance_uuid, t.c.virtual_name, t.c.device_name) + i.drop(migrate_engine) + + i = Index('block_device_mapping_instance_uuid_volume_id_idx', + t.c.instance_uuid, t.c.volume_id) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/121_add_indexes_to_bw_usage_cache.py b/nova/db/sqlalchemy/migrate_repo/versions/121_add_indexes_to_bw_usage_cache.py new file mode 100644 index 000000000..fcbe49061 --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/121_add_indexes_to_bw_usage_cache.py @@ -0,0 +1,44 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + # Based on bw_usage_get_by_uuids + # from: nova/db/sqlalchemy/api.py + t = Table('bw_usage_cache', meta, autoload=True) + i = Index('bw_usage_cache_uuid_start_period_idx', + t.c.uuid, t.c.start_period) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('bw_usage_cache', meta, autoload=True) + i = Index('bw_usage_cache_uuid_start_period_idx', + t.c.uuid, t.c.start_period) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/122_add_indexes_to_certificates.py b/nova/db/sqlalchemy/migrate_repo/versions/122_add_indexes_to_certificates.py new file mode 100644 index 000000000..1201ce6be --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/122_add_indexes_to_certificates.py @@ -0,0 +1,59 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('certificates', meta, autoload=True) + + # Based on certificate_get_all_by_project + # from: nova/db/sqlalchemy/api.py + i = Index('certificates_project_id_deleted_idx', + t.c.project_id, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on certificate_get_all_by_user + # from: nova/db/sqlalchemy/api.py + i = Index('certificates_user_id_deleted_idx', + t.c.user_id, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('certificates', meta, autoload=True) + + i = Index('certificates_project_id_deleted_idx', + t.c.project_id, t.c.deleted) + i.drop(migrate_engine) + + i = Index('certificates_user_id_deleted_idx', + t.c.user_id, t.c.deleted) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/123_add_indexes_to_dns_domains.py b/nova/db/sqlalchemy/migrate_repo/versions/123_add_indexes_to_dns_domains.py new file mode 100644 index 000000000..6bc0aed91 --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/123_add_indexes_to_dns_domains.py @@ -0,0 +1,44 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + # Based on _dnsdomain_get + # from: nova/db/sqlalchemy/api.py + t = Table('dns_domains', meta, autoload=True) + i = Index('dns_domains_domain_deleted_idx', + t.c.domain, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('dns_domains', meta, autoload=True) + i = Index('dns_domains_domain_deleted_idx', + t.c.domain, t.c.deleted) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/124_add_indexes_to_fixed_ips.py b/nova/db/sqlalchemy/migrate_repo/versions/124_add_indexes_to_fixed_ips.py new file mode 100644 index 000000000..0ae4a4d51 --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/124_add_indexes_to_fixed_ips.py @@ -0,0 +1,76 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('fixed_ips', meta, autoload=True) + + # Based on network_get_all_by_host + # from: nova/db/sqlalchemy/api.py + i = Index('fixed_ips_host_idx', t.c.host) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on fixed_ip_get_by_network_host + # from: nova/db/sqlalchemy/api.py + i = Index('fixed_ips_network_id_host_deleted_idx', + t.c.network_id, t.c.host, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on fixed_ip_associate + # from: nova/db/sqlalchemy/api.py + i = Index('fixed_ips_address_reserved_network_id_deleted_idx', + t.c.address, t.c.reserved, t.c.network_id, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('fixed_ips', meta, autoload=True) + + # Based on network_get_all_by_host + # from: nova/db/sqlalchemy/api.py + i = Index('fixed_ips_host_idx', t.c.host) + i.drop(migrate_engine) + + # Based on fixed_ip_get_by_network_host + # from: nova/db/sqlalchemy/api.py + i = Index('fixed_ips_network_id_host_deleted_idx', + t.c.network_id, t.c.host, t.c.deleted) + i.drop(migrate_engine) + + # Based on fixed_ip_associate + # from: nova/db/sqlalchemy/api.py + i = Index('fixed_ips_address_reserved_network_id_deleted_idx', + t.c.address, t.c.reserved, t.c.network_id, t.c.deleted) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/125_add_indexes_to_floating_ips.py b/nova/db/sqlalchemy/migrate_repo/versions/125_add_indexes_to_floating_ips.py new file mode 100644 index 000000000..b953b28b9 --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/125_add_indexes_to_floating_ips.py @@ -0,0 +1,68 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('floating_ips', meta, autoload=True) + + # Based on floating_ip_get_all_by_host + # from: nova/db/sqlalchemy/api.py + i = Index('floating_ips_host_idx', t.c.host) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on floating_ip_get_all_by_project + # from: nova/db/sqlalchemy/api.py + i = Index('floating_ips_project_id_idx', t.c.project_id) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on floating_ip_allocate_address + # from: nova/db/sqlalchemy/api.py + i = Index('floating_ips_pool_deleted_fixed_ip_id_project_id_idx', + t.c.pool, t.c.deleted, t.c.fixed_ip_id, t.c.project_id) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('floating_ips', meta, autoload=True) + + i = Index('floating_ips_host_idx', t.c.host) + i.drop(migrate_engine) + + i = Index('floating_ips_project_id_idx', t.c.project_id) + i.drop(migrate_engine) + + i = Index('floating_ips_pool_deleted_fixed_ip_id_project_id_idx', + t.c.pool, t.c.deleted, t.c.fixed_ip_id, t.c.project_id) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/126_add_indexes_to_instance_faults.py b/nova/db/sqlalchemy/migrate_repo/versions/126_add_indexes_to_instance_faults.py new file mode 100644 index 000000000..3ed8277a6 --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/126_add_indexes_to_instance_faults.py @@ -0,0 +1,44 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + # Based on instance_fault_get_by_instance_uuids + # from: nova/db/sqlalchemy/api.py + t = Table('instance_faults', meta, autoload=True) + i = Index('instance_faults_instance_uuid_deleted_created_at_idx', + t.c.instance_uuid, t.c.deleted, t.c.created_at) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('instance_faults', meta, autoload=True) + i = Index('instance_faults_instance_uuid_deleted_created_at_idx', + t.c.instance_uuid, t.c.deleted, t.c.created_at) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/127_add_indexes_to_instance_type_extra_specs.py b/nova/db/sqlalchemy/migrate_repo/versions/127_add_indexes_to_instance_type_extra_specs.py new file mode 100644 index 000000000..80ef0f983 --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/127_add_indexes_to_instance_type_extra_specs.py @@ -0,0 +1,44 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + # Based on instance_type_extra_specs_get_item + # from: nova/db/sqlalchemy/api.py + t = Table('instance_type_extra_specs', meta, autoload=True) + i = Index('instance_type_extra_specs_instance_type_id_key_idx', + t.c.instance_type_id, t.c.key) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('instance_type_extra_specs', meta, autoload=True) + i = Index('instance_type_extra_specs_instance_type_id_key_idx', + t.c.instance_type_id, t.c.key) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/128_add_indexes_to_instances.py b/nova/db/sqlalchemy/migrate_repo/versions/128_add_indexes_to_instances.py new file mode 100644 index 000000000..a429a7685 --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/128_add_indexes_to_instances.py @@ -0,0 +1,96 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('instances', meta, autoload=True) + + # Based on service_get_all_compute_sorted + # from: nova/db/sqlalchemy/api.py + i = Index('instances_host_deleted_idx', + t.c.host, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on instance_get_all_by_reservation + # from: nova/db/sqlalchemy/api.py + i = Index('instances_reservation_id_idx', t.c.reservation_id) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on instance_get_active_by_window + # from: nova/db/sqlalchemy/api.py + i = Index('instances_terminated_at_launched_at_idx', + t.c.terminated_at, t.c.launched_at) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on security_group_in_use + # from: nova/db/sqlalchemy/api.py + i = Index('instances_uuid_deleted_idx', + t.c.uuid, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on instance_get_all_hung_in_rebooting + # from: nova/db/sqlalchemy/api.py + i = Index('instances_task_state_updated_at_idx', + t.c.task_state, t.c.updated_at) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('instances', meta, autoload=True) + + i = Index('instances_host_deleted_idx', + t.c.host, t.c.deleted) + i.drop(migrate_engine) + + i = Index('instances_reservation_id_idx', t.c.reservation_id) + i.drop(migrate_engine) + + i = Index('instances_terminated_at_launched_at_idx', + t.c.terminated_at, t.c.launched_at) + i.drop(migrate_engine) + + i = Index('instances_uuid_deleted_idx', + t.c.uuid, t.c.deleted) + i.drop(migrate_engine) + + i = Index('instances_task_state_updated_at_idx', + t.c.task_state, t.c.updated_at) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/129_add_indexes_to_iscsi_targets.py b/nova/db/sqlalchemy/migrate_repo/versions/129_add_indexes_to_iscsi_targets.py new file mode 100644 index 000000000..e904742ae --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/129_add_indexes_to_iscsi_targets.py @@ -0,0 +1,57 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('iscsi_targets', meta, autoload=True) + + # Based on iscsi_target_count_by_host + # from: nova/db/sqlalchemy/api.py + i = Index('iscsi_targets_host_idx', t.c.host) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on volume_allocate_iscsi_target + # from: nova/db/sqlalchemy/api.py + i = Index('iscsi_targets_host_volume_id_deleted_idx', + t.c.host, t.c.volume_id, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('iscsi_targets', meta, autoload=True) + + i = Index('iscsi_targets_host_idx', t.c.host) + i.drop(migrate_engine) + + i = Index('iscsi_targets_host_volume_id_deleted_idx', + t.c.host, t.c.volume_id, t.c.deleted) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/130_add_indexes_to_key_pairs.py b/nova/db/sqlalchemy/migrate_repo/versions/130_add_indexes_to_key_pairs.py new file mode 100644 index 000000000..82517e53a --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/130_add_indexes_to_key_pairs.py @@ -0,0 +1,44 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + # Based on key_pair_get + # from: nova/db/sqlalchemy/api.py + t = Table('key_pairs', meta, autoload=True) + i = Index('key_pair_user_id_name_idx', + t.c.user_id, t.c.name) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('key_pairs', meta, autoload=True) + i = Index('key_pair_user_id_name_idx', + t.c.user_id, t.c.name) + i.drop(migrate_engine) diff --git a/nova/db/sqlalchemy/migrate_repo/versions/131_add_indexes_to_networks.py b/nova/db/sqlalchemy/migrate_repo/versions/131_add_indexes_to_networks.py new file mode 100644 index 000000000..11a9dde86 --- /dev/null +++ b/nova/db/sqlalchemy/migrate_repo/versions/131_add_indexes_to_networks.py @@ -0,0 +1,107 @@ +# vim: tabstop=4 shiftwidth=4 softtabstop=4 + +# Copyright 2012 OpenStack LLC. +# All Rights Reserved. +# +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +from sqlalchemy import Index, MetaData, Table +from sqlalchemy.exc import IntegrityError + + +def upgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('networks', meta, autoload=True) + + # Based on network_get_by_bridge + # from: nova/db/sqlalchemy/api.py + i = Index('networks_bridge_deleted_idx', + t.c.bridge, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on network_get_all_by_host + # from: nova/db/sqlalchemy/api.py + i = Index('networks_host_idx', t.c.host) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on network_query + # from: nova/db/sqlalchemy/api.py + i = Index('networks_project_id_deleted_idx', + t.c.project_id, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on network_get_all_by_uuids + # from: nova/db/sqlalchemy/api.py + i = Index('networks_uuid_project_id_deleted_idx', + t.c.uuid, t.c.project_id, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on network_create_safe + # from: nova/db/sqlalchemy/api.py + i = Index('networks_vlan_deleted_idx', + t.c.vlan, t.c.deleted) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + # Based on network_get_by_cidr + # from: nova/db/sqlalchemy/api.py + i = Index('networks_cidr_v6_idx', t.c.cidr_v6) + try: + i.create(migrate_engine) + except IntegrityError: + pass + + +def downgrade(migrate_engine): + meta = MetaData() + meta.bind = migrate_engine + + t = Table('networks', meta, autoload=True) + + i = Index('networks_bridge_deleted_idx', + t.c.bridge, t.c.deleted) + i.drop(migrate_engine) + + i = Index('networks_host_idx', t.c.host) + i.drop(migrate_engine) + + i = Index('networks_project_id_deleted_idx', + t.c.project_id, t.c.deleted) + i.drop(migrate_engine) + + i = Index('networks_uuid_project_id_deleted_idx', + t.c.uuid, t.c.project_id, t.c.deleted) + i.drop(migrate_engine) + + i = Index('networks_vlan_deleted_idx', + t.c.vlan, t.c.deleted) + i.drop(migrate_engine) + + i = Index('networks_cidr_v6_idx', t.c.cidr_v6) + i.drop(migrate_engine) -- cgit