[Maria-developers] Thread pool (request for review)
![](https://secure.gravatar.com/avatar/6b1cecb6596aaa9fe497d5bd2bb2f5c1.jpg?s=120&d=mm&r=g)
Hello, Worklog#246 http://askmonty.org/worklog/Server-BackLog/?tid=246 is ready to review. The worklog is implemented in lp:~maria-captains/maria/5.5-threadpool, and if someone prefers to have a one big diff, it is in the attachment(diff.diff) It would be great if Monty, Serg (optionally Kristian if there is interest) could take a some time to review it in the near future. The worklog has extensive description of the implementation, might be helpful to consult it before reviewing. The branch currently has thread-handling=pool-if-threads as default, I found it easier for testing. Based on results of benchmark (see below), it seems to be generally performing well. So maybe we should use it as default, beta version of 5.5 for better testing coverage . Maybe we can keep it as new default after testing default, if there are no complains. Benchmarking: I also run some sysbench on 3 machines (2 Linuxes - fb-maria1 with 16 cores and pitbull with 24 cores, and windows one with 8 cores but good IO), results in https://docs.google.com/spreadsheet/ccc?key=0AqvHLyfRWjmbdDY0U1MzZGlXY3VGdUx xcC1pUlBZeHc I used sysbench 0.4 to run some of "unit" oltp tests (point-select and update-nokey), as well as mere "classic" OLTP-readonly and OLTP-readwrite. All tests are run with number of concurrent clients ranging from 16 to 4096, with warm cache, with sysbench table having 1Mio records. The results seem to be quite different on all of the machines tested (the machines are very different, in terms of cores, IO etc) , yet threadpool has a positive effect on all 3 machines, and the positive effect seems to grow with the number of cores. Some words about how I ran the benchmarks 1. Benchmark client and server used different CPUs - (I used 'taskset' (Linux), or 'start /affinity' (Windows) to run benchmark client on #CPUs/4, the rest of CPUs are used by the server). On Linuxes, I used --thread_pool_size=<N> where N is number of cores I dedicate to server. 2.I used innodb_flush_log_at_trx_commit=0 and innodb_flush_method= ALL_O_DIRECT to avoid extensive fsyncing, which I think is ok for the purposes of the testing for this WL. 3. Every "write" benchmark (oltp_rw and update_nokey) started with new server (i.e I kill mysqld, remove innodb files, and restart mysqld for each test),whereas every "read" benchmark reused the same running server instance. Starting afresh with a new server on write benchmarks is done mailnly to eliminate the effects of the purge lag. I attach config file sysbench.ini is the one I used on fb-maria1 In case I missed to say it before, Happy Xmas and Happy New Year! Wlad PS. Elena, we need to have some plan on testing it. PPS. Kristian, tried to integrate the 5.5-threadpool branch into Buildbot, and stuck. Can you help?
![](https://secure.gravatar.com/avatar/06b8a7f236cf59aa9a83cab1a0551ca8.jpg?s=120&d=mm&r=g)
Hi!
"Vladislav" == Vladislav Vaintroub
writes:
Vladislav> Hello, Vladislav> Worklog#246 http://askmonty.org/worklog/Server-BackLog/?tid=246 is ready Vladislav> to review. The worklog is implemented in Vladislav> lp:~maria-captains/maria/5.5-threadpool, and if someone prefers to have a Vladislav> one big diff, it is in the attachment(diff.diff) Vladislav> It would be great if Monty, Serg (optionally Kristian if there is interest) Vladislav> could take a some time to review it in the near future. The worklog has Vladislav> extensive description of the implementation, might be helpful to consult it Vladislav> before reviewing. I will look at this tomorrow. Vladislav> The branch currently has thread-handling=pool-if-threads as default, I found Vladislav> it easier for testing. Based on results of benchmark (see below), it seems Vladislav> to be generally performing well. So maybe we should use it as default, beta Vladislav> version of 5.5 for better testing coverage . Maybe we can keep it as new Vladislav> default after testing default, if there are no complains. We should probably not have this as default as the behavior if you have many long running queries with the thread pool will be much worse than with one-thread-per-connection. Vladislav> Benchmarking: Vladislav> I also run some sysbench on 3 machines (2 Linuxes - fb-maria1 with 16 cores Vladislav> and pitbull with 24 cores, and windows one with 8 cores but good IO), Vladislav> results in Vladislav> https://docs.google.com/spreadsheet/ccc?key=0AqvHLyfRWjmbdDY0U1MzZGlXY3VGdUx Vladislav> xcC1pUlBZeHc Vladislav> I used sysbench 0.4 to run some of "unit" oltp tests (point-select and Vladislav> update-nokey), as well as mere "classic" OLTP-readonly and OLTP-readwrite. Vladislav> All tests are run with number of concurrent clients ranging from 16 to 4096, Vladislav> with warm cache, with sysbench table having 1Mio records. Vladislav> The results seem to be quite different on all of the machines tested (the Vladislav> machines are very different, in terms of cores, IO etc) , yet threadpool has Vladislav> a positive effect on all 3 machines, and the positive effect seems to grow Vladislav> with the number of cores. Please work with Axel to make a benchmark article out of the above. <cut> Regards, Monty
![](https://secure.gravatar.com/avatar/6b1cecb6596aaa9fe497d5bd2bb2f5c1.jpg?s=120&d=mm&r=g)
-----Original Message----- From: Michael Widenius [mailto:monty@askmonty.org] Sent: Dienstag, 27. Dezember 2011 11:42 To: Vladislav Vaintroub Cc: maria-developers@lists.launchpad.net; serg@askmonty.org; Kristian Nielsen; elena@askmonty.org Subject: re: Thread pool (request for review)
H Monty,
We should probably not have this as default as the behavior if you have many long running queries with the thread pool will be much worse than with one-thread-per-connection.
We should measure it, but the implementation is such that if one has many long running queries, the performance should not be that much worse. We do start extra threads every now and then, either when queries block (threads have to wait), or if no new queries can be processed. Even if CPUs are loaded 100% with existing long queries, even then new threads will be started (just to ensure queries are processed, even if that is unlikely to improve throughput) Wlad
participants (2)
-
Michael Widenius
-
Vladislav Vaintroub