Bug #42288 thread_handling = pool-of-threads is slow
Submitted: 23 Jan 2009 1:57 Modified: 15 Aug 2012 19:11
Reporter: Mark Callaghan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:6.0.9 OS:Linux
Assigned to:
Tags: pool-of-threads, thread_handling

[23 Jan 2009 1:57] Mark Callaghan
Description:
I ran sysbench OLTP readonly for 6.0.9 with and without the thread pool enabled. It is up to 2X faster for InnoDB and 3X for blackhole without the thread pool using:
* 4 core server
* 1, 2, 4, 8 and 16 concurrent users
* CPU bound test

My assumption is that this difference will get worse on larger SMP servers.

To enable the thread pool I set:
* thread_handling=pool-of-threads
* thread_pool-size=20

The numbers are the value on the transactions: line from sysbench. This is TPS.

for innodb

option       #users
        1   2   4    8   16
----------------------------
nopool 447 448 736 1056 708
pool   211 346 447  387 356

for blackhole

option       #users
        1    2   4    8   16
----------------------------
nopool 999 1890 3428 3428 2999
pool   401  676  839  864  959

How to repeat:
see above
[23 Jan 2009 16:14] Mark Callaghan
And the mutex for this is LOCK_event_loop
[18 Mar 2009 5:20] Mark Callaghan
I need to rerun the tests on a 4-core server. Here are results from a 16-core server. They are not as bad:

* 6.0.9 
* innodb
* modified sysbench readonly
* transactions / second for 1, 2, 4, 8 16 concurrent sessions

  1   2   4    8   16 concurrent users
213 432 764 1150 788 without thread pool
185 323 555  801 668 with thread pool
[18 Mar 2009 16:37] Mark Callaghan
And modified sysbench readonly results for blackhole on the 16-core server:
  1    2    4    8   16 concurrent users
529 1110 2085 3569 3688  without a thread pool
432  722 1145 1500 1711  with a thread pool and thread_pool_size=20
[19 Mar 2009 17:14] Mark Callaghan
I get 10% better throughput for 16 concurrent users when LOCK_event_loop and LOCK_thd_add in scheduler.cc are changed to use MY_MUTEX_INIT_FAST in the pthread_mutex_init() calls.
[19 Mar 2009 17:38] Mark Callaghan
From my SHOW GLOBAL MUTEX STATUS patch and using the pool-of-threads backport we are working on for 5.0, here are the hot mutexes from a run with:
* sysbench readonly
* 16 core server and linux 2.6
* thread_handling=pool-of-threads, thread_pool_size=20

SHOW GLOBAL MUTEX STATUS
Locks   Spins   Sleeps  Name    Line    Users
4377993 9895197 2354647 scheduler.cc    268     1
9664940 4935027 638017  scheduler.cc    269     1
8755858 4291150 399476  mysqld.cc       3277    1
1563346 102878  9944    my_thr_init.c   155     1
4377816 162611  4558    mysqld.cc       3323    1
8756067 192858  3661    mysqld.cc       3278    1
8755177 363963  3363    slave.h 462     2
937827  2075    26      mf_tempdir.c    30      1
625392  1630    17      my_bitmap.c     76      1
[19 Mar 2009 17:54] Mark Callaghan
And lock waits by caller.

BY CALLERS
Locks   Spins   Sleeps  Name    Line    Users
0 0 2354699 scheduler.cc 568 0
0 0 424107 scheduler.cc 684 0
0 0 223241 sql_base.cc 539 0
0 0 176244 sql_base.cc 1375 0
0 0 132951 scheduler.cc 431 0
0 0 80981 scheduler.cc 399 0

scheduler.cc:568 in libevent_thread_proc

564   for (;;)
565   {
566     THD *thd= NULL;
567     login_error = 0;
568     (void) pthread_mutex_lock(&LOCK_event_loop);

scheduler.cc:684

681 static void libevent_thd_add(THD* thd)
682 {
683   char c=0;
684   pthread_mutex_lock(&LOCK_thd_add);

scheduler.cc:431 is pthread_mutex_lock(&LOCK_thd_add) at the end of libevent_add_thd_callback

scheduler.cc:399 is pthread_mutex_lock(&LOCK_thd_add) at the start of libevent_add_thd_callback
[14 Apr 2009 8:17] Sergei Golubchik
may be relevant:

http://blogs.sun.com/timc/entry/testing_the_new_pool_of
http://blogs.sun.com/timc/entry/testing_the_new_pool_of1
[29 Sep 2009 19:33] Sveta Smirnova
Thank you for the report.

With current mysql-6.0-codebase on 4 cores box I get results following:

Innodb without pool-of-threads
1		2		4		8		16
89.84	166		117.98	70.51	48.63
Innodb with pool-of-threads
90.92	153.67	125.14	84.16	53.58
Blackhole with pool-of-threads
306.50	290.33	257.11	168.00	165.34
Blachole without pool-of-threads
305.15	302.18	208.12	209.15	163.35

This looks like problem is fixed. Please test in your environment to be sure.
[29 Sep 2009 19:34] Sveta Smirnova
Version # is 6.0.14-alpha
[29 Sep 2009 19:36] Mark Callaghan
Where is 6.0.14-alpha? On dev.mysql.com, 6.0 is only listed in the archives section. The community server link lists 5.4, 5.1, 5.0
[29 Sep 2009 20:25] Sveta Smirnova
Thank you for the feedback.

Looks like I used link to internal branch. Public branch https://code.launchpad.net/~mysql/mysql-server/mysql-next-mr has pool_of_threads_scheduler code.

Please apply patch following to get it working.

$bzr diff
=== modified file 'sql/scheduler.h'
--- sql/scheduler.h     2009-06-22 09:36:50 +0000
+++ sql/scheduler.h     2009-09-29 12:02:45 +0000
@@ -56,7 +56,7 @@
   NOT_IN_USE_OP= 0, NORMAL_OP= 1, CONNECT_OP, KILL_OP, DIE_OP
 };
 
-#define HAVE_POOL_OF_THREADS 0                  /* For easyer tests */
+#define HAVE_POOL_OF_THREADS 1                  /* For easyer tests */
 #define pool_of_threads_scheduler(A) one_thread_per_connection_scheduler(A)
 
 class thd_scheduler
[2 Oct 2009 23:08] Mark Callaghan
Don't you also need to remove the line that follows that in scheduler.h?
#define pool_of_threads_scheduler(A) one_thread_per_connection_scheduler(A

But when that is done, pool_of_threads_scheduler doesn't exist.
I don't think pool-of-threads code is in the mysql-next-mr branch on launchpad.
[6 Oct 2009 17:35] Sveta Smirnova
Mark,

thank you for pointing to this problem. I am trying to find in which public tree exists pool-of-threads and update the report/retest when I know the answer.
[15 Aug 2012 19:11] Sveta Smirnova
Closed as "Won't fix", because thread pooling is implemented in different way now.