backups db table lacks any indices which is slowing down common queries
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Cinder |
New
|
Undecided
|
Eric Harney |
Bug Description
I observed strangely long duration for MariaDB queries to the backups table which is used by cinder-backup. This is very likely due to the fact that this table has no indexes helping with common queries:
```
EXPLAIN SELECT * FROM backups WHERE project_
+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------
| 1 | SIMPLE | backups | ALL | NULL | NULL | NULL | NULL | 431890 | Using where |
+------
EXPLAIN SELECT * FROM backups WHERE volume_
+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------
| 1 | SIMPLE | backups | ALL | NULL | NULL | NULL | NULL | 431890 | Using where |
+------
```
```
SHOW indexes FROM backups;
+------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+------
| backups | 0 | PRIMARY | 1 | id | A | 431646 | NULL | NULL | | BTREE | | | NO |
+------
```
As you can see I have >500k entries in this table, but most of them are (marked as) deleted:
```
SELECT deleted, count(deleted) FROM backups GROUP BY deleted;
+------
| deleted | count(deleted) |
+------
| 0 | 35689 |
| 1 | 482261 |
+------
```
I know about the `cinder-manage db purge` [2] tool. But on larger clouds a few (ten)thousand rows of deleted volumes / backups / ... do accumulate before they are purged away depending on retention time.
And so very common queries for e.g. backups for a certain project_id or volume_id will end up as sequential scans using where.
You might argue that the table is simple and likely cached. But it's not about a single query for a list of backups for a certain project. It's rather about tooling making more intensive use of the API being slowed down on each request unnecessarily.
The volumes table in comparison certainly has some indexes:
```
SHOW indexes FROM volumes;
+------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+------
| volumes | 0 | PRIMARY | 1 | id | A | 78598 | NULL | NULL | | BTREE | | | NO |
| volumes | 1 | ix_volumes_
| volumes | 1 | ix_volumes_group_id | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | NO |
| volumes | 1 | volumes_
| volumes | 1 | volumes_
+------
```
and volumes_
```
EXPLAIN * FROM volumes WHERE deleted=0;
+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------
| 1 | SIMPLE | volumes | ALL | NULL | NULL | NULL | NULL | 78598 | Using where |
+------
```
Would it not make sense to have indexes on volume_id, project_id and also the deleted column to cover the most common selections?
[1] https:/
[2] https:/
summary: |
- backups db table lacks indexes to speed up common queries + backups db table lacks indexes which is slowing down common queries |
description: | updated |
description: | updated |
Changed in cinder: | |
assignee: | nobody → Eric Harney (eharney) |
summary: |
- backups db table lacks indexes which is slowing down common queries + backups db table lacks any indices which is slowing down common queries |
Thanks Eric for tanking care of this one.
There was another bug filed a while ago with similar observations: /bugs.launchpad .net/cinder/ +bug/1694935
https:/