Testing the New Pool-of-Threads Scheduler in MySQL 6.0, Part 2

In my last blog, I introduced my investigation of the "Pool-of-Threads" scheduler in MySQL 6.0. Read on to see where I went next.

I now want to take a different approach to comparing the two schedulers. It is one thing to compare how the schedulers work "flat out" - with a transaction request rate that is limited only by the maximum throughput of the system under test. I would like to instead look at how the two schedulers compare when I drive mysqld at a consistent transaction rate, then vary only the number of connections over which the transaction requests are arriving. I will aim to come up with a transaction rate that sees CPU utilization somewhere in the 40-60% range.

This is more like how real businesses use MySQL every day, as opposed to the type of benchmarking that computer companies usually engage in. This will also allow me to look at how the schedulers run at much higher connection counts - which is where the pool-of-threads scheduler is supposed to shine.

Now, I will let you all know that I first conducted my experiments with mysqld and the load generator (sysbench) on the same system. I was again not sure this would be be the best methodology, primarily because I would end up having one operating system instance scheduling in some cases a very large number of sysbench threads along with the mysqld threads.

It turned out the results from this mode threw up some issues (like not being able to get my desired throughput with 2048 connections in pool-of-threads mode), so I repeated my experiments - the second set of results have the load generation coming from two remote systems, each with a dedicated 1 Gbit ethernet link to the DB server.

The CPU utilization I have captured was just the %USR plus %SYS for the mysqld process. This makes the two sets of metrics comparable.

Here are my results. First for experiments where sysbench ran on the same host as mysqld:

Then for experiments where sysbench ran on two remote hosts, each with a dedicated Gigabit Ethernet link to the database server:

As you can see, the pool-of-threads model does incur an overhead, both in terms of CPU consumption and response time, at low connections counts. As hoped though, the advantage swings in pool-of-threads' favour. This is particularly noticeable in the case where our clients are remote. It is arguable that an architecture involving many hundreds or thousands of client connections is more likely to have those clients located remote from the DB server.

Now, the first issue I have is that while pool-of-threads starts to win on response time, the response time is still increasing in a similar fashion to thread-per-connection's response time (note - the scale is logarithmic). This is not what I expected, so we have a scalability problem in there somewhere.

The second issue is where I have to confess - I only got one "lucky" run where my target transaction rate was achieved for pool-of-threads at 2048 connections. For many other runs, the target rate could not be achieved, as these raw numbers show:

connections tps mysqld
%usr
mysqld
%sys
mysqld
%cpu
avg-resp 95%-resp
2048962.2225.2314.9340.161943.782368.78
20481197.0030.5911.2041.79317.98435.19
2048836.5021.9811.0933.072259.362287.03
2048963.0026.4912.0738.561333.671128.93
2048992.2525.8115.0840.891851.172280.50
2048915.7124.1615.0539.212220.452342.06
2048919.5424.2515.0539.302210.952331.45
2048917.0924.1515.0539.202217.862321.40
2048875.0923.2013.2936.492188.692344.91
20481180.6231.3514.5745.921439.961772.86
20481185.8030.7414.2444.981185.711814.24
20481146.9030.3415.2345.571602.851842.14
20481141.4730.2015.2245.421612.341873.95
20481158.7430.4712.9943.46999.761870.35
20481177.5930.6714.9745.641403.221838.84

This indicates we have some sort of bottleneck right at or around the 2048 thread point. This is not what we want with pool-of-threads, so I will continue my investigation.

Comments:

Tim, Do you know if the pool-of-threads penalty is similar to shared servers penalty in Oracle?

Posted by neel on April 10, 2009 at 01:19 PM PDT #

MySQL offers many freedoms, including the freedom to publish performance results. I don't think that Oracle allows that.

In MySQL, a thread is allocated for the duration of a query. That is not the case in Oracle MTS. It does finer grain sharing. For the targeted usage, fast OLTP queries, this won't be a problem. But some of us want to use it on reporting servers.

Posted by Mark Callaghan on April 10, 2009 at 05:08 PM PDT #

Neel, I can't say yet if the penalty is similar to that for Oracle's Shared Server feature (which I have tested), as I do not have much data.

Oracle does allow you the flexibility to run Shared Server and conventional dedicated shadow processes together on the same instance. I have noticed that Google has developed a similar option in their work. I think we should consider this for MySQL.

Posted by Tim Cook on April 13, 2009 at 02:37 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

Tim Cook's Weblog The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today