serverあれこれ

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとMariaDBがインストールされた仮想マシン(CentOS 8.1)を構築します。

Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/centos-8.1"
  config.vm.hostname = "co81sysbenchmariadb"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "co81sysbenchmariadb"
     vbox.gui = true
     vbox.cpus = 2
     vbox.memory = 4096
  end
config.vm.network "private_network", ip: "192.168.55.101", :netmask => "255.255.255.0"
  config.vm.provision "shell", inline: <<-SHELL
dnf -y install langpacks-ja
localectl set-locale LANG=ja_JP.UTF-8
dnf install -y epel-release
dnf check-update
dnf -y update
timedatectl set-timezone Asia/Tokyo

# install mariadb
dnf -y install mariadb mariadb-server
sudo systemctl enable mariadb.service
sudo systemctl start mariadb.service
mysql -uroot -e "SET PASSWORD = PASSWORD('root'); FLUSH PRIVILEGES;"
mysql -uroot -proot -e "CREATE DATABASE sysbench DEFAULT CHARACTER SET utf8mb4;"
mysql -uroot -proot -e "CREATE USER sysbench@localhost IDENTIFIED BY 'sysbench';"
mysql -uroot -proot -e "GRANT ALL PRIVILEGES ON sysbench.* TO 'sysbench'@'localhost';"
mysql -uroot -proot -e "FLUSH PRIVILEGES;"

# install and execute sysbench.
dnf --enablerepo=epel -y install sysbench
sysbench cpu run --threads=2
sysbench memory run --threads=2
sysbench fileio run --file-test-mode=seqwr --threads=2
sysbench fileio run --file-test-mode=rndwr --threads=2

sysbench /usr/share/sysbench/oltp_read_write.lua prepare --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua run --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --threads=2

SHELL
end

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇インストール方法
シェルから以下のコマンド実行します

echo "mariadb-server-10.0 mysql-server/root_password password root" | sudo debconf-set-selections

echo "mariadb-server-10.0 mysql-server/root_password_again password root" | sudo debconf-set-selections

sudo apt-get -y install mariadb-server

sudo mysql -uroot -e "CREATE DATABASE sysbench DEFAULT CHARACTER SET utf8;"

sudo mysql -uroot -e "CREATE USER sysbench@localhost IDENTIFIED BY 'sysbench';"

sudo mysql -uroot -e "GRANT ALL PRIVILEGES ON sysbench.* TO 'sysbench'@'localhost';"

sudo mysql -uroot -e "FLUSH PRIVILEGES;"

sudo apt-get -y install curl

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash

sudo apt-get -y install sysbench

〇CPUベンチマークの実行

sysbench --test=cpu run --num-threads=2

〇メモリベンチマークの実行

sysbench --test=memory run --num-threads=2

〇ファイルIOベンチマークの実行

sysbench --test=fileio run --file-test-mode=seqwr --num-threads=2

sysbench --test=fileio prepare --file-test-mode=rndwr --num-threads=2

sysbench --test=fileio run --file-test-mode=rndwr --num-threads=2

〇OLTPベンチマークの実行

sysbench --db-driver=mysql \
  --mysql-user=sysbench \
  --mysql-password=sysbench \
  --mysql-port=3306 \
  --mysql-db=sysbench \
  --range_size=100 \
  --table_size=10000 \
  --tables=2 \
  --threads=1 \
  --events=0 \
  --time=60 \
  --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare

sysbench --db-driver=mysql \
  --mysql-user=sysbench \
  --mysql-password=sysbench \
  --mysql-port=3306 \
  --mysql-db=sysbench \
  --range_size=100 \
  --table_size=10000 \
  --tables=2 \
  --threads=1 \
  --events=0 \
  --time=60 \
  --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua run

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとMariaDBがインストールされた仮想マシン(Debian Stretch/9.5)を構築します。
Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/debian-10"
  config.vm.hostname = "db10sysbenchmariadb"
config.vm.network "public_network", ip:"192.168.1.103", :netmask => "255.255.255.0"
config.vm.network "private_network", ip: "192.168.55.103", :netmask => "255.255.255.0"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "db10sysbenchmariadb"
     vbox.gui = true
     vbox.cpus = 2
     vbox.memory = 2048
  end
  config.vm.provision "shell", inline: <<-SHELL
