Benchmarks

Benchmarking pg_query_setting v0.1

Here is a quick Bash script to benchmark pg_query_setting:

#!/bin/sh

DB=benchs
SCALE=200
CLIENTS=20
JOBS=$CLIENTS
DURATION=600   # 10 minutes
REPEAT=5

echo "##### Preparing benchmark database..."
dropdb --if-exists "${DB}"
createdb "${DB}"
pgbench --initialize --scale ${SCALE} "${DB}"
psql -Xc "CREATE EXTENSION pg_query_settings;" "${DB}"
echo

echo "##### Benchmarking without pg_query_settings"
psql -Xc "ALTER SYSTEM RESET session_preload_libraries"
psql -Xc "SELECT pg_reload_conf()"
for i in $(seq 1 ${REPEAT})
do
  echo "### run $i"
  pgbench --select-only --jobs ${JOBS} --client ${CLIENTS} --time ${DURATION} "${DB}"
  echo
done
echo

echo "##### Adding pg_query_setting to shared_preload_libraries"
psql -Xc "ALTER SYSTEM SET session_preload_libraries TO 'pg_query_settings'"
psql -Xc "SELECT pg_reload_conf()"
echo

for CONFIGS in 1 1000 1000000
do
  echo "##### Benchmarking with pg_query_settings (${CONFIGS} config)"
  psql -Xc "TRUNCATE pgqs_config" "${DB}"
  psql -Xc "INSERT INTO pgqs_config SELECT i, 'work_mem', '10MB' FROM generate_series(1,${CONFIGS}) i" "${DB}"
  for i in $(seq 1 ${REPEAT})
  do
    echo "### run $i"
    pgbench --select-only --jobs ${JOBS} --client ${CLIENTS} --time ${DURATION} "${DB}"
    echo
  done
done

echo "##### Removing pg_query_setting from shared_preload_libraries"
psql -Xc "ALTER SYSTEM RESET session_preload_libraries"
psql -Xc "SELECT pg_reload_conf()"

This script creates a benchs database, and runs pgbench against it. I selected a SELECT-only benchmark and a small (3GB) database that could fit in memory, so that it only hurts CPU, which is our primary concern with this extension. shared_buffers is set to a bigger value (4GB).

There are 4 tests:

• without pg_query_settings in shared_preload_libraries to have a base duration;
• with pg_query_settings in shared_preload_libraries, but an empty config table;
• with pg_query_settings in shared_preload_libraries, but a 1k-tuples config table;
• with pg_query_settings in shared_preload_libraries, but a 1M-tuples config table.

Each test is executed 5 times (see the REPEAT variable), so that we may estimate an average duration.

All tests last 10 minutes (see the DURATION variable). That makes the benchmark quite long to execute (more than 3 hours).

The CONFIGS sequence may change.

Here are some preliminary results:

Having pg_query_settings without additional config lead to approximately 5% decrease in performance. That was my expectation. Having pg_query_settings with a thousand config makes it way much higher, around 41%. Of course, a thousand configuration for queries is probably very high. But still, performance goes really bad. It's even worse with a million configuration for queries.

This is quite normal as we read the whole table each time. We don't use an index to quickly get our tuples because of our code. A quick test shows a 0.085ms via an index scan, and a 0.353ms via a table scan for a 1k-tuple config table. So we absolutely need to make use of the index :)

I also tried with another sequence (1 10 50 100 500). Here are the results:

Test	without	0 config	1 config	10 configs	50 configs	100 configs	500 configs	0 config %	1 config %	10 configs %	50 configs %	100 configs %	500 configs %
#1	110,495.13	106,345.70	102,833.17	103,331.83	100,268.29	96,818.06	77,241.68	-3.90%	-7.45%	-6.93%	-10.20%	-14.13%	-43.05%
#2	109,481.60	106,270.62	103,226.63	103,025.75	99,728.10	96,748.29	77,239.54	-3.02%	-6.06%	-6.27%	-9.78%	-13.16%	-41.74%
#3	108,549.39	106,065.08	102,380.13	102,836.65	99,698.18	97,384.55	77,070.06	-2.34%	-6.03%	-5.56%	-8.88%	-11.46%	-40.85%
#4	108,009.10	106,034.38	103,203.21	102,938.85	99,838.31	96,975.86	76,989.82	-1.86%	-4.66%	-4.93%	-8.18%	-11.38%	-40.29%
#5	107,997.52	105,994.76	103,323.73	102,772.72	99,611.61	97,574.97	76,938.31	-1.89%	-4.52%	-5.08%	-8.42%	-10.68%	-40.37%
							Average	-2.60%	-5.74%	-5.75%	-9.09%	-12.16%	-41.26%

Results are a bit less worrying but we still get quickly to 10% decrease in performance.