mysql-bench

countup.rb

require "time"
require "mysql2"

CLIENT_COUNT = ENV.fetch("CLIENT_COUNT", 1).to_i
REPEAT_COUNT = 10
MYSQL_CONFIG = {
  host: ENV["MYSQL_HOST"],
  username: ENV["MYSQL_USERNAME"],
  password: ENV["MYSQL_PASSWORD"],
  database: ENV["MYSQL_DATABASE"],
}.freeze
STARTED_AT = Time.parse(ENV["STARTED_AT"])

INSERT_SQL = "INSERT INTO order_counts (shop_id, ordered_on, count) VALUES (1, '#{Date.today}', 1) ON DUPLICATE KEY UPDATE count = count + VALUES(count)"
SELECT_SQL = "SELECT 1 FROM order_counts WHERE shop_id = 1 AND ordered_on = '#{Date.today}'"
UPDATE_SQL = "UPDATE order_counts SET count = count + 1 WHERE shop_id = 1 AND ordered_on = '#{Date.today}'"

def new_client(without_database: false)
  config = MYSQL_CONFIG
  config = MYSQL_CONFIG.reject { |k, _| k == :database } if without_database
  Mysql2::Client.new(config)
end

def fetch_count(client)
  client.query("SELECT count FROM order_counts WHERE shop_id = 1 AND ordered_on = '#{Date.today}'").first["count"]
end

def extract_semaphores_info(status)
  status.each_line.with_object({}) do |line, ret|
    next unless line.start_with?("RW-")

    line.chomp!
    type, _, spins, _, rounds, _, _, waits = line.split(/ ,?/)
    ret[type] = {
      spins: spins.to_i,
      rounds: rounds.to_i,
      waits: waits.to_i,
    }
  end
end

def insert_and_update(client)
  client.query(INSERT_SQL)
end

def select_and_update(client)
  if client.query(SELECT_SQL).size > 0
    client.query(UPDATE_SQL)
  else
    client.query(INSERT_SQL)
  end
end

client = new_client
count = fetch_count(client)

puts "Wait until #{STARTED_AT}"
sleep 0.001 until Time.now > STARTED_AT
puts "Start (count = #{count})"

before_semaphores = extract_semaphores_info(client.query("SHOW ENGINE INNODB STATUS").to_a.first["Status"])

m = method(ENV.fetch("UPDATE_METHOD", "insert_and_update"))

prng = Random.new(42)
total_time = 0
ths = Array.new(CLIENT_COUNT) do
  sleep_times = REPEAT_COUNT.times.map { prng.rand }

  Thread.new do
    c = new_client

    REPEAT_COUNT.times do |i|
      s = Process.clock_gettime(Process::CLOCK_MONOTONIC)
      m.call(c)
      total_time += Process.clock_gettime(Process::CLOCK_MONOTONIC) - s
      sleep sleep_times[i]
    end
  end
end
ths.each(&:join)

status = client.query("SHOW ENGINE INNODB STATUS").to_a.first["Status"]

puts "Client count: #{CLIENT_COUNT}"
puts "Total query time: #{total_time.round(3)}"

extract_semaphores_info(status).each do |type, data|
  puts type
  data.each do |k, v|
    puts "  #{k}: #{v - before_semaphores[type][k]}"
  end
end
puts "Finish (count = #{fetch_count(client)})"

Dockerfile

FROM ruby:2.6

WORKDIR /app
RUN mkdir vendor
COPY Gemfile Gemfile.lock /app/
RUN bundle install --no-cache -j4

COPY setup.rb countup.rb /app/

ENTRYPOINT ["bundle", "exec", "ruby"]
CMD ["countup.rb"]

Gemfile

# frozen_string_literal: true

source "https://rubygems.org"

git_source(:github) {|repo_name| "https://github.com/#{repo_name}" }

gem "mysql2"

Gemfile.lock

GEM
  remote: https://rubygems.org/
  specs:
    mysql2 (0.5.3)

PLATFORMS
  ruby

DEPENDENCIES
  mysql2

BUNDLED WITH
   1.7

run_task.sh

#!/bin/bash

set -eo pipefail

REPOSITORY=mysql-bench
TASK_FAMILY=mysql-bench

for arg_name in MYSQL_HOST STARTED_AT TASK_COUNT UPDATE_METHOD; do
  if [ -z "${!arg_name}" ]; then
    echo "\$$arg_name must be specified" >&2
    exit 1
  fi
done

dir=$(cd $(dirname $0) && pwd)
cd $dir

repo_uri=$((aws ecr describe-repositories --repository-names $REPOSITORY | jq -r '.repositories[] | .repositoryUri') || true)
if [ -z "$repo_uri" ]; then
  echo "Create ECR repository '$REPOSITORY'"
  repo_uri=$(aws ecr create-repository --repository-name $REPOSITORY | jq -r '.repository.repositoryUri')
fi

echo "Build logger docker image"
docker build . -t $REPOSITORY

echo "Push $repo_uri:latest"
docker tag $REPOSITORY:latest $repo_uri:latest
aws ecr get-login-password --region ap-northeast-1 | docker login --username AWS --password-stdin $repo_uri
docker push $repo_uri:latest

PAGER=cat aws ecs register-task-definition --cli-input-json "$(cat <<JSON
{
  "family": "$TASK_FAMILY",
  "containerDefinitions": [
    {
      "name": "main",
      "image": "$repo_uri",
      "essential": true,
      "environment": [
        {
          "name": "MYSQL_HOST",
          "value": "$MYSQL_HOST"
        },
        {
          "name": "MYSQL_USERNAME",
          "value": "admin"
        },
        {
          "name": "MYSQL_PASSWORD",
          "value": "password"
        },
        {
          "name": "MYSQL_DATABASE",
          "value": "admin"
        },
        {
          "name": "STARTED_AT",
          "value": "$STARTED_AT"
        },
        {
          "name": "CLIENT_COUNT",
          "value": "4"
        },
        {
          "name": "UPDATE_METHOD",
          "value": "$UPDATE_METHOD"
        }
      ],
      "logConfiguration": {
        "logDriver": "awslogs",
        "options": {
          "awslogs-group": "/ecs/$TASK_FAMILY",
          "awslogs-region": "ap-northeast-1",
          "awslogs-stream-prefix": "ecs"
        }
      }
    }
  ],
  "cpu": "128",
  "memory": "128"
}
JSON
)"

# Execute first the following command:
#
# aws ecs run-task --cluster $CLUSTER --task-definition $TASK_FAMILY --overrides '{
#   "containerOverrides": [
#     {
#       "name": "main",
#       "command": ["setup.rb"]
#     }
#   ]
# }'

launched_count=0
while [ "$launched_count" -lt "$TASK_COUNT" ]; do
  task_count=$(($TASK_COUNT - $launched_count))
  if [ $task_count -ge 10 ]; then
    task_count=10
  fi

  launched_count=$(($launched_count + $task_count))
  echo "Launch $launched_count/$TASK_COUNT tasks"
  resp=$(aws ecs run-task --cluster $CLUSTER --task-definition $TASK_FAMILY --count $task_count)

  if [ "$(echo $resp | jq '.failures | length')" -ne 0 ]; then
    echo -e "$resp" >&2
    exit 1
  fi
done

setup.rb

require "date"
require "mysql2"

MYSQL_CONFIG = {
  host: ENV["MYSQL_HOST"],
  username: ENV["MYSQL_USERNAME"],
  password: ENV["MYSQL_PASSWORD"],
}.freeze

client = Mysql2::Client.new(MYSQL_CONFIG)
client.query("CREATE DATABASE IF NOT EXISTS `#{ENV["MYSQL_DATABASE"]}`")

client = Mysql2::Client.new(MYSQL_CONFIG.merge(database: ENV["MYSQL_DATABASE"]))

client.query(<<~SQL)
  CREATE TABLE IF NOT EXISTS `order_counts` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `shop_id` int(10) unsigned NOT NULL,
    `ordered_on` date NOT NULL,
    `count` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ux_shop_id_ordered_on` (`shop_id`,`ordered_on`) USING BTREE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL

1000.times do |i|
  shop_id = i + 1

  sql = "INSERT IGNORE INTO order_counts (shop_id, ordered_on, count) VALUES "
  sql << ((Date.today - 365) .. Date.today).map do |ordered_on|
    "(%d, '%s', %d)" % [shop_id, ordered_on, 0]
  end.join(",")

  client.query(sql)
end

puts "Succeeded in creating records"