zlib affects queries performance

[dbalabka]

After some benchmarking I have figure out that zlib compresion affects query performance a lot.

w/ zlib:

+------------+----------------------------+--------+--------+------+-----+------------+-----------+------------+------------+-------------+------------+---------+------------+
| benchmark  | subject                    | groups | params | revs | its | mem_peak   | best      | mean       | mode       | worst       | stdev      | rstdev  | diff       |
+------------+----------------------------+--------+--------+------+-----+------------+-----------+------------+------------+-------------+------------+---------+------------+
| QueryBench | benchPdoQueries            |        | []     | 1    | 10  | 4,439,176b | 1.17000ms | 1.87690ms  | 1.68573ms  | 3.83200ms   | 0.68067ms  | 36.27%  | 0.00%      |
| QueryBench | benchSyncQueries           |        | []     | 1    | 10  | 4,439,184b | 5.76100ms | 46.13990ms | 10.12466ms | 228.74400ms | 74.52826ms | 161.53% | +2,358.30% |
| QueryBench | benchAsyncQueries          |        | []     | 1    | 10  | 4,439,184b | 4.63700ms | 9.31360ms  | 6.30906ms  | 20.17200ms  | 4.89743ms  | 52.58%  | +396.22%   |
| QueryBench | benchAsyncQueriesUsingPool |        | []     | 1    | 10  | 6,633,522b | 3.53700ms | 4.33780ms  | 3.87706ms  | 6.75500ms   | 0.98273ms  | 22.65%  | +131.12%   |
+------------+----------------------------+--------+--------+------+-----+------------+-----------+------------+------------+-------------+------------+---------+------------+

w/o zlib:

+------------+----------------------------+--------+--------+------+-----+------------+-----------+-----------+-----------+------------+-----------+--------+----------+
| benchmark  | subject                    | groups | params | revs | its | mem_peak   | best      | mean      | mode      | worst      | stdev     | rstdev | diff     |
+------------+----------------------------+--------+--------+------+-----+------------+-----------+-----------+-----------+------------+-----------+--------+----------+
| QueryBench | benchPdoQueries            |        | []     | 1    | 10  | 4,433,944b | 1.28300ms | 1.50140ms | 1.52774ms | 1.76400ms  | 0.13752ms | 9.16%  | 0.00%    |
| QueryBench | benchSyncQueries           |        | []     | 1    | 10  | 4,433,952b | 4.29400ms | 7.52330ms | 4.86931ms | 15.60100ms | 4.11548ms | 54.70% | +401.09% |
| QueryBench | benchAsyncQueries          |        | []     | 1    | 10  | 4,433,952b | 3.94700ms | 4.96600ms | 4.40386ms | 9.93300ms  | 1.68206ms | 33.87% | +230.76% |
| QueryBench | benchAsyncQueriesUsingPool |        | []     | 1    | 10  | 6,592,336b | 2.97000ms | 3.20040ms | 3.27979ms | 3.35800ms  | 0.12980ms | 4.06%  | +113.16% |
+------------+----------------------------+--------+--------+------+-----+------------+-----------+-----------+-----------+------------+-----------+--------+----------+

Probably it must not be enabled by default when zlib is available.

Benchmark source: https://github.com/torinaki/mysql/blob/benchmarks/benchmarks/QueryBench.php

[bwoebi]

Probably a good point - usually you're anyway having the mysql server on the same machine or having a very high bandwidth to it, making CPU and not bandwidth the bottleneck...

[dbalabka]

@bwoebi I have take a look on compression/uncompression implementation and have following question. Have you considered possibility to use Stream Compression Filters instead of zlib_encode/zlib_decode? I just saw such approach in https://github.com/clue/php-zlib-react

[bwoebi]

It is not possible to reliably attach buffering stream filters to a non-blocking stream.

Thus: no.

[dbalabka]

@bwoebi I have disabled compression and now the most time is spent on protocol parsing. Hoply when PHP JIT will be released it will perform much better.
Just A Thought. If I'm right mysqlnd already using PHP streams. It would be great if in future there will be possbile to parse MySQL protocol using mysqlnd implementation and native stream wrapper. Not sure is it possible to make it work with non-blocking streams(accoriding to your previous comment).

[bwoebi]

Yes, protocol parsing is the most CPU intensive part of this. Something like a JIT will probably indeed bring quite some benefit.

I'll look at disabling zlib compression by default.