Re: Why are commits consuming most of the database time?

Tom,

Thank you for your comments, they are very much appreciated.

You are correct that the transactions are typically short, likely with
dozens of rows.

Do you know of any problems or defects in this area?

Would there be any usefulness to generating Postgres log files?

Once again, thanks for your help.

-Tim

On Tue, Apr 4, 2023 at 10:55 AM Tim Slechta <trslechta(at)gmail(dot)com> wrote:

>
>
> On Tue, Apr 4, 2023 at 9:57 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Tim Slechta <trslechta(at)gmail(dot)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
>>
>