pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPower cut and performance problem
9+ messages / 3 participants
[nested] [flat]
* Power cut and performance problem
@ 2006-03-20 23:19 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-20 23:27 ` Re: Power cut and performance problem Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 9+ messages in thread
From: Daniel Caune @ 2006-03-20 23:19 UTC (permalink / raw)
To: pgsql-sql
Hi,
We had a power cut lastly and it seems that our PostgreSQL database
suffers from performance since. For example, a simple query such as
"SELECT MIN(a-primary-key-column) FROM a-table" takes quite a very long
time; actually I gave up before getting the result.
I shutdown and started up the database, and I took at the log file; I
don't see any fatal error:
LOG: database system was interrupted at 2006-03-20 22:20:22 GMT
LOG: checkpoint record is at 10C/14919ED4
LOG: redo record is at 10C/1487E270; undo record is at 0/0; shutdown
FALSE
LOG: next transaction ID: 166159120; next OID: 41575
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 10C/1487E270
LOG: incomplete startup packet
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
LOG: record with zero length at 10C/14CF39F0
LOG: redo done at 10C/14CF39B4
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
LOG: incomplete startup packet
LOG: received fast shutdown request
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down at 2006-03-20 22:30:09 GMT
LOG: checkpoint record is at 10C/14CF3A34
LOG: redo record is at 10C/14CF3A34; undo record is at 0/0; shutdown
TRUE
LOG: next transaction ID: 166159788; next OID: 41575
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
LOG: incomplete startup packet
LOG: autovacuum: processing database "postgres"
LOG: incomplete startup packet
LOG: received fast shutdown request
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down at 2006-03-20 22:31:24 GMT
LOG: checkpoint record is at 10C/14CF3A78
LOG: redo record is at 10C/14CF3A78; undo record is at 0/0; shutdown
TRUE
LOG: next transaction ID: 166159796; next OID: 41575
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"
LOG: autovacuum: processing database "postgres"
LOG: incomplete startup packet
Where can I check, please? Is it more likely a hardware problem (the
machine seems ok, no error detected)?
Regards,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Power cut and performance problem
2006-03-20 23:19 Power cut and performance problem Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-20 23:27 ` Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jeff Frost @ 2006-03-20 23:27 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
I see you're running autovacuum. What's your disk subsytem look like? By
chance is it sitting on a RAID 5 that's running in degraded mode right now
while it scrubs?
On Mon, 20 Mar 2006, Daniel Caune wrote:
> Hi,
>
> We had a power cut lastly and it seems that our PostgreSQL database
> suffers from performance since. For example, a simple query such as
> "SELECT MIN(a-primary-key-column) FROM a-table" takes quite a very long
> time; actually I gave up before getting the result.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Power cut and performance problem
@ 2006-03-20 23:50 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-20 23:52 ` Re: Power cut and performance problem Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 9+ messages in thread
From: Daniel Caune @ 2006-03-20 23:50 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
> I see you're running autovacuum. What's your disk subsytem look like?
By
> chance is it sitting on a RAID 5 that's running in degraded mode right
now
> while it scrubs?
>
Yes, that should be the problem. I will check that tomorrow morning
with a Linux administrator. Thanks.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Power cut and performance problem
2006-03-20 23:50 Re: Power cut and performance problem Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-20 23:52 ` Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jeff Frost @ 2006-03-20 23:52 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
On Mon, 20 Mar 2006, Daniel Caune wrote:
>> I see you're running autovacuum. What's your disk subsytem look like?
> By
>> chance is it sitting on a RAID 5 that's running in degraded mode right
> now
>> while it scrubs?
>>
>
> Yes, that should be the problem. I will check that tomorrow morning
> with a Linux administrator. Thanks.
BTW, I didn't complete my first thought above, which was to ask when you last
vacuumed the DB, but then I saw that you were running autovac, so that wasn't
likely the problem.
BTW, if the problem is actually a raid array that is rebuilding, it should be
(hopefullY) fixed by tomorrow morning.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Power cut and performance problem
@ 2006-03-21 14:18 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-21 15:49 ` Re: Power cut and performance problem Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 9+ messages in thread
From: Daniel Caune @ 2006-03-21 14:18 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
> BTW, I didn't complete my first thought above, which was to ask when
you
> last
> vacuumed the DB, but then I saw that you were running autovac, so that
> wasn't
> likely the problem.
>
> BTW, if the problem is actually a raid array that is rebuilding, it
should
> be
> (hopefullY) fixed by tomorrow morning.
>
An administrator is checking the raid status this morning. Anyway, I
did some tests and it seems that some results are weird.
For example, the execution of the following query is fast as it used to
be (gslog_event_id is the primary key on gslog_event):
select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms)
while the following query is really slow (several minutes):
select min(gslog_event_id) from gslog_event; (index on the primary key
is taken)
I'm not a hardware expert at all, but I supposed that the whole
performance would be degraded when a problem occurs with RAID disks. Am
I wrong? Could it be something else? Are there some tools that check
the state of a PostgreSQL database?
--
Daniel
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Power cut and performance problem
2006-03-21 14:18 Re: Power cut and performance problem Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-21 15:49 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-22 03:38 ` Re: Power cut and performance problem Aftab Alam <aalam@tatashare.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jeff Frost @ 2006-03-21 15:49 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
On Tue, 21 Mar 2006, Daniel Caune wrote:
> For example, the execution of the following query is fast as it used to
> be (gslog_event_id is the primary key on gslog_event):
>
> select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms)
>
>
> while the following query is really slow (several minutes):
>
> select min(gslog_event_id) from gslog_event; (index on the primary key
> is taken)
>
>
> I'm not a hardware expert at all, but I supposed that the whole
> performance would be degraded when a problem occurs with RAID disks. Am
> I wrong? Could it be something else? Are there some tools that check
> the state of a PostgreSQL database?
You would be correct, a hardware problem should manifest itself on both those
queries. What is the explain analyze output of those two queries? It's
possible you have a corrupt index on gslog_event. If that's the case, a
reindex would likely remedy the problem. Is postgres logging any errors?
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Power cut and performance problem
2006-03-21 14:18 Re: Power cut and performance problem Daniel Caune <daniel.caune@ubisoft.com>
2006-03-21 15:49 ` Re: Power cut and performance problem Jeff Frost <jeff@frostconsultingllc.com>
@ 2006-03-22 03:38 ` Aftab Alam <aalam@tatashare.com>
0 siblings, 0 replies; 9+ messages in thread
From: Aftab Alam @ 2006-03-22 03:38 UTC (permalink / raw)
To: 'Jeff Frost' <jeff@frostconsultingllc.com>; 'Daniel Caune' <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
unsubscribe
Regards,
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Jeff Frost
Sent: Tuesday, March 21, 2006 9:19 PM
To: Daniel Caune
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Power cut and performance problem
On Tue, 21 Mar 2006, Daniel Caune wrote:
> For example, the execution of the following query is fast as it used to
> be (gslog_event_id is the primary key on gslog_event):
>
> select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms)
>
>
> while the following query is really slow (several minutes):
>
> select min(gslog_event_id) from gslog_event; (index on the primary key
> is taken)
>
>
> I'm not a hardware expert at all, but I supposed that the whole
> performance would be degraded when a problem occurs with RAID disks. Am
> I wrong? Could it be something else? Are there some tools that check
> the state of a PostgreSQL database?
You would be correct, a hardware problem should manifest itself on both
those
queries. What is the explain analyze output of those two queries? It's
possible you have a corrupt index on gslog_event. If that's the case, a
reindex would likely remedy the problem. Is postgres logging any errors?
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Power cut and performance problem
@ 2006-03-21 16:13 Daniel Caune <daniel.caune@ubisoft.com>
2006-03-22 03:38 ` Re: Power cut and performance problem Aftab Alam <aalam@tatashare.com>
0 siblings, 1 reply; 9+ messages in thread
From: Daniel Caune @ 2006-03-21 16:13 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
> > For example, the execution of the following query is fast as it used
to
> > be (gslog_event_id is the primary key on gslog_event):
> >
> > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms)
> >
> >
> > while the following query is really slow (several minutes):
> >
> > select min(gslog_event_id) from gslog_event; (index on the primary
key
> > is taken)
> >
> >
> > I'm not a hardware expert at all, but I supposed that the whole
> > performance would be degraded when a problem occurs with RAID disks.
Am
> > I wrong? Could it be something else? Are there some tools that
check
> > the state of a PostgreSQL database?
>
> You would be correct, a hardware problem should manifest itself on
both
> those
> queries. What is the explain analyze output of those two queries?
It's
> possible you have a corrupt index on gslog_event. If that's the case,
a
> reindex would likely remedy the problem. Is postgres logging any
errors?
>
The UNIX administrator confirms that this is not a RAID problem.
I truncate my table. This is not the most efficient way, but it's okay
because this is a data stage table. It seems that it fixes my
performance problem. As you said, perhaps the problem was more related
to index corruption. Truncating data and inserting new data recreate
the index and therefore fix the problem.
Thanks,
--
Daniel
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Power cut and performance problem
2006-03-21 16:13 Re: Power cut and performance problem Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-03-22 03:38 ` Aftab Alam <aalam@tatashare.com>
0 siblings, 0 replies; 9+ messages in thread
From: Aftab Alam @ 2006-03-22 03:38 UTC (permalink / raw)
To: 'Daniel Caune' <daniel.caune@ubisoft.com>; 'Jeff Frost' <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
unsubscribe
Regards,
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Daniel Caune
Sent: Tuesday, March 21, 2006 9:44 PM
To: Jeff Frost
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Power cut and performance problem
> > For example, the execution of the following query is fast as it used
to
> > be (gslog_event_id is the primary key on gslog_event):
> >
> > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms)
> >
> >
> > while the following query is really slow (several minutes):
> >
> > select min(gslog_event_id) from gslog_event; (index on the primary
key
> > is taken)
> >
> >
> > I'm not a hardware expert at all, but I supposed that the whole
> > performance would be degraded when a problem occurs with RAID disks.
Am
> > I wrong? Could it be something else? Are there some tools that
check
> > the state of a PostgreSQL database?
>
> You would be correct, a hardware problem should manifest itself on
both
> those
> queries. What is the explain analyze output of those two queries?
It's
> possible you have a corrupt index on gslog_event. If that's the case,
a
> reindex would likely remedy the problem. Is postgres logging any
errors?
>
The UNIX administrator confirms that this is not a RAID problem.
I truncate my table. This is not the most efficient way, but it's okay
because this is a data stage table. It seems that it fixes my
performance problem. As you said, perhaps the problem was more related
to index corruption. Truncating data and inserting new data recreate
the index and therefore fix the problem.
Thanks,
--
Daniel
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Attachments:
winmail.dat (application/ms-tnef, 3.1K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-03-22 03:38 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-20 23:19 Power cut and performance problem Daniel Caune <daniel.caune@ubisoft.com>
2006-03-20 23:27 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-20 23:50 Re: Power cut and performance problem Daniel Caune <daniel.caune@ubisoft.com>
2006-03-20 23:52 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-21 14:18 Re: Power cut and performance problem Daniel Caune <daniel.caune@ubisoft.com>
2006-03-21 15:49 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-03-22 03:38 ` Aftab Alam <aalam@tatashare.com>
2006-03-21 16:13 Re: Power cut and performance problem Daniel Caune <daniel.caune@ubisoft.com>
2006-03-22 03:38 ` Aftab Alam <aalam@tatashare.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