Spurious Stalls
Hi Group,
My team has been very happy using Postgres, hosting Bitbucket. Thanks very much for all the community contributions, to the platform.
Lately, though, about once a day now, for about a week, we have been experiencing periods of stalling. When Postgres stalls, we haven't been able to recover, without restarting the database, unfortunately.
This brings our uptime down some, that we'd like to avoid (99.2%) :( We'd like to do a better job keeping things running.
It would be great to get your input about it. Alternately, if someone is available, as a consultant, that would be great too.
Here is some background, about the issue. We have found the following symptoms.
- During this performance issue, we found the following symptoms.
- Running queries do not return.
- The application sometimes can no longer get new connections.
- The CPU load increases
- There is no I/O wait.
- There is no swapping.
Also, our database configuration, is attached to this email, as postgresql.conf, for reference, along with a profile of our hardware and tuning, as pg_db_profile.txt.
While the database was unavailable, we also collected a lot of data. Looking through this info, a few things pop-out to us, that may be problematic, or useful to notice.
- Disk I/O appears to be all write, and little read.
- In previous incidents, with the same symptoms, we have seen pg processes spending much time in s_lock
- That info is attached to this email also, as files named perf_*.
Problem
As you can probably see below, at 11:54, the DB stops returning rows.Also, transactions stop returning, causing the active transaction time to trend up to the sky.
Consequences of Problem
Once transactions stop returning, we see connections pile-up. Eventually, we reach a max, and clients can no longer connect.The cpu utilization increases to nearly 100%, in user space, and stays there, until the database is restarted.
Events Before Problem
This is likely the most useful part. As the time approaches 11:54, there are periods of increased latency. There is also a marked increase in write operations, in general.Lastly, about 10 minutes before outage, postgres writes a sustained 30 MB/s of temp files.
After investigating this, we found a query that was greatly exceeding work_mem. We've since optimized it, and hopefully, that will have a positive effect on the above.
We may not know until the next issue happens, though.
With a problem like this, I am not exactly positive how to proceed. I am really looking forward to hearing your thoughts, and opinions, if you can share them.
Thanks very much,
-Chris
Вложения
- read_write_ops.png
- transaction_times.png
- read_write_latency.png
- pgsql_connections.png
- temp_files.png
- tuples.png
- cpu_utilization.png
- pg_db_profile.txt
- postgresql.conf
- perf_example_vmstat
- perf_example_dmesg
- perf_example_ipcs
- perf_example_locks.csv
- perf_example_pginfo
- perf_example_ps_auxfww
- perf_example_iotop
- perf_example_strace.47700
- perf_example_backtrace.47700
- perf_example_stack.47700
- perf_example_status.47700
- perf_example_strace.46462
- perf_example_syscall.47700
- perf_example_backtrace.46462
- perf_example_stack.46462
- perf_example_status.46462
- perf_example_strace.29561
- perf_example_syscall.46462
- perf_example_backtrace.29561
- perf_example_stack.29561
- perf_example_status.29561
- perf_example_syscall.29561
- perf_example_strace.81372
- perf_example_backtrace.81372
- perf_example_stack.81372
- perf_example_status.81372
- perf_example_syscall.81372
- perf_example_vacuum
В списке pgsql-general по дате отправления: