Investigate performance degradation with compression

[renecannao]

There is a severe performance degradation when clients connect to ProxySQL using compression.

Some example with this query:

QUERY="SELECT t1.id id1, t1.k k1, t1.c c1, t1.pad pad1, t2.id id2, t2.k k2, t2.c c2, t2.pad pad2 FROM sbtest.sbtest1 t1 JOIN sbtest.sbtest1 t2 LIMIT 10000000"

Direct connection without compression:

$ time mysql --quick -u sbtest -psbtest -h 127.0.0.1 -P3306 -e "$QUERY" > /dev/null
real    0m25.040s
user    0m24.070s
sys     0m0.967s

Direct connection with compression:

$ time mysql --quick -u sbtest -psbtest -h 127.0.0.1 -P3306 -e "$QUERY" -C > /dev/null
real    0m59.931s
user    0m34.521s
sys     0m0.921s

Using ProxySQL without compression:

$ time mysql --quick -u sbtest -psbtest -h 127.0.0.1 -P6033 -e "$QUERY" > /dev/null
real    0m25.307s
user    0m24.276s
sys     0m0.989s

Using ProxySQL with compression:

$ time mysql --quick -u sbtest -psbtest -h 127.0.0.1 -P6033 -e "$QUERY" -C > /dev/null
real    2m0.343s
user    0m30.441s
sys     0m0.455s

It seems that compression is a big performance penalty for both MySQL and ProxySQL , but it seems that ProxySQL is twice as slow than MySQL.

ProxySQL uses compress() instead of lower level zlib functions, and this uses Z_DEFAULT_COMPRESSION that is 6 (on a scale from 1 to 9).
So I guess it is "just" a matter of compression level.

[dankow]

In our experience, a query with a large result set being run on a compressed connection affected the performance of ProxySQL in general, not just for that query. We saw a general drop in throughput for the whole ProxySQL server (shown by an increase in backend connections in use) which makes it a more serious bug than if it only affected performance for that one query.

[MichalisDBA]

@renecannao

Sorry to post here but there is a similar problem that makes the queries slow if you set mysql-threshold_resultset_size to a value of 536870912 and greater.

#4707

[renecannao]

@MichalisDBA : it is unlikely related.
But we will try to look into it as soon as we finalize some high priority deadlines

[ThomasVerhoeven1998]

What is the expected ETA for a fix? Or do you recommend disabling compression if you have this currently enabled?

[renecannao]

@ThomasVerhoeven1998 : this issue was observed with an exceptional large resultset, and probably shouldn't be noticeable with smaller resultset.
If you are affected, right now the only solution is to completely disable compression, setting mysql-have_compress=false

[charles-001]

We encountered this issue and turning off compression like Rene suggested mitigated the issue.

[renecannao]

@dankow : I missed your comment, sorry.
Indeed, what you are saying makes perfectly sense.
When a worker thread is busy compressing (that means using a lot of CPU resources) it can't serve other connections: a portion of the traffic is affected.
If compression is used by multiple connections and multiple/all worker threads are busy compressing, then all the traffic is affected.

BTW, @yashwantsahu20 is already working on this.