PGbencher

This playbook intend to simplify bench process for PostgreSQL database. We have tested it with ansible 2.8.

Dependencies

Install ansible and some dependencies

Debian based

apt update && apt -y install python-pip

Red-Hat based

yum install -y epel-release && yum install -y python-pip

Then install latest ansible

pip install -U ansible>=2.8.3

Check ansible version

ansible --version
ansible 2.8.3
  config file = None
  configured module search path = [u'/root/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python2.7/dist-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 2.7.13 (default, Sep 26 2018, 18:42:22) [GCC 6.3.0 20170516]

Installation and Configuration

First you will need to clone this repository.

git clone https://github.com/wilfriedroset/pgbencher.git && cd pgbencher

Copy and edit the default configuration.

cp defaults/main.yml config.yml

Parameters

PostgreSQL related parameters:

• pgdatabase: name of the database to use during the bench
• pghost: fqdn or ip of postgresql host
• pgport: port of postgresql
• pgport_ro: read-only port of postgresql
• pgport_rw: read-write port of postgresql
• pguser: name of the user to use during bench
  • you must be a superuser or have the special CREATEDB privilege
• pgpass: password of the user

pgbench related parameters:

• fillfactor: Create the pgbench_accounts, pgbench_tellers and pgbench_branches tables with the given fillfactor. Default is 100.
• scale_factor: Multiply the number of rows generated by the scale factor.
• bench_plan: list of bench to execute.

Have a look to the official documentation for more information: https://www.postgresql.org/docs/11/pgbench.html

A bench is defined as a dictionary with:

• bench: type of the bench to run, one of tpcb-like, simple-update, select-only
• client: number of concurrent database clients
• jobs: number of threads
• transactions: number of transactions each client runs OR duration of benchmark test in seconds
  • transactions: '--transactions 100'
  • transactions: '--time 100'
• name (optional): name of the bench to easily identify it in summary file
• description (optional): description of the bench to easily understand the purpose of the bench
• port (optional): database server port number, will default to pgport
• additional_options (optional): others pgbench options, will default to none

Example

bench_plan:
  - {name: 'bench 100 transaction on RW port',
     description: 'Bench read-write performance',
     bench: 'tpcb-like',
     port: '{{ pgport_rw }}',
     client: '{{ client }}',
     jobs: '{{ jobs }}',
     transactions: '--transactions 100',
     additional_options: '{{ additional_options }}'}

Your first bench

Once configured, you can execute the playbook

ansible-playbook -i <host>, ./main.yml

Where host is the fqdn from which you want to run the bench. The playbook will generate a result file formated as yaml on the host from which you are running the playbook.

Example:

---
bench 100 transaction on RW port:
  description: Bench read-write performance
  result:
    latency average:
      unit: ms
      value: 13.179
    number of clients: 10
    number of threads: 2
    number of transactions actually processed: 100
    number of transactions per client: 100
    query mode: simple
    scaling factor: 1
    tps (excluding connections establishing): 760.933029
    tps (including connections establishing): 758.789821
    transaction type: <builtin:TPC-B (sort of)>

    rc: 0
    stderr:
  params:
    additional_options: --vacuum-all -n -r
    bench: tpcb-like
    client: 10
    description: Bench read-write performance
    jobs: 2
    name: bench 100 transaction on RW port
    port: '5432'
    transactions: --transactions 100

bench for 100 seconds:
  description: Bench read-write performance during a given period
  result:
    duration:
      unit: s
      value: 100
    latency average:
      unit: ms
      value: 10.599
    number of clients: 10
    number of threads: 2
    number of transactions actually processed: 9428100
    query mode: simple
    scaling factor: 1
    tps (excluding connections establishing): 943.512205
    tps (including connections establishing): 943.490068
    transaction type: <builtin:TPC-B (sort of)>

    rc: 0
    stderr:
  params:
    additional_options: --vacuum-all -n -r
    bench: tpcb-like
    client: 10
    description: Bench read-write performance during a given period
    jobs: 2
    name: bench for 100 seconds
    port: '5432'
    transactions: --time 100

bench 100 transactions on RO port:
  description: Bench read-only performance
  result:
    latency average:
      unit: ms
      value: 1.039
    number of clients: 10
    number of threads: 2
    number of transactions actually processed: 100
    number of transactions per client: 100
    query mode: simple
    scaling factor: 1
    tps (excluding connections establishing): 9843.643566
    tps (including connections establishing): 9620.750034
    transaction type: <builtin:select only>

    rc: 0
    stderr:
  params:
    additional_options: --vacuum-all -n -r
    bench: select-only
    client: 10
    description: Bench read-only performance
    jobs: 2
    name: bench 100 transactions on RO port
    port: '5432'
    transactions: --transactions 100

With the results of your benchs you will also have the specs of the server from which you have run the bench. This is basically the content of ansible_facts.

Local test using docker

Spawn a postgresql

docker run --rm --name some-postgres -e POSTGRES_PASSWORD=$PASS -d postgres

Test on Debian

docker run --rm -v $PWD:/mnt -it --link some-postgres:postgres debian:stretch bash
apt update && apt -y install python-pip && pip install -q ansible>=2.8.3
ansible-playbook -i localhost, /mnt/main.yml

Test on Centos

docker run --rm -v $PWD:/mnt -it --link some-postgres:postgres centos:7 bash
yum install -y epel-release && yum install -y python-pip && pip install -q ansible>=2.8.3
ansible-playbook -i localhost, /mnt/main.yml