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 of over 48,000 articles and solutions.

Current Customers and Partners

Log in for full access

Log In