Fluctuating performance of updates on small table with trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux