While looking to optimize my Rails performance on Amazon EC2 and RDS, I wanted to experiment to see if there are any performance improvements with enabling MySQL compression in the connection protocol since with RDS, the MySQL instance is running on another machine and I’m unsure how far away that machine really is. (Spoiler: it seems to worsen performance with EC2 and RDS)

A couple years ago, I wanted to use MySQL Stored Procedures with a Rails app and found that Rails didn’t support it. The default MySQL connection from ActiveRecord didn’t have the proper flag (Mysql::CLIENT_MULTI_RESULTS) enabled to deal with the results from a stored procedure. This is discussion is currently captured in Lighthouse

It looks like the flag (Mysql::CLIENT_MULTI_RESULTS) is now enabled by default in Rails 2.3.5 (maybe a bit earlier)

If you look in: activerecord/lib/active_record/connection_adapters/mysql_adapter.rb

ActiveRecord::Base class method mysql_connection about line #73

default_flags = Mysql.const_defined?(:CLIENT_MULTI_RESULTS) ? Mysql::CLIENT_MULTI_RESULTS : 0

Do a monkey patch and bitwise OR the flags to change it to:

default_flags = Mysql::CLIENT_MULTI_RESULTS | Mysql::CLIENT_COMPRESS

(assuming your MySQL gem is installed and supports both flags like the MySQL 2.8.1 gem does)

I then did somewhat unscientific comparision with and without the flag

./script/runner 'puts Benchmark.realtime{Business.all(:limit=>10000)}.to_s'

Results

With the patch, the test typically took 1.6 seconds. Without the patch it typically took 0.6 seconds. So it looks like the added compression adds more overhead on the CPU than what you gain in network savings when dealing with EC2 and RDS in the same availability zone.

So as I saw with MySQL compression enabled in my Sphinx setup the network bandwidth between RDS and EC2 instances isn’t the bottleneck I’m looking for.