Find My Team

Client Compression in mysql2

Image of engineering blog author: Shane EmmonsShane Emmons

Recently our Ops team has wanted to experiment with client compression on MySQL to help alleviate some internal network congestion. After much trial and error we hit upon a solution for the mysql2 gem in Rails 2.x, 3.x, 4.x and Sequel. I hope this helps others struggling to find a way to do this. Comeback soon to see what effect Client Compression had on our network.

Rails 2.x

# config/initializers/enable_connection_flags.rb
module ActiveRecord
  class Base
    def self.mysql2_connection(config)
      config[:username] = 'root' if config[:username].nil?

      if Mysql2::Client.const_defined? :FOUND_ROWS
        config[:flags] = config[:flags] ? config[:flags] | Mysql2::Client::FOUND_ROWS : Mysql2::Client::FOUND_ROWS
      end

      client = Mysql2::Client.new(config.symbolize_keys)
      options = [config[:host], config[:username], config[:password], config[:database], config[:port], config[:socket], 0]
      ConnectionAdapters::Mysql2Adapter.new(client, logger, options, config)
    end
  end
end

# config/database.yml
development:
  adapter: mysql2
  database: teamsnap
  username: root
  password:
  host: localhost
  flags: <%= require"mysql2";Mysql2::Client::COMPRESS %>

Rails 3.x

# config/initializers/enable_connection_flags.rb
module ActiveRecord
  class Base
    def self.mysql2_connection(config)
      config[:username] = 'root' if config[:username].nil?

      if Mysql2::Client.const_defined? :FOUND_ROWS
        config[:flags] = config[:flags] ? config[:flags] | Mysql2::Client::FOUND_ROWS : Mysql2::Client::FOUND_ROWS
      end

      client = Mysql2::Client.new(config.symbolize_keys)
      options = [config[:host], config[:username], config[:password], config[:database], config[:port], config[:socket], 0]
      ConnectionAdapters::Mysql2Adapter.new(client, logger, options, config)
    end
  end
end

# config/database.yml
development:
  adapter: mysql2
  database: teamsnap
  username: root
  password:
  host: localhost
  flags: <%= Mysql2::Client::COMPRESS %>

Rails 4.x

ActiveRecord::Base.define_singleton_method(:mysql2_connection) do |config|
  config = config.symbolize_keys

  config[:username] = 'root' if config[:username].nil?

  if Mysql2::Client.const_defined? :FOUND_ROWS
    config[:flags] = config[:flags] ? config[:flags] | Mysql2::Client::FOUND_ROWS : Mysql2::Client::FOUND_ROWS
  end

  client = Mysql2::Client.new(config)
  options = [config[:host], config[:username], config[:password], config[:database], config[:port], config[:socket], 0]
  ActiveRecord::ConnectionAdapters::Mysql2Adapter.new(client, logger, options, config)
end

# config/database.yml
development:
  adapter: mysql2
  database: teamsnap
  username: root
  password:
  host: localhost
  flags: <%= Mysql2::Client::COMPRESS %>

Sequel

# config/initializers/sequel.rb
database_url = Rails.application.secrets.database_url
DB = Sequel.connect(
  database_url,
  :flags => Rails.application.secrets.database_flags
)
Sequel.default_timezone = :utc

# config/secrets.yml
database_url: <%= ENV["DATABASE_URL"] %>
database_flags: <%= eval(ENV["DATABASE_FLAGS"] || "") %>

# .env
DATABASE_URL="mysql2://root:@localhost/teamsnap?wait_timeout=31536000&reconnect=true"
DATABASE_FLAGS="Mysql2::Client::COMPRESS"