

Chartio develops non-blocking MySQL queries using Tornado - jdavis
http://chart.io/2011/making-mysql-queries-asynchronous-in-tornado/

======
wulczer
A quick search reveals this post:

[http://jan.kneschke.de/2008/9/9/async-mysql-queries-with-
c-a...](http://jan.kneschke.de/2008/9/9/async-mysql-queries-with-c-api/)

with a little more in-depth exploration of the MySQL async API. The problem
seems to be that that (undocumented) API does not handle EAGAIN and that
there's no way to connect asynchronously (mysql_connnect always blocks).

Compare with PostgreSQL, which has had an async API for a long time:

[http://developer.postgresql.org/pgdocs/postgres/libpq-
async....](http://developer.postgresql.org/pgdocs/postgres/libpq-async.html)

and for which I wrote a Twisted library that does fully asynchronous
connection building and query execution:
<https://github.com/wulczer/txpostgres>.

Since the asynchronous API is exposed in psycopg2, the Python connector
library, it should be trivial to hook it up to Tornado as well.

~~~
piotrSikora
It's already done: <https://github.com/FSX/momoko>

------
tptacek
I swagged this out a couple years ago on a lark for Ruby/EventMachine:

<https://github.com/tqbf/asymy>

I have a much more mature version internally, and my understanding is that you
can get gem-ified libraries that do async database stuff for EventMachine
today.

I lost interest in this pretty quickly once I added Redis to my stack. Redis
is trivial to talk to asynchronously, and by sticking a queue in between your
async components and your database layer, it becomes easy to implement the
half-async model --- plus you get very smart caching and another layer of
indexing "for free".

------
ivank
For Twisted, there's <https://github.com/hybridlogic/txMySQL>, or the standard
adbapi, which runs queries in a thread pool.

Also, some Twisted documentation was improved after Tornado was released;
there's now "Twisted.Web in 60 seconds":
[http://twistedmatrix.com/documents/11.0.0/web/howto/web-
in-6...](http://twistedmatrix.com/documents/11.0.0/web/howto/web-
in-60/index.html)

------
jamesaguilar
> While this isn’t a big deal in nearly all production environments (you want
> to know when your database gets slow, so you can optimize queries or add
> indexing), it matters a lot to us because a slow query can affect other
> customers queries.

One more reason to be leery of single-threaded eventing systems. You'd never
run into this issue with a threaded web app, and it would perform just as well
provided you kept your datastructures as independent as they are in your
current eventing setup.

Comparing an eventing system and a threading system, the eventing system is
inherently _less_ shared-nothing. It shares everything the threading system
does _and_ the event thread.

~~~
tptacek
You appear only to have read the first graf of this post, as the whole point
of eventing the database access layer is to eliminate the "slow query blocks"
problem.

But that's not why I'm commenting. Rather:

You're able to make that last assertion only by shifting the meaning of the
word "shared" and denuding it of all its concurrency implications. Yes, event
systems "share" the event loop, in all the glory of the word "shared".
However, no two contexts in an evented system ever step on each other for
access to a shared resource.

~~~
jamesaguilar
> You appear only to have read the first graf of this post

I read a little more than that, but I commented on what was interesting to me.
If I'm reading the rest of it right, it's basically a tutorial on making a
python extension for two specific mysql API functions. That's fine, but it's
not that interesting (to me).

> However, no two contexts in an evented system ever step on each other for
> access to a shared resource.

Isn't that exactly what is happening when other requests are blocked by a
blocking mysql call? They are stepping on each other for access to the shared
event thread resource, which they need concurrent access to. Is this not the
case? Please help me understand if I am misreading you.

------
cgbystrom
In search of performance we also developed a non-blocking MySQL driver for
Python (and gevent). It's used by our real-time web framework, Planet
(<http://www.planetframework.com>).

While we could've gotten better performance with more processes, we wanted to
stick with one process per core. Given this and a non-blocking driver we
naturally got a good performance gain.

amysql, as it's called, is up on GitHub: <https://github.com/esnme/amysql>

------
thingsilearned
Just to re-iterate Justin's warnings: while the thought of making everything
non-blocking is exciting, this probably should only be used if you're
expecting (and can't do anything about it) very slow queries.

~~~
tptacek
Really? I've never scaled up a consumer web app this way, but is it really
considered sound to insert blocking database calls in async web apps? This
sounds wrong to me.

~~~
BarkMore
It works if the service runs a sufficient number of application instances
behind a load balancer. In one of the videos about Tornado, a person from
FriendFeed stated that FriendFeed runs multiple instances of the application
with blocking calls to MySQL. There were many complaints about FriendFeed, but
being slow was not one of them. (See slide 21 here
<http://bret.appspot.com/entry/tornado-tech-talk>).

------
EvanMiller
This should be called "not-as-blocking" MySQL queries. As they say in the blog
post, the calls to send and read still block, but they've made it so you don't
call read until there is at least something to read. I imagine this will be an
improvement over many setups.

To add to the list of truly non-blocking MySQL libraries, check out Erlang's
MySQL driver: <https://github.com/Eonblast/Emysql>

------
IgorPartola
This is great. I was going to write my own lib that would use a whole bunch of
threads and a non-blocking queue system for executing queries. While I find
Tornado somewhat half-baked (it defaults to returning errors in HTML, but was
built for handling feeds; its content-encoding header handling is broken,
etc), there are lots of applications where non-blocking MySQL would be great.

~~~
thingsilearned
The title is a little misleading. The method here certainly isn't dependent on
Tornado, but for explanation/demo purposes it's a common context to explain it
in.

~~~
IgorPartola
Yes, I realize. This would work for anything in Python, and possibly other
things as well. node.js could ditch thread pool as well.

------
gaius
Interesting, Oracle is currently recommending _not_ using async OCI calls in
the 11g documentation (altho' the feature is obviously there), instead they
recommend you to use blocking calls on separate threads. This means you can
get notified when your query completes on the client with no network roundtrip
to find out if it's still executing.

~~~
kevingadd
There's no reason completion notifications can't occur in an async API. It
sounds like oracle calls polling "asynchronous", which is not particularly
accurate. If the polling operations also involve network round trips to check
for query completion, that is a tremendously stupid API.

~~~
gaius
Reason's simple: you have to support in your 11g server clients written in the
dim and distant past, so you can't monkey with the protocol too much. You
_could_ do this as you suggest by subscribing for DCN events when your
transaction completes, but obviously not with SELECTs.

------
DonnyV
With so many database options why would you use MySql?

~~~
IgorPartola
Some reasons:

* It's already in place

* Everyone on your team only knows MySQL

* That's what you know and you are under a deadline

* It's the right tool for the job (as opposed to something very different, from the NoSQL world)

* You are using MySQL Cluster instead of MySQL, but don't have the cash to upgrade to Oracle

~~~
Luyt
_"It's the right tool for the job (as opposed to something very different,
from the NoSQL world)"_

If I may toot my own horn, I just upgraded the layout of <http://mongodb-is-
web-scale.com/> so that you can scroll while the video stays visible.

~~~
invisible
Believe it or not, sometimes mongodb is the perfect tool for the job. It
actually does support safe wrriting features, so some of the hype in that
video is false. MySQL is always a great piece of the stack but it's not always
good.

~~~
Luyt
The video also shows its age, MongoDB hasn't stood still in the meantime. I
put up a transcript of this movie because I found the mingling of the
technical discussion with contemplating working on a horrible farm an
interesting concept.

