MySQL 6.0, Libevent

« previous entry | next entry »
Jan. 1st, 2008 | 11:30 am

Currently in the 6.0 tree we have support for Libevent. This is one of those things I've been wanting to see
us do for years and it was finally completed a few weeks ago by Damien
Katz (aka CouchDB) with some help from Dmitri Lenev.

So what does this do? MySQL uses a 1:1 ratio for connections and
threads. Libevent allows us to pull threads into pools and use them
for connections. This is pretty awesome for 8way machines since
partially solves thread contention issues. Below is an example of
MyISAM doing large number of inserts with the number of users doing
inserts increasing (1-300 users). The graph shows the time taken for
each test run. Similar numbers show up when you run benchmarks with
Innodb (leave concurrent set to zero as you should normally do, this
will allow libevent to more effectively control the thread usage).
The green line shows the test running with 20 threads, while the blue
is unlimited. I do not have good rules of thumb yet for thread number
vs processors. The machine I used for this test is an 8way that I have
on loan from Intel. I will work out a similar test for Solaris once I
can get the code to compile there.

Pretty awesome :)
Libevent Picture.jpg

Link | Leave a comment | Add to Memories | Tell a Friend

Comments {17}

dormando

(no subject)

from: [info]dormando
date: Jan. 1st, 2008 08:15 pm (UTC)
Link

I'd say "yay, finally!" but it might be a while now :) I hope there's a concept of max_connections vs max_active_threads.

Reply | Thread

Brian "Krow" Aker

(no subject)

from: [info]krow
date: Jan. 2nd, 2008 04:54 am (UTC)
Link

Well MySQL has max connections, that has been around since nearly the beginning.

6.0 is as stable as 5.1 is. Avoid backup and any new engines and you will be fine.

Reply | Parent | Thread

dormando

(no subject)

from: [info]dormando
date: Jan. 2nd, 2008 05:38 am (UTC)
Link

What I mean is a concept of max connections and "max active connections" being separate.

So you could say:

max_connections = 10000

max_active_connections = 100

... and 10,000 clients could connect, but the clients will queue if there're more than 100 queries in flight.

Reply | Parent | Thread

Brian "Krow" Aker

(no subject)

from: [info]krow
date: Jan. 2nd, 2008 06:17 am (UTC)
Link

Damien's code looks to be such that you set the number of threads, and that set of threads are allocated at startup. That pool of threads will never grow.

Reply | Parent | Thread

thread pool per user

from: [info]72squared
date: Jun. 25th, 2008 03:21 pm (UTC)
Link

what if the thread pools were allocated per user? That way one mysql user would be able to consume only the thread pool for itself. Then the application designer can put slower queries connecting via the same username, and not block out other users? This would also prevent the condition where the root user could be starved out.

Does that make sense?

Reply | Parent | Thread

(no subject)

from: [info]jamesd
date: Jan. 2nd, 2008 04:09 pm (UTC)
Link

Good to see. Next stop some way to prevent all of the threads being taken by slow queries that cause the server to hit max_connections by not letting the (often) far more frequent fast queries run. I assume that it's possible for the optimiser to say that query x is expected to take n operations and have a pool for tasks expected to be "fast" where fast's n is user-configurable.

Reply | Thread

Brian "Krow" Aker

(no subject)

from: [info]krow
date: Jan. 2nd, 2008 06:22 pm (UTC)
Link

Not yet. The code for these two items are entirely on different layers (and the server really is not designed for this sort of way of thinking).

Not even using the SQL hints would work.

Perhaps if the user gave a hint in he way of a flag... or this could possibly be done with prepared statements (where the original cost could be stored and used for priority).

Reply | Parent | Thread

(no subject)

from: [info]jamesd
date: Jan. 3rd, 2008 03:33 am (UTC)
Link

