<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hello,<br>
<br>
Please see some comments inline.<br>
<br>
Best Regards,<br>
Alexei Kornienko<br>
<br>
On 03/21/2014 11:11 PM, Joe Gordon wrote:<br>
</div>
<blockquote
cite="mid:CAHXdxOdqR=rNZsu5bs5TanFVvfi16oLrbXB6y6AsO51oNbEjQw@mail.gmail.com"
type="cite">
<div dir="ltr"><br>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On Fri, Mar 21, 2014 at 4:04 AM, Sean
Dague <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:sean@dague.net" target="_blank">sean@dague.net</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 class="">On 03/20/2014 06:18 PM, Joe Gordon wrote:<br>
><br>
><br>
><br>
> On Thu, Mar 20, 2014 at 3:03 PM, Alexei Kornienko<br>
</div>
<div class="">> <<a moz-do-not-send="true"
href="mailto:alexei.kornienko@gmail.com">alexei.kornienko@gmail.com</a>
<mailto:<a moz-do-not-send="true"
href="mailto:alexei.kornienko@gmail.com">alexei.kornienko@gmail.com</a>>>
wrote:<br>
><br>
> Hello,<br>
><br>
> We've done some profiling and results are quite
interesting:<br>
> during 1,5 hour ceilometer inserted 59755
events (59755 calls to<br>
> record_metering_data)<br>
> this calls resulted in total 2591573 SQL
queries.<br>
><br>
> And the most interesting part is that 291569
queries were ROLLBACK<br>
> queries.<br>
> We do around 5 rollbacks to record a single
event!<br>
><br>
> I guess it means that MySQL backend is
currently totally unusable in<br>
> production environment.<br>
><br>
><br>
> It should be noticed that SQLAlchemy is horrible
for performance, in<br>
> nova we usually see sqlalchemy overheads of well
over 10x (time<br>
> nova.db.api call vs the time MySQL measures when
slow log is recording<br>
> everything).<br>
<br>
</div>
That's not really a fair assessment. Python object
inflation takes time.<br>
I do get that there is SQLA overhead here, but even if you
trimmed it<br>
out you would not get the the mysql query time.<br>
<br>
</blockquote>
<div><br>
</div>
<div>To give an example from nova:</div>
<div><br>
</div>
<div>doing a nova list with no servers:</div>
<div><br>
</div>
<div>
<div>stack@devstack:~/devstack$ nova --timing list </div>
<div><br>
</div>
<div>
<div>| GET <a moz-do-not-send="true"
href="http://10.0.0.16:8774/v2/a82ededa9a934b93a7184d06f302d745/servers/detail">http://10.0.0.16:8774/v2/a82ededa9a934b93a7184d06f302d745/servers/detail</a>
| 0.0817470550537 |</div>
</div>
</div>
<div><br>
</div>
<div>So nova command takes 0.0817470550537 seconds.</div>
<div><br>
</div>
<div>Inside the nova logs (when putting a timer around all
nova.db.api calls [1] ),
nova.db.api.instance_get_all_by_filters takes 0.06
seconds:</div>
<div><br>
</div>
<div>
<div> 2014-03-21 20:58:46.760 DEBUG nova.db.api
[req-91879f86-7665-4943-8953-41c92c42c030 demo demo]
'instance_get_all_by_filters' 0.06 seconds timed
/mnt/stack/nova/nova/db/api.py:1940</div>
</div>
<div><br>
</div>
<div>But the sql slow long reports the same query takes only
0.001006 seconds with a lock_time of 0.000269 for a total
of 0.00127 seconds.</div>
<div><br>
</div>
<div>
<div> # Query_time: 0.001006 Lock_time: 0.000269
Rows_sent: 0 Rows_examined: 0</div>
<div><br>
</div>
</div>
<div><br>
</div>
<div>So in this case only 2% of the time
that nova.db.api.instance_get_all_by_filters takes is
spent inside of mysql. Or to put it differently
nova.db.api.instance_get_all_by_filters is 47 times
slower then the raw DB call underneath.</div>
<div><br>
</div>
<div>Yes I agree that that turning raw sql data into python
objects should take time, but I just don't think it should
take 98% of the time.</div>
</div>
</div>
</div>
</blockquote>
<span id="result_box" class="short_text" lang="en"><span class="hps">If
you would open actual code of </span></span>nova.db.api.instance_get_all_by_filters
- <a
href="https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/api.py#L1817">https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/api.py#L1817</a><br>
You will find out that python code is actually doing lot's of
things: <br>
1) setup join conditions<br>
2) create query filters<br>
3) doing some heavy matching, loops in exact_filter, regex_filter,
tag_filter<br>
This code won't go away with python objects since it's related to
busyness logic.<br>
I think that it's quite <span id="result_box" class="short_text"
lang="en"><span class="hps">hypocritical</span></span> to say that
the problem is "turning raw sql data into python objects"<br>
<br>
<blockquote
cite="mid:CAHXdxOdqR=rNZsu5bs5TanFVvfi16oLrbXB6y6AsO51oNbEjQw@mail.gmail.com"
type="cite">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<div><br>
</div>
<div>[1] <a moz-do-not-send="true"
href="https://github.com/jogo/nova/commit/7743ee366bbf8746f1c0f634f29ebf73bff16ea1">https://github.com/jogo/nova/commit/7743ee366bbf8746f1c0f634f29ebf73bff16ea1</a></div>
<div><br>
</div>
<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">That
being said, having Ceilometer's write path be highly tuned
and not<br>
use SQLA (and written for every back end natively) is
probably appropriate.<br>
</blockquote>
<div><br>
</div>
<div>While I like this idea, they loose free postgresql
support by dropping SQLA. But that is a solvable problem.</div>
<div> </div>
<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 class="">
<div class="h5"><br>
-Sean<br>
<br>
--<br>
Sean Dague<br>
Samsung Research America<br>
<a moz-do-not-send="true" href="mailto:sean@dague.net">sean@dague.net</a>
/ <a moz-do-not-send="true"
href="mailto:sean.dague@samsung.com">sean.dague@samsung.com</a><br>
<a moz-do-not-send="true" href="http://dague.net"
target="_blank">http://dague.net</a><br>
<br>
</div>
</div>
<br>
_______________________________________________<br>
OpenStack-dev mailing list<br>
<a moz-do-not-send="true"
href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a><br>
<a moz-do-not-send="true"
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>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
OpenStack-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a>
<a class="moz-txt-link-freetext" href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a>
</pre>
</blockquote>
<br>
</body>
</html>