# Test PostgreSQL tuning with HammerDB

## In this learning path

- [Introduction](https://learn.arm.com/learning-paths/servers-and-cloud-computing/postgresql_tune/)
- [Measure the impact of tuning PostgreSQL](https://learn.arm.com/learning-paths/servers-and-cloud-computing/postgresql_tune/before_and_after/)
- [Tune storage, kernel, compiler, and library settings for performance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/postgresql_tune/kernel_comp_lib/)
- [Tune PostgreSQL configuration for performance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/postgresql_tune/tuning/)
- [Test PostgreSQL tuning with HammerDB](https://learn.arm.com/learning-paths/servers-and-cloud-computing/postgresql_tune/testing/)
- [Next Steps](https://learn.arm.com/learning-paths/servers-and-cloud-computing/postgresql_tune/_next-steps/)

## Measure PostgreSQL tuning changes
Use this section if you need a repeatable way to test PostgreSQL changes. If you already have a performance test methodology for your PostgreSQL deployment, use that instead.

HammerDB TPROC-C is one option for comparing a baseline with a tuned configuration. It does not replace a performance test that reflects your application’s SQL statements, data size, client concurrency, and latency requirements.

## Before you begin
You need a physical system or cloud instance with [PostgreSQL](https://www.postgresql.org/) installed and configured, plus enough storage and memory for the test database.

## About HammerDB
[HammerDB](https://www.hammerdb.com/) is a database performance test tool that supports PostgreSQL, MySQL, MariaDB, Db2, and SQL Server. It provides TPROC-C and TPROC-H workloads. TPROC-C models an [online transaction processing](https://www.hammerdb.com/docs/ch03s01.html) workload based on TPC-C, while TPROC-H models a decision-support workload based on TPC-H.

TPROC-C and TPROC-H results are not official TPC-C or TPC-H benchmark results. If you need a TPC-C or TPC-H result, run the corresponding official TPC benchmark and follow its rules.

This Learning Path uses TPROC-C, which simulates a company processing customer orders and managing warehouses containing the products it sells.

For more information, see the [HammerDB documentation](https://www.hammerdb.com/document.html).

## Installing HammerDB
Follow the [installation instructions](https://www.hammerdb.com/docs/ch01.html) to install HammerDB.

## Running tests using the GUI
You can use TCL scripts to run HammerDB automatically.

You can also run a HammerDB test with the GUI. If you are interested in running HammerDB tests through a GUI, read the [Quick Start](https://www.hammerdb.com/docs/ch02.html) guide.

## Running tests using TCL scripts
The [CLI and command documentation](https://www.hammerdb.com/docs/ch09s08.html) explains how to set up and run TCL-script-based tests.

The example in the documentation is for MySQL. The example below is for testing `PostgreSQL`.

The script creates a test database with 1000 warehouses. The user count is set to 128 to populate the database faster. It uses stored procedures (`pg_storedprocs`) instead of functions because HammerDB recommends this option for PostgreSQL version 11 and later.

1. Use a text editor to save the code below in a file named `build.tcl`
   
   Replace `postgresql_host_ip` with your IP address.

   ```tcl
   #!/bin/tclsh
   dbset db pg
   dbset bm TPROC-C
   diset connection pg_host <postgresql_host_ip>
   diset connection pg_port 5432
   diset tpcc pg_storedprocs true
   diset tpcc pg_count_ware 1000
   diset tpcc pg_num_vu 128
   diset tpcc pg_raiseerror true
   buildschema
   ```

2. Execute the script:
  
   ```tcl
   hammerdbcli auto build.tcl
   ```

The next script runs six TPROC-C test iterations. It runs with 8, 16, 32, 64, 128, and 256 users. Each iteration ramps for three minutes and then runs for 15 minutes.

1. Use a text editor to save the code below in a file named `test.tcl`
   
   Replace `postgresql_host_ip` with your IP address.

   ```tcl
   #!/bin/tclsh
   dbset db pg
   dbset bm TPROC-C
   diset connection pg_host <postgresql_host_ip>
   diset connection pg_port 5432
   diset connection pg_sslmode disable
   diset tpcc pg_timeprofile true
   diset tpcc pg_storedprocs true
   diset tpcc pg_count_ware 1000
   diset tpcc pg_num_vu 128
   diset tpcc pg_driver timed
   diset tpcc pg_rampup 3
   diset tpcc pg_duration 15
   diset tpcc pg_raiseerror true
   loadscript

   puts "Start Test(s)"
   foreach z { 8 16 32 64 128 256 } {
       puts "Testing with $z users"
       vuset vu $z
       vuset iterations 1
       vuset logtotemp 1
       vuset unique 1
       vucreate
       vurun
       runtimer 7200
       vudestroy
       after 5000
   }
   puts "Tests complete"
   ```

2. Execute the script:
  
   ```tcl
   hammerdbcli auto test.tcl
   ```

Use these scripts as a starting point. Adjust the data size, user count, ramp time, duration, and SQL execution mode to create a workload closer to your use case.

## Running tests against a pool of PostgreSQL nodes
You can run tests against a PostgreSQL cluster. The cluster can consist of a primary read/write node and one or more read-only standby nodes.

See the [HammerDB connection pool documentation](https://www.hammerdb.com/docs/ch04s06.html#d0e2280) for cluster testing options.

## What you’ve learned
You’ve learned how to use HammerDB TPROC-C as a repeatable PostgreSQL test workload.

Use the same workload, system configuration, and measurement process before and after each tuning change so you can attribute the result to the change you made.