agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedStucks in the middle
5+ messages / 4 participants
[nested] [flat]
* Stucks in the middle
@ 2015-12-29 11:11 Yevgeny <jebjick@gmail.com>
2015-12-29 18:23 ` Re: Stucks in the middle Steve Midgley <science@misuse.org>
2015-12-29 18:24 ` Re: Stucks in the middle Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 5+ messages in thread
From: Yevgeny @ 2015-12-29 11:11 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Stucks in the middle
2015-12-29 11:11 Stucks in the middle Yevgeny <jebjick@gmail.com>
@ 2015-12-29 18:23 ` Steve Midgley <science@misuse.org>
1 sibling, 0 replies; 5+ messages in thread
From: Steve Midgley @ 2015-12-29 18:23 UTC (permalink / raw)
To: Yevgeny <jebjick@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Stucks in the middle
2015-12-29 11:11 Stucks in the middle Yevgeny <jebjick@gmail.com>
@ 2015-12-29 18:24 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-01-09 21:46 ` Re: Stucks in the middle Yevgeny <jebjick@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2015-12-29 18:24 UTC (permalink / raw)
To: Yevgeny <jebjick@gmail.com>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Stucks in the middle
2015-12-29 11:11 Stucks in the middle Yevgeny <jebjick@gmail.com>
2015-12-29 18:24 ` Re: Stucks in the middle Adrian Klaver <adrian.klaver@aklaver.com>
@ 2016-01-09 21:46 ` Yevgeny <jebjick@gmail.com>
2016-01-09 21:50 ` Re: Stucks in the middle bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Yevgeny @ 2016-01-09 21:46 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Stucks in the middle
2015-12-29 11:11 Stucks in the middle Yevgeny <jebjick@gmail.com>
2015-12-29 18:24 ` Re: Stucks in the middle Adrian Klaver <adrian.klaver@aklaver.com>
2016-01-09 21:46 ` Re: Stucks in the middle Yevgeny <jebjick@gmail.com>
@ 2016-01-09 21:50 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: bricklen @ 2016-01-09 21:50 UTC (permalink / raw)
To: Yevgeny <jebjick@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2016-01-09 21:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-12-29 11:11 Stucks in the middle Yevgeny <jebjick@gmail.com>
2015-12-29 18:23 ` Steve Midgley <science@misuse.org>
2015-12-29 18:24 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-01-09 21:46 ` Yevgeny <jebjick@gmail.com>
2016-01-09 21:50 ` bricklen <bricklen@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox