gregs1104/pgbench-tools

PostgreSQL Benchmarking Toolset

pgbench-tools's Issues

\setrandom no longer in pg

Hello,
I am new to postgres, please forgive me if I am mistaken.
It seems that 9.6 no longer accepts \setrandom. In my branch I have a blind guess at the values.

https://github.com/deinarson/pgbench-tools/commit/0238959131c42e31ef073a3b61b3d6b79a969427

I would pr but I am not sure if this is the correct way to deal with this.

Running pgbench-tools in 2 different host (1 host with test db and 1 host with results db)

Is there a possibility that the test can be done in different hosts, ie installing pgbench-tools and the database results in another server different from where is the test database ?, or if there is an example of configuration to do it in some side?

pgbench missing transaction timestamps

When running the benchmark I get this error that seems to indicate that the version of pgbench I'm using is lacking some patch that would add timestamps to the output:

ERROR:  the pgbench used for this test is missing transaction
timestamps. No latency information will be imported into
the database and some plots will be missing.

Running PG13 on a Rocky Linux 8.7, here are the installed rpms:

postgresql13-13.9-1PGDG.rhel8.x86_64
postgresql13-contrib-13.9-1PGDG.rhel8.x86_64
postgresql13-devel-13.9-1PGDG.rhel8.x86_64
postgresql13-libs-13.9-1PGDG.rhel8.x86_64
postgresql13-server-13.9-1PGDG.rhel8.x86_64

The pgbench binary is at /usr/pgsql-13/bin/pgbench and comes from the postgresql13-13.9-1PGDG.rhel8.x86_64 package.

Error running against postgres14.

Can you guys can help me understand if I'm doing something wrong on my test?

When I run "./runset" I got this error:

Run set #2 of 3 with 1 clients scale=1
Workers using 8 CPUs
Running tests using: psql -h localhost -U postgres -p 5432 -d pgbench
Storing results using: psql -h localhost -U postgres -p 5432 -d results
Did not find standard pgbench tables
Cleaning up database pgbench
VACUUM
CHECKPOINT
Waiting for checkpoint statistics
ERROR: invalid input syntax for type integer: ""
LINE 1: ...XXXXXXX.ec2.internal',':-i -I dtGvp','1','1','','1','91...

using the tool with PostgreSQL 10

Hi,

I'm currently running pgbench-tools on PG 10. I had to patch a few things for this. Interested in a pull request ? Should the tool work with all versions, or only latest ? (because testing for the version would make the patch more complicated)

Regards

gnuplot error in container

When running it in a container I'm getting the following error from gnupot:

gnuplot> set xlabel 'Time'; set ylabel 'wMB/s'; plot 
                                                     ^
         line 0: function to plot expected


gnuplot> set xlabel 'Time'; set ylabel 'rMB/s'; plot 
                                                     ^
         line 0: function to plot expected


gnuplot> set xlabel 'Time'; set ylabel '%util'; plot 
                                                     ^
         line 0: function to plot expected


gnuplot> set xlabel 'Time'; set ylabel 'us'; plot 
                                                  ^
         line 0: function to plot expected

can't find plans

Will this error affect the result?

gnuplot graphs have garbled text on Mac OS/homebrew

In July 2019 pgbench-tools merged PR #15 which switched to using gnuplot's pngcairo output "terminal", to use gnuplot's terminology. This improves the quality of graphs compared to the basic png output. In July 2020, I noticed that output stopped working correctly under the gnuplot chain distributed by the Mac homebrew tool. The text of the labels is corrupted, with successive characters overwriting each other. It's easy to test for the problem:

$ gnuplot
set output "test.png"
set term pngcairo
plot sin(x)
quit
$ open test.png

And I'm attaching a sample. Commit a81305c gave a workaround for the problem, which is simply to switch back to set term png and lose some graphics quality.

The problem appears resolved for both OS X Mojave and Catalina after this update:

$ brew update && brew upgrade
...
Updated Homebrew from 5de4378a9 to 7eb1a0969.

The versions of gnuplot and cairo didn't change between those two commits. This makes me think it was a typography library bug, in a lower level drawing package; maybe freetype? I'm still bisecting a root cause, mainly to defend against a future regression now that it's fixed. I'll document a bit further and revert the workaround once I'm satisfied with this issue report.

HOW TO USE THIS

How can i use this tool....????

Where i can find Setup Doc?

when i run psql -f init/resultdb.sql -d results -U postgres and got the error ,my version 9.2.4

psql:init/resultdb.sql:38: NOTICE: CREATE TABLE will create implicit sequence "tests_test_seq" for serial column "tests.test"
psql:init/resultdb.sql:38: ERROR: type "jsonb" does not exist
LINE 26: artifacts jsonb

