<div dir="ltr">Mike,<div><br></div><div>Thanks a lot for your response!</div><div>Some comments:</div><div><span style="font-family:arial,sans-serif;font-size:13px">> There’s some in-Python filtering following it which does not seem necessary; the "</span><span style="font-family:arial,sans-serif;font-size:13px">alloc</span><span style="font-family:arial,sans-serif;font-size:13px">.</span><span style="font-family:arial,sans-serif;font-size:13px">vxlan_vni</span><span style="font-family:arial,sans-serif;font-size:13px"> </span><span style="font-family:arial,sans-serif;font-size:13px">not</span><span style="font-family:arial,sans-serif;font-size:13px"> </span><span style="font-family:arial,sans-serif;font-size:13px">in</span><span style="font-family:arial,sans-serif;font-size:13px"> </span><span style="font-family:arial,sans-serif;font-size:13px">vxlan_vnis” phrase </span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px">> could just as well be a SQL “NOT IN” expression. </span><br></div><div><span style="font-family:arial,sans-serif;font-size:13px">There we have to do specific set intersection between configured ranges and existing allocation. That could be done in sql,</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px">but that certainly would lead to a huge sql query text as full vxlan range could consist of 16 millions of ids.</span></div><div><span style="font-family:arial,sans-serif;font-size:13px"><br>
</span></div><div><span style="font-family:arial,sans-serif;font-size:13px">> </span><span style="font-family:arial,sans-serif;font-size:13px"> </span><span style="font-family:arial,sans-serif;font-size:13px">The synchronize_session=“fetch” is certainly a huge part of the time spent here</span></div>
<div>You've actually made a good point about <span style="font-family:arial,sans-serif;font-size:13px">synchronize_session=“fetch” which was obviously misused by me.</span></div><div><span style="font-family:arial,sans-serif;font-size:13px">It seems to save up to 40% of plain deleting time.</span></div>
<div><br></div><div><span style="font-family:arial,sans-serif;font-size:13px">I've fixed that and get some speedup with deletes for both mysql and postgress that reduced difference between chunked/non-chunked version:</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:13px;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid rgb(204,204,204)">
<colgroup><col width="137"><col width="122"><col width="117"><col width="82"><col width="128"><col width="133"><col width="100"></colgroup><tbody><tr style="height:21px"><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">
50k vnis to add/delete</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Pg adding vnis</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Pg deleting vnis</td>
<td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Pg Total</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Mysql adding vnis</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">
Mysql deleting vnis</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Mysql total</td></tr><tr style="height:21px"><td style="padding:2px 3px;vertical-align:bottom">non-chunked sql</td>
<td style="padding:2px 3px;vertical-align:bottom;text-align:right">22</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">15</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">37</td>
<td style="padding:2px 3px;vertical-align:bottom;text-align:right">15</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">15</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">30</td>
</tr><tr style="height:21px"><td style="padding:2px 3px;vertical-align:bottom">chuked in 100</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">20</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">
13</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">33</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">14</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">
14</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">28</td></tr></tbody></table></div><div class="gmail_extra"><br></div><div class="gmail_extra">Results of chunked and non-chunked version look closer, but gap increases with vni range size (based on few tests of 150k vni range)</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">So I'm going to fix chunked version that is on review now. If you think that the benefit doesn't worth complexity - please let me know.</div><div class="gmail_extra">
<br></div><div class="gmail_extra">Thanks,</div><div class="gmail_extra">Eugene.<br><br><div class="gmail_quote">On Mon, Jun 9, 2014 at 1:33 AM, Mike Bayer <span dir="ltr"><<a href="mailto:mbayer@redhat.com" target="_blank">mbayer@redhat.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div style="word-wrap:break-word"><br><div><div><div class="h5">
<div>On Jun 7, 2014, at 4:38 PM, Eugene Nikanorov <<a href="mailto:enikanorov@mirantis.com" target="_blank">enikanorov@mirantis.com</a>> wrote:</div><br><blockquote type="cite"><div dir="ltr">Hi folks,<div><br></div>
<div>There was a small discussion about the better way of doing sql operations for vni synchronization with the config.</div><div>Initial proposal was to handle those in chunks. Carl also suggested to issue a single sql query.</div>
<div>I've did some testing with my sql and postgress.</div><div>I've tested the following scenario: vxlan range is changed from 50000:150000 to 0:100000 and vice versa.</div><div>That involves adding and deleting 50000 vni in each test.</div>
<div><br></div><div>Here are the numbers:</div><div><table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:13px;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid rgb(204,204,204)">
<colgroup><col width="137"><col width="122"><col width="117"><col width="82"><col width="128"><col width="133"><col width="100"></colgroup><tbody><tr style="height:21px"><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">
50k vnis to add/delete</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Pg adding vnis</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Pg deleting vnis</td>
<td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Pg Total</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Mysql adding vnis</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">
Mysql deleting vnis</td><td style="padding:2px 3px;vertical-align:bottom;font-weight:bold;text-align:center">Mysql total</td></tr><tr style="height:21px"><td style="padding:2px 3px;vertical-align:bottom">non-chunked sql</td>
<td style="padding:2px 3px;vertical-align:bottom;text-align:right">23</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">22</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">45</td>
<td style="padding:2px 3px;vertical-align:bottom;text-align:right">14</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">20</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">34</td>
</tr><tr style="height:21px"><td style="padding:2px 3px;vertical-align:bottom">chunked in 100</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">20</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">
17</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">37</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">14</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">
17</td><td style="padding:2px 3px;vertical-align:bottom;text-align:right">31</td></tr></tbody></table></div><div><br></div><div>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)</div>
<div>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.</div><div>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.</div>
<div>In other words, difference between multiple DELETE sql statements and single one is even bigger (in percent) than these numbers show.</div><div><br></div><div>The code which I used to test is here: <a href="http://paste.openstack.org/show/83298/" target="_blank">http://paste.openstack.org/show/83298/</a></div>
<div>Right now the chunked version is commented out, so to switch between versions some lines should be commented and some - uncommented.</div></div></blockquote><div><br></div></div></div><div>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:</div>
<div><br></div><div>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).</div>
<div><br></div><div>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.</div>
<div><br></div><div>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 "<span>alloc</span><span>.</span><span>vxlan_vni</span> <span>not</span> <span>in</span> <span>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.</span></div>
<div><span><br></span></div><div>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. </div>
<div><span><br></span></div><div><span>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).</span></div>
<div><span><br></span></div><div><span>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. </span></div>
<div><span><br></span></div><div><span>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 !</span></div><span class=""><font color="#888888"><div>
<span><br></span></div><div><span>- mike</span></div><div><span><br></span></div><div><br></div></font></span></div></div><br>_______________________________________________<br>
OpenStack-dev mailing list<br>
<a href="mailto: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>
<br></blockquote></div><br></div></div>