Glance database with large number of entries (slow)

Solution In Progress - Updated -

Issue

  • The Glance API is very slow and the database shows a very large number of objects in tables where many of them are deleted:
mysql> SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "glance" and table_rows > 0 order by table_rows desc;  
+----------------------------------+------------+
| table_name                       | table_rows |
+----------------------------------+------------+
| image_properties                 |     154672 |
| image_locations                  |       9145 |
| images                           |       9052 |
| metadef_properties               |        119 |
| image_tags                       |        112 |
| image_members                    |        101 |
| metadef_namespace_resource_types |         58 |
| metadef_namespaces               |         30 |
| metadef_objects                  |         23 |
| tasks                            |         11 |
| task_info                        |         11 |
| metadef_resource_types           |          5 |
| alembic_version                  |          1 |
+----------------------------------+------------+
  • openstack image list is slower:
(overcloud) [stack@undercloud:~]$ time openstack image list
+--------------------------------------+--------------------------------------------------------------------------------------------+-------------+
| ID                                   | Name                                                                                       | Status      |
+--------------------------------------+--------------------------------------------------------------------------------------------+-------------+
...
+--------------------------------------+--------------------------------------------------------------------------------------------+-------------+
real    0m47.865suser   0m6.616ssys 0m0.346s
  • Running this command docker exec --user root -it glance_api glance-manage --debug --log-file glance-manage.log db purge --age_in_days 1 --max_rows 100000 would return the following errors in /var/log/containers/glance/glance-manage.log
2019-07-02 13:16:56.461 329821 ERROR glance.db.sqlalchemy.api [req-ddec3fd2-4133-405f-beb0-c58aace2a010 - - - - -] DBError detected when purging from images: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`glance`.`image_locations`, CONSTRAINT `image_locations_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`))') [SQL: u'DELETE FROM images WHERE images.id in (SELECT T1.id FROM (SELECT images.id \nFROM images \nWHERE images.deleted_at < %(deleted_at_1)s ORDER BY images.deleted_at \n LIMIT %(param_1)s) as T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2019, 7, 1, 13, 16, 56, 216268), u'param_1': 10000}] (Background on this error at: http://sqlalche.me/e/gkpj): DBReferenceError: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`glance`.`image_locations`, CONSTRAINT `image_locations_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`))') [SQL: u'DELETE FROM images WHERE images.id in (SELECT T1.id FROM (SELECT images.id \nFROM images \nWHERE images.deleted_at < %(deleted_at_1)s ORDER BY images.deleted_at \n LIMIT %(param_1)s) as T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2019, 7, 1, 13, 16, 56, 216268), u'param_1': 10000}] (Background on this error at: http://sqlalche.me/e/gkpj)

or

2019-07-02 13:07:04.306 329450 ERROR glance.db.sqlalchemy.api [req-0fb76681-38e7-4b8b-9bcc-ad4e3ef3af48 - - - - -] DBError detected when purging from images: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`glance`.`image_properties`, CONSTRAINT `image_properties_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`))') [SQL: u'DELETE FROM images WHERE images.id in (SELECT T1.id FROM (SELECT images.id \nFROM images \nWHERE images.deleted_at < %(deleted_at_1)s ORDER BY images.deleted_at \n LIMIT %(param_1)s) as T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2019, 7, 1, 13, 7, 3, 390466), u'param_1': 10000}] (Background on this error at: http://sqlalche.me/e/gkpj): DBReferenceError: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`glance`.`image_properties`, CONSTRAINT `image_properties_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`))') [SQL: u'DELETE FROM images WHERE images.id in (SELECT T1.id FROM (SELECT images.id \nFROM images \nWHERE images.deleted_at < %(deleted_at_1)s ORDER BY images.deleted_at \n LIMIT %(param_1)s) as T1)'] [parameters: {u'deleted_at_1': datetime.datetime(2019, 7, 1, 13, 7, 3, 390466), u'param_1': 10000}] (Background on this error at: http://sqlalche.me/e/gkpj)

Environment

  • Red Hat OpenStack Platform 13.0 (RHOSP)

Subscriber exclusive content

A Red Hat subscription provides unlimited access to our knowledgebase, tools, and much more.

Current Customers and Partners

Log in for full access

Log In

New to Red Hat?

Learn more about Red Hat subscriptions

Using a Red Hat product through a public cloud?

How to access this content