[openstack-dev] [Keystone] Best way to do something MySQL-specific?

Robert Collins robertc at robertcollins.net
Tue Jul 9 01:13:58 UTC 2013

On 9 July 2013 12:32, Adam Young <ayoung at redhat.com> wrote:

> * 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.  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.

PostgreSQL has similar but different characteristics, particular the latest
iteration of isolation behaviour where locks are held on *the result of a
query*, not on 'specific rows returned' - the difference being that adding
a new row that matches the query for rows to delete, would encounter a
conflict. You also need to delete small numbers of rows at a time, though
the reason in the plumbing is different. There are some nasty interlocks
you can cause with very large deletes and autovacuum too - if you trigger
deadlock detection it still takes /minutes/ to detect and cleanup, whereas
we want sub-second liveness.

> once every second would be strange indeed.  I would think maybe once every
> five minutes or so.  Schedule your clean up IAW your deployment and usage.

5m intervals exacerbate the issue until it's solved. If the cleanup deletes
no more than (say) 1000 rows per iteration, it could run every 5 minutes
but when run keep going until the db is cleaned.

> Deleting a chunk of tokens in bulk would be preferable to doing client
> side iteration, I can;t see how that would not be the case.

right, so I think Clint prefers that too, the question is how to get
sqlalchemy to output the appropriate sql for postgresql and mysql, which is


Robert Collins <rbtcollins at hp.com>
Distinguished Technologist
HP Cloud Services
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20130709/07aad4be/attachment.html>

More information about the OpenStack-dev mailing list