1

I have strange behavior from an Amazon RDS instance running PostgreSQL 9.6.11. This is new behavior from a system that has otherwise been behaving properly without any changes to code in the last few months. Additionally, we have a staging environment that the latest database and code changes were replicated to and it is not having these problems.

Nightly, thousands of jobs are run against the database server that consist mainly of very large SELECT queries (In the order of 8000 lines or so) that calculate statistics. Queries that typically take up to 1 minute, hang up, and do not complete, even after hours. However, if the server is rebooted to clear the queries, then I can run the same queries manually and they complete normally. The jobs will continue to process normally, and then eventually hang up again on stuck SELECT statements (usually 5 or 6, and it is not the same statements. They all vary slightly.).

I have done a lot of research and troubleshooting, but after determining that the query wasn't the problem, I'm at a loss.

The hung statements are all in active state and have been processing for hours. The server is pegged at 99%+ load.

What things should I look for? What problems could cause this issue? Why would a combination of large SELECT statements hang up? What commands can I run to get more information?

Improve this question

1 Answer 1

Reset to default
1

The most important information in addition to the connection state of the hung queries would be whether they are in any particular wait event.

You can get that information by running

SELECT state, wait_event_type, wait_event FROM pg_stat_activity

(you may need to collect this in a cron job to get the data over time)

Note that RDS also shows this information in “Performance Insights”, if you have it enabled.

Since you are on an older Postgres patch release, I would also investigate an upgrade of the minor version to see if that fixes the issue you are seeing.

Further reading: PostgreSQL docs: pg_stat_activity

Improve this answer
3
  • There isn't any wait_event that shows up. But thank you for the good information and well written answer.
    – Appleoddity
    Aug 26, 2019 at 2:48
  • Thats odd - it might be worth reaching out to AWS support. If you had direct access to the server I would recommend doing a gdb backtrace or using perf to find what keeps the database busy. In this case you can’t run it, but AWS should be able to run it for you.
    – Lukas Fittl
    Aug 26, 2019 at 2:53
  • After doing some more research, I see a strange behavior that may, or may not, be related to the issue. The hung queries have generated a number of "RowExclusiveLocks" in pg_locks. These are pure SELECT queries which should not be doing that. When I clear all the stuck queries, and then run one of the problematic queries manually the query completes as normal and only "AccessShareLocks" are generated on the database, which is expected. I'm currently testing an upgrade to v 9.6.14. Very strange.
    – Appleoddity
    Aug 26, 2019 at 16:34

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.