Exclusive Locks on Insert into large Logging tables - Mailing list pgsql-novice

Hi,

I’ve been trying to get down to the source of some locks. I’ve enabled log_loc_waits in the logs and I’ve had a script monitor the lock file and run a query to a file when it detects locks. From there, I’ve been able to determine that intermittently the largest tables in my database are have Exclusive Locks on them for up to 1-5 seconds (For example - LOG:  process 116665 acquired ExclusiveLock on extension of relation 59049887 of database 59049867 after 5838.015 ms).

I’m running PG10. The machine has 16GB of RAM.

The tables are mainly log tables . The queries that are waiting and those blocking are both insert statements.  The tables are 41GB, 34 GB and 33GB and contain many years of historical logs.

My question is, why would locking prevent an insertion. From what I’ve read (https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/) insert statements should not lock another insert.

Would partitioning the table help? Or would having a smaller record set mean fewer locks?

Thanks, Peter

--------------------------------------------------------------------------------

P.S. IF anyone else would like this approach, I’ve added the code here. The original select query is from the PGWatch2 project.

Enable in Postgres Config

log_lock_waits = on

-----BEGIN CODE - getlocks.sql-----

SELECT

    (extract(epoch from now()) * 1e9)::int8 AS epoch_ns,

    now()                      AS time_stamp,

    waiting.locktype           AS tag_waiting_locktype,

    waiting_stm.usename        AS tag_waiting_user,

    coalesce(waiting.mode, 'null'::text) AS tag_waiting_mode,

    coalesce(waiting.relation::regclass::text, 'null') AS tag_waiting_table,

    waiting_stm.query          AS waiting_query,

    waiting.pid                AS waiting_pid,

    other.locktype             AS other_locktype,

    other.relation::regclass   AS other_table,

    other_stm.query            AS other_query,

    other.mode                 AS other_mode,

    other.pid                  AS other_pid,

    other_stm.usename          AS other_user

FROM

    pg_catalog.pg_locks AS waiting

JOIN

    public.get_stat_activity() AS waiting_stm

    ON (

        waiting_stm.pid = waiting.pid

    )

JOIN

    pg_catalog.pg_locks AS other

    ON (

        (

            waiting."database" = other."database"

        AND waiting.relation  = other.relation

        )

        OR waiting.transactionid = other.transactionid

    )

JOIN

    public.get_stat_activity() AS other_stm

    ON (

        other_stm.pid = other.pid

    )

WHERE

    NOT waiting.GRANTED

AND

    waiting.pid <> other.pid

AND

    other.GRANTED

AND

    waiting_stm.datname = current_database();

-----END CODE-----

-----BEGIN CODE - monitor.sh-----

#!/usr/bin/env bash

query=$(cat getlocks.sql | sed -e "s/;$//")

psql -U postgres -d the_database -c "COPY ( $query ) TO STDOUT WITH CSV HEADER " --pset="footer=off" > locks.csv

tail -fn0 /var/log/postgresql/postgresql-10-main.log | \

  while read line ; do

        echo "$line" | grep --color -E '^.*LOG:.*\w*Lock\b.*$'

        if [ $? = 0 ]; then

          #echo Getting locks from database

          psql -U postgres -d the_database -c "COPY ( $query ) TO STDOUT WITH CSV" --pset="footer=off" >> locks.csv

        fi

done

-----END CODE-----