MariaDB (MySQL) cleanup of nova database
Hi,
I am trying to do cleanup on Newton (RHOSP 10) release nova DB, and want to remove old instances, which are removed LONG time ago (have about 7M rows on some tables).
I did some info gathering and found:
MariaDB [nova]> select TABLE_SCHEMA,TABLE_NAME,table_rows,AUTO_INCREMENT,TABLE_COMMENT from information_schema.tables where table_schema = 'nova' and table_rows >= 1000; +--------------+--------------------------------+------------+----------------+---------------+ | TABLE_SCHEMA | TABLE_NAME | table_rows | AUTO_INCREMENT | TABLE_COMMENT | +--------------+--------------------------------+------------+----------------+---------------+ | nova | block_device_mapping | 1021919 | 3644687 | | | nova | instance_actions | 2637028 | 7906316 | | | nova | instance_actions_events | 523519 | 8849807 | | | nova | instance_extra | 1367875 | 3486497 | | | nova | instance_faults | 6079 | 14042 | | | nova | instance_id_mappings | 1162779 | 3486305 | | | nova | instance_info_caches | 1182036 | 3512678 | | | nova | instance_metadata | 62785 | 192781 | | | nova | instance_system_metadata | 7700511 | 20926265 | | | nova | instances | 1192711 | 3486311 | | | nova | reservations | 7059421 | 20933180 | | | nova | s3_images | 160869 | 482213 | | | nova | shadow_instance_actions_events | 2367811 | 7135407 | | | nova | task_log | 103302 | 307409 | | | nova | virtual_interfaces | 3732556 | 10949276 | | +--------------+--------------------------------+------------+----------------+---------------+ 15 rows in set (0.25 sec)
MariaDB [nova]> SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `COLUMN_NAME`, `REFERENCED_TABLE_SCHEMA`, `REFERENCED_TABLE_NAME`, `REFERENCED_COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `TABLE_SCHEMA` = SCHEMA() AND `REFERENCED_TABLE_NAME` IS NOT NULL ORDER BY `REFERENCED_TABLE_NAME` ASC; +--------------+-------------------------------------+-------------------+-------------------------+-----------------------+------------------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------+-------------------------------------+-------------------+-------------------------+-----------------------+------------------------+ | nova | aggregate_metadata | aggregate_id | nova | aggregates | id | | nova | aggregate_hosts | aggregate_id | nova | aggregates | id | | nova | pci_devices | compute_node_id | nova | compute_nodes | id | | nova | consoles | pool_id | nova | console_pools | id | | nova | instance_extra | instance_uuid | nova | instances | uuid | | nova | instance_system_metadata | instance_uuid | nova | instances | uuid | | nova | virtual_interfaces | instance_uuid | nova | instances | uuid | | nova | consoles | instance_uuid | nova | instances | uuid | | nova | instance_faults | instance_uuid | nova | instances | uuid | | nova | instance_actions | instance_uuid | nova | instances | uuid | | nova | block_device_mapping | instance_uuid | nova | instances | uuid | | nova | instance_info_caches | instance_uuid | nova | instances | uuid | | nova | migrations | instance_uuid | nova | instances | uuid | | nova | instance_metadata | instance_uuid | nova | instances | uuid | | nova | security_group_instance_association | instance_uuid | nova | instances | uuid | | nova | fixed_ips | instance_uuid | nova | instances | uuid | | nova | instance_actions_events | action_id | nova | instance_actions | id | | nova | instance_group_member | group_id | nova | instance_groups | id | | nova | instance_group_policy | group_id | nova | instance_groups | id | | nova | instance_type_projects | instance_type_id | nova | instance_types | id | | nova | instance_type_extra_specs | instance_type_id | nova | instance_types | id | | nova | reservations | usage_id | nova | quota_usages | id | | nova | security_group_rules | parent_group_id | nova | security_groups | id | | nova | security_group_rules | group_id | nova | security_groups | id | | nova | security_group_instance_association | security_group_id | nova | security_groups | id | +--------------+-------------------------------------+-------------------+-------------------------+-----------------------+------------------------+ 25 rows in set (0.14 sec)
I compiled some delete line, but it gives me an error:
MariaDB [nova]> DELETE instances,instance_extra,instance_system_metadata, virtual_interfaces,consoles,instance_faults,instance_actions,block_device_mapping, instance_info_caches,migrations,instance_metadata,security_group_instance_association, fixed_ips,instance_actions_events FROM instances JOIN instance_extra.instance_uuid = instances.uuid JOIN instance_system_metadata.instance_uuid = instances.uuid JOIN virtual_interfaces.instance_uuid = instances.uuid JOIN consoles.instance_uuid = instances.uuid JOIN instance_faults.instance_uuid = instances.uuid JOIN instance_actions.instance_uuid = instances.uuid JOIN block_device_mapping.instance_uuid = instances.uuid JOIN instance_info_caches.instance_uuid = instances.uuid JOIN migrations.instance_uuid = instances.uuid JOIN instance_metadata.instance_uuid = instances.uuid JOIN security_group_instance_association.instance_uuid = instances.uuid JOIN fixed_ips.instance_uuid = instances.uuid JOIN instance_actions_events.action_id = instance_actions.id WHEREnova.instances.uuid= "d918a3b2-0a4b-4c73-9d06-ab58e458a648"; ERROR 1066 (42000): Not unique table/alias: 'instances'
--- update --- I have tried to add DB and get DN, table and column in apostrofas, but still getting error.
MariaDB [nova]> DELETE `nova`.`instances`,`nova`.`instance_extra`,`nova`.`instance_system_metadata`,`nova`.`virtual_interfaces`,`nova`.`consoles`,
-> `nova`.`instance_faults`,`nova`.`instance_actions`,`nova`.`block_device_mapping`,`nova`.`instance_info_caches`,`nova`.`migrations`,
-> `nova`.`instance_metadata`,`nova`.`security_group_instance_association`,`nova`.`fixed_ips`,`nova`.`instance_actions_events`
-> FROM `nova`.`instances`
-> JOIN `nova`.`instance_extra`.`instance_uuid` = `nova`.`instances`.`uuid`
-> JOIN `nova`.`instance_system_metadata`.`instance_uuid` = `nova`.`instances`.`uuid`
-> JOIN `nova`.`virtual_interfaces`.`instance_uuid` = `nova`.`instances`.`uuid`
-> JOIN `nova ...add a comment