Investigate performance degradation with compression #4721
Description
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.