Make sure desired script exists before running tests

Minor irritation but the benchmark will run through all the stages of loading data etc even if the configured test script does not exist. I typo'd a test name and really wasted a lot of time running huge tests with zero results.

diff --git a/runset b/runset
index 5e7a05c..955a0be 100755
--- a/runset
+++ b/runset
@@ -8,6 +8,12 @@ if [ ! -f $PGBENCHBIN ]; then
   exit
 fi

+# Make sure the configured test script actually exists
+if [ ! -f "${TESTDIR}/${SCRIPT}" ]; then
+  echo "ERROR: cannot find test script ${TESTDIR}/${SCRIPT}, aborting"
+  exit
+fi
+
 TESTPSQL="psql -h $TESTHOST -U $TESTUSER -p $TESTPORT -d $TESTDB"

 # See if this database has all the standard pgbench tables in it

When running pgbench-tools in PG13 docker container File "./timed-os-stats", line 136, in <module>

postgres@bus-dev-01:/home$ python -V
Python 3.7.3
postgres@bus-dev-01:/home$ ./runset
Removing old pgbench tables
DROP TABLE
VACUUM
Creating new pgbench tables
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.05 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.38 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.22 s, vacuum 0.08 s, primary keys 0.07 s).

real 0m0.464s
user 0m0.105s
sys 0m0.024s
Run set #1 of 3 with 1 clients scale=1
Workers using 8 CPUs
Running tests using: psql -h localhost -U postgres -p 5432 -d pgbench
Storing results using: psql -h localhost -U postgres -p 5432 -d results
Found standard pgbench tables with prefix=pgbench_
Cleaning up database pgbench
TRUNCATE TABLE
VACUUM
CHECKPOINT
Waiting for checkpoint statistics
INSERT 0 1
This is test 146
Script select executing for 1 concurrent users...
Traceback (most recent call last):
File "./timed-os-stats", line 136, in
Traceback (most recent call last):
File "./timed-os-stats", line 114, in
capture("iostat -mx 1")
File "./timed-os-stats", line 67, in capture
capture("vmstat 1")
File "./timed-os-stats", line 67, in capture
sys.stdout.write(line)
File "./timed-os-stats", line 29, in write
self.f.write(x)
TypeError: write() argument must be str, not bytes
sys.stdout.write(line)
File "./timed-os-stats", line 29, in write
Exception ignored in: <main.FlushFile object at 0x7f25d77b32b0>
AttributeError: 'FlushFile' object has no attribute 'flush'
self.f.write(x)
TypeError: write() argument must be str, not bytes
Exception ignored in: <main.FlushFile object at 0x7fcb942612b0>
AttributeError: 'FlushFile' object has no attribute 'flush'
^C

How to change testing pgbench params in config to do a quick test with pgbench-tools (POSTGRESQL 10)

Hi, i'm asking about how to change testing pgbench params in config to do a quick test with pgbench-tools in postgresql-10, also i've to change a bit code to make it run and work the project, i'll upload the changes in file code.
my enviroment is:

• ubuntu server bionic 18.04, Postgresql 10.7-1 with patroni - HA, Replication (2 nodes sync and asinc),

• 1 test example DB called "pedidos".

• clone pgbench-tools:
  git clone https://github.com/emerichunter/pgbench-tools.git

• INSTALL gnuplot:
  sudo apt-get install gnuplot

After testing, trying and failure I have to change code in this files:
runset
runset.txt

benchwarmer
benchwarmer.txt

Mainly changing lines with the:
[ -z $PGBENCHBIN ] || [ ! -x $PGBENCHBIN ]
[ ! -z $GNUPLOT ] && [ -x $GNUPLOT ]
to
[[ -z ${PGBENCHBIN} ]] || [[ ! -x ${PGBENCHBIN} ]]
[[ ! -z ${GNUPLOT} ]] && [[ -x ${GNUPLOT} ]]

Then I've to do the next to init and restart pgbench-tools testing:

Executed the follow:

I don't have to do: createdb pgbench because my testing DB is called "pedidos".

0.- createdb -U postgres -h patroni_prueba1 results

1.- psql -h patroni_prueba1 -f init/resultdb.sql -d results

2.- config. PGDATA path:
export PGDATA="/data/patroni/"

3.- Create testing sets with a description:
./newset 'Initial Config'

4.- Let to user execute the scripts with privilege:
chmod +x benchwarmer
chmod +x cleanup

5.- Run the set tests output STDIN AND OUT to file test1.txt:
./runset > test1.txt 2>&1

6.- If didn't work i do:
dropdb -h patroni_prueba1 -U postgres -w results

