| 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: | |
| Category: | MySQL Server | Severity: | S5 (Performance) |
| Version: | 6.0.9 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | pool-of-threads, thread_handling | ||
[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.
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