apt-get update
DEBIAN_FRONTEND=noninteractive apt-get -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" upgrade
apt-get -y install task-japanese gawk
sed -i -e 's/# ja_JP.UTF-8 UTF-8/ja_JP.UTF-8 UTF-8/' /etc/locale.gen
locale-gen
update-locale LANG=ja_JP.UTF-8
localectl set-locale LANG=ja_JP.UTF-8
localectl set-keymap jp106
timedatectl set-timezone Asia/Tokyo


# install mariadb
echo "mariadb-server-10.0 mysql-server/root_password password root" | sudo debconf-set-selections
echo "mariadb-server-10.0 mysql-server/root_password_again password root" | sudo debconf-set-selections
apt-get -y install mariadb-server
mysql -uroot -proot -e "CREATE DATABASE sysbench DEFAULT CHARACTER SET utf8mb4;"
mysql -uroot -proot -e "CREATE USER sysbench@localhost IDENTIFIED BY 'sysbench';"
mysql -uroot -proot -e "GRANT ALL PRIVILEGES ON sysbench.* TO 'sysbench'@'localhost';"
mysql -uroot -proot -e "FLUSH PRIVILEGES;"

# install and execute sysbench.
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
apt-get -y install sysbench


sysbench --test=cpu run --num-threads=2
sysbench --test=memory run --num-threads=2
sysbench --test=fileio run --file-test-mode=seqwr --num-threads=2
#sysbench --test=fileio run --file-test-mode=rndwr --num-threads=2

sysbench --db-driver=mysql \
  --mysql-user=sysbench \
  --mysql-password=sysbench \
  --mysql-port=3306 \
  --mysql-db=sysbench \
  --range_size=100 \
  --table_size=10000 \
  --tables=2 \
  --threads=1 \
  --events=0 \
  --time=60 \
  --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare

sysbench --db-driver=mysql \
  --mysql-user=sysbench \
  --mysql-password=sysbench \
  --mysql-port=3306 \
  --mysql-db=sysbench \
  --range_size=100 \
  --table_size=10000 \
  --tables=2 \
  --threads=1 \
  --events=0 \
  --time=60 \
  --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua run

SHELL
end

○関連情報
・sysbenchに関する他の記事はこちらを参照してください。

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇インストール方法
1.下準備
~/.ansible.cfgに以下の内容を設定します

[ssh_connection]
pipelining=True
[defaults]
host_key_checking = False

2. インストール対象ホストのユーザやパスワードを環境に合わせてinventoryファイルに記入します

inventory例

[sysbench]
192.168.1.61

[all:vars]
ansible_ssh_port=22
ansible_ssh_user=vagrant
ansible_ssh_pass=vagrant
ansible_sudo_pass=vagrant

3.inventory、sysbench-mariadb.ymlを準備し、以下のコマンドを実行します。

ansible-playbook -i inventory sysbench-mariadb.yml

sysbench-mariadb.yml

