postgresql - postgres transactions rate slows after a while

Question

I'm using postgres 12 on ubuntu 20.04 lts. I've noticed that after a while postgres transaction per seconds drops as per the screen shot. In order to test my application, I'm just running a set of API in batch. Batch works fine until certain number of iterations after that it slows downs and transaction rate drops suddenly. Only thing i've noticed out of ordinary is LWLock: WALWriteLock. My wal segment size is default 1GB. Database is running on SSD/xfs file system. I don't know if this is related to checkpoints or something else. Thank you for your time and looking into it.

#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

#wal_level = replica                    # minimal, replica, or logical
                                        # (change requires restart)
#fsync = on                             # flush data to disk for crash safety
                                        # (turning this off can cause
                                        # unrecoverable data corruption)
synchronous_commit = on                 # synchronization level;
                                        # off, local, remote_write, remote_apply, or on
#wal_sync_method = fsync                # the default is the first option
                                        # supported by the operating system:
                                        #   open_datasync
                                        #   fdatasync (default on Linux)
                                        #   fsync
                                        #   fsync_writethrough
                                        #   open_sync
full_page_writes = on                   # recover from partial page writes
#wal_compression = off                  # enable compression of full-page writes
#wal_log_hints = off                    # also do full page writes of non-critical updates
                                        # (change requires restart)
#wal_init_zero = on                     # zero-fill new WAL files
#wal_recycle = on                       # recycle WAL files
wal_buffers = 16MB                      # min 32kB, -1 sets based on shared_buffers
                                        # (change requires restart)
#wal_writer_delay = 200ms               # 1-10000 milliseconds
#wal_writer_flush_after = 1MB           # measured in pages, 0 disables

#commit_delay = 0                       # range 0-100000, in microseconds
#commit_siblings = 5                    # range 1-1000

# - Checkpoints -

#checkpoint_timeout = 5min              # range 30s-1d
max_wal_size = 1GB
min_wal_size = 512MB
checkpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB         # measured in pages, 0 disables
#checkpoint_warning = 30s               # 0 disables

标签： postgresqlwal