Open Stack

On 07/08/2013 08:32 PM, Adam Young wrote:
> On 07/08/2013 04:35 PM, Clint Byrum wrote:
>> Excerpts from Adam Young's message of 2013-07-08 13:18:55 -0700:
>>> On 07/01/2013 01:35 PM, Clint Byrum wrote:
>>>> The way the new keystone-manage command "token_flush" works right now
>>>> is quite broken by MySQL and InnoDB's gap locking behavior:
>>>>
>>>> https://bugs.launchpad.net/1188378
>>>>
>>>> Presumably other SQL databases like PostgreSQL will have similar
>>>> problems
>>>> with doing massive deletes, but I am less familiar with them.
>>>>
>>>> I am trying to solve this in keystone, and my first attempt is here:
>>>>
>>>> https://review.openstack.org/#/c/32044/
>>>>
>>>> However, MySQL does not support using "LIMIT" in a sub-query that
>>>> is feeding an IN() clause, so that approach will not work. Likewise,
>>>> sqlalchemy does not support the MySQL specific extension to DELETE
>>>> which
>>>> allows it to have a LIMIT clause.
>>>>
>>>> Now, I can do some hacky things, like just deleting all of the expired
>>>> tokens from the oldest single second, but that could also potentially
>>>> be millions of tokens, and thus, millions of gaps to lock.
>>>>
>>>> So, there is just not one way to work for all databases, and we have to
>>>> have a special mode for MySQL.
>>>>
>>>> I was wondering if anybody has suggestions and/or examples of how to do
>>>> that with sqlalchemy.
>>>>
>>>> _______________________________________________
>>>> OpenStack-dev mailing list
>>>> OpenStack-dev at lists.openstack.org
>>>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>> In general, if you have millions of roles, you need a real database.  I
>>> would not try to work through SQL Alchemy for this. Instead, you
>>> probably just want to make sure that the token_flush is run fairly
>>> regularly on your database.
>>>
>> I'm not sure I understand you.
>>
>> * I am asking about millions of tokens, not roles.
> Heh, I mean Rows, and somehow type roles.
>
>> * I am asking about MySQL.. presumably a "real" database.
>   I have to admit I am a bit of a Postgresql Bigot. I don't really
> consider MySQL a real database, althought it has improved a lot over the
> years.

As has PostgreSQL. VACUUM, anyone?

 >   I am not up to speed on"InnoDB's gap locking behavior" but it is
> not something I would expect to be a problem in Postgresql.

InnoDB and PostgreSQL behave in very different manners regarding locking 
and transaction isolation, even though they both implement a version of 
MVCC. In InnoDB's case, its implementation of MVCC is optimized more for 
storage space (it allows using a series of log records to reconstruct or 
undo a record to a particular "version" of the record) vs. PostgreSQL, 
which stores every version of every record in its data space.

AFAIK, PostgreSQL won't issue gap locks like InnoDB because it will 
simply write a new version of the records -- a version that marks the 
record as deleted -- to its data files, instead of gap locking to ensure 
that rows affected by a DELETE statement with an open-ended (or 
non-existent) WHERE clause can be properly isolated (and rolled back 
properly in the case of a failure).

In any case, MySQL is certainly a production-capable database like 
PostgreSQL. It has its quirks and downsides, as does any system, 
including PostgreSQL. Biases and false assumptions should be set aside. ;)

Best,
-jay