<br><br>On Tuesday, July 9, 2013, Clint Byrum wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Excerpts from Adam Young's message of 2013-07-09 06:19:19 -0700:<br>
> On 07/08/2013 11:06 PM, Clint Byrum wrote:<br>
> > Excerpts from Adam Young's message of 2013-07-08 17:32:30 -0700:<br>
> >> On 07/08/2013 04:35 PM, Clint Byrum wrote:<br>
> >>> Excerpts from Adam Young's message of 2013-07-08 13:18:55 -0700:<br>
> >>>> On 07/01/2013 01:35 PM, Clint Byrum wrote:<br>
> >>>>> The way the new keystone-manage command "token_flush" works right now<br>
> >>>>> is quite broken by MySQL and InnoDB's gap locking behavior:<br>
> >>>>><br>
> >>>>> <a href="https://bugs.launchpad.net/1188378" target="_blank">https://bugs.launchpad.net/1188378</a><br>
> >>>>><br>
> >>>>> Presumably other SQL databases like PostgreSQL will have similar problems<br>
> >>>>> with doing massive deletes, but I am less familiar with them.<br>
> >>>>><br>
> >>>>> I am trying to solve this in keystone, and my first attempt is here:<br>
> >>>>><br>
> >>>>> <a href="https://review.openstack.org/#/c/32044/" target="_blank">https://review.openstack.org/#/c/32044/</a><br>
> >>>>><br>
> >>>>> However, MySQL does not support using "LIMIT" in a sub-query that<br>
> >>>>> is feeding an IN() clause, so that approach will not work. Likewise,<br>
> >>>>> sqlalchemy does not support the MySQL specific extension to DELETE which<br>
> >>>>> allows it to have a LIMIT clause.<br>
> >>>>><br>
> >>>>> Now, I can do some hacky things, like just deleting all of the expired<br>
> >>>>> tokens from the oldest single second, but that could also potentially<br>
> >>>>> be millions of tokens, and thus, millions of gaps to lock.<br>
> >>>>><br>
> >>>>> So, there is just not one way to work for all databases, and we have to<br>
> >>>>> have a special mode for MySQL.<br>
> >>>>><br>
> >>>>> I was wondering if anybody has suggestions and/or examples of how to do<br>
> >>>>> that with sqlalchemy.<br>
> >>>>><br>
> >>>>> _______________________________________________<br>
> >>>>> OpenStack-dev mailing list<br>
> >>>>> <a href="javascript:;" onclick="_e(event, 'cvml', 'OpenStack-dev@lists.openstack.org')">OpenStack-dev@lists.openstack.org</a><br>
> >>>>> <a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
> >>>> In general, if you have millions of roles, you need a real database. I<br>
> >>>> would not try to work through SQL Alchemy for this. Instead, you<br>
> >>>> probably just want to make sure that the token_flush is run fairly<br>
> >>>> regularly on your database.<br>
> >>>><br>
> >>> I'm not sure I understand you.<br>
> >>><br>
> >>> * I am asking about millions of tokens, not roles.<br>
> >> Heh, I mean Rows, and somehow type roles.<br>
> >><br>
> >>> * I am asking about MySQL.. presumably a "real" database.<br>
> >> I have to admit I am a bit of a Postgresql Bigot. I don't really<br>
> >> consider MySQL a real database, althought it has improved a lot over the<br>
> >> years. I am not up to speed on"InnoDB's gap locking behavior" but it is<br>
> >> not something I would expect to be a problem in Postgresql.<br>
> >><br>
> > You may want to update your snark! MySQL has its warts which are pretty<br>
> > easy to take shots at, but it has been a "real" ACID compliant database<br>
> > for well over a decade.<br>
> My snark is more recently generated than that. There are plenty of<br>
> places where MySQL has fallen down.<br>
><br>
> InnoDB support was not mandatory, and without it, MySQL was not really<br>
> ACID compliant. Using InnoDB was troublesome enough that the RHEL 6<br>
> version of MySQL defaults to MyISAM.<br>
><br>
<br>
Its not so much that it was troublesome to use InnoDB as it was that<br>
people were used to MyISAM's few mis-features (fulltext and delayed<br>
inserts mostly) and needed a long warning (run up to 5.5) that the<br>
default was changing. Before 5.5 was released, Drizzle _completely<br>
removed_ MyISAM because it causes way more problems than it solves.<br>
<br>
> ><br>
> > Please have a read at how InnoDB's isolation level handling works<br>
> > [1]. You can compare it to Postgres [2]. InnoDB's default isolation<br>
> > level, repeatable read, has a locking behavior that is particularly<br>
> > ugly for indexes like 'token.valid'. Put simply, you can't add more<br>
> > valid tokens until you're done deleting any valid expired tokens (if<br>
> > you mention valid in the delete, which flush_tokens does not).<br>
> ><br>
> > This is because token.valid == 1 may end up locked during the entire<br>
> > DELETE. The same could be true for expires too, even the primary key,<br>
> > though that is typically not locked with ranges.<br>
> ><br>
> > Yes, Postgres's behaviors are much more desirable here, as inserts almost<br>
> > go unmentioned in the postgres description of transaction isolation. But<br>
> > there are well documented ways of working around this weirdness. I had<br>
> > forgotten that one way is to use READ UNCOMMITTED. That should avoid<br>
> > any of the large range locks and work with MySQL, Postgres, and Sqlite.<br>
><br>
><br>
> I think the real solution is for us to stop writing tokens to the<br>
> Database. If we are using PKI tokens, there is no need for a database<br>
> entry for valid tokens, only for revoked tokens.<br>
><br>
> That makes this whole thing into a non-problem.<br>
<br>
Yes please! Is this something that is in development, or at least recorded<br>
in a bug that someone like me can grab? I would love for that to be the<br>
default behavior.<br>
<br>
Of course, that would not solve the issue for those who want to audit<br>
tokens (is this a large contingent of users?). In fact the flush tokens<br>
command would be bad for those users as well, whereas pt-archiver's<br>
nibbling approach that copies rows out of the table into another database<br>
would work quite nicely.</blockquote><div><br></div><div>Sounds like a behavior that would be easily configurable.</div><div><span></span> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
_______________________________________________<br>
OpenStack-dev mailing list<br>
<a href="javascript:;" onclick="_e(event, 'cvml', 'OpenStack-dev@lists.openstack.org')">OpenStack-dev@lists.openstack.org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
</blockquote><br><br>-- <br><div><br></div>-Dolph<br>