Hi all,<br><br>I've been taking a look at the way Nova uses its MySQL database. Having done MySQL performance audits for years as a consultant, a few things jumped out right away at me. First is the way that SQLAlchemy is wrapping nearly every query in an unnecessary "ping check" and rollback, eg.:<div>
<br></div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">select 1;<br>
select ... from ... where ...;<br>rollback;<br>select 1;<br>update ... where ...;<br>commit;<br>rollback;</blockquote><div><br></div><div>You can find a complete sample here: <a href="http://paste.openstack.org/show/18731/" target="_blank">http://paste.openstack.org/show/18731/</a> </div>
<div><div><div><br></div>I think I understand the reason for both the "select 1" and the "rollback" statements. However, in the interest of performance for large-scale deployments, I feel pretty strongly that they need to go away.<br>
<br>As I see it, there are three factors here.<br><br>(I) Most of the code in db/sqlalchemy/api.py abstracts a "unit of work" to a very low level, generally a single database read or write, and does not currently support transactions spanning multiple consistent writes. This is why "select 1" and "rollback" appear around almost every query -- most functions in api.py is checking out a session object, doing one or two queries, and then releasing the session. This actually creates a much larger issue about transactional atomicity for larger operations, such as the open bug about network creation here, and is probably better for another discussion. </div>
<div><a href="https://bugs.launchpad.net/nova/+bug/755138" target="_blank">https://bugs.launchpad.net/nova/+bug/755138</a>.</div>
<div><br>(II) Connections are tested by the MySQLPingListener function, using "SELECT 1" statements, every time a connection / session is checked out of the pool. Since connections are usually OK, this adds overhead unnecessarily. It
would be more efficient to handle the errors when connections aren't
OK. I've opened a bug with a description of one possible way to fix this issue. There are probably other viable solutions as well.<br><a href="https://bugs.launchpad.net/nova/+bug/1007027" target="_blank">https://bugs.launchpad.net/nova/+bug/1007027</a></div>
<div><br></div><div>My understanding is that this was implemented to prevent "Database has gone away" errors from occurring every time that the database is restarted, or when connections are closed by the database after being idle for long periods. In my opinion, a better solution to these problems is to:</div>
* wrap queries in retry logic, which will catch disconnect errors and attempt to reconnect to the database. I have a patch for this, if folks are interested.<br><div>* set Nova's sql_idle_timeout to less than MySQL's wait_timeout, and set them both to a reasonably short interval (eg, 1 minute), rather than the default (which I think is 8 hours).</div>
<div><br>
(III) Transaction state is reset when connections are returned to the
pool, even if no transaction was in progress, or the transaction-in-progress already committed. This is completely
wasteful, and easy to disable.<br><a href="https://bugs.launchpad.net/nova/+bug/1007038" target="_blank">https://bugs.launchpad.net/nova/+bug/1007038</a><br><br>Caveat here is that this reset-on-return functionality *is* useful when code doesn't properly clean up its own transaction state. When I turned it off, it exposed a few bugs, which I haven't tracked down yet. Lowering the sql_idle_timeout will provide the same "solution" as the current reset-on-return behavior in that it will prevent long-running idle transactions that tie up resources unnecessarily.</div>
<div><br></div><div><br></div><div>In summary, I'd like to see Nova stop spamming the database with "SELECT 1" and "ROLLBACK", and think this should be pretty easy to do. Testing these two changes in my devstack seems to work fine, but I'd like to know what others think before I propose a changeset with this kind of potential impact.<br>
<br>
</div></div><div>Cheers,</div><div>Devananda</div>