Giriş
pgbench komutu veri tabanında tablolar oluşturur ve bunları 1 milyon satır ile doldurur. Daha sonra test yaparız
Örnek
$ pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 75.438 ms
initial connection time = 160.700 ms
tps = 132.559344 (without initial connection time)
$
Sonra shared_buffer seçeneğini değiştirelim. Önce şöyle
olsun$ show shared_buffers;
shared_buffers
----------------
128MB
(1 row)
sudo vi /etc/postgresql/15/main/postgresql.conf
...
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 1GB # min 128kB
# (change requires restart)
sudo systemctl restart postgresql
$ show shared_buffers;
shared_buffers
----------------
1GB
(1 row)
$ pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 47.632 ms
initial connection time = 148.379 ms
tps = 209.944478 (without initial connection time)
latency değeri azaldı. Ayrıca transaction per second değeri de arttı
-C seçeneği - Connection Overhead
The -C option in the pgbench indicates that for every single transaction, pgbench will close the open connection and create a new one. This is useful for measuring the connection overhead.
Örnek
$ pgbench -c 20 -t 100 -S my_benchmark_test_db -h 127.0.0.1 -p 6432 -U my_db_user -C -f mysql.sql
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 50
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
number of failed transactions: 0 (0.000%)
latency average = 178.276 ms
average connection time = 8.867 ms
tps = 112.185757 (including reconnection times)
SQL script 1:
- weight: 1 (targets 50.0% of total)
- 1022 transactions (51.1% of total, tps = 57.326922)
- number of failed transactions: 0 (0.000%)
- latency average = 85.993 ms
- latency stddev = 50.377 ms
SQL script 2: mysql.sql
- weight: 1 (targets 50.0% of total)
- 978 transactions (48.9% of total, tps = 54.858835)
- number of failed transactions: 0 (0.000%)
- latency average = 84.039 ms
- latency stddev = 51.036 ms
-c seçeneği - the_number_of_clients_to_connect_with
Kaç tane connection açılacağını belirtir.
Örnek
$ pgbench -c 1000 -T 60 my_benchmark_test_db -h 127.0.0.1 -p 5432 -U my_db_user
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
pgbench: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: sorry, too many clients already
connection to server at "127.0.0.1", port 5432 failed: FATAL: sorry, too many clients already
pgbench: error: could not create connection for client 44
Örnek
pgbench -c 1000 -T 60 my_benchmark_test_db -h 127.0.0.1 -p 6432 -U my_db_user
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type:
scaling factor: 50
query mode: simple
number of clients: 1000
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 47370
number of failed transactions: 0 (0.000%)
latency average = 1106.280 ms
initial connection time = 8788.955 ms
tps = 903.930420 (without initial connection time)
dmi@dmi-VirtualBox:~$
-i seçeneği - initialize
Örnek
$ /usr/pgsql-10/bin/pgbench -i -s 5 testdb_1
...
$ psql testdb_1
testdb_1=# \dt+
List of relations
Schema | Name | Type | Owner | Size |
--------+------------------+-------+----------+---------+----
public | pgbench_accounts | table | postgres | 64 MB |
public | pgbench_branches | table | postgres | 40 kB |
public | pgbench_history | table | postgres | 0 bytes |
public | pgbench_tellers | table | postgres | 40 kB |
(4 rows)
-s seçeneği - scale
Normal veri setinden ne kadar daha fazla kullanılacağını belirtir
Örnek
$ pgbench -i -s 50 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Password:
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)...
5000000 of 5000000 tuples (100%) done (elapsed 10.19 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 30.29 s (drop tables 0.05 s, create tables 0.04 s, client-side generate 10.64 s, vacuum 4.75 s, primary keys 14.81 s).
$
-t seçeneği - the_number_of_transactions_to_execute
pgbench -c <the_number_of_clients_to_connect_with> -j <the_number_of_workers_processes>
-t <the_number_of_transactions_to_execute> <sample_db_name>
-T seçeneği - duration of the test
Örnek
pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Örnek
pgbench -c 50 -j 2 -T 180 benchmark_delay
In this example, -c sets the number of client connections, -T defines the duration of the test in seconds, and -U specifies the user.