compression - 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.