Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

LP #1599520: "show warnings" uses an abnormal amount of system CPU if client compression is ON

Description

**Reported in Launchpad by Rick Pizzi last update 27-09-2016 08:55:30

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-user=sysbench --mysql-password=sbtest --mysql-db=sbtest --test=show_warnings.lua --max_requests=0 --max_time=20 --num-threads=64 --mysql-engine-trx=yes --mysql-compression=on run

  • watch system CPU reach 100%

This has been verified on 5.6.26-74.0-log Percona Server (GPL), Release 74.0

Environment

None

Smart Checklist

Activity

Show:

Laurynas Biveinis January 22, 2019 at 12:32 PM

, , since the issue is not reproducible on recent PS, can you test recent MySQL and comment on the upstream bug (which is still Verified) accordingly?

Nickolay Ihalainen January 22, 2019 at 5:46 AM

I can't reproduce the issue with recent Percona Server distributions: 5.6.42-84.2, 5.7.24-26, 8.0.13-4

Dockerfile.5.6.33

Dockerfile

select1.lua

show_warnings.lua

Build docker images: 

Measure performance for select 1 or show warnings while compression is enabled or disabled:

Results:

5.6.33 has a 5X performance drop. For modern versions it's 1.7-1.8X.

The observed picture is not changed significantly if SELECT 1 is replaced with SELECT 1/0 to generate warnings.

lpjirasync January 24, 2018 at 9:47 AM

**Comment from Launchpad by: Rick Pizzi on: 27-09-2016 08:22:08

This is an upstream bug, I have raised it in Oracle bug system too: https://bugs.mysql.com/bug.php?id=83151

Cannot Reproduce

Details

Assignee

Reporter

Labels

Upstream Bug URL

https://bugs.mysql.com/bug.php?id=83151

Priority

Smart Checklist

Created January 24, 2018 at 9:47 AM
Updated March 6, 2024 at 1:53 PM
Resolved January 22, 2019 at 5:46 AM