yahoo-eng-team team mailing list archive

[Vish Ishaya <vishvananda@xxxxxxxxx>]

Public bug reported:

We are getting deadlocks for concurrent quota reservations that we did
not see in grizzly:

see https://bugs.launchpad.net/nova/+bug/1283987

The deadlock handling needs to be fixed as per above, but we shouldn't
be deadlocking, here. It seems this is due to bad indexes in the
database:

mysql> show index from quota_usages;
+--------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| quota_usages |          0 | PRIMARY                         |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| quota_usages |          1 | ix_quota_usages_project_id      |            1 | project_id  | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| quota_usages |          1 | ix_quota_usages_user_id_deleted |            1 | user_id     | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| quota_usages |          1 | ix_quota_usages_user_id_deleted |            2 | deleted     | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)
 
mysql> explain select * from quota_usages where project_id='foo' and user_id='bar' and deleted=0;
+----+-------------+--------------+------+------------------------------------------------------------+----------------------------+---------+-------+------+------------------------------------+
| id | select_type | table        | type | possible_keys                                              | key                        | key_len | ref   | rows | Extra                              |
+----+-------------+--------------+------+------------------------------------------------------------+----------------------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | quota_usages | ref  | ix_quota_usages_project_id,ix_quota_usages_user_id_deleted | ix_quota_usages_project_id | 768     | const |    1 | Using index condition; Using where |
+----+-------------+--------------+------+------------------------------------------------------------+----------------------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

We should have an index on project_id/deleted and
project_id/user_id/deleted instead of the current values.

** Affects: nova
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Compute (nova).
https://bugs.launchpad.net/bugs/1308715

Title:
  Deadlock on quota_usages due to misisng index

Status in OpenStack Compute (Nova):
  New

Bug description:
  We are getting deadlocks for concurrent quota reservations that we did
  not see in grizzly:

  see https://bugs.launchpad.net/nova/+bug/1283987

  The deadlock handling needs to be fixed as per above, but we shouldn't
  be deadlocking, here. It seems this is due to bad indexes in the
  database:

  mysql> show index from quota_usages;
  +--------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table        | Non_unique | Key_name                        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +--------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | quota_usages |          0 | PRIMARY                         |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
  | quota_usages |          1 | ix_quota_usages_project_id      |            1 | project_id  | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
  | quota_usages |          1 | ix_quota_usages_user_id_deleted |            1 | user_id     | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
  | quota_usages |          1 | ix_quota_usages_user_id_deleted |            2 | deleted     | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
  +--------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  4 rows in set (0.01 sec)
   
  mysql> explain select * from quota_usages where project_id='foo' and user_id='bar' and deleted=0;
  +----+-------------+--------------+------+------------------------------------------------------------+----------------------------+---------+-------+------+------------------------------------+
  | id | select_type | table        | type | possible_keys                                              | key                        | key_len | ref   | rows | Extra                              |
  +----+-------------+--------------+------+------------------------------------------------------------+----------------------------+---------+-------+------+------------------------------------+
  |  1 | SIMPLE      | quota_usages | ref  | ix_quota_usages_project_id,ix_quota_usages_user_id_deleted | ix_quota_usages_project_id | 768     | const |    1 | Using index condition; Using where |
  +----+-------------+--------------+------+------------------------------------------------------------+----------------------------+---------+-------+------+------------------------------------+
  1 row in set (0.00 sec)

  We should have an index on project_id/deleted and
  project_id/user_id/deleted instead of the current values.

To manage notifications about this bug go to:
https://bugs.launchpad.net/nova/+bug/1308715/+subscriptions