[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
different.

-Rob


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