pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLocks
7+ messages / 6 participants
[nested] [flat]
* Locks
@ 2003-10-15 12:45 Greg Spiegelberg <gspiegelberg@cranel.com>
0 siblings, 0 replies; 7+ messages in thread
From: Greg Spiegelberg @ 2003-10-15 12:45 UTC (permalink / raw)
To: pgsql-admin
Is there a good all-inclusive resource out there that documents
LOCK in PostgreSQL, any side-effects when used, and how UPDATE,
DELETE, cursors, transactions, and so on use them?
Thanks,
Greg
--
Greg Spiegelberg
Sr. Product Development Engineer
Cranel, Incorporated.
Phone: 614.318.4314
Fax: 614.431.8388
Email: gspiegelberg@Cranel.com
Cranel. Technology. Integrity. Focus.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Locks
@ 2020-11-16 17:50 Yambu <hyambu@gmail.com>
2020-11-16 17:59 ` Re: Locks David G. Johnston <david.g.johnston@gmail.com>
2020-11-16 18:06 ` RE: Locks Alvaro Aguayo <aaguayo@opensysperu.com>
2020-11-16 18:52 ` Re: Locks Keith Fiske <keith.fiske@crunchydata.com>
0 siblings, 3 replies; 7+ messages in thread
From: Yambu @ 2020-11-16 17:50 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi
How can i find out when a lock happened?
Im writing a function that will check why some queries ran slowly at
particular times, so i want to know if i can store lock information
somewhere and retrieve what time the lock happened?
regards
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locks
2020-11-16 17:50 Locks Yambu <hyambu@gmail.com>
@ 2020-11-16 17:59 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2020-11-16 17:59 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Monday, November 16, 2020, Yambu <hyambu@gmail.com> wrote:
> Hi
> How can i find out when a lock happened?
> Im writing a function that will check why some queries ran slowly at
> particular times, so i want to know if i can store lock information
> somewhere and retrieve what time the lock happened?
>
No. The cost/benefit of such a capability makes it seem undesirable.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Locks
2020-11-16 17:50 Locks Yambu <hyambu@gmail.com>
@ 2020-11-16 18:06 ` Alvaro Aguayo <aaguayo@opensysperu.com>
2 siblings, 0 replies; 7+ messages in thread
From: Alvaro Aguayo @ 2020-11-16 18:06 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi.
If you mean currently held locks, there is some info in pg_locks & pg_stat_activity which may be helpful for you. Check the Wiki: https://wiki.postgresql.org/wiki/Lock_Monitoring
Regards,
Alvaro Aguayo
From: Yambu <hyambu@gmail.com>
Sent: 16 November 2020 12:51
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Locks
Hi
How can i find out when a lock happened?
Im writing a function that will check why some queries ran slowly at particular times, so i want to know if i can store lock information somewhere and retrieve what time the lock happened?
regards
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Locks
2020-11-16 17:50 Locks Yambu <hyambu@gmail.com>
@ 2020-11-16 18:52 ` Keith Fiske <keith.fiske@crunchydata.com>
2 siblings, 0 replies; 7+ messages in thread
From: Keith Fiske @ 2020-11-16 18:52 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Nov 16, 2020 at 12:50 PM Yambu <hyambu@gmail.com> wrote:
> Hi
> How can i find out when a lock happened?
> Im writing a function that will check why some queries ran slowly at
> particular times, so i want to know if i can store lock information
> somewhere and retrieve what time the lock happened?
> regards
>
That historical information is not kept in the database. However, you can
turn on logging of lock waits if they are longer than a given amount of
time. Just set it to your desired threshold in the postgresql.conf and
reload
https://www.postgresql.org/docs/13/runtime-config-logging.html#GUC-LOG-LOCK-WAITS
Note that if you set this very low, you can greatly increase the size of
your log files. So I'd recommend figuring out a threshold value where it's
actually a problem if the wait is that long.
Couple this with the log_min_duration_statement, which will also log
queries that have run longer than a given time, and you could then do some
log analysis to match up long running queries with associated locks. Again,
be cautious with setting this too low and making your logs too large to
manage.
https://www.postgresql.org/docs/13/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT
If you're looking for something to help with log analysis, I'd recommend
pgbadger. If you have the above to options turned on, it allows the reports
it generates to be very informative.
https://github.com/darold/pgbadger
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Locks
@ 2021-04-09 10:31 Yambu <hyambu@gmail.com>
2021-04-09 11:22 ` Re: Locks Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Yambu @ 2021-04-09 10:31 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello
I'm experiencing a lot of locks on my DB ,does CPU usage that stays around
96% cause locks on my DB server?
[image: image.png]
regards
Attachments:
image.png (image/png, 561.3K) download
view image
* Re: Locks
2021-04-09 10:31 Locks Yambu <hyambu@gmail.com>
@ 2021-04-09 11:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2021-04-09 11:22 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, 2021-04-09 at 12:31 +0200, Yambu wrote:
> I'm experiencing a lot of locks on my DB ,does CPU usage that stays around 96% cause locks on my DB server?
Use a connection pool with a way fewer connections on the backend.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-04-09 11:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-15 12:45 Locks Greg Spiegelberg <gspiegelberg@cranel.com>
2020-11-16 17:50 Locks Yambu <hyambu@gmail.com>
2020-11-16 17:59 ` David G. Johnston <david.g.johnston@gmail.com>
2020-11-16 18:06 ` Alvaro Aguayo <aaguayo@opensysperu.com>
2020-11-16 18:52 ` Keith Fiske <keith.fiske@crunchydata.com>
2021-04-09 10:31 Locks Yambu <hyambu@gmail.com>
2021-04-09 11:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox