Обсуждение: Is there any other way to do this?

Поиск
Список
Период
Сортировка

Is there any other way to do this?

От
Wei Weng
Дата:
Hi, I have a small table that has only 23 rows, but I do frequent updates(
every second ) on it.

After running the updates for a while, the performance of SELECT from that
table has deteriated into something like 30 seconds.

So, natually, I did a VACUUM ANALYZE first. Here is the VERBOSE output.

Test=> VACUUM VERBOSE analyze schedule ;
INFO:  vacuuming "public.schedule"
INFO:  index "schedule_pkey" now contains 23 row versions in 2519 pages
DETAIL:  2499 index pages have been deleted, 2499 are currently reusable.
CPU 0.27s/0.04u sec elapsed 12.49 sec.
INFO:  "schedule": found 0 removable, 23 nonremovable row versions in 37638
pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 974282 unused item pointers.
0 pages are entirely empty.
CPU 3.64s/0.48u sec elapsed 76.15 sec.
INFO:  vacuuming "pg_toast.pg_toast_22460"
INFO:  index "pg_toast_22460_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.03 sec.
INFO:  "pg_toast_22460": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.01s/0.00u sec elapsed 0.03 sec.
INFO:  analyzing "public.schedule"
INFO:  "schedule": 37638 pages, 23 rows sampled, 23 estimated total rows
VACUUM

And it didn't help at all. The explain of the query still shows up as:

Test=> explain select id from schedule;
                          QUERY PLAN
-------------------------------------------------------------
  Seq Scan on schedule  (cost=0.00..37638.23 rows=23 width=4)
(1 row)

It still takes 30 seconds to finish a simple query. ugh.

So I then tried VACUUM FULL schedule. Here is the output:

fazzt=> VACUUM FULL VERBOSE schedule ;
INFO:  vacuuming "public.schedule"
INFO:  "schedule": found 0 removable, 23 nonremovable row versions in 37638
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 253 to 418 bytes long.
There were 974282 unused item pointers.
Total free space (including removable row versions) is 303672968 bytes.
37629 pages are or will become empty, including 0 at the end of the table.
37638 pages containing 303672968 free bytes are potential move destinations.
CPU 3.08s/0.50u sec elapsed 28.64 sec.
INFO:  index "schedule_pkey" now contains 23 row versions in 2182 pages
DETAIL:  0 index row versions were removed.
2162 index pages have been deleted, 2162 are currently reusable.
CPU 0.28s/0.02u sec elapsed 10.90 sec.
INFO:  "schedule": moved 13 row versions, truncated 37638 to 1 pages
DETAIL:  CPU 10.83s/10.96u sec elapsed 370.42 sec.
INFO:  index "schedule_pkey" now contains 23 row versions in 2182 pages
DETAIL:  13 index row versions were removed.
2162 index pages have been deleted, 2162 are currently reusable.
CPU 0.20s/0.05u sec elapsed 10.33 sec.
INFO:  vacuuming "pg_toast.pg_toast_22460"
INFO:  "pg_toast_22460": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_22460_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM


This time it worked! But VACUUM FULL requires an exclusive lock on the table
which I don't really want to grant. So my question is: why is VACUUM ANALYZE
didn't do the job? Is there any setting I can tweak to make a VACUUM without
granting a exclusive lock?

Thanks!


Wei



Re: Is there any other way to do this?

От
"Steinar H. Gunderson"
Дата:
On Tue, May 17, 2005 at 06:58:20PM -0400, Wei Weng wrote:
> This time it worked! But VACUUM FULL requires an exclusive lock on the
> table which I don't really want to grant. So my question is: why is VACUUM
> ANALYZE didn't do the job? Is there any setting I can tweak to make a
> VACUUM without granting a exclusive lock?

You just didn't vacuum often enough. Plain VACUUM (with ANALYZE or not) only
deletes dead rows, it does not reclaim the space used for them (and thus does
not compress the remaining ones into fewer pages, so they take less time to
scan). If you simply VACUUM regularily (try autovacuum from contrib, it will
probably be useful) the problem simply will never be as bad as you describe
here, and you won't need to VACUUM FULL.

/* Steinar */
--
Homepage: http://www.sesse.net/

Re: Is there any other way to do this?

От
Christopher Kings-Lynne
Дата:
> This time it worked! But VACUUM FULL requires an exclusive lock on the
> table which I don't really want to grant. So my question is: why is
> VACUUM ANALYZE didn't do the job? Is there any setting I can tweak to
> make a VACUUM without granting a exclusive lock?

You need to run normal vacuum analyze every few minutes or so, to stop
it growing.  I suggest pg_autovacuum.

Chris