pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedselect count(*) performance (vacuum did not help)
9+ messages / 6 participants
[nested] [flat]
* select count(*) performance (vacuum did not help)
@ 2007-09-24 14:07 Gábor Farkas <gabor@nekomancer.net>
2007-09-24 14:23 ` Re: select count(*) performance (vacuum did not help) Pavan Deolasee <pavan.deolasee@gmail.com>
2007-09-24 14:30 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 2 replies; 9+ messages in thread
From: Gábor Farkas @ 2007-09-24 14:07 UTC (permalink / raw)
To: pgsql-performance
hi,
i have the following table:
CREATE TABLE "main_activity" (
"id" serial NOT NULL PRIMARY KEY,
"user_id" integer NOT NULL,
"sessionid" varchar(128) NOT NULL,
"login" timestamp with time zone NOT NULL,
"activity" timestamp with time zone NOT NULL,
"logout" timestamp with time zone NULL
)
the problem is that it contains around 20000 entries, and a select
count(*) takes around 2 minutes. that's too slow.
some background info:
- this table has a lot of updates and inserts, it works very similarly
to a session-table for a web-application
- there is a cron-job that deletes all the old entries, so it's size is
rougly between 15000 and 35000 entries (it's run daily, and every day
deletes around 10000 entries)
- but in the past, the cron-job was not in place, so the table's size
grew to around 800000 entries (in around 80 days)
- then we removed the old entries, added the cronjob, vacuumed +
analyzed the table, and the count(*) is still slow
- the output of the vacuum+analyze is:
INFO: vacuuming "public.main_activity"
INFO: index "main_activity_pkey" now contains 11675 row versions in
57301 pages
DETAIL: 41001 index row versions were removed.
56521 index pages have been deleted, 20000 are currently reusable.
CPU 1.03s/0.27u sec elapsed 56.08 sec.
INFO: index "main_activity_user_id" now contains 11679 row versions in
41017 pages
DETAIL: 41001 index row versions were removed.
37736 index pages have been deleted, 20000 are currently reusable.
CPU 0.70s/0.42u sec elapsed 62.04 sec.
INFO: "main_activity": removed 41001 row versions in 4310 pages
DETAIL: CPU 0.15s/0.37u sec elapsed 20.48 sec.
INFO: "main_activity": found 41001 removable, 11672 nonremovable row
versions in 160888 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 14029978 unused item pointers.
0 pages are entirely empty.
CPU 5.53s/1.71u sec elapsed 227.35 sec.
INFO: analyzing "public.main_activity"
INFO: "main_activity": 160888 pages, 4500 rows sampled, 4594 estimated
total rows
(please note that the "4594 estimated total rows"... the row-count
should be around 15000)
- this is on postgresql 7.4.8 .yes, i know it's too old, and currently
we are preparing a migration to postgres8.1 (or 8.2, i'm not sure yet),
but for now i have to solve the problem on this database
thanks a lot,
gabor
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: select count(*) performance (vacuum did not help)
2007-09-24 14:07 select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
@ 2007-09-24 14:23 ` Pavan Deolasee <pavan.deolasee@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Pavan Deolasee @ 2007-09-24 14:23 UTC (permalink / raw)
To: Gábor Farkas <gabor@nekomancer.net>; +Cc: pgsql-performance
On 9/24/07, Gábor Farkas <gabor@nekomancer.net> wrote:
>
>
> INFO: "main_activity": found 41001 removable, 11672 nonremovable row
> versions in 160888 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 14029978 unused item pointers.
> 0 pages are entirely empty.
> CPU 5.53s/1.71u sec elapsed 227.35 sec.
> INFO: analyzing "public.main_activity"
> INFO: "main_activity": 160888 pages, 4500 rows sampled, 4594 estimated
> total rows
>
>
Looking at the number of rows vs number of pages, ISTM that VACUUM FULL
should help you.
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: select count(*) performance (vacuum did not help)
2007-09-24 14:07 select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
@ 2007-09-24 14:30 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:04 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
1 sibling, 1 reply; 9+ messages in thread
From: Heikki Linnakangas @ 2007-09-24 14:30 UTC (permalink / raw)
To: Gábor Farkas <gabor@nekomancer.net>; +Cc: pgsql-performance
Gábor Farkas wrote:
> - this table has a lot of updates and inserts, it works very similarly
> to a session-table for a web-application
Make sure you run VACUUM often enough.
> - there is a cron-job that deletes all the old entries, so it's size is
> rougly between 15000 and 35000 entries (it's run daily, and every day
> deletes around 10000 entries)
Running vacuum after these deletes to immediately reclaim the dead space
would also be a good idea.
> - but in the past, the cron-job was not in place, so the table's size
> grew to around 800000 entries (in around 80 days)
That bloated your table, so that there's still a lot of empty pages in
it. VACUUM FULL should bring it back to a reasonable size. Regular
normal non-FULL VACUUMs should keep it in shape after that.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: select count(*) performance (vacuum did not help)
2007-09-24 14:07 select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 14:30 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-09-24 15:04 ` Gábor Farkas <gabor@nekomancer.net>
2007-09-24 15:07 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 9+ messages in thread
From: Gábor Farkas @ 2007-09-24 15:04 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
Heikki Linnakangas wrote:
> Gábor Farkas wrote:
>> - but in the past, the cron-job was not in place, so the table's size
>> grew to around 800000 entries (in around 80 days)
>
> That bloated your table, so that there's still a lot of empty pages in
> it. VACUUM FULL should bring it back to a reasonable size. Regular
> normal non-FULL VACUUMs should keep it in shape after that.
>
hmm... can a full-vacuum be performed while the database is still "live"
(i mean serving requests)?
will the db still be able to respond to queries?
or in a different way:
if i do a full vacuum to that table only, will the database still serve
data from the other tables at a normal speed?
thanks,
gabor
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: select count(*) performance (vacuum did not help)
2007-09-24 14:07 select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 14:30 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:04 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
@ 2007-09-24 15:07 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:14 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
0 siblings, 1 reply; 9+ messages in thread
From: Heikki Linnakangas @ 2007-09-24 15:07 UTC (permalink / raw)
To: Gábor Farkas <gabor@nekomancer.net>; +Cc: pgsql-performance
Gábor Farkas wrote:
> hmm... can a full-vacuum be performed while the database is still "live"
> (i mean serving requests)?
>
> will the db still be able to respond to queries?
VACUUM FULL will exclusive lock the table, which means that other
queries accessing it will block and wait until it's finished.
> or in a different way:
>
> if i do a full vacuum to that table only, will the database still serve
> data from the other tables at a normal speed?
Yes. The extra I/O load vacuum full generates while it's running might
disrupt other activity, though.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: select count(*) performance (vacuum did not help)
2007-09-24 14:07 select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 14:30 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:04 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 15:07 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-09-24 15:14 ` Gábor Farkas <gabor@nekomancer.net>
2007-09-24 15:29 ` Re: select count(*) performance (vacuum did not help) Bill Moran <wmoran@collaborativefusion.com>
2007-09-24 15:34 ` Re: select count(*) performance (vacuum did not help) Csaba Nagy <nagy@ecircle-ag.com>
2007-09-24 15:37 ` Re: select count(*) performance (vacuum did not help) Dave Dutcher <dave@tridecap.com>
0 siblings, 3 replies; 9+ messages in thread
From: Gábor Farkas @ 2007-09-24 15:14 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
Heikki Linnakangas wrote:
> Gábor Farkas wrote:
>>
>> if i do a full vacuum to that table only, will the database still serve
>> data from the other tables at a normal speed?
>
> Yes. The extra I/O load vacuum full generates while it's running might
> disrupt other activity, though.
>
i see.
will i achieve the same thing by simply dropping that table and
re-creating it?
gabor
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: select count(*) performance (vacuum did not help)
2007-09-24 14:07 select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 14:30 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:04 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 15:07 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:14 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
@ 2007-09-24 15:29 ` Bill Moran <wmoran@collaborativefusion.com>
2 siblings, 0 replies; 9+ messages in thread
From: Bill Moran @ 2007-09-24 15:29 UTC (permalink / raw)
To: \"Gábor Farkas\" <gabor@nekomancer.net>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-performance
In response to "Gábor Farkas" <gabor@nekomancer.net>:
> Heikki Linnakangas wrote:
> > Gábor Farkas wrote:
> >>
> >> if i do a full vacuum to that table only, will the database still serve
> >> data from the other tables at a normal speed?
> >
> > Yes. The extra I/O load vacuum full generates while it's running might
> > disrupt other activity, though.
> >
>
> i see.
>
> will i achieve the same thing by simply dropping that table and
> re-creating it?
Yes. Once you've done so, keep up the vacuum schedule you've already
established. You may want to (as has already been suggested) explicitly
vacuum this table after large delete operations as well.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: select count(*) performance (vacuum did not help)
2007-09-24 14:07 select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 14:30 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:04 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 15:07 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:14 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
@ 2007-09-24 15:34 ` Csaba Nagy <nagy@ecircle-ag.com>
2 siblings, 0 replies; 9+ messages in thread
From: Csaba Nagy @ 2007-09-24 15:34 UTC (permalink / raw)
To: Gábor Farkas <gabor@nekomancer.net>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-performance
On Mon, 2007-09-24 at 17:14 +0200, Gábor Farkas wrote:
> will i achieve the same thing by simply dropping that table and
> re-creating it?
If you have an index/PK on that table, the fastest and most useful way
to rebuild it is to do CLUSTER on that index. That will be a lot faster
than VACUUM FULL and it will also order your table in index order... but
it will also lock it in exclusive mode just as VACUUM FULL would do it.
If your table has just a few live rows and lots of junk in it, CLUSTER
should be fast enough. With 20K entries I would expect it to be fast
enough not to be a problem...
Cheers,
Csaba.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: select count(*) performance (vacuum did not help)
2007-09-24 14:07 select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 14:30 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:04 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 15:07 ` Re: select count(*) performance (vacuum did not help) Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:14 ` Re: select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
@ 2007-09-24 15:37 ` Dave Dutcher <dave@tridecap.com>
2 siblings, 0 replies; 9+ messages in thread
From: Dave Dutcher @ 2007-09-24 15:37 UTC (permalink / raw)
To: 'Gábor Farkas' <gabor@nekomancer.net>; +Cc: pgsql-performance
> -----Original Message-----
> From: Gábor Farkas
>
>
> i see.
>
> will i achieve the same thing by simply dropping that table
> and re-creating it?
Yes. Or even easier (if you don't need the data anymore) you can use the
truncate command. Which deletes everything in the table including dead
rows.
Dave
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-09-24 15:37 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-24 14:07 select count(*) performance (vacuum did not help) Gábor Farkas <gabor@nekomancer.net>
2007-09-24 14:23 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2007-09-24 14:30 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:04 ` Gábor Farkas <gabor@nekomancer.net>
2007-09-24 15:07 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-24 15:14 ` Gábor Farkas <gabor@nekomancer.net>
2007-09-24 15:29 ` Bill Moran <wmoran@collaborativefusion.com>
2007-09-24 15:34 ` Csaba Nagy <nagy@ecircle-ag.com>
2007-09-24 15:37 ` Dave Dutcher <dave@tridecap.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