Thread: select count(*) very slow on an already vacuumed table.
Hi
I have .5 million rows in a table. My problem is select count(*) takes ages.
VACUUM FULL does not help. can anyone please tell me
how to i enhance the performance of the setup.
Regds
mallah.
postgresql.conf
----------------------
max_fsm_pages = 55099264 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 5000
tradein_clients=# explain analyze SELECT count(*) from eyp_rfi;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=78311.37..78311.37 rows=1 width=0) (actual time=42306.902..42306.903 rows=1 loops=1)
-> Seq Scan on eyp_rfi (cost=0.00..77046.49 rows=505949 width=0) (actual time=0.032..41525.007 rows=505960 loops=1)
Total runtime: 42306.995 ms
(3 rows)
tradein_clients=# SELECT count(*) from eyp_rfi;
count
--------
505960
(1 row)
tradein_clients=# 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.
Nonremovable row versions range from 186 to 2036 bytes long.
There were 42587 unused item pointers.
Total free space (including removable row versions) is 21413836 bytes.
0 pages are or will become empty, including 0 at the end of the table.
38693 pages containing 19146684 free bytes are potential move destinations.
CPU 2.62s/0.40u sec elapsed 38.45 sec.
INFO: index "eyp_rfi_date" now contains 505960 row versions in 1197 pages
DETAIL: 0 index row versions were removed.
4 index pages have been deleted, 4 are currently reusable.
CPU 0.05s/0.29u sec elapsed 0.87 sec.
INFO: index "eyp_rfi_receiver_uid" now contains 505960 row versions in 1163 pages
DETAIL: 0 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.03s/0.42u sec elapsed 1.33 sec.
INFO: index "eyp_rfi_inhouse" now contains 505960 row versions in 1208 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.04s/0.21u sec elapsed 1.20 sec.
INFO: index "eyp_rfi_rfi_id_key" now contains 505960 row versions in 1201 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.33u sec elapsed 0.81 sec.
INFO: index "eyp_rfi_list_id_idx" now contains 505960 row versions in 1133 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.43u sec elapsed 1.12 sec.
INFO: index "eyp_rfi_status" now contains 505960 row versions in 1448 pages
DETAIL: 0 index row versions were removed.
4 index pages have been deleted, 4 are currently reusable.
CPU 0.05s/0.22u sec elapsed 1.08 sec.
INFO: index "eyp_rfi_list_id" now contains 505960 row versions in 1133 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.43u sec elapsed 1.00 sec.
INFO: index "eyp_rfi_receiver_email" now contains 505960 row versions in 2801 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.16s/0.52u sec elapsed 10.38 sec.
INFO: index "eyp_rfi_subj" now contains 80663 row versions in 463 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.14u sec elapsed 3.20 sec.
INFO: index "eyp_rfi_sender" now contains 505960 row versions in 3025 pages
DETAIL: 0 index row versions were removed.
6 index pages have been deleted, 6 are currently reusable.
CPU 0.10s/0.39u sec elapsed 4.99 sec.
INFO: index "eyp_sender_uid_idx" now contains 505960 row versions in 1216 pages
DETAIL: 0 index row versions were removed.
5 index pages have been deleted, 5 are currently reusable.
CPU 0.04s/0.36u sec elapsed 2.61 sec.
INFO: index "eyp_rfi_rec_uid_idx" now contains 505960 row versions in 1166 pages
DETAIL: 0 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.05s/0.41u sec elapsed 2.04 sec.
INFO: index "eyp_rfi_index" now contains 505960 row versions in 2051 pages
DETAIL: 0 index row versions were removed.
7 index pages have been deleted, 7 are currently reusable.
CPU 0.10s/0.28u sec elapsed 8.16 sec.
INFO: "eyp_rfi": moved 0 row versions, truncated 71987 to 71987 pages
DETAIL: CPU 2.03s/2.09u sec elapsed 95.24 sec.
INFO: vacuuming "pg_toast.pg_toast_19609"
INFO: "pg_toast_19609": found 0 removable, 105342 nonremovable row versions in 21038 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2034 bytes long.
There were 145 unused item pointers.
Total free space (including removable row versions) is 16551072 bytes.
0 pages are or will become empty, including 0 at the end of the table.
18789 pages containing 16512800 free bytes are potential move destinations.
CPU 0.70s/0.09u sec elapsed 41.64 sec.
INFO: index "pg_toast_19609_index" now contains 105342 row versions in 296 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.02u sec elapsed 0.63 sec.
INFO: "pg_toast_19609": moved 0 row versions, truncated 21038 to 21038 pages
DETAIL: CPU 0.01s/0.01u sec elapsed 10.03 sec.
VACUUM
tradein_clients=# explain analyze SELECT count(*) from eyp_rfi;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=78311.50..78311.50 rows=1 width=0) (actual time=50631.488..50631.489 rows=1 loops=1)
-> Seq Scan on eyp_rfi (cost=0.00..77046.60 rows=505960 width=0) (actual time=0.030..49906.198 rows=505964 loops=1)
Total runtime: 50631.658 ms
(3 rows)
On Wednesday 14 April 2004 18:53, Rajesh Kumar Mallah wrote: > Hi > I have .5 million rows in a table. My problem is select count(*) takes > ages. VACUUM FULL does not help. can anyone please tell me > how to i enhance the performance of the setup. > SELECT count(*) from eyp_rfi; If this is the actual query you're running, and you need a guaranteed accurate result, then you only have one option: write a trigger function to update a table_count table with every insert/delete to eyp_rfi. There is loads of info on this (and why it isn't as simple as you might think) in the archives. First though: 1. Is this the actual query, or just a representation? 2. Do you need an accurate figure or just something "near enough"? -- Richard Huxton Archonet Ltd
Richard Huxton wrote: >On Wednesday 14 April 2004 18:53, Rajesh Kumar Mallah wrote: > > >>Hi >>I have .5 million rows in a table. My problem is select count(*) takes >>ages. VACUUM FULL does not help. can anyone please tell me >>how to i enhance the performance of the setup. >> >> > > > >>SELECT count(*) from eyp_rfi; >> >> > >If this is the actual query you're running, and you need a guaranteed accurate >result, then you only have one option: write a trigger function to update a >table_count table with every insert/delete to eyp_rfi. > > it is just an example. in general all the queries that involves eyp_rfi become slow. reloading the table makes the query faster. mallah. >There is loads of info on this (and why it isn't as simple as you might think) >in the archives. First though: >1. Is this the actual query, or just a representation? >2. Do you need an accurate figure or just something "near enough"? > > >
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; +--------+ | count | +--------+ | 564870 | +--------+ 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 What kind of upgrades shoud be put on the server for it to become reasonable fast. Regds mallah. Richard Huxton wrote: >On Wednesday 14 April 2004 18:53, Rajesh Kumar Mallah wrote: > > >>Hi >>I have .5 million rows in a table. My problem is select count(*) takes >>ages. VACUUM FULL does not help. can anyone please tell me >>how to i enhance the performance of the setup. >> >> > > > >>SELECT count(*) from eyp_rfi; >> >> > >If this is the actual query you're running, and you need a guaranteed accurate >result, then you only have one option: write a trigger function to update a >table_count table with every insert/delete to eyp_rfi. > >There is loads of info on this (and why it isn't as simple as you might think) >in the archives. First though: >1. Is this the actual query, or just a representation? >2. Do you need an accurate figure or just something "near enough"? > > >
The relation size for this table is 1.7 GB
tradein_clients=# SELECT public.relation_size ('general.rfis');
+------------------+
| relation_size |
+------------------+
| 1,762,639,872 |
+------------------+
(1 row)
Regds
mallah.
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;
> +--------+
> | count |
> +--------+
> | 564870 |
> +--------+
> 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
>
> What kind of upgrades shoud be put on the server for it to become
> reasonable fast.
>
>
> Regds
> mallah.
>
>
>
>
> Richard Huxton wrote:
>
>> On Wednesday 14 April 2004 18:53, Rajesh Kumar Mallah wrote:
>>
>>
>>> Hi
>>> I have .5 million rows in a table. My problem is select count(*) takes
>>> ages. VACUUM FULL does not help. can anyone please tell me
>>> how to i enhance the performance of the setup.
>>>
>>
>>
>>
>>
>>> SELECT count(*) from eyp_rfi;
>>>
>>
>>
>> If this is the actual query you're running, and you need a guaranteed
>> accurate result, then you only have one option: write a trigger
>> function to update a table_count table with every insert/delete to
>> eyp_rfi.
>>
>> There is loads of info on this (and why it isn't as simple as you
>> might think) in the archives. First though:
>> 1. Is this the actual query, or just a representation?
>> 2. Do you need an accurate figure or just something "near enough"?
>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
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. -- Richard Huxton Archonet Ltd
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. > > Hi, Thanks for the interest . my config are not the default ones. i was running iostat while running the query. Looks like one of the disks doesnt' go past a read performance of 20 ,000 KBytes/sec while the other disk it goes as high as 40,000 . What i am ding currently is loading the table in both the disks and compare the table scan speeds. The performance is definitely better in the newly loaded table in the other disk . the load in server is 13 because i am simultaneously re-loading the data in other table. rt2=# SELECT count(*) from rfis where sender_uid > 0; +--------+ | count | +--------+ | 564870 | +--------+ (1 row) Time: 10288.359 ms rt2=# shall post the comparitive details under normal load soon regds mallah. > > >>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. > > >
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. > > >
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
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
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. >> > > >
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
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
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
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.
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/