When should I use MySQL compressed protocol?

Question

I've learned that MySQL can compress communication between servers and clients.

Compression is used if both client and server support zlib compression, and the client requests compression.

(from MySQL Forge Wiki)

The most obvious pros and cons are

• pros: Reduced payload size
• cons: Increased computation time

So, is compressed protocol something I should enable whenever I can afford servers with adequate specs? Are there other factors I should consider?

Answer 1

Performance benefits are going to be largely dependent on the size of the result sets that you are sending, in addition to the network bandwidth and latency between the database server and its clients.

The larger the result sets, the larger the latency, or the less bandwidth, the more likely you will see the benefit of compression.

Your maximum level of service is limited to the smallest bottleneck. So, you need to analyze where you're currently at regarding network and CPU resources.

The most optimized database server utilizes 100% of its CPU 100% of the time, otherwise you're wasting computing resources by having a processor that's sitting there not doing anything. Of course, you don't want it at 101%, so your target range is well below 100%. Yet, my point is that if you have a lot of headroom before you reach a CPU bottleneck, and the result sets are a significant size, and the network is a factor, then turn compression on. CPU cycles are cheap, especially unused ones (you do pay for electricity and cooling).

If you pay for bandwidth, trading CPU usage for bandwidth is easily justified, and even if you're not anywhere near reaching the bandwidth bottleneck, that faster speed, and higher level of service, is worth something.

Don't forget that the client must also expend CPU cycles to decompress the data. Not a major issue, but still a factor. In general, today's CPUs are faster than today's networks.

Answer 2

I know it's late, but I though I might share this:

• Large result sets vs compression

It turns out that 100 Mbit link (with 1.4 ms round-trip time) is not fast enough... With compression, total indexing time reduced to 87 sec from 127 sec. That’s almost 1.5x improvement in total run time. MySQL query time improvement is even greater. On the other hand 1 Gbit link was fast enough; and total run time was 1.2x times worse with compression.

Unless your database and client are on the same machine, on 100 Mbits network and slower, enable the compression!

However, your final decision might also to depend on the balance between the cost of CPU cycles (compress/decompress) and Bandwith usage (more data on the wire).