"show warnings" uses an abnormal amount of system CPU if client compression is ON
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
New
|
Undecided
|
Unassigned | |||
5.6 |
New
|
Undecided
|
Unassigned | |||
5.7 |
New
|
Undecided
|
Unassigned |
Bug Description
When using client/server compression, issuing SHOW WARNINGS eats an incredible amount of system CPU time. This behaviour is already visible with 16 threads, at 64 threads it will saturate a 32 CPU machine completely.
We have encountered this bug in production, where an ORM was configured to issue a SHOW WARNINGS statement after each and every query. We had complete MySQL stalls that lasted from several seconds to few minutes because of this, as the machine was CPU bound during this time with 99% of system time used by MySQL server. MySQL was unable to perform any operation during the stall.
This only happens if you have MySQL compression enabled!
How to reproduce:
- install sysbench with MySQL driver from usual source
- copy the attached LUA script to your test system
- run sysbench as follows: ./sysbench --mysql-host=.... --mysql-
- watch system CPU reach 100%
This has been verified on 5.6.26-74.0-log Percona Server (GPL), Release 74.0
This is an upstream bug, I have raised it in Oracle bug system too: https:/ /bugs.mysql. com/bug. php?id= 83151