• I've to do the above command because it had hanging connection of transactions..

7.- Delete the directory failed test: rm -rf /pgbench-tools/results/*

8.- Start again from the step 0.

The output from log file (test1.txt) is: (Also having troubles with GNUPLOT)
test1.txt

Some test output is:

and so on...until test 6 I've to kill pgbench-tools process because the large testing time...

I would like to change testing pgbench params like:
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000

this last to make a bit fast the test, and changing them to test differents settings, I hope you can help me, thanks and regards.

runtest problem

Some strange errors (when i follow the setup):

Run set #3 of 3 with 32 clients scale=1000
Running tests using: psql -h localhost -U postgres -p 5432 -d pgbench
Storing results using: psql -h localhost -U postgres -p 5432 -d results
ERROR: relation "pgbench_branches" does not exist
LINE 1: select count(*) from pgbench_branches
^
ERROR: Attempt to determine database scale returned "", aborting

Error in init/resultsdb

A semicolon on line #5 in init/resultdb.sql is the culprit.

Is this expected?

Hi,

I'm getting the following errors during the test run (below). Should they be happening?

{{{
Run set #2 of 3 with 16 clients scale=1
Running tests using: psql -h localhost -U postgres -p 5432 -d pgbench
Storing results using: psql -h localhost -U postgres -p 5432 -d results
Found standard pgbench tables with prefix=pgbench_
Cleaning up database pgbench
TRUNCATE TABLE
Skipping vacuum, it was recently ran by the last test
CHECKPOINT
Waiting for checkpoint statistics
INSERT 0 1
This is test 11
Script select.sql executing for 16 concurrent users...
./benchwarmer: line 229: 25716 Killed ./timed-os-stats vmstat > results/$TEST/vmstat.log (wd: ~/pgbench-tools-master)
Traceback (most recent call last):
File "../../kill_pg", line 29, in
kill_pg(int(sys.argv[1]))
File "../../kill_pg", line 12, in kill_pg
pgid = os.getpgid(pid)
OSError: [Errno 3] No such process
./benchwarmer: line 229: 25718 Killed ./timed-os-stats meminfo > results/$TEST/meminfo.log (wd: ~/pgbench-tools-master)
UPDATE 1
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 1
duration: 60 s
number of transactions actually processed: 298863
tps = 4979.932254 (including connections establishing)
tps = 4989.163544 (excluding connections establishing)
Cleaning up database pgbench
TRUNCATE TABLE
VACUUM
CHECKPOINT
Worst latency results:
218952
246585
252718
265586
284780

gnuplot> set xlabel 'Time'; set ylabel 'wMB/s'; plot
^
line 0: function to plot expected

gnuplot> set xlabel 'Time'; set ylabel 'rMB/s'; plot
^
line 0: function to plot expected

gnuplot> set xlabel 'Time'; set ylabel '%util'; plot
^
line 0: function to plot expected

Run set #2 of 3 with 32 clients scale=1
}}}

Some configuration advice for pgbench-tools

1. whether there is a way to support polling of multiple scripts.
   config

SCRIPT="select update insert nobranch"
SCALES="300"
SETCLIENTS="8 16 32 64"
SETTIMES=2

2. use patroni or postgres replication, want to test read and write a little more accurate.
   Does pgbench-tools will support, postgres master test mix sql(insert,update), and postgres slave test sql(read) at the same time, and if it could, may i ask that how make it with new sql file.

3. how to postgres need password login.
   config whether to consider adding something like: psql "host=10.66.33.8 port=5432 user=postgres password=test123ca " -c 'create database pgbench;'

If you use createdb, how to add password command example , some like ,
createdb -h 192.168.1.123 -U postgres -W passwd123 pgbenchDB
or
createdb -h 192.168.1.123 -U postgres -f $DIR/.pgpass pgbenchDB

When running pgbench-tools in remote container getting pid kill errors

Traceback (most recent call last):
  File "../../kill_pg", line 29, in <module>
    kill_pg(int(sys.argv[1]))
  File "../../kill_pg", line 12, in kill_pg
    pgid = os.getpgid(pid)
OSError: [Errno 3] No such process
Traceback (most recent call last):
  File "../../kill_pg", line 29, in <module>
    kill_pg(int(sys.argv[1]))
  File "../../kill_pg", line 12, in kill_pg
    pgid = os.getpgid(pid)
OSError: [Errno 3] No such process
./benchwarmer: line 254:   604 Killed                  ./timed-os-stats meminfo > results/$TEST/meminfo.log  (wd: /usr/local/lib/pgbench-tools)

Getting this error when running the benchmarks from a container thats accessing the postgres database container remotely