playing with mysql-handlersocket

main.sh

## Adding precona repos
apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
echo "deb http://repo.percona.com/apt trusty main" > /etc/apt/sources.list.d/percona.list
echo "deb-src http://repo.percona.com/apt trusty main" >> /etc/apt/sources.list.d/percona.list
apt-get update

## Installing percona server
apt-get install -y percona-server-server-5.6 percona-server-common-5.6 percona-server-client-5.6 percona-server-5.6-dbg # root/root
mysql -uroot -proot -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -uroot -proot -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -uroot -proot -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

## Disabling query cache, just for testing
mysql -uroot -proot -e "SET GLOBAL query_cache_size = 0;"
mysql -uroot -proot -e "SHOW VARIABLES LIKE 'query_cache_size';"

## Installing ruby, I'm not a huge bash fan
add-apt-repository -y ppa:brightbox/ruby-ng-experimental
apt-get update
apt-get install -y ruby2.1 ruby-dev
ruby -v

## gem
apt-get install -y gcc build-essential libmysqlclient-dev
gem install mysql2
curl https://gist.githubusercontent.com/iliabylich/f262719397635064f319/raw/mysql2-test.rb | ruby

## seed the database
curl https://gist.githubusercontent.com/iliabylich/f262719397635064f319/raw/seed.rb | ruby

## configure handlersocket
nano /etc/mysql/my.cnf
# loose_handlersocket_port = 9998
# loose_handlersocket_port_wr = 9999
# loose_handlersocket_threads = 16
# loose_handlersocket_threads_wr = 1
service mysql restart

mysql -uroot -proot -e "show processlist" | grep handlersocket # Does it work?

mysql2-test.rb

require 'mysql2'

client = Mysql2::Client.new(password: 'root')

processlist = client.query('show processlist')

processlist.each do |row|
  puts row.inspect
end

mysql_bm.rb

require 'mysql2'
require 'benchmark'

MYSQL_CLIENT = Mysql2::Client.new(password: 'root')
ITERATIONS = 5
MAX_ID = ENV['MAX_ID'] || MYSQL_CLIENT.query('SELECT COUNT(*) as count from test_db.test_table').first['count']

def random_id
  rand(MAX_ID)
end

def random_name
  "name#{random_id}"
end

def mysql_repeat(query)
  ITERATIONS.times do
    MYSQL_CLIENT.query(query).to_a
  end
end

GC.disable # we don't need to measure GC, right?

Benchmark.bm(40) do |bm|
  bm.report('pure mysql, select by id') do
    mysql_repeat("SELECT * from test_db.test_table where id = #{random_id}")
  end
  bm.report('pure mysql, select by name') do
    mysql_repeat("SELECT * from test_db.test_table where name = '#{random_name}'")
  end
  bm.report('pure mysql, select by counter') do
    mysql_repeat("SELECT * from test_db.test_table where counter = #{random_id}")
  end
end

seed.rb

require 'mysql2'

client = Mysql2::Client.new(password: 'root')

COUNT = 1_000_000
BATCH_SIZE = 1_000

client.query('DROP DATABASE test_db') rescue nil
client.query('CREATE DATABASE test_db')
client.query('USE test_db;')
client.query('CREATE TABLE test_table (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), counter INT, some_date DATE)')

(COUNT / BATCH_SIZE).times do |batch_number|
  query = "INSERT INTO test_table (name, counter, some_date) VALUES"
  values = BATCH_SIZE.times.map do |i|
    name = "name#{batch_number}"
    counter = i
    date = (Date.today - rand(100)).to_s
    "('#{name}', '#{counter}', '#{date}')"
  end.join(', ')
  query = [query, values].join(' ')
  client.query(query)
  puts "#{batch_number * BATCH_SIZE} done"
end