Bug #76686 | reduce mutex contention of LOCK_done during Group Commit | ||
---|---|---|---|
Submitted: | 14 Apr 2015 9:14 | Modified: | 24 Nov 2015 13:49 |
Reporter: | zhai weixiang (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
Version: | 5.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[14 Apr 2015 9:14]
zhai weixiang
[14 Apr 2015 9:15]
zhai weixiang
a simple patch to prove the performance improvement , not fully tested (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: signal_partition_57.diff (application/octet-stream, text), 7.59 KiB.
[28 Apr 2015 12:32]
MySQL Verification Team
Hi Zhai, Thanks for the report and for the patch. I need to double check: > threads, orignal , after patching > 16, 31300, 31400 > 32, 34900, 35300 > 64, 36500, 38500 > 128, 37700, 39500 > 256, 35600, 37200 What these numbers represent (original and after patch)? rw/sec, no of locks.. ? I did test your patch and I can say running the update_non_index.lua test I get 10-15% more requests per second and and 40% shorter response time without your patch. can you confirm your numbers, what exactly are you counting and did you compare response times, rw requests/sec and some other metric during the test with your patch? all best Bogdan Kecman
[28 Apr 2015 14:07]
zhai weixiang
Hey, Bogdan. These numbers represen updates/sec. Can you give me some details about your testing server..This is really a simple patch and shouldn't introduce in regression! I just re-tested the patch and can still see the improvement though the number is a little different. my sysbench command: sysbench/sysbench --debug=off --test=sysbench/tests/db/update_non_index.lua --oltp-tables-count=10 --oltp-point-selects=0 --oltp-table-size=100000 --num-threads=$1 --max-requests=10000000000 --max-time=7200000 --oltp-auto-inc=off --mysql-engine-trx=yes --mysql-table-engine=innodb --oltp-test-mod=complex --mysql-db=$2 --mysql-host=$host --mysql-port=$port --mysql-user=$username --report-interval=5 --percentile=99 run The server has 24 cpu cores and 196GB memory, all data can fit in memory. I set sync_binlog = 1, innodb_flush_log_at_trx_commit=1 (different with the previous test) updates/sec : threads, Original, after patching 16, 17300, 17400 32, 25700, 26100 64, 31700, 33500 128, 37000, 39100 256, 37600, 38800 I've checked the RT, and didn't find any regression.
[7 May 2015 8:45]
MySQL Verification Team
Hi Zhai, > These numbers represen updates/sec. Then I definitely can't reproduce your results and I tried on 2 different boxes. > Can you give me some details about your testing server.. > This is really a simple patch and shouldn't introduce in regression! I too don't see reason for the regression but I do see the regression on 2 servers I tested on. First server is a small testing box (E6850@3GHz, 16G ram, sata spinning disks in 1+0 raid) and second server is 12 core Xeon(R) CPU E5-1650 v2 @ 3.50GHz with 128G ram single spinning disk (I did not test with SSD's). > I just re-tested the patch and can still see > the improvement though the number is a little different. I run the test 6 times on both machines, every time the patched version had less updates/sec then unpatched version, both compiled on the machine where test was running. > my sysbench command: > > sysbench/sysbench --debug=off --test=sysbench/tests/db/update_non_index.lua --oltp-tables-count=10 --oltp-point-selects=0 --oltp-table-size=100000 --num-threads=$1 --max-requests=10000000000 --max-time=7200000 --oltp-auto-inc=off --mysql-engine-trx=yes --mysql-table-engine=innodb --oltp-test-mod=complex --mysql-db=$2 --mysql-host=$host --mysql-port=$port --mysql-user=$username --report-interval=5 --percentile=99 run The tests I made is different (running with different num-threads everything else is same each time): sysbench --num-threads=64 --test=./sysbench/tests/db/update_non_index.lua --mysql-socket=/tmp/mysql.sock --mysql-user=sb --mysql-password=sysbench --mysql-db=sysbench run I'll re-run it with additional parameters to sysbench but I doubt I'll see a difference > The server has 24 cpu cores and 196GB memory, all data can fit in memory. I think all data fits into ram on my 128G server but I did not inspect the test so can't be 100% sure will check, and did not fit in ram on my first server (intentionally using smaller server). I seen more degradation on the first server (data not all in ram) then on the bigger one but in both cases I see less updates/sec with patch. > I set sync_binlog = 1, innodb_flush_log_at_trx_commit=1 (different with the previous test) I was running the test with "default" config (only ibd pool is 80% of the ram everything else is not configured so defaults used), seems appropriate for testing this type of patch > I've checked the RT, and didn't find any regression. I'll re-run the test on a larger, 24 core machine to see if I can reproduce your results but in any way having regression on the smaller setup is worrying. all best Bogdan
[7 May 2015 10:29]
zhai weixiang
Hi, Bogdan With default configuration , I guess the main bottleneck will not be group commit, but dirty page preflush because the default log file size is too small. :) I suggest you configure the following option: innodb_log_files_in_group=4 innodb_log_file_size=4G innodb_log_buffer_size=100M Besides, If we don't consider the result of the test (of course, the test is important !!), what' your opinion about this patch ? should the performance be improved In theory ?
[7 May 2015 10:53]
MySQL Verification Team
Hi, I'll redo the test on larger machine and with different config but even if this do change result for the better there's still a question of 10-15% lower performance on smaller setup that we can't ignore. Why this patch introduces this degradation I can't say, from looking at the patch I see it can improve performance in certain situation and do not affect performance in other - I don't see a scenario where it will degrade performance; but there's obviously something I'm not seeing that's there as tests are pretty conclusive :( Let me rerun the test and then, if I, in any scenario, reproduce your results, we can then introduce this patch to a review committee for further analysis. all best Bogdan Kecman
[9 Jun 2015 11:43]
MySQL Verification Team
Hi, I did verify this. Now it's up to "higher instances" on how to proceed :) thanks for your work Bogdan Kecman
[24 Nov 2015 13:49]
zhai weixiang
just to record, there is a bug in the patch i attached before, cond_index will overflow after long time running and turn negative. This situation should be taken into consideration.
[8 Jan 2016 6:23]
zhai weixiang
rebase to 5.7.10 and fix several bugs
Attachment: group_commit_signal.diff (text/x-patch), 10.99 KiB.