pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [ SOLVED ] select count(*) very slow on an already
12+ messages / 7 participants
[nested] [flat]
* Re: [ SOLVED ] select count(*) very slow on an already
@ 2004-04-15 11:35 Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 10:59 ` Re: [ SOLVED ] select count(*) very slow on an already pginfo <pginfo@t1.unisoftbg.com>
2004-04-15 13:36 ` Re: [ SOLVED ] select count(*) very slow on an already Bill Moran <wmoran@potentialtech.com>
0 siblings, 2 replies; 12+ messages in thread
From: Rajesh Kumar Mallah @ 2004-04-15 11:35 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
Hi,
The problem was solved by reloading the Table.
the query now takes only 3 seconds. But that is
not a solution.
The problem is that such phenomenon obscures our
judgement used in optimising queries and database.
If a query runs slow we really cant tell if its a problem
with query itself , hardware or dead rows.
I already did vacumm full on the table but it still did not
have that effect on performance.
In fact the last figures were after doing a vacuum full.
Can there be any more elegent solution to this problem.
Regds
Mallah.
Richard Huxton wrote:
>On Thursday 15 April 2004 08:10, Rajesh Kumar Mallah wrote:
>
>
>>The problem is that i want to know if i need a Hardware upgrade
>>at the moment.
>>
>>Eg i have another table rfis which contains ~ .6 million records.
>>
>>
>
>
>
>>SELECT count(*) from rfis where sender_uid > 0;
>>
>>
>
>
>
>>Time: 117560.635 ms
>>
>>Which is approximate 4804 records per second. Is it an acceptable
>>performance on the hardware below:
>>
>>RAM: 2 GB
>>DISKS: ultra160 , 10 K , 18 GB
>>Processor: 2* 2.0 Ghz Xeon
>>
>>
>
>Hmm - doesn't seem good, does it? If you run it again, is it much faster
>(since the data should be cached then)? What does "vmstat 10" show while
>you're running the query?
>
>One thing you should have done is read the performance tuning guide at:
> http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
>The default values are very conservative, and you will need to change them.
>
>
>
>>What kind of upgrades shoud be put on the server for it to become
>>reasonable fast.
>>
>>
>
>If you've only got one disk, then a second disk for OS/logging. Difficult to
>say more without knowing numbers of users/activity etc.
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
2004-04-15 11:35 Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-04-15 10:59 ` pginfo <pginfo@t1.unisoftbg.com>
1 sibling, 0 replies; 12+ messages in thread
From: pginfo @ 2004-04-15 10:59 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
Hi ,
I am not sure, but I remember the same problem.
It was ot 7.3.x version and and I needet to reindex the table.
I think after 7.4 vacuum also work correct with reindex.
But I am not sure.
regards,
ivan.
Rajesh Kumar Mallah wrote:
> Hi,
>
> The problem was solved by reloading the Table.
> the query now takes only 3 seconds. But that is
> not a solution.
>
> The problem is that such phenomenon obscures our
> judgement used in optimising queries and database.
>
> If a query runs slow we really cant tell if its a problem
> with query itself , hardware or dead rows.
>
> I already did vacumm full on the table but it still did not
> have that effect on performance.
> In fact the last figures were after doing a vacuum full.
>
> Can there be any more elegent solution to this problem.
>
> Regds
> Mallah.
>
> Richard Huxton wrote:
>
> >On Thursday 15 April 2004 08:10, Rajesh Kumar Mallah wrote:
> >
> >
> >>The problem is that i want to know if i need a Hardware upgrade
> >>at the moment.
> >>
> >>Eg i have another table rfis which contains ~ .6 million records.
> >>
> >>
> >
> >
> >
> >>SELECT count(*) from rfis where sender_uid > 0;
> >>
> >>
> >
> >
> >
> >>Time: 117560.635 ms
> >>
> >>Which is approximate 4804 records per second. Is it an acceptable
> >>performance on the hardware below:
> >>
> >>RAM: 2 GB
> >>DISKS: ultra160 , 10 K , 18 GB
> >>Processor: 2* 2.0 Ghz Xeon
> >>
> >>
> >
> >Hmm - doesn't seem good, does it? If you run it again, is it much faster
> >(since the data should be cached then)? What does "vmstat 10" show while
> >you're running the query?
> >
> >One thing you should have done is read the performance tuning guide at:
> > http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
> >The default values are very conservative, and you will need to change them.
> >
> >
> >
> >>What kind of upgrades shoud be put on the server for it to become
> >>reasonable fast.
> >>
> >>
> >
> >If you've only got one disk, then a second disk for OS/logging. Difficult to
> >say more without knowing numbers of users/activity etc.
> >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
2004-04-15 11:35 Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-04-15 13:36 ` Bill Moran <wmoran@potentialtech.com>
2004-04-15 16:19 ` Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 1 reply; 12+ messages in thread
From: Bill Moran @ 2004-04-15 13:36 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
Rajesh Kumar Mallah wrote:
>
> Hi,
>
> The problem was solved by reloading the Table.
> the query now takes only 3 seconds. But that is
> not a solution.
If dropping/recreating the table improves things, then we can reasonably
assume that the table is pretty active with updates/inserts. Correct?
> The problem is that such phenomenon obscures our
> judgement used in optimising queries and database.
Lots of phenomenon obscure that ...
> If a query runs slow we really cant tell if its a problem
> with query itself , hardware or dead rows.
>
> I already did vacumm full on the table but it still did not
> have that effect on performance.
> In fact the last figures were after doing a vacuum full.
If the data gets too fragmented, a vacuum may not be enough. Also, read
up on the recommendations _against_ vacuum full (recommending only using
vacuum on databases) With full, vacuum condenses the database, which may
actually hurt performance. A regular vacuum just fixes things up, and
may leave unused space lying around. However, this should apparently
achieve a balance between usage and vacuum. See the docs, they are much
better at describing this than I am.
> Can there be any more elegent solution to this problem.
As a guess, look into CLUSTER (a Postgres SQL command). CLUSTER will
basically recreate the table while ordering rows based on an index.
(this might benefit you in other ways as well) Don't forget to analyze
after cluster. If the problem is caused by frequent updates/inserts,
you may find that re-clustering the table on a certain schedule is
worthwhile.
Be warned, this suggestion is based on an educated guess, I make no
guarantees that it will help your problem. Read the docs on cluster
and come to your own conclusions.
>
> Regds
> Mallah.
>
>
>
>
>
> Richard Huxton wrote:
>
>> On Thursday 15 April 2004 08:10, Rajesh Kumar Mallah wrote:
>>
>>
>>> The problem is that i want to know if i need a Hardware upgrade
>>> at the moment.
>>>
>>> Eg i have another table rfis which contains ~ .6 million records.
>>>
>>
>>
>>
>>
>>> SELECT count(*) from rfis where sender_uid > 0;
>>>
>>
>>
>>
>>
>>> Time: 117560.635 ms
>>>
>>> Which is approximate 4804 records per second. Is it an acceptable
>>> performance on the hardware below:
>>>
>>> RAM: 2 GB
>>> DISKS: ultra160 , 10 K , 18 GB
>>> Processor: 2* 2.0 Ghz Xeon
>>>
>>
>> Hmm - doesn't seem good, does it? If you run it again, is it much
>> faster (since the data should be cached then)? What does "vmstat 10"
>> show while you're running the query?
>>
>> One thing you should have done is read the performance tuning guide at:
>> http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
>> The default values are very conservative, and you will need to change
>> them.
>>
>>> What kind of upgrades shoud be put on the server for it to become
>>> reasonable fast.
>>>
>> If you've only got one disk, then a second disk for OS/logging.
>> Difficult to say more without knowing numbers of users/activity etc.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
2004-04-15 11:35 Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 13:36 ` Re: [ SOLVED ] select count(*) very slow on an already Bill Moran <wmoran@potentialtech.com>
@ 2004-04-15 16:19 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 17:44 ` Re: [ SOLVED ] select count(*) very slow on an already Richard Huxton <dev@archonet.com>
2004-04-15 18:48 ` Re: [ SOLVED ] select count(*) very slow on an already Bill Moran <wmoran@potentialtech.com>
0 siblings, 2 replies; 12+ messages in thread
From: Rajesh Kumar Mallah @ 2004-04-15 16:19 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
Bill Moran wrote:
> Rajesh Kumar Mallah wrote:
>
>>
>> Hi,
>>
>> The problem was solved by reloading the Table.
>> the query now takes only 3 seconds. But that is
>> not a solution.
>
>
> If dropping/recreating the table improves things, then we can reasonably
> assume that the table is pretty active with updates/inserts. Correct?
Yes the table results from an import process and under goes lots
of inserts and updates , but thats before the vacuum full operation.
the table is not accessed during vacuum. What i want to know is
is there any wat to automate the dumping and reload of a table
individually. will the below be safe and effective:
begin work;
create table new_tab AS select * from tab;
truncate table tab;
insert into tab select * from new_tab;
drop table new_tab;
commit;
analyze tab;
i havenot tried it but plan to do so.
but i feel insert would take ages to update
the indexes if any.
BTW
is there any way to disable checks and triggers on
a table temporarily while loading data (is updating
reltriggers in pg_class safe?)
>
>> The problem is that such phenomenon obscures our
>> judgement used in optimising queries and database.
>
>
> Lots of phenomenon obscure that ...
>
true. but there should not be too many.
>> If a query runs slow we really cant tell if its a problem
>> with query itself , hardware or dead rows.
>>
>> I already did vacumm full on the table but it still did not
>> have that effect on performance.
>> In fact the last figures were after doing a vacuum full.
>
>
> If the data gets too fragmented, a vacuum may not be enough. Also, read
> up on the recommendations _against_ vacuum full (recommending only using
> vacuum on databases) With full, vacuum condenses the database, which may
> actually hurt performance. A regular vacuum just fixes things up, and
> may leave unused space lying around. However, this should apparently
> achieve a balance between usage and vacuum. See the docs, they are much
> better at describing this than I am.
>
i understand simultaneous vacuum and usage detoriates performance mostly.
but this case is different.
>> Can there be any more elegent solution to this problem.
>
>
> As a guess, look into CLUSTER (a Postgres SQL command). CLUSTER will
> basically recreate the table while ordering rows based on an index.
> (this might benefit you in other ways as well) Don't forget to analyze
> after cluster. If the problem is caused by frequent updates/inserts,
> you may find that re-clustering the table on a certain schedule is
> worthwhile.
i could consider that option also.
>
> Be warned, this suggestion is based on an educated guess, I make no
> guarantees that it will help your problem. Read the docs on cluster
> and come to your own conclusions.
Thanks .
Regds
mallah.
>
>>
>> Regds
>> Mallah.
>>
>>
>>
>>
>>
>> Richard Huxton wrote:
>>
>>> On Thursday 15 April 2004 08:10, Rajesh Kumar Mallah wrote:
>>>
>>>
>>>> The problem is that i want to know if i need a Hardware upgrade
>>>> at the moment.
>>>>
>>>> Eg i have another table rfis which contains ~ .6 million records.
>>>>
>>>
>>>
>>>
>>>
>>>
>>>> SELECT count(*) from rfis where sender_uid > 0;
>>>>
>>>
>>>
>>>
>>>
>>>
>>>> Time: 117560.635 ms
>>>>
>>>> Which is approximate 4804 records per second. Is it an acceptable
>>>> performance on the hardware below:
>>>>
>>>> RAM: 2 GB
>>>> DISKS: ultra160 , 10 K , 18 GB
>>>> Processor: 2* 2.0 Ghz Xeon
>>>>
>>>
>>>
>>> Hmm - doesn't seem good, does it? If you run it again, is it much
>>> faster (since the data should be cached then)? What does "vmstat 10"
>>> show while you're running the query?
>>>
>>> One thing you should have done is read the performance tuning guide at:
>>> http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
>>> The default values are very conservative, and you will need to
>>> change them.
>>>
>>>> What kind of upgrades shoud be put on the server for it to become
>>>> reasonable fast.
>>>>
>>>
>>> If you've only got one disk, then a second disk for OS/logging.
>>> Difficult to say more without knowing numbers of users/activity etc.
>>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
2004-04-15 11:35 Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 13:36 ` Re: [ SOLVED ] select count(*) very slow on an already Bill Moran <wmoran@potentialtech.com>
2004-04-15 16:19 ` Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-04-15 17:44 ` Richard Huxton <dev@archonet.com>
2004-04-15 18:29 ` Re: [ SOLVED ] select count(*) very slow on an already Mark Lubratt <mark.lubratt@indeq.com>
2004-04-16 08:23 ` Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 2 replies; 12+ messages in thread
From: Richard Huxton @ 2004-04-15 17:44 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
On Thursday 15 April 2004 17:19, Rajesh Kumar Mallah wrote:
> Bill Moran wrote:
> > Rajesh Kumar Mallah wrote:
> >> Hi,
> >>
> >> The problem was solved by reloading the Table.
> >> the query now takes only 3 seconds. But that is
> >> not a solution.
> >
> > If dropping/recreating the table improves things, then we can reasonably
> > assume that the table is pretty active with updates/inserts. Correct?
>
> Yes the table results from an import process and under goes lots
> of inserts and updates , but thats before the vacuum full operation.
> the table is not accessed during vacuum. What i want to know is
> is there any wat to automate the dumping and reload of a table
> individually. will the below be safe and effective:
Shouldn't be necessary assuming you vacuum (not full) regularly. However,
looking back at your original posting, the vacuum output doesn't seem to show
any rows that need removing.
# VACUUM full verbose eyp_rfi;
INFO: vacuuming "public.eyp_rfi"
INFO: "eyp_rfi": found 0 removable, 505960 nonremovable row versions in
71987 pages
DETAIL: 0 dead row versions cannot be removed yet.
Since your select count(*) showed 505960 rows, I can't see how
dropping/replacing could make a difference on a sequential scan. Since we're
not using any indexes I don't see how it could be related to that.
> begin work;
> create table new_tab AS select * from tab;
> truncate table tab;
> insert into tab select * from new_tab;
> drop table new_tab;
> commit;
> analyze tab;
>
> i havenot tried it but plan to do so.
> but i feel insert would take ages to update
> the indexes if any.
It will have to update them, which will take time.
> BTW
>
> is there any way to disable checks and triggers on
> a table temporarily while loading data (is updating
> reltriggers in pg_class safe?)
You can take a look at pg_restore and copy how it does it.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
2004-04-15 11:35 Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 13:36 ` Re: [ SOLVED ] select count(*) very slow on an already Bill Moran <wmoran@potentialtech.com>
2004-04-15 16:19 ` Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 17:44 ` Re: [ SOLVED ] select count(*) very slow on an already Richard Huxton <dev@archonet.com>
@ 2004-04-15 18:29 ` Mark Lubratt <mark.lubratt@indeq.com>
1 sibling, 0 replies; 12+ messages in thread
From: Mark Lubratt @ 2004-04-15 18:29 UTC (permalink / raw)
To: pgsql-performance
On Apr 15, 2004, at 12:44 PM, Richard Huxton wrote:
> On Thursday 15 April 2004 17:19, Rajesh Kumar Mallah wrote:
>> Bill Moran wrote:
>
>> BTW
>>
>> is there any way to disable checks and triggers on
>> a table temporarily while loading data (is updating
>> reltriggers in pg_class safe?)
>
> You can take a look at pg_restore and copy how it does it.
>
>
Does SET CONSTRAINT take care of checks within the transaction?
Triggers would be a different matter...
Mark
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
2004-04-15 11:35 Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 13:36 ` Re: [ SOLVED ] select count(*) very slow on an already Bill Moran <wmoran@potentialtech.com>
2004-04-15 16:19 ` Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 17:44 ` Re: [ SOLVED ] select count(*) very slow on an already Richard Huxton <dev@archonet.com>
@ 2004-04-16 08:23 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-17 14:29 ` Re: [ SOLVED ] select count(*) very slow on an already Jeff <threshar@torgo.978.org>
1 sibling, 1 reply; 12+ messages in thread
From: Rajesh Kumar Mallah @ 2004-04-16 08:23 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Bill Moran <wmoran@potentialtech.com>; pgsql-performance
I am running an update on the same table
update rfis set inquiry_status='APPROVED' where inquiry_status='a';
Its running for past 20 mins. and top output is below.
The PID which is executing the query above is 6712. Can anyone
tell me why it is in an uninterruptable sleep and does it relate
to the apparent poor performance? Is it problem with the disk
hardware. I know at nite this query will run reasonably fast.
I am running on a decent hardware .
Regds
mallah.
1:41pm up 348 days, 21:10, 1 user, load average: 11.59, 13.69, 11.49
85 processes: 83 sleeping, 1 running, 0 zombie, 1 stopped
CPU0 states: 8.1% user, 2.3% system, 0.0% nice, 89.0% idle
CPU1 states: 3.3% user, 2.3% system, 0.0% nice, 93.2% idle
CPU2 states: 7.4% user, 1.4% system, 0.0% nice, 90.0% idle
CPU3 states: 9.3% user, 7.4% system, 0.0% nice, 82.2% idle
Mem: 2064796K av, 2053964K used, 10832K free, 0K shrd, 22288K
buff
Swap: 2048244K av, 88660K used, 1959584K free 1801532K
cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
* 6712 postgres 16 0 86592 84M 83920 D 11.1 4.1 1:36 postmaster*
13103 postgres 15 0 54584 53M 52556 S 3.5 2.6 0:01 postmaster
13034 root 16 0 1072 1072 848 R 2.1 0.0 0:02 top
13064 postgres 15 0 67256 65M 64516 D 2.1 3.2 0:01 postmaster
13088 postgres 16 0 43324 42M 40812 D 2.1 2.0 0:00 postmaster
13076 postgres 15 0 49016 47M 46628 S 1.9 2.3 0:00 postmaster
26931 postgres 15 0 84880 82M 83888 S 1.7 4.1 3:52 postmaster
13107 postgres 15 0 18400 17M 16488 S 1.5 0.8 0:00 postmaster
13068 postgres 15 0 44632 43M 42324 D 1.3 2.1 0:00 postmaster
13074 postgres 15 0 68852 67M 66508 D 1.3 3.3 0:00 postmaster
13108 postgres 15 0 11692 11M 10496 S 1.3 0.5 0:00 postmaster
13075 postgres 15 0 50860 49M 47680 S 1.1 2.4 0:04 postmaster
13066 postgres 15 0 56112 54M 53724 S 0.9 2.7 0:01 postmaster
13109 postgres 15 0 14528 14M 13272 S 0.9 0.7 0:00 postmaster
24454 postgres 15 0 2532 2380 1372 S 0.7 0.1 11:58 postmaster
12 root 15 0 0 0 0 SW 0.5 0.0 816:30 bdflush
24455 postgres 15 0 1600 1476 1380 S 0.5 0.0 9:11 postmaster
12528 postgres 15 0 84676 82M 79920 S 0.3 4.0 0:02 postmaster
12575 postgres 15 0 76660 74M 75796 D 0.3 3.7 0:09 postmaster
13038 postgres 15 0 48952 47M 46436 D 0.3 2.3 0:00 postmaster
13069 postgres 15 0 57464 56M 54852 S 0.3 2.7 0:00 postmaster
13102 postgres 15 0 17864 17M 16504 D 0.3 0.8 0:00 postmaster
Richard Huxton wrote:
>On Thursday 15 April 2004 17:19, Rajesh Kumar Mallah wrote:
>
>
>>Bill Moran wrote:
>>
>>
>>>Rajesh Kumar Mallah wrote:
>>>
>>>
>>>>Hi,
>>>>
>>>>The problem was solved by reloading the Table.
>>>>the query now takes only 3 seconds. But that is
>>>>not a solution.
>>>>
>>>>
>>>If dropping/recreating the table improves things, then we can reasonably
>>>assume that the table is pretty active with updates/inserts. Correct?
>>>
>>>
>>Yes the table results from an import process and under goes lots
>>of inserts and updates , but thats before the vacuum full operation.
>>the table is not accessed during vacuum. What i want to know is
>>is there any wat to automate the dumping and reload of a table
>>individually. will the below be safe and effective:
>>
>>
>
>Shouldn't be necessary assuming you vacuum (not full) regularly. However,
>looking back at your original posting, the vacuum output doesn't seem to show
>any rows that need removing.
>
># VACUUM full verbose eyp_rfi;
>INFO: vacuuming "public.eyp_rfi"
>INFO: "eyp_rfi": found 0 removable, 505960 nonremovable row versions in
>71987 pages
>DETAIL: 0 dead row versions cannot be removed yet.
>
>Since your select count(*) showed 505960 rows, I can't see how
>dropping/replacing could make a difference on a sequential scan. Since we're
>not using any indexes I don't see how it could be related to that.
>
>
>
>>begin work;
>>create table new_tab AS select * from tab;
>>truncate table tab;
>>insert into tab select * from new_tab;
>>drop table new_tab;
>>commit;
>>analyze tab;
>>
>>i havenot tried it but plan to do so.
>>but i feel insert would take ages to update
>>the indexes if any.
>>
>>
>
>It will have to update them, which will take time.
>
>
>
>>BTW
>>
>>is there any way to disable checks and triggers on
>>a table temporarily while loading data (is updating
>>reltriggers in pg_class safe?)
>>
>>
>
>You can take a look at pg_restore and copy how it does it.
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
2004-04-15 11:35 Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 13:36 ` Re: [ SOLVED ] select count(*) very slow on an already Bill Moran <wmoran@potentialtech.com>
2004-04-15 16:19 ` Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 17:44 ` Re: [ SOLVED ] select count(*) very slow on an already Richard Huxton <dev@archonet.com>
2004-04-16 08:23 ` Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-04-17 14:29 ` Jeff <threshar@torgo.978.org>
0 siblings, 0 replies; 12+ messages in thread
From: Jeff @ 2004-04-17 14:29 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Richard Huxton <dev@archonet.com>; Bill Moran <wmoran@potentialtech.com>; pgsql-performance
On Apr 16, 2004, at 4:23 AM, Rajesh Kumar Mallah wrote:
>
>
> I am running an update on the same table
>
> update rfis set inquiry_status='APPROVED' where inquiry_status='a';
>
> Its running for past 20 mins. and top output is below.
> The PID which is executing the query above is 6712. Can anyone
> tell me why it is in an uninterruptable sleep and does it relate
> to the apparent poor performance? Is it problem with the disk
> hardware. I know at nite this query will run reasonably fast.
>
I've had this problem recently. The problem is simply that the disk
cannot keep up. Most likely you don't see it at night because traffic
is lower. There are only 2 solutions: 1. get more disks 2. write to
the db less
The machine I was running on had a single(!) disk. It was a quad xeon
so there was plenty of cpu. I'd see 8-9 processes stuck in the "D"
state. Doing a simple ls -l somefile would take 10-15 seconds and of
course, db performance was abysmal.
I had a lowly P2 with a few disks in it that was able to run circles
around it for the simple fact the machine was not waiting for disk.
Again, proof that disk is far more important than CPU in a db.
good luck.
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
2004-04-15 11:35 Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 13:36 ` Re: [ SOLVED ] select count(*) very slow on an already Bill Moran <wmoran@potentialtech.com>
2004-04-15 16:19 ` Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-04-15 18:48 ` Bill Moran <wmoran@potentialtech.com>
1 sibling, 0 replies; 12+ messages in thread
From: Bill Moran @ 2004-04-15 18:48 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-performance
Rajesh Kumar Mallah wrote:
> Bill Moran wrote:
>
>> Rajesh Kumar Mallah wrote:
>>
>>> Hi,
>>>
>>> The problem was solved by reloading the Table.
>>> the query now takes only 3 seconds. But that is
>>> not a solution.
>>
>> If dropping/recreating the table improves things, then we can reasonably
>> assume that the table is pretty active with updates/inserts. Correct?
>
> Yes the table results from an import process and under goes lots
> of inserts and updates , but thats before the vacuum full operation.
> the table is not accessed during vacuum. What i want to know is
> is there any wat to automate the dumping and reload of a table
> individually. will the below be safe and effective:
The CLUSTER command I described is one way of doing this. It
essentially automates the task of copying the table, dropping
the old one, and recreating it.
>> If the data gets too fragmented, a vacuum may not be enough. Also, read
>> up on the recommendations _against_ vacuum full (recommending only using
>> vacuum on databases) With full, vacuum condenses the database, which may
>> actually hurt performance. A regular vacuum just fixes things up, and
>> may leave unused space lying around. However, this should apparently
>> achieve a balance between usage and vacuum. See the docs, they are much
>> better at describing this than I am.
>>
> i understand simultaneous vacuum and usage detoriates performance mostly.
> but this case is different.
Just want to make sure we're on the same page here. I'm not talking about
vacuuming simultaneous with anything. I'm simply saying that "vacuum full"
isn't always the best choice. You should probably only be doing "vacuum".
The reason and details for this are in the admin docs.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
@ 2004-04-15 19:24 Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>
2004-04-15 20:13 ` Re: [ SOLVED ] select count(*) very slow on an already Bill Moran <wmoran@potentialtech.com>
0 siblings, 1 reply; 12+ messages in thread
From: Shea,Dan [CIS] @ 2004-04-15 19:24 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
Bill, if you had alot of updates and deletions and wanted to optimize your
table, can you just issue the cluster command.
Will the cluster command rewrite the table without the obsolete data that a
vacuum flags or do you need to issue a vacuum first?
Dan.
-----Original Message-----
From: Bill Moran [mailto:wmoran@potentialtech.com]
Sent: Thursday, April 15, 2004 2:49 PM
To: Rajesh Kumar Mallah
Cc: Postgres Performance
Subject: Re: [PERFORM] [ SOLVED ] select count(*) very slow on an
already
Rajesh Kumar Mallah wrote:
> Bill Moran wrote:
>
>> Rajesh Kumar Mallah wrote:
>>
>>> Hi,
>>>
>>> The problem was solved by reloading the Table.
>>> the query now takes only 3 seconds. But that is
>>> not a solution.
>>
>> If dropping/recreating the table improves things, then we can reasonably
>> assume that the table is pretty active with updates/inserts. Correct?
>
> Yes the table results from an import process and under goes lots
> of inserts and updates , but thats before the vacuum full operation.
> the table is not accessed during vacuum. What i want to know is
> is there any wat to automate the dumping and reload of a table
> individually. will the below be safe and effective:
The CLUSTER command I described is one way of doing this. It
essentially automates the task of copying the table, dropping
the old one, and recreating it.
>> If the data gets too fragmented, a vacuum may not be enough. Also, read
>> up on the recommendations _against_ vacuum full (recommending only using
>> vacuum on databases) With full, vacuum condenses the database, which may
>> actually hurt performance. A regular vacuum just fixes things up, and
>> may leave unused space lying around. However, this should apparently
>> achieve a balance between usage and vacuum. See the docs, they are much
>> better at describing this than I am.
>>
> i understand simultaneous vacuum and usage detoriates performance mostly.
> but this case is different.
Just want to make sure we're on the same page here. I'm not talking about
vacuuming simultaneous with anything. I'm simply saying that "vacuum full"
isn't always the best choice. You should probably only be doing "vacuum".
The reason and details for this are in the admin docs.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 3: 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] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
2004-04-15 19:24 Re: [ SOLVED ] select count(*) very slow on an already Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>
@ 2004-04-15 20:13 ` Bill Moran <wmoran@potentialtech.com>
0 siblings, 0 replies; 12+ messages in thread
From: Bill Moran @ 2004-04-15 20:13 UTC (permalink / raw)
To: Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>; +Cc: pgsql-performance
Shea,Dan [CIS] wrote:
> Bill, if you had alot of updates and deletions and wanted to optimize your
> table, can you just issue the cluster command.
> Will the cluster command rewrite the table without the obsolete data that a
> vacuum flags or do you need to issue a vacuum first?
From the reference docs:
"During the cluster operation, a temporary copy of the table is created that
contains the table data in the index order. Temporary copies of each index
on the table are created as well. Therefore, you need free space on disk at
least equal to the sum of the table size and the index sizes.
"CLUSTER preserves GRANT, inheritance, index, foreign key, and other ancillary
information about the table.
"Because the optimizer records statistics about the ordering of tables, it is
advisable to run ANALYZE on the newly clustered table. Otherwise, the optimizer
may make poor choices of query plans."
The primary reason CLUSTER exists is to allow you to physically reorder a table
based on a key. This should provide a performance improvement if data with
the same key is accessed all at once. (i.e. if you do "SELECT * FROM table WHERE
key=5" and it returns 100 rows, those 100 rows are guaranteed to be all on the
same part of the disk after CLUSTER, thus a performance improvement should result.)
Updates and inserts will add data in the next available space in a table with no
regard for any keys, and _may_ require running all over the disk to retrieve
the data in the previous example query.
I doubt if CLUSTER is an end-all optimization tool. The specific reason I
suggested it was because the original poster was asking for an easier way to
drop/recreate a table (as prior experimentation had shown this to improve
performance) I can't think of anything easier than "CLUSTER <tablename> ON
<keyname>"
Since CLUSTER recreates the table, it implicitly removes the dead tuples.
However, it's going to be a LOT slower than vacuum, so if dead tuples are the
main problem, vacuum is still the way to go.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [ SOLVED ] select count(*) very slow on an already
@ 2004-04-16 13:40 Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>
0 siblings, 0 replies; 12+ messages in thread
From: Shea,Dan [CIS] @ 2004-04-16 13:40 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
Just a note, I was trying the cluster command and was short on space. I
figured I had enough space for the new table and index. It failed on me
twice.
The reason is that I noticed for the command to complete, it needed the
space of the new table and 2x the space of the new index.
It looks like it creates the new table, then a new index. Afterwards it
looked like it creates another index in the DB pgsql_tmp. So for me this is
an important consideration, since the new index size was about 7GB.
I had not anticipated the second index size so that is why it failed. I
ended up creating a link of pgsql_tmp to another parttion to successfully
complete.
Dan.
-----Original Message-----
From: Bill Moran [mailto:wmoran@potentialtech.com]
Sent: Thursday, April 15, 2004 4:14 PM
To: Shea,Dan [CIS]
Cc: Postgres Performance
Subject: Re: [PERFORM] [ SOLVED ] select count(*) very slow on an
already
Shea,Dan [CIS] wrote:
> Bill, if you had alot of updates and deletions and wanted to optimize your
> table, can you just issue the cluster command.
> Will the cluster command rewrite the table without the obsolete data that
a
> vacuum flags or do you need to issue a vacuum first?
From the reference docs:
"During the cluster operation, a temporary copy of the table is created that
contains the table data in the index order. Temporary copies of each index
on the table are created as well. Therefore, you need free space on disk at
least equal to the sum of the table size and the index sizes.
"CLUSTER preserves GRANT, inheritance, index, foreign key, and other
ancillary
information about the table.
"Because the optimizer records statistics about the ordering of tables, it
is
advisable to run ANALYZE on the newly clustered table. Otherwise, the
optimizer
may make poor choices of query plans."
The primary reason CLUSTER exists is to allow you to physically reorder a
table
based on a key. This should provide a performance improvement if data with
the same key is accessed all at once. (i.e. if you do "SELECT * FROM table
WHERE
key=5" and it returns 100 rows, those 100 rows are guaranteed to be all on
the
same part of the disk after CLUSTER, thus a performance improvement should
result.)
Updates and inserts will add data in the next available space in a table
with no
regard for any keys, and _may_ require running all over the disk to retrieve
the data in the previous example query.
I doubt if CLUSTER is an end-all optimization tool. The specific reason I
suggested it was because the original poster was asking for an easier way to
drop/recreate a table (as prior experimentation had shown this to improve
performance) I can't think of anything easier than "CLUSTER <tablename> ON
<keyname>"
Since CLUSTER recreates the table, it implicitly removes the dead tuples.
However, it's going to be a LOT slower than vacuum, so if dead tuples are
the
main problem, vacuum is still the way to go.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2004-04-17 14:29 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-15 11:35 Re: [ SOLVED ] select count(*) very slow on an already Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 10:59 ` pginfo <pginfo@t1.unisoftbg.com>
2004-04-15 13:36 ` Bill Moran <wmoran@potentialtech.com>
2004-04-15 16:19 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 17:44 ` Richard Huxton <dev@archonet.com>
2004-04-15 18:29 ` Mark Lubratt <mark.lubratt@indeq.com>
2004-04-16 08:23 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-17 14:29 ` Jeff <threshar@torgo.978.org>
2004-04-15 18:48 ` Bill Moran <wmoran@potentialtech.com>
2004-04-15 19:24 Re: [ SOLVED ] select count(*) very slow on an already Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>
2004-04-15 20:13 ` Bill Moran <wmoran@potentialtech.com>
2004-04-16 13:40 Re: [ SOLVED ] select count(*) very slow on an already Shea,Dan [CIS] <Dan.Shea@ec.gc.ca>
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