Not really keen on SQL hints for this. More something that I think belongs in the storage engines with just a work estimate or row estimate clue from the optimiser. Then the storage engine can do whatever makes sense for its own implementation, like knowing if/when disk accesses are required and perhaps using that to decide finally that something is slow if the engine does the disk work itself. Perfection not required, just good enough.

Still, definite progress being made here even if I immediately start thinking of other things that would be helpful... :)

Reply | Parent | Thread

Brian "Krow" Aker

(no subject)

from: [info]krow
date: Jan. 3rd, 2008 03:47 am (UTC)
Link

I dislike SQL hints has well....

I've pointed Kostja and Dmitri at your comments. Hopefully they will respond.

Reply | Parent | Thread

peter_zaitsev

(no subject)

from: [info]peter_zaitsev
date: Jan. 5th, 2008 06:03 pm (UTC)
Link

Sure. It is quite expected to work well for CPU bound case with short queries.

James brings one tricky issue - how do you make sure all slots are not busy by complex queries so small queries starve.

There are more tricky issues - for example how to maintain true concurrency - threads may well be stalled waiting on the row level locks, disk IO waits is another great example.

To implement it working for large number of cases much larger overhaul is needed having MySQL kernel to control concurrency so waiting threads allow someone else to run.

But this is surely great first step.

Reply | Thread

Brian "Krow" Aker

(no subject)

from: [info]krow
date: Jan. 6th, 2008 12:29 am (UTC)
Link

Agreed, this is mainly for a setup where the database is doing all lightweight queries. You could keep down long queries by running them under one user and limiting concurrent connections.

Good start, but yes, it is going to need more to work in a way so that no one will shoot themselves in the foot.

Reply | Parent | Thread

w00t! can't wait!

from: [info]72squared
date: Feb. 28th, 2008 05:32 pm (UTC)
Link

Been waiting for this performance improvement for a long time. Very exciting!

Reply | Thread

Brian "Krow" Aker

Re: w00t! can't wait!

from: [info]krow
date: Feb. 29th, 2008 04:55 pm (UTC)
Link

There is more good stuff coming along from this in the near future, so hang on :)

Reply | Parent | Thread

libev would be nice[r]

from: [info]rogerdpack
date: Jul. 5th, 2008 12:00 am (UTC)
Link

libev support might be nice :)

I haven't looked at the new code too much, but noticed that it might have one outstanding DB query per thread in the pool.
I assume this is pools running on the server itself? So it basically limits the totally max_actively_being_run_queries?

Will the client get any libev/libevent support? That would be sweet, and possibly allow for a single client thread to do multiple simultaneous queries to the database. If I'm understanding it correctly, anyway.

Client-side support that was this way might fit nicely in with my own work of single threaded web servers.[1]
Just thinking out loud.
Thanks for doing that stuff!
-R
[1] http://betterlogic.com/roger/?p=339

Reply | Thread

Brian "Krow" Aker

Re: libev would be nice[r]

from: [info]krow
date: Jul. 5th, 2008 12:04 am (UTC)
Link

At some point I will look at libev, but so far no one has done this in any of the communities I belong too. From my understanding of the library libev can emulate libevent so I don't see why someone couldn't take the server and do a side by side comparison.

Reply | Parent | Thread

Re: libev would be nice[r]

from: anonymous
date: Mar. 30th, 2009 08:01 pm (UTC)
Link

eBay spearheaded having event ports / pool of threads support in their own custom build of MySQL enterprise about 2 years ago with their donation of a sample implementation (developed by Igor Chernyshev), which MySQL productionalized the implementation and eBay has been running for over 1.5 years now. What is different about the implementation in 6.0 with what MySQL has worked on with eBay?

Reply | Parent | Thread

Brian "Krow" Aker

Re: libev would be nice[r]

from: [info]krow
date: Mar. 30th, 2009 08:04 pm (UTC)
Link

EBay's version used a specific Solaris call, while the version in MySQL/Drizzle uses libevents. We will be introducing the Solaris one when we get a chance.

Reply | Parent | Thread