- hosts: sysbench
  vars:
    - dbname: sysbench
    - dbuser: sysbench
    - dbpassword: sysbench
    - dbhost: localhost
    - dbport: 3306
  tasks:
    - name: set password for root
      shell: echo "mariadb-server-10.1 mysql-server/root_password password root" | sudo debconf-set-selections
      become: yes
    - name: set password for root
      shell: echo "mariadb-server-10.1 mysql-server/root_password_again password root" | sudo debconf-set-selections
      become: yes
    - name: install mariadb
      apt:
        name: mariadb-server
        state: present
      become: yes
    - name: enable and start mariadb
      systemd:
        name: mysql
        enabled: yes
        state: started
      become: yes
    - name: Install required software
      apt: name={{ item }} state=present
      with_items:
        - libmysqlclient-dev
        - python-pip
      become: true
    - name: install MySQL-python using pip
      pip:
        name: "{{ item }}"
        state: forcereinstall
      with_items:
        - pip
        - MySQL-python
      become: true
    - name: create db
      mysql_db:
        name={{ dbname }}
        state=present
        encoding=utf8
        login_user=root
      become: true
    - name: create and grant a database user
      mysql_user:
        name={{ dbuser }}
        password={{ dbpassword }}
        priv="{{ dbuser }}.*:ALL"
        state=present
      become: true
    - name: install sysbench
      apt: name={{ item }} state=present
      with_items:
        - sysbench
      become: true
    - name: execute cpu benchmark
      shell: sysbench cpu run --threads=2 > /tmp/result
      become: true
    - name: execute memory benchmark
      shell: sysbench memory run --threads=2 >> /tmp/result
      become: true
    - name: execute fileio benchmark(seqwr)
      shell: sysbench fileio run --file-test-mode=seqwr --threads=2 >> /tmp/result
      become: true
    - name: execute fileio benchmark(rndwr)
      shell: sysbench fileio run --file-test-mode=rndwr --threads=2 >> /tmp/result
      become: true
    - name: prepare oltp_read_write benchmark
      shell: sysbench /usr/share/sysbench/oltp_read_write.lua prepare --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench >> /tmp/result
      become: true
    - name: execute oltp_read_write benchmark
      shell: sysbench /usr/share/sysbench/oltp_read_write.lua run --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --threads=2 >> /tmp/result
      become: true
    - name: fetch result
      fetch:
        src: /tmp/result
        dest: ./result

○関連情報
・Ansibleに関する他の記事はこちらを参照してください。

・sysbenchに関する他の記事はこちらを参照してください。

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとPostgreSQL10がインストールされた仮想マシン(CentOS7.5)を構築します。

Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/centos-7.5"
  config.vm.hostname = "co75sysbenchmariadb"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "co75sysbenchmariadb"
     vbox.cpus = 2
     vbox.memory = 2048
     vbox.customize ["modifyvm", :id, "--nicpromisc2","allow-all"]
  end
  # private network
config.vm.network "private_network", ip: "192.168.55.65", :netmask => "255.255.255.0"
  # bridge netwrok
config.vm.network "public_network", ip: "192.168.1.65", :netmask => "255.255.255.0"
  config.vm.provision "shell", inline: <<-SHELL
localectl set-locale LANG=ja_JP.UTF-8
timedatectl set-timezone Asia/Tokyo

# download and install postgresql.
wget https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
rpm -Uvh pgdg-centos10-10-2.noarch.rpm

yum -y update
yum -y install postgresql10-server postgresql10-devel postgresql10-contrib
systemctl enable postgresql-10

# initialize postgresql server
/usr/pgsql-10/bin/postgresql-10-setup initdb

echo "listen_addresses='*'" >> /var/lib/pgsql/10/data/postgresql.conf

sed -i 's/host.*all.*all.*127.0.0.1/#host    all             all             127.0.0.1/g' /var/lib/pgsql/10/data/pg_hba.conf
sed -i 's#^host.*all.*all.*::1/128.*ident#host    all             all             ::1/128    password#g' /var/lib/pgsql/10/data/pg_hba.conf

echo "host    all         all         127.0.0.1/32          password" >> /var/lib/pgsql/10/data/pg_hba.conf
echo "host    all         all         192.168.1.0/24          password" >> /var/lib/pgsql/10/data/pg_hba.conf
echo "host    all         all         192.168.55.0/24          password" >> /var/lib/pgsql/10/data/pg_hba.conf
systemctl restart postgresql-10.service

su - postgres << EOF
createdb -T template0 --locale=ja_JP.UTF-8 --encoding=UTF8 sysbench
psql -c "
alter user postgres with password 'postgres';
create user sysbench with password 'sysbench';
grant all privileges on database sysbench to sysbench;
"
EOF
echo "postgres:postgres" | chpasswd


# install and execute sysbench.
yum -y install epel-release
yum --enablerepo=epel -y install sysbench
sysbench cpu run --threads=2
sysbench memory run --threads=2
sysbench fileio run --file-test-mode=seqwr --threads=2
sysbench fileio run --file-test-mode=rndwr --threads=2

