What is MySQL's connection compression overhead on small datasets?

Question

Is there any significant overhead to turning on mysql's connection compression for high load databases or is it always a winner?

We have databases delivering very small datasets back to the requesting program (PHP) but these databases receive very large bursts of traffic from time to time. If I turn on connection compression am I likely to see a significant degredation in performance under load?

We're using modern hardware but not it's a fairly average server setup i.e. one or two dual core CPU's, 8 GB of ram, etc.

Answer 1

There is no way to tell in your situation if it will help or not help. There is simply not enough information.
Does "large bursts of traffic from time to time" mean lots of small packets or a few very large packets or does it relate more to the number of sql queries?

Are the Database Servers on a remote machine(s)?
What's the bandwidth between application and database server, Lan Mbits,Gbits,Tbits. or WAN Kbits,Mbits.?
What does "High Load" mean. are you running out of CPU resources, Memory, Disk IO or bandwidth?

Some notes...

• Compression is cpu intensive
• Compression of large datasets is bandwidth friendly
• Compression on small datasets (what is small?) will probably only add a negligible increase in CPU usage, but will also only improve bandwidth negligibly
• If your application and mysql server are on the same machine I doubt you will get any advantage from compression, because the bandwdth is high and compression and decompression require CPU resources.

Without a more concise description of what the conditions are its hard to give a concise answer.

DC