Exclusive Locks on Insert into large Logging tables

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-----

Want faster answers from InfoTech? Check out www.portal.jims.net<http://www.portal.jims.net>

[Jim's Group]<http://jims.net>
Peter Neave | DevOps Lead | Peter(dot)Neave(at)jims(dot)net<mailto:Peter(dot)Neave(at)jims(dot)net>
________________________________
Jim's Group Pty Ltd
48 Edinburgh Rd | Mooroolbark | VIC, 3138
P 1300 130 490 | Intl +61 3 8419 2910

This email and any attachment(s) are confidential. If you are not the intended recipient you must not copy, use, disclose, distribute or rely on the information contained in it. If you have received this email in error, please notify the sender immediately by reply email and delete the email from your system. Confidentiality and legal privilege attached to this communication are not waived or lost by reason of mistaken delivery to you. While Jim's Group employs Anti-Virus Software, we cannot guarantee that this email or the attachment(s) are unaffected by computer virus, corruption or other defects and we recommend that this email and any attachments be tested before opening.

Please consider the environment before printing this email.

--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au