tags: - postgresql categories: - informational comments: true

date: 2022-04-10 00:00:00

DESCRIPTION

Monitor table locks by sending lock stats via collectd to riemann

ERRORS

VERIFICATION

COMMANDS

Create mock database and load data

Console 1

mgw clone /dbmon
cd dbmon
mgw testservice

Console 2

mkdir yugabyte
cd yugabyte
curl -O https://downloads.yugabyte.com/releases/2.11.2.0/yugabyte-2.11.2.0-b89-darwin-x86_64.tar.gz
tar xzf yugabyte-2.11.2.0-b89-darwin-x86_64.tar.gz yugabyte-2.11.2.0/share/
cd yugabyte-2.11.2.0

export PGPASSWORD="welcome"
psql -h 127.26.210.54 -d billing -U billing_owner -f share/schema.sql
psql -h 127.26.210.54 -d billing -U billing_owner -f share/products.sql
psql -h 127.26.210.54 -d billing -U billing_owner -f share/users.sql
psql -h 127.26.210.54 -d billing -U billing_owner -f share/orders.sql
psql -h 127.26.210.54 -d billing -U billing_owner -f share/reviews.sql

Create lock - update share lock

Console 3

psql -h 127.26.210.54 -d billing -U billing_owner

billing=>begin;
BEGIN
billing=> update orders set user_id = 112 where id = 5;
UPDATE 1
billing=>

Console 4

psql -h 127.26.210.54 -d billing -U billing_owner

billing=>begin;
BEGIN
billing=> update orders set user_id = 114 where id = 5;

Monitor script as database owner

AccessExclusiveLock AccessShareLock ExclusiveLock RowExclusiveLock RowShareLock ShareLock ShareRowExclusiveLock ShareUpdateExclusiveLock

Details from pg_locks table

References

https://docs.yugabyte.com/latest/explore/query-1-performance/pg-stat-activity/

https://download.yugabyte.com/#macos

https://stackoverflow.com/questions/22775150/how-to-simulate-deadlock-in-postgresql

https://wiki.postgresql.org/wiki/Lock_Monitoring