July, 2023 | SQL Conjuror

PostgreSQL – Using pgbench for Benchmark Testing

By Enrique 31/07/2023 21/03/2024 PostgreSQL, Database Administration, Performance Optimization

pgbench is a tool for testing the performance of a PostgreSQL database. It works by simulating a specified number of concurrent clients who are executing a series of SQL commands. You can use pgbench to measure the performance of your database and to compare the performance of different database configurations. The pgbench approach is based on TPC-B . The TPC-B benchmark focuses on benchmarking as opposed to OLTP type testing. Read More

The measurement is based on transactions per second. In other words – you’ll define how many transactions to execute per client and this will result in a rate per second.

Using pgbench for Benchmark Testing

1. Create a sample database

CREATE DATABASE example;

We now have an empty database named example. Let’s return to our bash shell to execute pgbench commands. We can do this by issuing the \q (quit) command.

2. Initialize the pgbench DB. This will create the sample tables in the DB

pgbench -h 10.190.30.209 -p 5432 -U jvalencia -i -s 50 example

In the command above, we ran pgbench with the -i option and the -s option followed by the database name (example).

The -i (initialize) option will tell pgbench to initialize the database specified. This will create the following tables within the example database.

The -s is the scaling option. It is used to multiply the number of rows entered into each table. It will take the default rows and multiply it by 50 or whatever scaling number you entered.

3. Establish a baseline.

pgbench -h 10.190.30.209 -p 5432 -U jvalencia -c 10 -j 2 -t 10000 example

-c number of clients

-j number of threads

-t amount of transactions

These values are 10000 transactions per client (10 x 10000 = 100,000 transactions).

3. Get results

Below is a sample result set.

The part of the output that we are most interested in is the following:

tps = 2077.939220 (without initial connection time)

This indicates that our baseline is 2077 database transactions per second.

Cheers!

PostgreSQL – Analyzing Queries Using pg_stat_statements

By Enrique 19/07/2023 21/03/2024 PostgreSQL, Database Administration, Performance Optimization

pg_stat_statements tracks planning and execution statistics of all SQL statements executed by a server.

Find the top 10 time-consuming queries

SELECT 
  round(
    (
      100 * total_exec_time / sum(total_exec_time) OVER ()
    ):: numeric, 
    2
  ) percent, 
  round(total_exec_time :: numeric, 2) AS total, 
  calls, 
  round(mean_exec_time :: numeric, 2) AS mean, 
  substring(query, 1, 200) 
FROM 
  pg_stat_statements 
ORDER BY 
  total_exec_time DESC 
LIMIT 
  10;

Find the queries that are writing to the temp the most Read More

select queryid, left(query,40), calls, temp_blks_read,
temp_blks_written from pg_stat_statements order by
temp_blks_written desc;

Find the queries that are reading from temp the most

select queryid, left(query,40), calls, temp_blks_read,
temp_blks_written from pg_stat_statements order by temp_blks_read
desc;

Find the queries that are reading from disk the most

select queryid, left(query,40), calls, local_blks_read,
local_blks_read/calls as avg_read_per_call from pg_stat_statements
order by local_blks_read desc;

Find the queries with the highest execution times

select queryid, left(query,40), mean_exec_time, max_exec_time from
pg_stat_statements where calls > 10 order by mean_exec_time desc,
max_exec_time desc;

Cheers!

PostgreSQL – Backing up and restoring one or more tables using pg_dump and pg_restore

By Enrique 05/07/2023 21/03/2024 PostgreSQL, Database Administration

There are 2 types of backups in PostgreSQL:

Logical
Physical

Logical backups in PostgreSQL can be taken using pg_dump. A backup taken using pg_dump can exist in both a text file (human-readable script file) format or a custom format. Read More

If you take a backup in plain format (.sql file), we can use psql to restore.

If you take a backup in a custom format (tar/dir/binary), the only option to restore is pg_restore.

pg_restore is 10x faster than psql.

When you do a backup using pg_dump, it will not apply any locks; hence, it will not cause any waits to the database traffic.

There are a few things to note before using pg_dump and planning your backup strategy:

Backing up multiple databases is not possible using pg_dump. It can only backup one database at a time.
pg_dump is not suitable for point-in-time recovery. To do a point-in-time recovery, WAL segments need to be replayed, but it will not be possible to replay transaction logs (WALs) after restoring backups taken using pg_dump.
To ensure that all the data from the disk is readable and have no corrupted pages, it is important to have a periodical logical backup testing.

Backing up a single or several tables from a database is possible using pg_dump. However, backing up a table from one database and another table from another database simultaneously is not possible. pg_dump can only work on one database at a time. Furthermore, taking a backup of an entire database excluding one or more tables is also possible.

We shall use the flag -t to backup and restore a table.

Syntax for backing up a table:

pg_dump -h source_server -p source_port -U  database_name -Fformat -t tablename -f /location/dumpfile_name.bin

Syntax to restore a custom format dump:

pg_restore -h target_server -p target_port -U username -d database_name -t tablename /backup_location/dumpfile_name.bin

Syntax to restore a plain format dump:

psql -h target_server -p target_port -U username -d mypgdb -f /backup_dir/tables.sql

Example: Backing up one or more tables

1. In the example below, we are going to backup employees and salary tables.

Custom format:

pg_dump -t employees -t salary -Fc -f /postgres/backups/employees_salary.bin -v

Plain Format:

pg_dump -t employees -t salary -f /postgres/backups/employees_salary.sql -v

2. If you want to restore these tables to another database in a target server, then create the database first. You may ignore this step if the database already exists.

psql -c "CREATE DATABASE perf_review"

3. Now, we are going to restore the two tables from backup to another database called perf_review:

Custom format:

pg_restore -p5432 -U postgres -t employees -t salary -d perf_review /postgres/backups/employees_salary.bin -v

Plain Format:

psql -p5432 -U postgres -t employees -t salary -d perf_review -f /postgres/backups/employees_salary.sql

Cheers!

Facebook

Google

Twitter

Month: July 2023

PostgreSQL – Using pgbench for Benchmark Testing

PostgreSQL – Analyzing Queries Using pg_stat_statements

PostgreSQL – Backing up and restoring one or more tables using pg_dump and pg_restore

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud