From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tim Slechta <trslechta@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Why are commits consuming most of the database time?
Date: Tue, 04 Apr 2023 10:57:55 -0400
Message-ID: <1509461.1680620275@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJVU3y2ydC15z4QOCC2U8fgxx==KP3Pko-f5Jz+WnzhPs53uTw@mail.gmail.com>
References: <CAJVU3y2ydC15z4QOCC2U8fgxx==KP3Pko-f5Jz+WnzhPs53uTw@mail.gmail.com>
Tim Slechta <trslechta@gmail.com> writes:
> The test data below is from a non-virtualized (client system and database
> server) Postgres 14 environment, with no replication, no high availability,
> and with no load balancing. This environment has older and slower disk
> drives, and the test is driven by a single client process.
> In this case 24% of the round trips (client to database and back) are for
> commit processing. However, commit processing is consuming 89% of the
> total database time. (All times are measured from within the client.)
You didn't say how big the transactions are, but if they're not writing
a lot of data apiece, this result seems totally non-surprising. The
commits have to push WAL log data down to disk before they can promise
that the transaction's results are durable, while the statements within
the transactions probably are not waiting for any disk writes at all.
If you don't need strict ACID compliance, you could turn off
synchronous_commit so that commits don't wait for WAL flush.
(This doesn't risk the consistency of your database, but it does
mean that a crash might lose the last few transactions that clients
were told got committed.)
If you do need strict ACID compliance, get a better disk subsystem.
Or, perhaps, just a better OS ... Windows is generally not thought of
as the best-performing platform for Postgres.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, trslechta@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Why are commits consuming most of the database time?
In-Reply-To: <1509461.1680620275@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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