0

I have been noticing that sometimes we get a lot of open connections for a long time. Enabling enhanced metrics in RDS PostgreSQL I saw the following:

enter image description here

It seems this tuple wait event is making a lot of queries wait. I have taken a look and it seems it is from locks. I have run the query they suggest in the docs but it does not return anything, so not sure what is happening. In the Top sql queries you can see the following:

enter image description here

The queries are something like this (sorry for it being incomplete, RDS does not give me the full query):

UPDATE "customers_customer" SET "follow_up_date" = (CASE WHEN ("customers_customer"."id" = 140005033) THEN NULL WHEN ("customers_customer"."id" = 138501530) THEN NULL WHEN ("customers_customer"."id" = 140000447) THEN NULL WHEN ("customers_customer"."id" = 139999963) THEN NULL WHEN ("customers_customer"."id" = 140016560) THEN NULL WHEN ("customers_customer"."id" = 139998510) THEN NULL WHEN ("customers_customer"."id" = 138485030) THEN NULL WHEN ("customers_customer"."id" = 138507549) THEN NULL WHE

I'm not sure but I think this query comes from a mass update call on the Django ORM to a the customer model on the follow up date field.

So how can I debug this problem? Or mitigate it in the future? For now is not killing the database, but queries were starting to pile up and we have had problems with that in the past. If you need more information, please drop a comment below!

Improve this question
4
  • Did you try to run that lock monitoring query many times? Even if this lock does occur often, it could by chance not be occurring at all at the exact instant you ran the query.
    – jjanes
    Commented May 17, 2023 at 16:12
  • I have tried several times and the same empty result. With how much is happening (from the graph) I would guess that I should guess results way more frequently, but no idea to be honest.
    – Antonio Gamiz Delgado
    Commented May 17, 2023 at 16:18
  • I think I would start by figuring out what the full query is, and where it is coming from. Maybe you can increase the logging and get it from the logs, if there is not some other way.
    – jjanes
    Commented May 17, 2023 at 16:19
  • I would try querying pg_stat_activity and pg_locks separately. Maybe something has gone wrong with the join causing rows to disappear (but it works for me in my local install, so I don't what the problem might be on RDS)
    – jjanes
    Commented May 17, 2023 at 16:27

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.