Bug #83151 | high number of SHOW WARNINGS can stall server if compressed protocol in use | ||
---|---|---|---|
Submitted: | 26 Sep 2016 14:27 | Modified: | 23 Jan 2019 14:13 |
Reporter: | Riccardo Pizzi | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Connection Handling | Severity: | S2 (Serious) |
Version: | 5.6.33 | OS: | CentOS |
Assigned to: | CPU Architecture: | Any | |
Tags: | performance |
[26 Sep 2016 14:27]
Riccardo Pizzi
[27 Sep 2016 6:31]
MySQL Verification Team
Hello Riccardo, Thank you for the report. Thanks, Umesh
[23 Jan 2019 14:10]
Nikolai Ikhalainen
Hello, Are you still able to reproduce this issue? I'm getting significantly better results for show warnings (or select 1) query execution in recent 5.6, 5.7 and 8.0: docker cp show_warnings.lua mysql83151_sysbench_1:/usr/share/sysbench/ for h in mysql5633 mysql5643 mysql5725 mysql8014; do echo $h; docker-compose exec sysbench bash -c "echo -n no compression;sysbench --threads=16 --mysql-compression=off --mysql-host=$h --mysql-user=root --mysql-password=secret --mysql-db=information_schema /usr/share/sysbench/show_warnings.lua run 2>&1 |grep -F queries:;echo -n ' compression';sysbench --threads=16 --mysql-compression=on --mysql-host=$h --mysql-user=root --mysql-password=secret --mysql-db=information_schema /usr/share/sysbench/show_warnings.lua run 2>&1 |grep -F queries:" ; done mysql5633 no compression queries: 1420836 (142018.55 per sec.) compression queries: 430418 (43025.41 per sec.) mysql5643 no compression queries: 1323344 (132289.08 per sec.) compression queries: 1025112 (102478.62 per sec.) mysql5725 no compression queries: 1211112 (121062.25 per sec.) compression queries: 893404 (89311.30 per sec.) mysql8014 no compression queries: 994196 (99382.34 per sec.) compression queries: 821052 (82070.60 per sec.) the test environment created with docker-compose up: docker-compose.yml: version: '2' services: mysql5633: image: mysql:5.6.33 environment: - MYSQL_ROOT_PASSWORD=secret mysql5643: image: mysql:5.6.43 environment: - MYSQL_ROOT_PASSWORD=secret mysql5725: image: mysql:5.7.25 environment: - MYSQL_ROOT_PASSWORD=secret mysql8014: image: mysql:8.0.14 environment: - MYSQL_ROOT_PASSWORD=secret command: ["--loose-default-authentication-plugin=mysql_native_password"] sysbench: image: severalnines/sysbench:latest command: ["/bin/sleep", "86400"] show_warnings.lua: #!/usr/bin/env sysbench require("oltp_common") function prepare_statements() end function event() con:query("SELECT 1/0") con:query("SHOW WARNINGS") end
[23 Jan 2019 14:13]
Riccardo Pizzi
You did not mention CPU consumption in your tests. Not sure docker is a good way to test this bug...
[26 Jan 2019 4:54]
Nikolai Ikhalainen
Hi Riccardo, I have all 4 CPU cores fully utilized during test. I'm seeing a huge improvements for for small queries and compression inside and outside docker compare to 5.6.33 (with sysbench, but not with mysqlslap). So, are you still able to reproduce this bug with fresh mysql 5.6, 5.7 or 8.0?
[14 Sep 2021 10:02]
MySQL Verification Team
I also don't see any real problem on current 8.0.28-trunk. 16 threads running with --compress=0 yields 132k qps vs 88k qps with --compress=1 and the CPU cores are fully used and no severe degradation happens with more threads, even 32 threads doesn't get slower. -- processor : 7 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz stepping : 9 microcode : 0x21 --