sysbench /usr/share/sysbench/oltp_read_write.lua prepare --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=sysbench --pgsql-password=sysbench --pgsql-db=sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua run --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=sysbench --pgsql-password=sysbench --pgsql-db=sysbench --threads=2


SHELL
end

○関連情報
・sysbenchに関する他の記事はこちらを参照してください。

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとPostgreSQL10がインストールされた仮想マシン(Debian Stretch/9.5)を構築します。

Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/debian-9.5"
  config.vm.hostname = "db95sysbenchpg"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "db95sysbenchpg"
     vbox.cpus = 2
     vbox.memory = 2048
     vbox.customize ["modifyvm", :id, "--nicpromisc2","allow-all"]
  end
config.vm.network "private_network", ip: "192.168.55.107", :netmask => "255.255.255.0"
config.vm.network "public_network", ip:"192.168.1.107", :netmask => "255.255.255.0"
  config.vm.provision "shell", inline: <<-SHELL
apt-get -y install task-japanese
sed -i -e 's/# ja_JP.UTF-8 UTF-8/ja_JP.UTF-8 UTF-8/' /etc/locale.gen
locale-gen
update-locale LANG=ja_JP.UTF-8
localectl set-locale LANG=ja_JP.UTF-8
localectl set-keymap jp106
timedatectl set-timezone Asia/Tokyo
apt-get update
timedatectl set-timezone Asia/Tokyo
DEBIAN_FRONTEND=noninteractive apt-get -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" upgrade

# install postgresql
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt-get -y install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get upgrade
apt-get -y install postgresql-10

echo "listen_addresses='*'" >> /etc/postgresql/10/main/postgresql.conf

#sed -i 's/host.*all.*all.*127.0.0.1/#host    all             all             127.0.0.1/g' /etc/postgresql/10/main/pg_hba.conf

echo "host    all         all         127.0.0.1/32          password" >> /etc/postgresql/10/main/pg_hba.conf
echo "host    all         all         192.168.1.0/24          password" >> /etc/postgresql/10/main/pg_hba.conf
echo "host    all         all         192.168.55.0/24          password" >> /etc/postgresql/10/main/pg_hba.conf

su - postgres << EOF
createdb -T template0 --locale=ja_JP.UTF-8 --encoding=UTF8 sysbench
psql -c "
alter user postgres with password 'postgres';
create user sysbench with password 'sysbench';
grant all privileges on database sysbench to sysbench;
"
EOF
echo "postgres:postgres" | chpasswd


# install and execute sysbench.
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
apt-get -y install sysbench
sysbench cpu run --threads=2
sysbench memory run --threads=2
sysbench fileio run --file-test-mode=seqwr --threads=2
sysbench fileio run --file-test-mode=rndwr --threads=2

sysbench /usr/share/sysbench/oltp_read_write.lua prepare --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=sysbench --pgsql-password=sysbench --pgsql-db=sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua run --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=sysbench --pgsql-password=sysbench --pgsql-db=sysbench --threads=2

SHELL
end

○関連情報
・sysbenchに関する他の記事はこちらを参照してください。

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとPostgreSQLがインストールされた仮想マシン(Ubuntu16.04)を構築します。

Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/ubuntu-16.04"
  config.vm.hostname = "ub1604sysbenchpg"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "ub1604sysbenchpg"
     vbox.cpus = 2
     vbox.memory = 2048
     vbox.customize ["modifyvm", :id, "--nicpromisc2","allow-all"]
  end
config.vm.network "private_network", ip: "192.168.55.65", :netmask => "255.255.255.0"
config.vm.network "public_network", ip:"192.168.1.65", :netmask => "255.255.255.0"
  config.vm.provision "shell", inline: <<-SHELL
apt-get update
DEBIAN_FRONTEND=noninteractive apt-get -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" upgrade
locale-gen ja_JP.UTF-8
localectl set-locale LANG=ja_JP.UTF-8
timedatectl set-timezone Asia/Tokyo

# install postgresql
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt-get -y install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get upgrade
apt-get -y install postgresql-10

echo "listen_addresses='*'" >> /etc/postgresql/10/main/postgresql.conf

#sed -i 's/host.*all.*all.*127.0.0.1/#host    all             all             127.0.0.1/g' /etc/postgresql/10/main/pg_hba.conf

