pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql TPS Bottleneck
9+ messages / 7 participants
[nested] [flat]
* Postgresql TPS Bottleneck
@ 2022-03-31 11:50 wakandavision@outlook.com
0 siblings, 4 replies; 9+ messages in thread
From: wakandavision@outlook.com @ 2022-03-31 11:50 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi everyone,
I am a bachelor's student and writing my thesis about the scaling and
performance of an application. The application is using postgresql as a
database but we can't scale any further currently as it seems postgres
is hitting the limit.
With the application, as well as with pgbench, we don't get more than
(max) 70k TPS on postgres. But the servers' resources are not utilized
completely (more below).
I've tried many different configurations but none of them had any major
performance impact (unless fsync and synchronous_commit = off).
This is the (custom) configuration I am using:
shared_buffers=65551953kB
effective_cache_size=147491895kB
huge_pages=on
min_wal_size=20GB
max_wal_size=200GB
wal_buffers=1GB
max_wal_senders=0
archive_mode=off
wal_level=minimal
work_mem=2GB
maintenance_work_mem=4GB
checkpoint_completion_target=0.9
checkpoint_timeout = 30min
random_page_cost=1.1
bgwriter_flush_after = 2MB
effective_io_concurrency = 200
# Disabled just for performance experiments
fsync = off
synchronous_commit = off
full_page_writes = on
max_worker_processes=64
max_parallel_workers=64
max_parallel_workers_per_gather=10
max_parallel_maintenance_workers=12
The system is as follows:
* 64 Cores (Intel Xeon Gold 6130 (Skylake, 2.10GHz, 2 CPUs/node, 16
cores/CPU))
* 192 GiB RAM (12 * 16GiB DIMM DDR4 Synchronous Registered (Buffered)
2666 MHz (0.4 ns))
* 2 * SSD SATA Samsung MZ7KM240HMHQ0D3 (one is used for the WAL and the
other for the data)
* 10 Gbps network link
* OS: Debian 11
* Postgres 13 from apt
(I've also written a stackoverflow post about it -
https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o )
[https://cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon@2.png?v=73d79a89bded]<https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o;
performance - Postgresql bottleneck neither CPU, network nor I/O - Stack Overflow<https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o;
We are testing our application for performance, which is using Postgresql 13 as a database. It is very insert and update heavy and we cannot get more than 65k TPS on the database. But none of the m...
stackoverflow.com
Below is just an example of the pgbench I ran:
pgbench -i -s 50 -U postgres -h <DB_HOST> -d <DB_NAME>
pgbench -c 64 -j 32 -t 100000 -h <DB_HOST> -U postgres <DB_NAME>
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 64
number of threads: 32
number of transactions per client: 100000
number of transactions actually processed: 6400000/6400000
latency average = 0.976 ms
tps = 65584.664360 (including connections establishing)
tps = 65594.330678 (excluding connections establishing)
As comparison (average of three runs with pgbench as above):
num clients default config custom config above
10 11336 16848
20 19528 30187
30 25769 39430
40 29792 50150
50 31096 60133
60 33900 64916
70 34986 64308
80 34170 63075
90 35108 59910
100 34864 58320
120 35124 55414
140 33403 53610
(with fsync=off alone I almost get the TPS from the right already)
For `-S -M prepared` the TPS is ~700k and for `-S` ~500k but as the
application is very write heavy this is not really useful for me.
With the app the CPU is only at 25% load and the disks are also no
problem. For pgbench its about 75% CPU but still no disk bottleneck
(about 5%).
There are also Grafana snapshots I created for the system (node-
exporter) and postgres (prometheus-postgres-exporter) while running
with our application (same configuration as above). Both do not show
any kind of bottleneck (except high amounts context switches and pages
in/out)
node: https://147.87.255.221:3000/dashboard/snapshot/3eXe1sS3QDL6cbvI7HkPjYnjrVLCNOOF
postgres: https://147.87.255.221:3000/dashboard/snapshot/wHkRphdr3D4k5kRckhn57Pc6ZD3st1x7
I have also looked at postgresql's lock tables while running the above
experiment, but there is nothing which seemed strange to me. There are
about 300 locks but all are granted (select * from pg_locks).
Also, the following query:
select wait_event, count(*) from pg_stat_activity where state='idle in
transaction' group by wait_event;
did not show some contention there the output looks always similar to
this (80 clients):
wait_event | count
--------------------------+-------
ClientRead | 2
SerializableFinishedList | 1
Thanks to the slack channel I got a link to edb which used a more
powerful server and they achieved also about 70k TPS but did not set
fsync=off. So maybe they were limited by disk IO (just guessing, as
unfortunately, it is not pointed out in the post).
https://www.enterprisedb.com/blog/pgbench-performance-benchmark-postgresql-12-and-edb-advanced-serve...
So, my question is if anyone knows what could be the bottleneck, or if
it is even possible to get more TPS in this write-heavy load.
(dmesg does also not contain error messages which would point to a
kernel misconfiguration)
Optimally I would like to fully use the CPU and get about 3-4 times
more TPS (if even possible).
Thanks already for everyone's time and help.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql TPS Bottleneck
@ 2022-03-31 14:18 Guillaume Cottenceau <gc@mnc.ch>
parent: wakandavision@outlook.com
3 siblings, 0 replies; 9+ messages in thread
From: Guillaume Cottenceau @ 2022-03-31 14:18 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
<wakandavision 'at' outlook.com> writes:
> Optimally I would like to fully use the CPU and get about 3-4 times
> more TPS (if even possible).
Disclaimer: I'm really not a pg performance expert.
I don't understand your hope to fully use the CPU; if your
scenario is disk-limited, which may very well be the case, then
of course you cannot fully use the CPU. With synchronous commits
and fsync, the system is probably spending time just waiting for
the disks to report the writes completion. Are iostat/vmstat
showing a lot of IO-wait?
Also, if you can live with a few lost transactions in case of
server crash, synchronous_commit=off is very ok and provides a
lot of performance gain.
--
Guillaume Cottenceau
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql TPS Bottleneck
@ 2022-03-31 18:55 MichaelDBA <MichaelDBA@sqlexec.com>
parent: wakandavision@outlook.com
3 siblings, 0 replies; 9+ messages in thread
From: MichaelDBA @ 2022-03-31 18:55 UTC (permalink / raw)
To: wakandavision@outlook.com; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
While setting these 2 parameters to off will make things go faster
(especially for fsync), it is unrealistic to have these settings in a
production environment, especiall fsync=off. You might get by with
synchronous_commit=off, but with fsync=off you could end up with
corruption in your database. synchronous_commit may not make anything
go faster just change where the time is being spent.
Regards,
Michael Vitale
wakandavision@outlook.com wrote on 3/31/2022 7:50 AM:
> fsync = off
> synchronous_commit = off
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql TPS Bottleneck
@ 2022-03-31 19:16 Tomas Vondra <tomas.vondra@enterprisedb.com>
parent: wakandavision@outlook.com
3 siblings, 1 reply; 9+ messages in thread
From: Tomas Vondra @ 2022-03-31 19:16 UTC (permalink / raw)
To: wakandavision@outlook.com, "pgsql-performance@lists.postgresql.org" <pgsql-performance@lists.postgresql.org>
On 3/31/22 13:50, wakandavision@outlook.com wrote:
> Hi everyone,
>
> I am a bachelor's student and writing my thesis about the scaling and
> performance of an application. The application is using postgresql as a
> database but we can't scale any further currently as it seems postgres
> is hitting the limit.
>
> With the application, as well as with pgbench, we don't get more than
> (max) 70k TPS on postgres. But the servers' resources are not utilized
> completely (more below).
>
> I've tried many different configurations but none of them had any major
> performance impact (unless fsync and synchronous_commit = off).
>
> This is the (custom) configuration I am using:
>
> shared_buffers=65551953kB
> effective_cache_size=147491895kB
> huge_pages=on
> min_wal_size=20GB
> max_wal_size=200GB
> wal_buffers=1GB
> max_wal_senders=0
> archive_mode=off
> wal_level=minimal
> work_mem=2GB
> maintenance_work_mem=4GB
> checkpoint_completion_target=0.9
> checkpoint_timeout = 30min
> random_page_cost=1.1
> bgwriter_flush_after = 2MB
> effective_io_concurrency = 200
> # Disabled just for performance experiments
> fsync = off
> synchronous_commit = off
> full_page_writes = on
> max_worker_processes=64
> max_parallel_workers=64
> max_parallel_workers_per_gather=10
> max_parallel_maintenance_workers=12
>
> The system is as follows:
>
> * 64 Cores (Intel Xeon Gold 6130 (Skylake, 2.10GHz, 2 CPUs/node, 16
> cores/CPU))
> * 192 GiB RAM (12 * 16GiB DIMM DDR4 Synchronous Registered (Buffered)
> 2666 MHz (0.4 ns))
> * 2 * SSD SATA Samsung MZ7KM240HMHQ0D3 (one is used for the WAL and the
> other for the data)
> * 10 Gbps network link
> * OS: Debian 11
> * Postgres 13 from apt
>
> (I've also written a stackoverflow post about it -
> https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o
> <https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o;
> )
> <https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o;
>
> performance - Postgresql bottleneck neither CPU, network nor I/O - Stack
> Overflow
> <https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o;
> We are testing our application for performance, which is using
> Postgresql 13 as a database. It is very insert and update heavy and we
> cannot get more than 65k TPS on the database. But none of the m...
> stackoverflow.com
>
>
>
> Below is just an example of the pgbench I ran:
>
> pgbench -i -s 50 -U postgres -h <DB_HOST> -d <DB_NAME>
> pgbench -c 64 -j 32 -t 100000 -h <DB_HOST> -U postgres <DB_NAME>
>
I'd bet you need to use "pgbench -N" because the regular transaction
updates the "branch" table, and you only have 50 branches. Which
probably means a lot of conflicts and locking. The other thing you might
try is "-M prepared" which saves time on query planning.
FWIW I really doubt "fsync=off" will give you any meaningful results.
Maybe try assessing the hardware capability first, using tools like fio
to measure IOPS with different workloads.
Then try pgbench with a single client, and finally increase the number
of clients and see how it behaves and compare it to what you expect.
In any case, every system has a bottleneck. You're clearly hitting one,
otherwise the numbers would go faster. Usually, it's either CPU bound,
in which case "perf top" might tell us more, or it's IO bound, in which
case try e.g. "iostat -x -k 1" or something.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql TPS Bottleneck
@ 2022-04-01 02:40 Mladen Gogala <gogala.mladen@gmail.com>
parent: wakandavision@outlook.com
3 siblings, 0 replies; 9+ messages in thread
From: Mladen Gogala @ 2022-04-01 02:40 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On 3/31/22 07:50, wakandavision@outlook.com wrote:
> Hi everyone,
>
> I am a bachelor's student and writing my thesis about the scaling and
> performance of an application. The application is using postgresql as a
> database but we can't scale any further currently as it seems postgres
> is hitting the limit.
>
> With the application, as well as with pgbench, we don't get more than
> (max) 70k TPS on postgres. But the servers' resources are not utilized
> completely (more below).
I would try monitoring using "perf top" and "atop -d" to see what is
going on on the system. Also, try using sar to figure out what's going
on. Are you paging, waiting for I/O or having some other kind of
bottleneck. Once you figure where is your system spending time, you can
address the problem. In addition to that, analyze the log files with
pgbadger to find out which queries are time consuming and try optimizing
them.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql TPS Bottleneck
@ 2022-04-20 06:49 wakandavision@outlook.com
parent: Tomas Vondra <tomas.vondra@enterprisedb.com>
0 siblings, 1 reply; 9+ messages in thread
From: wakandavision@outlook.com @ 2022-04-20 06:49 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@enterprisedb.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi, thanks for your answer.
We have a Grafana instance monitoring all those metrics, no one I asked so far could identify an obvious bottleneck.
However, I have done further experiments to see if we are missing something.
While running the benchmark with our application I've run tools on the
DB node to smoke up the resources. These were cpuburn, iperf and fio.
While cpuburn did result in a small drop of Postgres TPS it was nothing
which was not expected. However, iperf and fio did not have any impact
at all (except iperf when more than our 10Gbps are sent - clearly). The
disks were utilized 100% but Postgres stayed at about 65k TPS.
The next thing I did was starting two independent Postgres instances on
the same server and run independent client applications against each of
them. This resulted in our application getting almost double of the TPS
compared to running a single instance (from 13k to 23k) - Each Postgres
instance had about 45k TPS which did not increase (?).
I think what's also interesting is that our DB server has the TPS peak
when using about 80 clients (more results in the TPS going down again),
while when I search the internet most benchmarks peak at about 400-600
clients.
Does anyone have an idea what might be the problem?
Maybe I am missing a kernel/Postgres configuration parameter?
________________________________
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Sent: Thursday, March 31, 2022 9:16 PM
To: wakandavision@outlook.com <wakandavision@outlook.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgresql TPS Bottleneck
On 3/31/22 13:50, wakandavision@outlook.com wrote:
> Hi everyone,
>
> I am a bachelor's student and writing my thesis about the scaling and
> performance of an application. The application is using postgresql as a
> database but we can't scale any further currently as it seems postgres
> is hitting the limit.
>
> With the application, as well as with pgbench, we don't get more than
> (max) 70k TPS on postgres. But the servers' resources are not utilized
> completely (more below).
>
> I've tried many different configurations but none of them had any major
> performance impact (unless fsync and synchronous_commit = off).
>
> This is the (custom) configuration I am using:
>
> shared_buffers=65551953kB
> effective_cache_size=147491895kB
> huge_pages=on
> min_wal_size=20GB
> max_wal_size=200GB
> wal_buffers=1GB
> max_wal_senders=0
> archive_mode=off
> wal_level=minimal
> work_mem=2GB
> maintenance_work_mem=4GB
> checkpoint_completion_target=0.9
> checkpoint_timeout = 30min
> random_page_cost=1.1
> bgwriter_flush_after = 2MB
> effective_io_concurrency = 200
> # Disabled just for performance experiments
> fsync = off
> synchronous_commit = off
> full_page_writes = on
> max_worker_processes=64
> max_parallel_workers=64
> max_parallel_workers_per_gather=10
> max_parallel_maintenance_workers=12
>
> The system is as follows:
>
> * 64 Cores (Intel Xeon Gold 6130 (Skylake, 2.10GHz, 2 CPUs/node, 16
> cores/CPU))
> * 192 GiB RAM (12 * 16GiB DIMM DDR4 Synchronous Registered (Buffered)
> 2666 MHz (0.4 ns))
> * 2 * SSD SATA Samsung MZ7KM240HMHQ0D3 (one is used for the WAL and the
> other for the data)
> * 10 Gbps network link
> * OS: Debian 11
> * Postgres 13 from apt
>
> (I've also written a stackoverflow post about it -
> https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o
> <https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o;
> )
> <https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o;
>
> performance - Postgresql bottleneck neither CPU, network nor I/O - Stack
> Overflow
> <https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o;
> We are testing our application for performance, which is using
> Postgresql 13 as a database. It is very insert and update heavy and we
> cannot get more than 65k TPS on the database. But none of the m...
> stackoverflow.com
>
>
>
> Below is just an example of the pgbench I ran:
>
> pgbench -i -s 50 -U postgres -h <DB_HOST> -d <DB_NAME>
> pgbench -c 64 -j 32 -t 100000 -h <DB_HOST> -U postgres <DB_NAME>
>
I'd bet you need to use "pgbench -N" because the regular transaction
updates the "branch" table, and you only have 50 branches. Which
probably means a lot of conflicts and locking. The other thing you might
try is "-M prepared" which saves time on query planning.
FWIW I really doubt "fsync=off" will give you any meaningful results.
Maybe try assessing the hardware capability first, using tools like fio
to measure IOPS with different workloads.
Then try pgbench with a single client, and finally increase the number
of clients and see how it behaves and compare it to what you expect.
In any case, every system has a bottleneck. You're clearly hitting one,
otherwise the numbers would go faster. Usually, it's either CPU bound,
in which case "perf top" might tell us more, or it's IO bound, in which
case try e.g. "iostat -x -k 1" or something.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql TPS Bottleneck
@ 2022-04-20 15:49 Jeff Janes <jeff.janes@gmail.com>
parent: wakandavision@outlook.com
0 siblings, 1 reply; 9+ messages in thread
From: Jeff Janes @ 2022-04-20 15:49 UTC (permalink / raw)
To: wakandavision@outlook.com; +Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Wed, Apr 20, 2022 at 5:13 AM <wakandavision@outlook.com> wrote:
>
> The next thing I did was starting two independent Postgres instances on
> the same server and run independent client applications against each of
> them. This resulted in our application getting almost double of the TPS
> compared to running a single instance (from 13k to 23k) - Each Postgres
> instance had about 45k TPS which did not increase (?).
>
How could that be? Isn't there a one to one correspondence between app
progress and PostgreSQL transactions? How could one almost double while
the other did not increase? Anyway, 2x45 does seem like an increase
(smallish) over 65.
Your bottleneck for pgbench may be IPC/context switches. I noticed that -S
did about 7 times more than the default, and it only makes one round trip
to the database while the default makes 7.
You could package up the different queries made by the default transaction
into one function call, in order to do the same thing but with fewer round
trips to the database. This would be an easy way to see if my theory is
true. If it is, I don't know what that would mean for your app though, as
we know nothing about its structure.
I have a patch handy (attached) which implements this feature as the
builtin transaction "-b tpcb-func". If you don't want to recompile
pgbench, you could dissect the patch to reimplement the same thing as a -f
style transaction instead.
Note that packaging it up this way does violate the spirit of the
benchmark, as clearly someone is supposed to look at the results of the
first select before deciding to proceed with the rest of the transaction.
But you don't seem very interested in the spirit of the tpc-b benchmark,
just in using it as a tool to track down a bottleneck.
Cheers,
Jeff
Attachments:
[application/octet-stream] pgbench_function_v13.patch (8.2K, ../../CAMkU=1yAgvL2sgzK0v2Hc4R-c7ZD3GqY3ntWnoOwsvXv1VKvXg@mail.gmail.com/3-pgbench_function_v13.patch)
download | inline diff:
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 0c60077e1f..cdf07ae5ed 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -183,7 +183,7 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<replaceable>init_steps</replaceable> specifies the
initialization steps to be performed, using one character per step.
Each step is invoked in the specified order.
- The default is <literal>dtgvp</literal>.
+ The default is <literal>dtgvpn</literal>.
The available steps are:
<variablelist>
@@ -256,7 +256,7 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>f</literal> (create Foreign keys)</term>
+ <term><literal>f</literal> (create Foreign keys)</term>
<listitem>
<para>
Create foreign key constraints between the standard tables.
@@ -264,6 +264,15 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>n</literal> (create fuNctioN)</term>
+ <listitem>
+ <para>
+ Create the function versions of the <literal>tpcb-like</literal> transaction,
+ for use with the <literal>tpcb-func</literal> and <literal>tpcb-proc</literal> built-ins.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist></para>
</listitem>
</varlistentry>
@@ -410,8 +419,8 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<listitem>
<para>
Add the specified built-in script to the list of scripts to be executed.
- Available built-in scripts are: <literal>tpcb-like</literal>,
- <literal>simple-update</literal> and <literal>select-only</literal>.
+ Available built-in scripts are: <literal>tpcb-like</literal>, <literal>tpcb-func</literal>,
+ <literal>tpcb-proc</literal>, <literal>simple-update</literal> and <literal>select-only</literal>.
Unambiguous prefixes of built-in names are accepted.
With the special name <literal>list</literal>, show the list of built-in scripts
and exit immediately.
@@ -975,6 +984,19 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<listitem><para><literal>END;</literal></para></listitem>
</orderedlist>
+ <para>
+ If you select the <literal>tpcb-func</literal> built-in,
+ the above steps are carried out by a single call to a <application>PL/pgSQL</application> function,
+ reducing the overhead from inter-process-communication.
+ </para>
+
+ <para>
+ If you select the <literal>tpcb-proc</literal> built-in,
+ the above steps are executed for many iterations iterations in separate transactions by a single call to
+ a <application>PL/pgSQL</application> procedure,
+ reducing the overhead from inter-process-communication even further. Requires a PostgreSQL server of version 11 or better.
+ </para>
+
<para>
If you select the <literal>simple-update</literal> built-in (also <option>-N</option>),
steps 4 and 5 aren't included in the transaction.
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 364b5a2e47..0c043deabe 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -168,8 +168,8 @@ typedef struct socket_set
/********************************************************************
* some configurable parameters */
-#define DEFAULT_INIT_STEPS "dtgvp" /* default -I setting */
-#define ALL_INIT_STEPS "dtgGvpf" /* all possible steps */
+#define DEFAULT_INIT_STEPS "dtgvpn" /* default -I setting */
+#define ALL_INIT_STEPS "dtgGvpfn" /* all possible steps */
#define LOG_STEP_SECONDS 5 /* seconds between log messages */
#define DEFAULT_NXACTS 10 /* default nxacts */
@@ -625,6 +625,21 @@ static const BuiltinScript builtin_script[] =
"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
"END;\n"
},
+ {
+ "tpcb-func",
+ "<builtin: TPC-B (sort of) in PL/pgSQL>",
+ "\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
+ "\\set bid random(1, " CppAsString2(nbranches) " * :scale)\n"
+ "\\set tid random(1, " CppAsString2(ntellers) " * :scale)\n"
+ "\\set delta random(-5000, 5000)\n"
+ "select * from pgbench_transaction(:aid, :bid, :tid, :delta);\n"
+ },
+ {
+ "tpcb-proc",
+ "<builtin: TPC-B (sort of) in PL/pgSQL Procedure>",
+ "CALL pgbench_proc("CppAsString2(naccounts) " * :scale, " CppAsString2(ntellers)
+ " * :scale, " CppAsString2(nbranches) " * :scale, 100);\n"
+ },
{
"simple-update",
"<builtin: simple update>",
@@ -4375,6 +4390,59 @@ initCreateFKeys(PGconn *con)
}
}
+/*
+ * Create PL/pgSQL function with standard transaction
+ */
+static void
+initCreateFunctions(PGconn *con)
+{
+ static const char *const DDLFUNCs[] = {
+ "create or replace function pgbench_transaction(arg_aid int, arg_bid int, arg_tid int, arg_delta int) returns int as $$"
+ "DECLARE\n"
+ "abal int;\n"
+ "BEGIN\n"
+ "UPDATE pgbench_accounts SET abalance = abalance + arg_delta WHERE aid = arg_aid;\n"
+ "SELECT abalance into abal FROM pgbench_accounts WHERE aid = arg_aid;\n"
+ "UPDATE pgbench_tellers SET tbalance = tbalance + arg_delta WHERE tid = arg_tid;\n"
+ "UPDATE pgbench_branches SET bbalance = bbalance + arg_delta WHERE bid = arg_bid;\n"
+ "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (arg_tid, arg_bid, arg_aid, arg_delta, CURRENT_TIMESTAMP);\n"
+ "RETURN abal;\n"
+ "END;\n"
+ "$$ language plpgsql",
+ "CREATE OR REPLACE PROCEDURE public.pgbench_proc(naccounts integer, ntellers integer, nbranches integer, loops integer)\n"
+ " LANGUAGE plpgsql\n"
+ "AS $procedure$ \n"
+ "DECLARE\n"
+ " account_id int; \n"
+ " branch_id int; \n"
+ " teller_id int; \n"
+ " d int;\n"
+ " abal int; \n"
+ "BEGIN\n"
+ " FOR x IN 1 .. loops LOOP\n"
+ " account_id = 1+floor(random()*naccounts); \n"
+ " branch_id = 1+floor(random()*nbranches); \n"
+ " teller_id = 1+floor(random()*ntellers); \n"
+ " d = floor(random()*10000-5000);\n"
+ " UPDATE pgbench_accounts SET abalance = abalance + d WHERE aid = account_id;\n"
+ " SELECT abalance into abal FROM pgbench_accounts WHERE aid = account_id;\n"
+ " UPDATE pgbench_tellers SET tbalance = tbalance + d WHERE tid = teller_id;\n"
+ " UPDATE pgbench_branches SET bbalance = bbalance + d WHERE bid = branch_id;\n"
+ " INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (teller_id, branch_id, account_id, d, CURRENT_TIMESTAMP);\n"
+ " COMMIT;\n"
+ " END LOOP;\n"
+ "END\n"
+ "$procedure$\n"
+ };
+ int i;
+
+ fprintf(stderr, "creating PL/pgSQL function...\n");
+ for (i = 0; i < lengthof(DDLFUNCs); i++)
+ {
+ executeStatement(con, DDLFUNCs[i]);
+ }
+}
+
/*
* Validate an initialization-steps string
*
@@ -4457,6 +4525,9 @@ runInitSteps(const char *initialize_steps)
op = "foreign keys";
initCreateFKeys(con);
break;
+ case 'n':
+ initCreateFunctions(con);
+ break;
case ' ':
break; /* ignore */
default:
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 3aa9d5d753..41c76a9ef2 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -152,7 +152,7 @@ pgbench(
# Run all builtin scripts, for a few transactions each
pgbench(
- '--transactions=5 -Dfoo=bla --client=2 --protocol=simple --builtin=t'
+ '--transactions=5 -Dfoo=bla --client=2 --protocol=simple --builtin=tpcb-l'
. ' --connect -n -v -n',
0,
[
diff --git a/src/bin/pgbench/t/002_pgbench_no_server.pl b/src/bin/pgbench/t/002_pgbench_no_server.pl
index 346a2667fc..e68f4a627a 100644
--- a/src/bin/pgbench/t/002_pgbench_no_server.pl
+++ b/src/bin/pgbench/t/002_pgbench_no_server.pl
@@ -131,7 +131,7 @@ my @options = (
],
[
'weight zero',
- '-b se@0 -b si@0 -b tpcb@0',
+ '-b se@0 -b si@0 -b tpcb-l@0',
[qr{weight must not be zero}]
],
[ 'init vs run', '-i -S', [qr{cannot be used in initialization}] ],
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql TPS Bottleneck
@ 2022-04-20 16:35 wakandavision@outlook.com
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: wakandavision@outlook.com @ 2022-04-20 16:35 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Clearly, I have only supplied half of the information there. I'm really sorry about that. The TPS measurement of the application does in no way correspond to the TPS of Postgres.
They are measured completely different but it's the measure we actually are interested in - as we want to assess the scalability of the application.
What I wanted to show is that the server we are hosting Postgres on is not bottlenecked (in an obvious way), as running two instances in parallel on the same server gives us almost double
the performance in our application and double the resource usage on the DB server. But what actually is strange(?), is that the TPS of Postgres does not change much, i.e. it's just 'distributed' to the two instances.
It would seem like our application could not handle more throughput, but I did the same with three instances, where we stayed again with 'only' double the performance and the TPS of Postgres distributed to three instances
(each client application running on an independent node).
I'm really getting frustrated here as I (and no one I asked yet) has an explanation for this behavior.
________________________________
From: Jeff Janes <jeff.janes@gmail.com>
Sent: Wednesday, April 20, 2022 5:49 PM
To: wakandavision@outlook.com <wakandavision@outlook.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgresql TPS Bottleneck
On Wed, Apr 20, 2022 at 5:13 AM <wakandavision@outlook.com<mailto:wakandavision@outlook.com>> wrote:
The next thing I did was starting two independent Postgres instances on
the same server and run independent client applications against each of
them. This resulted in our application getting almost double of the TPS
compared to running a single instance (from 13k to 23k) - Each Postgres
instance had about 45k TPS which did not increase (?).
How could that be? Isn't there a one to one correspondence between app progress and PostgreSQL transactions? How could one almost double while the other did not increase? Anyway, 2x45 does seem like an increase (smallish) over 65.
Your bottleneck for pgbench may be IPC/context switches. I noticed that -S did about 7 times more than the default, and it only makes one round trip to the database while the default makes 7.
You could package up the different queries made by the default transaction into one function call, in order to do the same thing but with fewer round trips to the database. This would be an easy way to see if my theory is true. If it is, I don't know what that would mean for your app though, as we know nothing about its structure.
I have a patch handy (attached) which implements this feature as the builtin transaction "-b tpcb-func". If you don't want to recompile pgbench, you could dissect the patch to reimplement the same thing as a -f style transaction instead.
Note that packaging it up this way does violate the spirit of the benchmark, as clearly someone is supposed to look at the results of the first select before deciding to proceed with the rest of the transaction. But you don't seem very interested in the spirit of the tpc-b benchmark, just in using it as a tool to track down a bottleneck.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql TPS Bottleneck
@ 2022-04-20 18:18 Benedict Holland <benedict.m.holland@gmail.com>
parent: wakandavision@outlook.com
0 siblings, 0 replies; 9+ messages in thread
From: Benedict Holland @ 2022-04-20 18:18 UTC (permalink / raw)
To: wakandavision@outlook.com; +Cc: Jeff Janes <jeff.janes@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Ypu wouldn't get an increasing running two instances on the same server.
Distributed database severs is a complex application and tuning it will
depend on storage and CPU capacity. It could be as simple as a bus. Are you
running this locally or on the cloud? Are you running this on a distributed
file system or across a network? There are a dozen different reasons why a
database would not be using 100% of capacity from indexing to disk or bus
bound or network bound.
Thanks,
Ben
On Wed, Apr 20, 2022, 1:27 PM <wakandavision@outlook.com> wrote:
> Clearly, I have only supplied half of the information there. I'm really
> sorry about that. The TPS measurement of the application does in no way
> correspond to the TPS of Postgres.
> They are measured completely different but it's the measure we actually
> are interested in - as we want to assess the scalability of the
> application.
>
> What I wanted to show is that the server we are hosting Postgres on is not
> bottlenecked (in an obvious way), as running two instances in parallel on
> the same server gives us almost double
> the performance in our application and double the resource usage on the DB
> server. But what actually is strange(?), is that the TPS of Postgres does
> not change much, i.e. it's just 'distributed' to the two instances.
>
> It would seem like our application could not handle more throughput, but I
> did the same with three instances, where we stayed again with 'only' double
> the performance and the TPS of Postgres distributed to three instances
> (each client application running on an independent node).
>
> I'm really getting frustrated here as I (and no one I asked yet) has an
> explanation for this behavior.
> ------------------------------
> *From:* Jeff Janes <jeff.janes@gmail.com>
> *Sent:* Wednesday, April 20, 2022 5:49 PM
> *To:* wakandavision@outlook.com <wakandavision@outlook.com>
> *Cc:* Tomas Vondra <tomas.vondra@enterprisedb.com>;
> pgsql-performance@lists.postgresql.org <
> pgsql-performance@lists.postgresql.org>
> *Subject:* Re: Postgresql TPS Bottleneck
>
> On Wed, Apr 20, 2022 at 5:13 AM <wakandavision@outlook.com> wrote:
>
>
> The next thing I did was starting two independent Postgres instances on
> the same server and run independent client applications against each of
> them. This resulted in our application getting almost double of the TPS
> compared to running a single instance (from 13k to 23k) - Each Postgres
> instance had about 45k TPS which did not increase (?).
>
>
> How could that be? Isn't there a one to one correspondence between app
> progress and PostgreSQL transactions? How could one almost double while
> the other did not increase? Anyway, 2x45 does seem like an increase
> (smallish) over 65.
>
> Your bottleneck for pgbench may be IPC/context switches. I noticed that
> -S did about 7 times more than the default, and it only makes one round
> trip to the database while the default makes 7.
>
> You could package up the different queries made by the default transaction
> into one function call, in order to do the same thing but with fewer round
> trips to the database. This would be an easy way to see if my theory is
> true. If it is, I don't know what that would mean for your app though, as
> we know nothing about its structure.
>
> I have a patch handy (attached) which implements this feature as the
> builtin transaction "-b tpcb-func". If you don't want to recompile
> pgbench, you could dissect the patch to reimplement the same thing as a -f
> style transaction instead.
>
> Note that packaging it up this way does violate the spirit of the
> benchmark, as clearly someone is supposed to look at the results of the
> first select before deciding to proceed with the rest of the transaction.
> But you don't seem very interested in the spirit of the tpc-b benchmark,
> just in using it as a tool to track down a bottleneck.
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2022-04-20 18:18 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-31 11:50 Postgresql TPS Bottleneck wakandavision@outlook.com
2022-03-31 14:18 ` Guillaume Cottenceau <gc@mnc.ch>
2022-03-31 18:55 ` MichaelDBA <MichaelDBA@sqlexec.com>
2022-03-31 19:16 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2022-04-20 06:49 ` wakandavision@outlook.com
2022-04-20 15:49 ` Jeff Janes <jeff.janes@gmail.com>
2022-04-20 16:35 ` wakandavision@outlook.com
2022-04-20 18:18 ` Benedict Holland <benedict.m.holland@gmail.com>
2022-04-01 02:40 ` Mladen Gogala <gogala.mladen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox