Thread: Stucks in the middle
Hello, I have a problem. Sometimes the whole sites stucks in the middle (doesn't matter which page is opened) because of some sql issue. When i reboot postresql it starts working good immediately. But when it's stuck i can see in the log that new and new queries are running, meaning it's stuck only for my session. What can be a reason and how can i solve this problem? Thank you -- View this message in context: http://postgresql.nabble.com/Stucks-in-the-middle-tp5879395.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On 12/29/2015 03:11 AM, Yevgeny wrote: > Hello, > > I have a problem. Sometimes the whole sites stucks in the middle (doesn't > matter which page is opened) because of some sql issue. > When i reboot postresql it starts working good immediately. > But when it's stuck i can see in the log that new and new queries are > running, meaning it's stuck only for my session. > > What can be a reason and how can i solve this problem? Without more information I do not see this as solvable, so: Postgres version OS The 'site' is? Framework? Method of access to Postgres; JDBC, ODBC, Python, PHP, etc. Define 'stuck'? Is it always the same SQL issue and if so what is the query? If not is it a similar class of issues? What does the log show at the time the query hangs? > > Thank you > > > > -- > View this message in context: http://postgresql.nabble.com/Stucks-in-the-middle-tp5879395.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Dec 29, 2015 at 3:11 AM, Yevgeny <jebjick@gmail.com> wrote:
Hello,
I have a problem. Sometimes the whole sites stucks in the middle (doesn't
matter which page is opened) because of some sql issue.
When i reboot postresql it starts working good immediately.
But when it's stuck i can see in the log that new and new queries are
running, meaning it's stuck only for my session.
What can be a reason and how can i solve this problem?
There are a lot of possible reasons, and I think you'll need to provide more details to get any accuracy on guesses from the list. Here are a few ideas:
- You have a middle tier app which is failing to release cursor or other type of open query
- You have a deadlock situation in Pg where you are mutually locking a row in some way that prevents your process from completing
- You are executing a query that is very long running (not optimized) so your connection appears blocked but is in fact just taking forever to complete
Steve
This command helps VACUUM FULL ANALYZE VERBOSE; but in 2-3 hours it starts working slowly again Any ideas? -- View this message in context: http://postgresql.nabble.com/Stucks-in-the-middle-tp5879395p5881284.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Sat, Jan 9, 2016 at 1:46 PM, Yevgeny <jebjick@gmail.com> wrote:
This command helps VACUUM FULL ANALYZE VERBOSE;
but in 2-3 hours it starts working slowly again
Any ideas?
VACUUM FULL requires a table lock, is it getting blocked behind other locks?
Here is a sample query to view outstanding locks.
SELECT w.locktype AS waiting_locktype,w.relation::regclass AS waiting_table,w.transactionid,
substr(w_stm.query,1,20) AS waiting_query,w.mode AS waiting_mode,w.pid AS waiting_pid,
other.locktype AS other_locktype,other.relation::regclass AS other_table,
other_stm.query AS other_query,other.mode AS other_mode,other.pid AS other_pid,
other.granted AS other_granted
FROM pg_catalog.pg_locks AS w
JOIN pg_catalog.pg_stat_activity AS w_stm ON (w_stm.pid = w.pid)
JOIN pg_catalog.pg_locks AS other ON ((w."database" = other."database" AND w.relation = other.relation) OR w.transactionid = other.transactionid)
JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid)
WHERE NOT w.granted
AND w.pid <> other.pid
SELECT w.locktype AS waiting_locktype,w.relation::regclass AS waiting_table,w.transactionid,
substr(w_stm.query,1,20) AS waiting_query,w.mode AS waiting_mode,w.pid AS waiting_pid,
other.locktype AS other_locktype,other.relation::regclass AS other_table,
other_stm.query AS other_query,other.mode AS other_mode,other.pid AS other_pid,
other.granted AS other_granted
FROM pg_catalog.pg_locks AS w
JOIN pg_catalog.pg_stat_activity AS w_stm ON (w_stm.pid = w.pid)
JOIN pg_catalog.pg_locks AS other ON ((w."database" = other."database" AND w.relation = other.relation) OR w.transactionid = other.transactionid)
JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid)
WHERE NOT w.granted
AND w.pid <> other.pid