Ask Your Question

deleted instance metadata

asked 2014-10-27 07:29:53 -0600

Kapache gravatar image

updated 2014-10-27 08:41:33 -0600

Is there a way to force the deletion of instances metadata in mysql?


I Delete this instance some time ago, but notice that myslq still has records of this instance.

************************** 68. row ***************************
              created_at: 2014-10-26 14:57:55
              updated_at: 2014-10-26 16:22:42
              deleted_at: 2014-10-26 16:22:42
                      id: 68
             internal_id: NULL
                 user_id: d33332622a7b4fc5b141bd879ef65ad4
              project_id: 7cb8c1cc282340159369280f8a40f838
               image_ref: 332911e3-cea4-4137-a9d4-759fd608b841
            launch_index: 0
                key_name: NULL
                key_data: NULL
             power_state: 1
                vm_state: deleted
               memory_mb: 2048
                   vcpus: 1
                hostname: windows
               user_data: I2Nsb3VkLWNvbmZpZwpob3N0bmFtZTogcHJvZnVzZS5jb20KZnFkbjogcHJvZnVzZS5jb20KbWFuYWdlX2V0Y19ob3N0czogdHJ1ZQpwYXNzd29yZDogZGlkaWRpCmNocGFzc3dkOiB7IGV4cGlyZTogRmFsc2UgfQpzc2hfcHdhdXRoOiBUcnVl
          reservation_id: r-ijfefcmy
            scheduled_at: 2014-10-26 14:58:01
             launched_at: 2014-10-26 14:58:16
           terminated_at: 2014-10-26 16:22:42
            display_name: windows
     display_description: windows
       availability_zone: nova
                  locked: 0
                 os_type: NULL
        instance_type_id: 15
                 vm_mode: NULL
                    uuid: 9c55783d-28bf-44fc-91b0-9d9f483b3062
            architecture: NULL
        root_device_name: /dev/vda
            access_ip_v4: NULL
            access_ip_v6: NULL
              task_state: NULL
default_ephemeral_device: NULL
     default_swap_device: /dev/vdb
                progress: 0
        auto_disk_config: 1
      shutdown_terminate: 0
       disable_terminate: 0
                 root_gb: 20
            ephemeral_gb: 0
               cell_name: NULL
                 deleted: 68
               locked_by: NULL
                 cleaned: 1
      ephemeral_key_uuid: NULL

My concerns are that the metadata history could cause mysql to become sluggish through time.

edit retag flag offensive close merge delete

3 answers

Sort by ยป oldest newest most voted

answered 2014-10-27 11:17:06 -0600

As mpetason said, use MYSQL maintenance queries to do this job. Script provided in this blog can be a base script, you can use to delete instance directly from database. Change it in a way you want. It is written to delete one instance at a time, but you can change it to delete bunch of VMs, that was terminated before for example 6 month ago.

Instance metadata stored on several tables on the mysql database. So you have to delete all of them. All tables are listed bellow:

  • nova.instance_faults
  • nova.instance_id_mappings
  • nova.instance_info_caches
  • nova.instance_system_metadata
  • nova.security_group_instance_association
  • nova.block_device_mapping
  • nova.fixed_ips
  • nova.instance_actions_events
  • nova.instance_actions
  • nova.virtual_interfaces
  • nova.instances
edit flag offensive delete link more

answered 2014-10-27 11:56:29 -0600

Kapache gravatar image


Thanks for the help guys,

I tried the blog script, but it gives me a foreign key error,

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (nova.instance_actions, CONSTRAINT fk_instance_actions_instance_uuid FOREIGN KEY (instance_uuid) REFERENCES instances (uuid))

I also tried this method same error message.

delete from nova.instances where deleted=63 and terminated_at < (NOW() - INTERVAL 6 Months);

I am not sure if disabling the foreign key check would work, or break the instance table?

edit flag offensive delete link more

answered 2014-10-27 10:47:26 -0600

mpetason gravatar image

It will be general MySQL maintenance. You could write a query that looks for instanced terminated before a certain date and then just delete the data. It will become sluggish if you are creating/deleting a lot of instances over time. I've seen users create scripts that delete this data based on queries looking for instances terminated before 6 months ago, or whatever amount of time you need records for.

edit flag offensive delete link more


Looks like disabling the foreign keys worked.

mysql> SET FOREIGN_KEY_CHECKS=0; Query OK, 0 rows affected (0.00 sec)

mysql> delete from nova.instances where deleted= 68 and terminated_at < (NOW() - INTERVAL 2 DAY); Query OK, 1 row affected (0.01 sec)

Kapache gravatar imageKapache ( 2014-10-27 12:03:41 -0600 )edit

Get to know Ask OpenStack

Resources for moderators

Question Tools

1 follower


Asked: 2014-10-27 07:29:53 -0600

Seen: 1,033 times

Last updated: Oct 27 '14