Posted on by HammerDB

Find the right PostgreSQL, MySQL and MariaDB parameters first time with hammerpost

Hammerpost is a HammerDB contribution project from @dineshkumar02 that wraps HammerDB with an automated framework to rapidly prototype multiple combinations of database parameters to find the optimal configuration for any specific environment.

You can find the hammerpost project and documentation here. This post gives a brief example of using hammerpost to encourage you to investigate this project further.  In this example, we already have a PostgreSQL database already running on a system and want to test different combinations of parameters.

Firstly, we will check that we can start our database.

Copy Code 


hammerdb@REDPOLL:~$ /home/hammerdb/pgsql/bin/pg_ctl -D /home/hammerdb/pgsql/data start
waiting for server to start....2023-04-04 11:27:29.650 GMT [3555] LOG:  redirecting log output to logging collector process
2023-04-04 11:27:29.650 GMT [3555] HINT:  Future log output will appear in directory "log".
done
server started

Next, we will install the hammerpost-agent on the same system as the database,  this example the system is running Ubuntu 22.04.

Copy Code 


hammerdb@REDPOLL:~$ git clone https://github.com/dineshkumar02/hammerpost-agent.git
Cloning into 'hammerpost-agent'...
remote: Enumerating objects: 38, done.
remote: Counting objects: 100% (38/38), done.
remote: Compressing objects: 100% (22/22), done.
remote: Total 38 (delta 15), reused 32 (delta 9), pack-reused 0
Receiving objects: 100% (38/38), 22.45 KiB | 1.25 MiB/s, done.
Resolving deltas: 100% (15/15), done.
hammerdb@REDPOLL:~$ cd hammerpost-agent
hammerdb@REDPOLL:~/hammerpost-agent$ make
GOOS=linux CGO_ENABLED=0 go build -o hammerpost-agent -v -ldflags="-X 'main.Version=0.1.0' -X 'main.GitCommit=45d7496' -X 'main.CommitDate=2023-04-02 19:14:52 +0530'"
hammerpost-agent

We can then start the agent on the same system as the database giving it the ability to start and stop our database with the modified parameters.

Copy Code 


hammerdb@REDPOLL:~/hammerpost-agent$ sudo ./hammerpost-agent --stop-cmd "/home/hammerdb/pgsql/bin/pg_ctl -D /home/hammerdb/pgsql/data stop -m f" --start-cmd "/home/hammerdb/pgsql/bin/pg_ctl -D /home/hammerdb/pgsql/data start" --pgdsn "postgres://postgres:postgres@localhost:5432/postgres" --db-type postgres
[GIN-debug] [WARNING] Creating an Engine instance with the Logger and Recovery middleware already attached.

[GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production.
 - using env:	export GIN_MODE=release
 - using code:	gin.SetMode(gin.ReleaseMode)

[GIN-debug] GET    /start                    --> main.main.func1 (3 handlers)
[GIN-debug] GET    /stop                     --> main.main.func2 (3 handlers)
[GIN-debug] GET    /info                     --> main.main.func3 (3 handlers)
[GIN-debug] GET    /metrics                  --> main.main.func4 (3 handlers)
[GIN-debug] POST   /set-param                --> main.main.func5 (3 handlers)
[GIN-debug] GET    /load                     --> main.main.func6 (3 handlers)
[GIN-debug] GET    /ping                     --> main.main.func7 (3 handlers)
[GIN-debug] [WARNING] You trusted all proxies, this is NOT safe. We recommend you to set a value.
Please check https://pkg.go.dev/github.com/gin-gonic/gin#readme-don-t-trust-all-proxies for details.
[GIN-debug] Listening and serving HTTP on :8989

Next we will grab the latest HammerDB PostgreSQL docker image with the drivers included.

Copy Code 


hammerdb@REDPOLL:~$ sudo docker pull tpcorg/hammerdb:postgres
[sudo] password for hammerdb: 
postgres: Pulling from tpcorg/hammerdb
...
Digest: sha256:59d901bfd14452ca3f345524384dd9d4b61effcc555e5133a71534440141c378
Status: Downloaded newer image for tpcorg/hammerdb:postgres
docker.io/tpcorg/hammerdb:postgres

and start the image

Copy Code 


hammerdb@REDPOLL:~$ sudo docker run --network=host -it --name hammerdb-postgres tpcorg/hammerdb:postgres bash
root@REDPOLL:/home/hammerdb/HammerDB-4.7#

We now install go inside the container and install hammerpost in the HammerDB directory. Note that it needs to be a more updated version than the default with the HammerDB Docker container.

Copy Code 


root@REDPOLL:/usr/local# wget https://go.dev/dl/go1.19.4.linux-amd64.tar.gz
root@REDPOLL:/usr/local# tar -xvf go1.19.4.linux-amd64.tar.gz
root@REDPOLL:/usr/local# export PATH=/usr/local/go/bin:$PATH
root@REDPOLL:/usr/local# apt-get install make

root@REDPOLL:/home/hammerdb/HammerDB-4.7# git clone https://github.com/dineshkumar02/hammerpost.git
root@REDPOLL:/home/hammerdb/HammerDB-4.7/hammerpost# go version
go version go1.19.4 linux/amd64
root@REDPOLL:/home/hammerdb/HammerDB-4.7/hammerpost# go get
root@REDPOLL:/home/hammerdb/HammerDB-4.7/hammerpost# make

Back in the main HammerDB directory we can link the hammerpost executable, templates and parameter file to local links, meaning we can run hammerpost straight from the local hammerdb directory. We have used hammerp as the name of the link to the hammerpost execuatable as the directory is already called hammerpost.

Copy Code 


root@REDPOLL:/home/hammerdb/HammerDB-4.7# ln -s ./hammerpost/hammerpost ./hammerp
root@REDPOLL:/home/hammerdb/HammerDB-4.7# ln -s ./hammerpost/hammer-templates ./hammer-templates
root@REDPOLL:/home/hammerdb/HammerDB-4.7# ln -s ./hammerpost/params.json ./params.json

With hammerpost installed the first step is to initialize the schema with an example as follows.

Copy Code 


root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --init --name test-bench-1 --pgdsn "postgres://postgres:postgres@localhost:5432/postgres" --users 4 --warehouses 10 --hammerpost-agent localhost:8989

╔ hammerpost - v0.1.0 ═════════════════════════════════════════╗
║                                                              ║
║                                                              ║
║                 OS linux                                     ║
║           Platform ubuntu-22.04                              ║
║             Kernel 5.15.0-56-generic                         ║
║             Uptime 12131                                     ║
║    Total Processes 233                                       ║
║           Load Avg 0.05                                      ║
║                CPU Intel(R) Core(TM) i7-4770K CPU @ 3.50GHz  ║
║          CPU Count 4                                         ║
║          CPU Cores 1                                         ║
║            CPU Mhz 3491.918                                  ║
║   Total Memory(GB) 7                                         ║
║    Free Memory(GB) 1                                         ║
║    Used Memory(GB) 0                                         ║
║                                                              ║
║                                                              ║
╚══════════════════════════════════════════════════════════════╝

DB type  postgres
schema initialized

We can then update the params.json file with the combination of parameters we want to test. In this example it is 4 tests with different combinations of shared_buffers and wal_buffers.

Copy Code 


root@REDPOLL:/home/hammerdb/HammerDB-4.7# vi params.json
{
        "shared_buffers": ["512MB", "1GB"],
        "wal_buffers": ["32MB", "64MB"]
}

We can then run the test. hammerpost will update the parameters, start and stop the database and run hammerdb.

Copy Code 


root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --run --name test-bench --pgdsn "postgres://postgres:postgres@redpoll:5432/postgres" --users 4 --warehouses 10 --hammerpost-agent redpoll:8989 --param-file ./params.json  --logfile test-bench1.log

╔ hammerpost - v0.1.0 ═════════════════════════════════════════╗
║                                                              ║
║                                                              ║
║                 OS linux                                     ║
║           Platform ubuntu-22.04                              ║
║             Kernel 5.15.0-56-generic                         ║
║             Uptime 16947                                     ║
║    Total Processes 237                                       ║
║           Load Avg 0.42                                      ║
║                CPU Intel(R) Core(TM) i7-4770K CPU @ 3.50GHz  ║
║          CPU Count 4                                         ║
║          CPU Cores 1                                         ║
║            CPU Mhz 3491.918                                  ║
║   Total Memory(GB) 7                                         ║
║    Free Memory(GB) 0                                         ║
║    Used Memory(GB) 0                                         ║
║                                                              ║
║                                                              ║
╚══════════════════════════════════════════════════════════════╝

DB type  postgres
Benchmark id  5
Parameter test cases  4

┌ Parameters ──────────┐
│                      │
│ shared_buffers:512MB │
│ wal_buffers:32MB     │
│                      │
│                      │
└──────────────────────┘

┌ Results ──────────────┐
│                       │
│ 66906 NOPM 151341 TPM │
│                       │
└───────────────────────┘

┌ Parameters ────────┐
│                    │
│ shared_buffers:1GB │
│ wal_buffers:32MB   │
│                    │
│                    │
└────────────────────┘

┌ Results ──────────────┐
│                       │
│ 70710 NOPM 161991 TPM │
│                       │
└───────────────────────┘

┌ Parameters ──────────┐
│                      │
│ shared_buffers:512MB │
│ wal_buffers:16MB     │
│                      │
│                      │
└──────────────────────┘

┌ Results ──────────────┐
│                       │
│ 67146 NOPM 153539 TPM │
│                       │
└───────────────────────┘

┌ Parameters ────────┐
│                    │
│ shared_buffers:1GB │
│ wal_buffers:16MB   │
│                    │
│                    │
└────────────────────┘

┌ Results ──────────────┐
│                       │
│ 66126 NOPM 151047 TPM │
│                       │
└───────────────────────┘

hammerdb run completed
root@REDPOLL:/home/hammerdb/HammerDB-4.7#

hammerpost will also gather system level metrics that you can use to analyze your tests.

Copy Code 


root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --test-details 9
+----------------------+----------------------+----------+----------------------+--------+-------+
|        START         |         END          | DURATION |      PARAMETERS      | OUTPUT | ERROR |
+----------------------+----------------------+----------+----------------------+--------+-------+
| 2023-04-04T15:11:38Z | 2023-04-04T15:14:34Z | 2m56s    | shared_buffers:512MB |        |       |
|                      |                      |          | wal_buffers:16MB     |        |       |
+----------------------+----------------------+----------+----------------------+--------+-------+
Test Details
root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --test-metrics 9
+-----------+--------------+-------------------------------+
| CPU USAGE | MEMORY USAGE |             TIME              |
+-----------+--------------+-------------------------------+
|     21.72 |        98.04 | 2023-04-04 15:13:27 +0000 UTC |
+-----------+--------------+-------------------------------+
|     95.36 |        98.35 | 2023-04-04 15:13:29 +0000 UTC |
+-----------+--------------+-------------------------------+
|     95.18 |        98.50 | 2023-04-04 15:13:31 +0000 UTC |
+-----------+--------------+-------------------------------+
|     98.75 |        98.57 | 2023-04-04 15:13:34 +0000 UTC |
+-----------+--------------+-------------------------------+
|    100.00 |        98.46 | 2023-04-04 15:13:36 +0000 UTC |
+-----------+--------------+-------------------------------+
|     97.96 |        98.50 | 2023-04-04 15:13:38 +0000 UTC |
+-----------+--------------+-------------------------------+
|     99.50 |        98.56 | 2023-04-04 15:13:40 +0000 UTC |
+-----------+--------------+-------------------------------+
|     98.99 |        98.35 | 2023-04-04 15:13:42 +0000 UTC |
+-----------+--------------+-------------------------------+
|    100.00 |        98.41 | 2023-04-04 15:13:45 +0000 UTC |
+-----------+--------------+-------------------------------+
|     94.72 |        98.49 | 2023-04-04 15:13:47 +0000 UTC |
+-----------+--------------+-------------------------------+
Test Metrics
root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --test-stats 9
+--------+--------+------------+----------+------------+----------+---------+
| AVGCPU | AVGMEM | AVGRPERSEC | AVGRMBPS | AVGWPERSEC | AVGWMBPS | AVGUTIL |
+--------+--------+------------+----------+------------+----------+---------+
|  94.69 |  98.40 |       0.00 |     0.00 |       0.00 |     0.00 |    0.00 |
+--------+--------+------------+----------+------------+----------+---------+

Hammerpost can be a great addition to your HammerDB benchmarking environment by enabling you to automate your tests to determine optimal parameter settings for your configuration.

Author