AnsibleでsysbenchとMariaDBをインストールする(Ubuntu18.04用)

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に関する他の記事はこちらを参照してください。