Open Stack

On Jun 7, 2014, at 4:38 PM, Eugene Nikanorov <enikanorov at mirantis.com> wrote:

> Hi folks,
> 
> There was a small discussion about the better way of doing sql operations for vni synchronization with the config.
> Initial proposal was to handle those in chunks. Carl also suggested to issue a single sql query.
> I've did some testing with my sql and postgress.
> I've tested the following scenario: vxlan range is changed from 50000:150000 to 0:100000 and vice versa.
> That involves adding and deleting 50000 vni in each test.
> 
> Here are the numbers:
> 50k vnis to add/delete	Pg adding vnis	Pg deleting vnis	Pg Total	Mysql adding vnis	 Mysql deleting vnis	Mysql total
> non-chunked sql	23	22	45	14	20	34
> chunked in 100	20	 17	37	14	 17	31
> 
> I've done about 5 tries to get each number to minimize random floating factor (due to swaps, disc or cpu activity or other factors)
> That might be surprising that issuing multiple sql statements instead one big is little bit more efficient, so I would appreciate if someone could reproduce those numbers.
> Also I'd like to note that part of code that iterates over vnis fetched from db is taking 10 seconds both on mysql and postgress and is a part of "deleting vnis" numbers.
> In other words, difference between multiple DELETE sql statements and single one is even bigger (in percent) than these numbers show.
> 
> The code which I used to test is here: http://paste.openstack.org/show/83298/
> Right now the chunked version is commented out, so to switch between versions some lines should be commented and some - uncommented.

I’ve taken a look at this, though I’m not at the point where I have things set up to run things like this within full context, and I don’t know that I have any definitive statements to make, but I do have some suggestions:

1. I do tend to chunk things a lot, selects, deletes, inserts, though the chunk size I work with is typically more like 1000, rather than 100.   When chunking, we’re looking to select a size that doesn’t tend to overload the things that are receiving the data (query buffers, structures internal to both SQLAlchemy as well as the DBAPI and the relational database), but at the same time doesn’t lead to too much repetition on the Python side (where of course there’s a lot of slowness).

2. Specifically regarding “WHERE x IN (…..)”, I always chunk those.  When we use IN with a list of values, we’re building an actual SQL string that becomes enormous.  This puts strain on the database’s query engine that is not optimized for SQL strings that are hundreds of thousands of characters long, and on some backends this size is limited; on Oracle, there’s a limit of 1000 items.   So I’d always chunk this kind of thing.

3. I’m not sure of the broader context of this code, but in fact placing a literal list of items in the IN in this case seems unnecessary; the “vmis_to_remove” list itself was just SELECTed two lines above.   There’s some in-Python filtering following it which does not seem necessary; the "alloc.vxlan_vni not in vxlan_vnis” phrase could just as well be a SQL “NOT IN” expression.  Not sure if determination of the “.allocated” flag can be done in SQL, if that’s a plain column, then certainly.    Again not sure if this is just an artifact of how the test is done here, but if the goal is to optimize this code for speed, doing a DELETE…WHERE .. IN (SELECT ..) is probably better.   I see that the SELECT is using a lockmode, but it would seem that if just the rows we care to DELETE are inlined within the DELETE itself this wouldn’t be needed either.

It’s likely that everything in #3 is pretty obvious already and there’s reasons it’s the way it is, but I’m just learning all of these codebases so feel free to point out more of the background for me.   

4. The synchronize_session=“fetch” is certainly a huge part of the time spent here, and it seems unclear why this synchronize is necessary.  When I use query.delete() I never use “fetch”; I either have synchronization turned off, as the operation is not dealing with any set of objects already in play, or I use “evaluate” which here is not possible with the IN (though there is a SQLAlchemy ticket for many years to implement “evaluate” using "IN (values)" that is pretty easy to implement, but if the query became an "IN (SELECT …)” that again would not be feasible).

5. I don’t have a great theory on why chunking does better here on the INSERT.   My vague notion here is that as with the DELETE, the systems in play do better when they aren’t tasked with building up very large internal buffers for operations, but that’s not something I have the background to prove.  

These are all just some impressions and as I’m totally new to this code base I may be way off, so please feel to help me get up to speed !

- mike


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20140608/57d851df/attachment.html>