yahoo-eng-team team mailing list archive

[Vish Ishaya <vishvananda@xxxxxxxxx>]

Public bug reported:

While investigating some database performance problems, we discovered
that there is no index on deleted for the reservations table. When this
table gets large, the expire_reservations code will do a full table scan
and take multiple seconds to complete. Because the expire runs on a
periodic, it can slow down the master database significantly and cause
nova or cinder to become extremely slow.

> EXPLAIN UPDATE reservations SET updated_at=updated_at, deleted_at='2014-07-24 22:26:17', deleted=id WHERE reservations.deleted = 0 AND reservations.expire < '2014-07-24 22:26:11';
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+------------------------------+
| id | select_type | table        | type  | possible_keys | key    | key_len | ref  | rows  | Extra                        |
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+------------------------------+
|  1 | SIMPLE      | reservations | index | NULL          | PRIMARY | 4      | NULL | 950366 | Using where; Using temporary |
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+------------------------------+

An index on (deleted, expire) would be the most efficient.

** Affects: cinder
     Importance: Undecided
         Status: New

** Affects: nova
     Importance: Undecided
         Status: New

** Also affects: cinder
   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/1348720

Title:
  Missing index for expire_reservations

Status in Cinder:
  New
Status in OpenStack Compute (Nova):
  New

Bug description:
  While investigating some database performance problems, we discovered
  that there is no index on deleted for the reservations table. When
  this table gets large, the expire_reservations code will do a full
  table scan and take multiple seconds to complete. Because the expire
  runs on a periodic, it can slow down the master database significantly
  and cause nova or cinder to become extremely slow.

  > EXPLAIN UPDATE reservations SET updated_at=updated_at, deleted_at='2014-07-24 22:26:17', deleted=id WHERE reservations.deleted = 0 AND reservations.expire < '2014-07-24 22:26:11';
  +----+-------------+--------------+-------+---------------+---------+---------+------+--------+------------------------------+
  | id | select_type | table        | type  | possible_keys | key    | key_len | ref  | rows  | Extra                        |
  +----+-------------+--------------+-------+---------------+---------+---------+------+--------+------------------------------+
  |  1 | SIMPLE      | reservations | index | NULL          | PRIMARY | 4      | NULL | 950366 | Using where; Using temporary |
  +----+-------------+--------------+-------+---------------+---------+---------+------+--------+------------------------------+

  An index on (deleted, expire) would be the most efficient.

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