Example

An End-To-End Tuning Example

We provide an end-to-end example to tune Postgres 9.6 for TPC-C workload on a AWS m5d.xlarge instance with OtterTune. The throughput is improved from ~500 txn/sec with default configuration to ~1000 txn/sec with recommended one. This is a good example to get started with OtterTune.

Hardware

We use m5d.xlarge instance on AWS, which has 4 CPUs, 16GB RAM, and 150GB local storage SSD. We use local SSD to store the target database data since it does not have IOPS limits compared to EBS. However, you will lose your local data once stop/restart/terminate the instance (more info). We use Ubuntu Server 16.04 LTS (HVM) AMI.

Installation

We choose Postgres 9.6 as the target database, i.e. the database we will tune on the client side. On the server side, we use MySQL to store the website data. We set up both client side and server side on the same machine, since we find that it has little effect on the performance. Follow the instruction on the Linux Quick Start Wiki to install the packages on the bare-metal instance.

Mount the local storage

We use local storage on AWS to save money. You need to format and mount the device, and specify the data directory path in Postgres. e.g. the device is /dev/nvme1n1 and we mount it to the directory /mnt/data. You should

mkfs -t ext4 /dev/nvme1n1   # Format the device
mount -t ext4 /dev/nvme1n1 /mnt/data   # Mount the device
sudo rsync -av /var/lib/postgresql /mnt/data   # Copy the data from the old directory to the mounted one.

Then set the data_directory in Postgres configuration to the new one (e.g. /mnt/data/postgresql/9.6/main), restart the database. Then it should save all the data to the local storage SSD.

Client-side

You should specify controller configuration, driver configuration, and OLTP-Bench configuration. More information can be found in Client-Side Wiki. In this example, we use TPC-C workload in OLTP-Bench, and change the settings in config/tpcc_config_postgres.xml as follows.

<time>300</time>  # observation time is 5 mins
<rate>unlimited</rate>  # unlimited rate
<scalefactor>200</scalefactor>  # Scale Factor in TPC-C
<terminals>50</terminals>   # 50 concurrent terminals

Also, we restore database every 10 loops as default. (question 4 in FAQ) Restoring from the dumped database is faster than reload it from OLTP-Bench. It takes around 10 mins to restore the database in this example.

Server-side

Set up the web server, and run celery worker. Details can be found in Get Started Wiki

Collect Training Data

After setting up all things above, you can then use OtterTune to tune Postgres. However, it needs initial training data to train ML models. You can create a no-tuning session on the website, and use LHS sampling to collect initial data, or upload existing data collected before. We provide 50 samples as training data which can be downloaded here. For detailed information, see Collecting Training Data Wiki page. When you have training data uploaded to the server, you can run Celery beat to get pruned metrics and important knobs in the server side.

Start The Tuning

Then you can run tuning loops. Create a tuning session on the website and update the upload code in the driver configuration. The client-side controller and driver will try the recommended configuration, collect the information, upload it to the server, and get the next configuration recommended by the server-side ML models. To run 50 loops, you can simply run the following command on the client side.

fab run_loops:50

When creating sessions, you should choose Postgres 9.6, and m3.xlarge hardware type which has same 16GB RAM size with m5d.xlarge. Currently, OtterTune gets memory information from the hardware type to ensure the recommended memory-related knobs (like shared_buffers) will not exceeds the hardware memory size.

You should see the throughput is improved from ~500 txn/sec with the default configuration to ~1000 txn/sec with the recommended one.

FAQ

See Wiki Page