Hi all,
I have a performance issue that I would really appreciate if somebody
could help me better understand and investigate. I experience
fluctuating performance of combined updates and inserts, seemingly
following a pattern which isn't immediately obvious.
In short I'm running PostgreSQL 14.1 on Linux on a small test machine
with 16 GB ram. Postgres is configured with shared_buffers = 4GB,
max_wal_size = 1GB.
The database contains a table with 14 columns and 100000 rows. The total
size of the table according to pg_total_relation_size is 20MB (so
basically nothing) and the table has no indexes, defaults or
constraints.
The table has one "before update, on each row"-trigger where the trigger
function does an insert in the table and then lets the update complete
by replacing NEW with OLD with one column modified. Each update
therefore becomes an insert immediately followed by an update.
There is only a single client which is written in Java and it runs on
the same machine as the database. It generates a reproducible load
consisting mainly of updates of two columns in single rows with a few
inserts mixed in. The inserts and updates are grouped together in
transactions of currently 20000 operations.
Inserts are always fast. As measured by an imprecise millisecond counter
they consistently take 0-1 ms.
Updates (that as mentioned above also cause an insert) are in phases
fast, 0-1 ms, and in phases mainly slow, about 10 ms. Performance starts
out fine, but then it seems that something happens that "flips a switch"
causing the updates to become slow for a while. A bit later they speed
up again, and the pattern repeats. When the updates are slow about 1 in
10 is fast, but it is highly irregular when that happens.
What puzzles me is that each time I run the test load against the table
it's always the exact same number of inserts/updates that happen in the
fast and slow phases. At first 56 pure inserts mixed with 1531 fast
updates, then 71 inserts and 606 slow updates, then 33 inserts and 471
fast updates etc. In other words, the time to do an update follows an
irregular square wave, where the "wavelength" (fast and slow phases) is
about 200-1500 updates. Apparently the flips between fast and slow keep
happening so there's no steady state.
The fact that the flips always happen after the same number of
inserts/updates makes me think that the underlying reason must be pretty
deterministic but there is no immediately discernible structure in the
load at the times when the update performance slows down. When it speeds
up again it is seemingly always at a point where at least 3 inserts are
executed right after each other, but that may be a coincidence.
Is there any feasible way to find out what it is that causes Postgres to
start doing slow updates? My guess would be a buffer filling up or
something similar, but the regularity between runs paired with the
irregular lengths of the fast and slow phases in each run doesn't really
seem to fit with this.
Best regards & thanks,
Mikkel Lauritsen