pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedselect count(*) very slow on an already vacuumed table.
7+ messages / 2 participants
[nested] [flat]
* select count(*) very slow on an already vacuumed table.
@ 2004-04-14 17:53 Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rajesh Kumar Mallah @ 2004-04-14 17:53 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select count(*) very slow on an already vacuumed table.
@ 2004-04-14 19:08 Richard Huxton <dev@archonet.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 2 replies; 7+ messages in thread
From: Richard Huxton @ 2004-04-14 19:08 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select count(*) very slow on an already vacuumed table.
@ 2004-04-14 20:43 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rajesh Kumar Mallah @ 2004-04-14 20:43 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
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"?
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select count(*) very slow on an already vacuumed table.
@ 2004-04-15 07:10 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 2 replies; 7+ messages in thread
From: Rajesh Kumar Mallah @ 2004-04-15 07:10 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
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"?
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select count(*) very slow on an already vacuumed table.
@ 2004-04-15 07:34 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rajesh Kumar Mallah @ 2004-04-15 07:34 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
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)
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select count(*) very slow on an already vacuumed table.
@ 2004-04-15 07:53 Richard Huxton <dev@archonet.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2004-04-15 07:53 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: select count(*) very slow on an already vacuumed table.
@ 2004-04-15 10:31 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rajesh Kumar Mallah @ 2004-04-15 10:31 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
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.
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-04-15 10:31 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-14 17:53 select count(*) very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-14 19:08 ` Richard Huxton <dev@archonet.com>
2004-04-14 20:43 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 07:10 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 07:34 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 07:53 ` Richard Huxton <dev@archonet.com>
2004-04-15 10:31 ` Rajesh Kumar Mallah <mallah@trade-india.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