So does Pooling Connections buy me anything?

« previous entry | next entry »
Feb. 6th, 2007 | 12:55 am

Every so often I have someone ask me "does it really matter if I pool
connections?".

The answer is yes!

We are currently looking at patches for MySQL to disconnect thread
context from actual threads (and yes, that means that if I can get
some bugs worked out I will have a copy of MySQL running with
libevent, Monty is working on a Windows and Solaris specific patch).
I was curious though about the current context cost so I rigged up a
million row insert test with the number of threads growing by a
hundred at each test run.

The run used:
~/mysqlds/5.1/bin/mysqlslap --create=create2.sql --query="INSERT
INTO accesslog VALUES (1,'GET','4428','HTTP/1.1','/
index.pl','lastnode_id=305&node_id=2802','krow.net','10.10.10.10',NULL,N
ULL,'somedork','2006-08-28 08:32:28')" --
concurrency="100,200,300,400,500,600,700,800,900,1000,1100,1200,1300,140
0,1500,1600,1700,1800,1900,2000" --csv=thread-test.csv --number-of-
queries=1000000 --iterations=10 --engine=blackhole

The setup for IO is real, but no actual disk IO occurred (and the
binlog was shutoff).

So what does it show? There is an increasing cost for additional
threads. Even if you examine the minimum cost around 700 threads the
cost kicks in (this is on a quad proc PPC with 4gigs of RAM). Sites
like Slashdot and Livejournal run with a low timeout on the socket to
keep the thread count down (the cost for reconnect is low but there
is a cost). Keep in mind that this is all active threads. Sites that
run with 2K or more connections normally do not have active threads
hitting the database like this).

It does show the value of the often used approach of using pooled
connections in Java.

And why didn't I go above 1K? Somewhere around 1.2K Ubuntu blew
chunks and started reporting that it was out of file descriptors
(which made no sense, I need to reconfirm the test on my FC5 AMD and
see if it has less issues, or if I need to dig into the server to see
what is up). I also learned from this that our out file descriptor
error message is lame and needs to report what it sees when it hits
the limit.
Picture 5.jpg

Link | Leave a comment | Share

Comments {1}

dormando

(no subject)

from: dormando
date: Feb. 6th, 2007 05:23 pm (UTC)
Link

Finally! I was trying to hack this up myself last year, but the THD class was too scary, and my C too weak. I moved on.

It's not too hard for someone who knows what they're doing to at least get something primitive going. Also, basic detection of "streaming" SQL would cut down on context switches for busy threads. Of course, there're probably better implementations than what I'm thinking.

Reply | Thread