pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlong lasting select, no io nor cpu usage ?
5+ messages / 2 participants
[nested] [flat]
* long lasting select, no io nor cpu usage ?
@ 2014-03-20 13:51 Franck Routier <franck.routier@axege.com>
0 siblings, 1 reply; 5+ messages in thread
From: Franck Routier @ 2014-03-20 13:51 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a table (sanact) with 23.125.525 rows (and a hundred columns).
I am doing a select, that did not finish after some 15 hours... Select
is as follows:
select * from sanact where sanact___rfovsnide = 'MYVERSION' order by
sanactcsu;
There is an index on sanact___rfovsnide and doing EXPLAIN shows it is used.
Resulting dataset should be 1626000 rows.
iostat shows 99.5% idle disks, almost no activity.
top shows almost no cpu usage.
Where should I be looking for a problem ?
Thanks in advance,
Franck
Attachments:
smime.p7s (application/pkcs7-signature, 4.2K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: long lasting select, no io nor cpu usage ?
@ 2014-03-20 13:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Franck Routier <franck.routier@axege.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2014-03-20 13:56 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
Franck Routier <franck.routier@axege.com> writes:
> I am doing a select, that did not finish after some 15 hours... Select
> is as follows:
> select * from sanact where sanact___rfovsnide = 'MYVERSION' order by
> sanactcsu;
> There is an index on sanact___rfovsnide and doing EXPLAIN shows it is used.
> Resulting dataset should be 1626000 rows.
> iostat shows 99.5% idle disks, almost no activity.
> top shows almost no cpu usage.
> Where should I be looking for a problem ?
pg_locks, probably.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: long lasting select, no io nor cpu usage ?
@ 2014-03-20 14:07 Franck Routier <franck.routier@axege.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Franck Routier @ 2014-03-20 14:07 UTC (permalink / raw)
To: pgsql-performance
Le 20/03/2014 14:56, Tom Lane a écrit :
> pg_locks, probably. regards, tom lane
select * from pg_stat_activity shows 'F'alse in the waiting column for
the query.
Can I rely on that or should I be investigating further for subtile
types of locks ?
Attachments:
smime.p7s (application/pkcs7-signature, 4.2K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: long lasting select, no io nor cpu usage ?
@ 2014-03-20 14:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Franck Routier <franck.routier@axege.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2014-03-20 14:15 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
Franck Routier <franck.routier@axege.com> writes:
> Le 20/03/2014 14:56, Tom Lane a écrit :
>> pg_locks, probably. regards, tom lane
> select * from pg_stat_activity shows 'F'alse in the waiting column for
> the query.
Hm. The next most likely theory is that it's waiting on network I/O,
but it's hard to tell that from the outside. Can you attach to the
stuck backend with gdb and get a stack trace?
http://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: long lasting select, no io nor cpu usage ?
@ 2014-03-20 16:27 Franck Routier <franck.routier@axege.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Franck Routier @ 2014-03-20 16:27 UTC (permalink / raw)
To: pgsql-performance
Le 20/03/2014 15:15, Tom Lane a écrit :
> Hm. The next most likely theory is that it's waiting on network I/O,
> but it's hard to tell that from the outside. Can you attach to the
> stuck backend with gdb and get a stack trace?
> http://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
>
> regards, tom lane
>
I found the problem, not related to postgresql after all.
The client (CloverETL, using jdbc) was stuck and not "consuming" the
records. I killed and restarted the ETL and all is fine now.
Thanks a lot for your time and help,
Franck
Attachments:
smime.p7s (application/pkcs7-signature, 4.2K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-03-20 16:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-20 13:51 long lasting select, no io nor cpu usage ? Franck Routier <franck.routier@axege.com>
2014-03-20 13:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-03-20 14:07 ` Franck Routier <franck.routier@axege.com>
2014-03-20 14:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-03-20 16:27 ` Franck Routier <franck.routier@axege.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox