stack queries by name or tenant are inefficient

[Miguel Grinberg]

Issuing a "heat stack-show <stack-name>" command currently requires all the stacks in the database to be inspected. Below is the SQL analysis of the query (for a stack table with 113 stacks in it):

mysql> explain select * from stack where deleted_at=NULL AND name='stack57';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | stack | ALL | NULL | NULL | NULL | NULL | 113 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Note how MySQL had to review all 113 entries to find the one that was requested.

Likewise, a "heat stack-list" query, requires all stacks to be inspected to find those that belong to the user's tenant. MySQL analysis (there are only 13 stacks on the specified tenant):

mysql> explain select * from stack where deleted_at=NULL AND tenant='39ce978ada4f4fc3b6da5588efd31d86';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | stack | ALL | NULL | NULL | NULL | NULL | 113 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Adding indexes for the name and tenant columns improves queries significantly:

mysql> create index stack_name on stack (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index stack_tenant on stack(tenant);
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 2

mysql> explain select * from stack where deleted_at=NULL AND name='stack57';
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | stack | ref | stack_name | stack_name | 768 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from stack where deleted_at=NULL AND tenant='39ce978ada4f4fc3b6da5588efd31d86';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| 1 | SIMPLE | stack | ref | stack_tenant | stack_tenant | 768 | const | 13 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

[Steve Baker (steve-stevebaker)]

see bug 1421885 and https://review.openstack.org/#/c/57086/ for a previous discussion on indexes

[Miguel Grinberg (miguelgrinberg)]

Interesting. In general I agree that going nuts with indexes is not always a good idea, since obviously indexes are not free. I think putting indexes in stack name and tenant is important, because these directly prevent a scale problem, as I mentioned in the analysis above.

In this previous attempt from bug 1252115 there were indexes added all over the place. I think that is extreme, really the most important thing is to make the first lookup efficient, as that will reduce the working set of rows to a manageable size without the expense of having to review all rows in the stack table. I would not worry about secondary indexes to help with sorting, the effectiveness of an index for the sort operation is much harder to demonstrate.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/157625
Committed: https://git.openstack.org/cgit/openstack/heat/commit/?id=132797aef5719dc20e6b1b5333ec9f7f7765ee12
Submitter: Jenkins
Branch: master

commit 132797aef5719dc20e6b1b5333ec9f7f7765ee12
Author: Miguel Grinberg <email address hidden>
Date: Fri Feb 20 00:56:14 2015 +0000

    Add name and tenant indexes to stack table

    This change adds indexes to the name and tenant columns of the stack
    table, to improve query performance.

    Change-Id: I0c675bda1b27814c4a7e9fce6c78af48266b5258
    Closes-Bug: #1421885