echo "host    all         all         127.0.0.1/32          password" >> /etc/postgresql/10/main/pg_hba.conf
echo "host    all         all         192.168.1.0/24          password" >> /etc/postgresql/10/main/pg_hba.conf
echo "host    all         all         192.168.55.0/24          password" >> /etc/postgresql/10/main/pg_hba.conf

su - postgres << EOF
createdb -T template0 --locale=ja_JP.UTF-8 --encoding=UTF8 sysbench
psql -c "
alter user postgres with password 'postgres';
create user sysbench with password 'sysbench';
grant all privileges on database sysbench to sysbench;
"
EOF
echo "postgres:postgres" | chpasswd
systemctl restart postgresql.service

# install and execute sysbench.
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench

sysbench cpu run --threads=2
sysbench memory run --threads=2
sysbench fileio run --file-test-mode=seqwr --threads=2
sysbench fileio run --file-test-mode=rndwr --threads=2

sysbench /usr/share/sysbench/oltp_read_write.lua prepare --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=sysbench --pgsql-password=sysbench --pgsql-db=sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua run --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=sysbench --pgsql-password=sysbench --pgsql-db=sysbench --threads=2


SHELL
end

○関連情報
・sysbenchに関する他の記事はこちらを参照してください。

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとPostgreSQLがインストールされた仮想マシン(Ubuntu18.04)を構築します。

Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/ubuntu-18.04"
  config.vm.hostname = "ub1804sysbenchpg"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "ub1804sysbenchpg"
     vbox.cpus = 2
     vbox.memory = 2048
     vbox.customize ["modifyvm", :id, "--nicpromisc2","allow-all"]
  end
config.vm.network "private_network", ip: "192.168.55.65", :netmask => "255.255.255.0"
config.vm.network "public_network", ip:"192.168.1.65", :netmask => "255.255.255.0"
  config.vm.provision "shell", inline: <<-SHELL
apt-get update
DEBIAN_FRONTEND=noninteractive apt-get -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" upgrade
locale-gen ja_JP.UTF-8
localectl set-locale LANG=ja_JP.UTF-8
timedatectl set-timezone Asia/Tokyo

# install postgresql
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt-get -y install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get upgrade
apt-get -y install postgresql-10

echo "listen_addresses='*'" >> /etc/postgresql/10/main/postgresql.conf

#sed -i 's/host.*all.*all.*127.0.0.1/#host    all             all             127.0.0.1/g' /etc/postgresql/10/main/pg_hba.conf

echo "host    all         all         127.0.0.1/32          password" >> /etc/postgresql/10/main/pg_hba.conf
echo "host    all         all         192.168.1.0/24          password" >> /etc/postgresql/10/main/pg_hba.conf
echo "host    all         all         192.168.55.0/24          password" >> /etc/postgresql/10/main/pg_hba.conf

su - postgres << EOF
createdb -T template0 --locale=ja_JP.UTF-8 --encoding=UTF8 sysbench
psql -c "
alter user postgres with password 'postgres';
create user sysbench with password 'sysbench';
grant all privileges on database sysbench to sysbench;
"
EOF
echo "postgres:postgres" | chpasswd
systemctl restart postgresql.service

# install and execute sysbench.
apt-get -y install sysbench
sysbench cpu run --threads=2
sysbench memory run --threads=2
sysbench fileio run --file-test-mode=seqwr --threads=2
sysbench fileio run --file-test-mode=rndwr --threads=2

sysbench /usr/share/sysbench/oltp_read_write.lua prepare --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=sysbench --pgsql-password=sysbench --pgsql-db=sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua run --db-driver=pgsql --pgsql-host=localhost --pgsql-port=5432 --pgsql-user=sysbench --pgsql-password=sysbench --pgsql-db=sysbench --threads=2

SHELL
end

○関連情報
・sysbenchに関する他の記事はこちらを参照してください。

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとMariaDBがインストールされた仮想マシン(CentOS7.5)を構築します。

Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/centos-7.5"
  config.vm.hostname = "co75sysbenchmariadb"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "co75sysbenchmariadb"
     vbox.cpus = 2
     vbox.memory = 2048
     vbox.customize ["modifyvm", :id, "--nicpromisc2","allow-all"]
  end
  # private network
