Обсуждение: select count(*) performance (vacuum did not help)

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

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

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

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

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

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

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.

> -----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