Open Stack

On Tuesday, July 9, 2013, Clint Byrum wrote:

> Excerpts from Adam Young's message of 2013-07-09 06:19:19 -0700:
> > On 07/08/2013 11:06 PM, Clint Byrum wrote:
> > > Excerpts from Adam Young's message of 2013-07-08 17:32:30 -0700:
> > >> 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 <javascript:;>
> > >>>>> 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.  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.
> > >>
> > > You may want to update your snark! MySQL has its warts which are pretty
> > > easy to take shots at, but it has been a "real" ACID compliant database
> > > for well over a decade.
> > My snark is more recently generated than that.  There are plenty of
> > places where MySQL has fallen down.
> >
> > InnoDB support was not mandatory, and without it, MySQL was not really
> > ACID compliant.  Using InnoDB was troublesome enough that the RHEL 6
> > version of MySQL defaults to MyISAM.
> >
>
> Its not so much that it was troublesome to use InnoDB as it was that
> people were used to MyISAM's few mis-features (fulltext and delayed
> inserts mostly) and needed a long warning (run up to 5.5) that the
> default was changing. Before 5.5 was released, Drizzle _completely
> removed_ MyISAM because it causes way more problems than it solves.
>
> > >
> > > Please have a read at how InnoDB's isolation level handling works
> > > [1]. You can compare it to Postgres [2]. InnoDB's default isolation
> > > level, repeatable read, has a locking behavior that is particularly
> > > ugly for indexes like 'token.valid'. Put simply, you can't add more
> > > valid tokens until you're done deleting any valid expired tokens (if
> > > you mention valid in the delete, which flush_tokens does not).
> > >
> > > This is because token.valid == 1 may end up locked during the entire
> > > DELETE. The same could be true for expires too, even the primary key,
> > > though that is typically not locked with ranges.
> > >
> > > Yes, Postgres's behaviors are much more desirable here, as inserts
> almost
> > > go unmentioned in the postgres description of transaction isolation.
> But
> > > there are well documented ways of working around this weirdness. I had
> > > forgotten that one way is to use READ UNCOMMITTED. That should avoid
> > > any of the large range locks and work with MySQL, Postgres, and Sqlite.
> >
> >
> > I think the real solution is for us to stop writing tokens to the
> > Database.  If we are using PKI tokens, there is no need for a database
> > entry for valid tokens, only for revoked tokens.
> >
> > That makes this whole thing into a non-problem.
>
> Yes please! Is this something that is in development, or at least recorded
> in a bug that someone like me can grab? I would love for that to be the
> default behavior.
>
> Of course, that would not solve the issue for those who want to audit
> tokens (is this a large contingent of users?). In fact the flush tokens
> command would be bad for those users as well, whereas pt-archiver's
> nibbling approach that copies rows out of the table into another database
> would work quite nicely.


Sounds like a behavior that would be easily configurable.


>
> _______________________________________________
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.org <javascript:;>
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>


-- 

-Dolph
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20130709/4a5af90d/attachment.html>