config.vm.network "private_network", ip: "192.168.55.65", :netmask => "255.255.255.0"
  # bridge netwrok
config.vm.network "public_network", ip: "192.168.1.65", :netmask => "255.255.255.0"
  config.vm.provision "shell", inline: <<-SHELL
localectl set-locale LANG=ja_JP.UTF-8
timedatectl set-timezone Asia/Tokyo

# install mariadb
yum -y install mariadb mariadb-server
sudo systemctl enable mariadb.service
sudo systemctl start mariadb.service
mysql -uroot -e "SET PASSWORD = PASSWORD('root'); FLUSH PRIVILEGES;"
mysql -uroot -proot -e "CREATE DATABASE sysbench DEFAULT CHARACTER SET utf8mb4;"
mysql -uroot -proot -e "CREATE USER sysbench@localhost IDENTIFIED BY 'sysbench';"
mysql -uroot -proot -e "GRANT ALL PRIVILEGES ON sysbench.* TO 'sysbench'@'localhost';"
mysql -uroot -proot -e "FLUSH PRIVILEGES;"

# install and execute sysbench.
yum -y install epel-release
yum --enablerepo=epel -y install sysbench
sysbench cpu run --threads=2
sysbench memory run --threads=2
sysbench fileio run --file-test-mode=seqwr --threads=2
sysbench fileio run --file-test-mode=rndwr --threads=2

sysbench /usr/share/sysbench/oltp_read_write.lua prepare --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua run --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --threads=2


SHELL
end

○関連情報
・sysbenchに関する他の記事はこちらを参照してください。

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとMariaDBがインストールされた仮想マシン(Ubuntu16.04)を構築します。

Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/ubuntu-16.04"
  config.vm.hostname = "ub1604sysbenchmariadb"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "ub1604sysbenchmariadb"
     vbox.cpus = 2
     vbox.memory = 2048
     vbox.customize ["modifyvm", :id, "--nicpromisc2","allow-all"]
  end
config.vm.network "private_network", ip: "192.168.55.65", :netmask => "255.255.255.0"
config.vm.network "public_network", ip:"192.168.1.65", :netmask => "255.255.255.0"
  config.vm.provision "shell", inline: <<-SHELL
apt-get update
DEBIAN_FRONTEND=noninteractive apt-get -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" upgrade
locale-gen ja_JP.UTF-8
localectl set-locale LANG=ja_JP.UTF-8
timedatectl set-timezone Asia/Tokyo

# install mariadb
echo "mariadb-server-10.0 mysql-server/root_password password root" | sudo debconf-set-selections
echo "mariadb-server-10.0 mysql-server/root_password_again password root" | sudo debconf-set-selections
apt-get -y install mariadb-server
mysql -uroot -proot -e "CREATE DATABASE sysbench DEFAULT CHARACTER SET utf8mb4;"
mysql -uroot -proot -e "CREATE USER sysbench@localhost IDENTIFIED BY 'sysbench';"
mysql -uroot -proot -e "GRANT ALL PRIVILEGES ON sysbench.* TO 'sysbench'@'localhost';"
mysql -uroot -proot -e "FLUSH PRIVILEGES;"

# install and execute sysbench.
apt-get -y install sysbench
sysbench --test=cpu run --num-threads=2
sysbench --test=memory run --num-threads=2
sysbench --test=fileio run --file-test-mode=seqwr --num-threads=2
sysbench --test=fileio run --file-test-mode=rndwr --num-threads=2

sysbench --test=oltp prepare --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench
sysbench --test=oltp run --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --num-threads=2

SHELL
end

○関連情報
・sysbenchに関する他の記事はこちらを参照してください。

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとMariaDBがインストールされた仮想マシン(Ubuntu18.04)を構築します。

Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/ubuntu-18.04"
  config.vm.hostname = "ub1804sysbenchmariadb"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "ub1804sysbenchmariadb"
     vbox.cpus = 2
     vbox.memory = 2048
     vbox.customize ["modifyvm", :id, "--nicpromisc2","allow-all"]
  end
config.vm.network "private_network", ip: "192.168.55.65", :netmask => "255.255.255.0"
config.vm.network "public_network", ip:"192.168.1.65", :netmask => "255.255.255.0"
  config.vm.provision "shell", inline: <<-SHELL
apt-get update
DEBIAN_FRONTEND=noninteractive apt-get -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" upgrade
locale-gen ja_JP.UTF-8
localectl set-locale LANG=ja_JP.UTF-8
timedatectl set-timezone Asia/Tokyo

# install mariadb
echo "mariadb-server-10.0 mysql-server/root_password password root" | sudo debconf-set-selections
echo "mariadb-server-10.0 mysql-server/root_password_again password root" | sudo debconf-set-selections
apt-get -y install mariadb-server
mysql -uroot -proot -e "CREATE DATABASE sysbench DEFAULT CHARACTER SET utf8mb4;"
mysql -uroot -proot -e "CREATE USER sysbench@localhost IDENTIFIED BY 'sysbench';"
mysql -uroot -proot -e "GRANT ALL PRIVILEGES ON sysbench.* TO 'sysbench'@'localhost';"
mysql -uroot -proot -e "FLUSH PRIVILEGES;"

# install and execute sysbench.
apt-get -y install sysbench
sysbench cpu run --threads=2
sysbench memory run --threads=2
sysbench fileio run --file-test-mode=seqwr --threads=2
sysbench fileio run --file-test-mode=rndwr --threads=2

sysbench /usr/share/sysbench/oltp_read_write.lua prepare --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua run --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --threads=2

SHELL
end

○関連情報
・sysbenchに関する他の記事はこちらを参照してください。

sysbenchでホストマシンやデータベースのベンチマークを行うことができます。

〇構築方法
以下のVagrantfileを使用して、sysbenchとMariaDBがインストールされた仮想マシン(Debian Stretch/9.5)を構築します。

Vagrantfile

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/debian-9.5"
  config.vm.hostname = "db95sysbenchmariadb"
  config.vm.provider :virtualbox do |vbox|
     vbox.name = "db95sysbenchmariadb"
     vbox.cpus = 2
     vbox.memory = 2048
     vbox.customize ["modifyvm", :id, "--nicpromisc2","allow-all"]
  end
config.vm.network "private_network", ip: "192.168.55.107", :netmask => "255.255.255.0"
config.vm.network "public_network", ip:"192.168.11.107", :netmask => "255.255.255.0"
  config.vm.provision "shell", inline: <<-SHELL
# update packages
apt-get update
#DEBIAN_FRONTEND=noninteractive apt-get -y -o Dpkg::Options::="--force-confdef" -o Dpkg::Options::="--force-confold" upgrade
locale-gen ja_JP.UTF-8
localectl set-locale LANG=ja_JP.UTF-8
timedatectl set-timezone Asia/Tokyo

# install mariadb
echo "mariadb-server-10.1 mysql-server/root_password password root" | sudo debconf-set-selections
echo "mariadb-server-10.1 mysql-server/root_password_again password root" | sudo debconf-set-selections
apt-get -y install mariadb-server
mysql -uroot -proot -e "CREATE DATABASE sysbench DEFAULT CHARACTER SET utf8mb4;"
mysql -uroot -proot -e "CREATE USER sysbench@localhost IDENTIFIED BY 'sysbench';"
mysql -uroot -proot -e "GRANT ALL PRIVILEGES ON sysbench.* TO 'sysbench'@'localhost';"
mysql -uroot -proot -e "FLUSH PRIVILEGES;"

# install and execute sysbench.
apt-get -y install sysbench
sysbench --test=cpu run --num-threads=2
sysbench --test=memory run --num-threads=2
sysbench --test=fileio run --file-test-mode=seqwr --num-threads=2
sysbench --test=fileio run --file-test-mode=rndwr --num-threads=2

sysbench --test=oltp prepare --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench
sysbench --test=oltp run --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --num-threads=2

SHELL
end

○関連情報
・sysbenchに関する他の記事はこちらを参照してください。