Re: Need to run CLUSTER to keep performance

Tom Lane wrote:
> Rafael Martinez <r.m.guerrero@usit.uio.no> writes:
>> Heikki Linnakangas wrote:
>>> On a small table like that you could run VACUUM every few minutes
>>> without much impact on performance. That should keep the table size in
>>> check.
>
>> Ok, we run VACUUM ANALYZE only one time a day, every night.
>
> There's your problem.
>
> Reading between the lines I gather that you think an update is "free"
> in the sense of not creating a need for vacuum.  It's not --- it's
> exactly equivalent to an insert + a delete, and it leaves behind a
> dead row that needs to be vacuumed.  If you do a lot of updates, you
> need to vacuum.
>

Hello again

We have more information about this 'problem'.

Tom, we have many other tables which are much bigger and have larger
amount of updates/deletes and are working very well with our actual
vacuum configuration. We are aware of how important is to run vacuum
jobs and we think we have a good understanding of how/why vacuum works.

We think the problem we are seeing sometimes with these small tables is
another thing.

We increased the vacuum analyze jobs, as you all pointed, from one a day
to four every hour (we did not run cluster at all since we started with
this new configuration). We started with this after a fresh 'cluster' of
the table. This has been in production since last week and the
performance of this table only gets worst and worst.

After 4 days with the new maintenance jobs, it took more than 4 sec to
run a select on this table. After running a cluster we are down to
around 50ms. again.

I can not believe 4 vacuum jobs every hour is not enough for this table.
If we see the statistics, it has only ca.67000 updates/day, ca.43
deletes/day and ca.48 inserts/day. This is nothing compare with many of
the systems we are administrating.

What we see in common between these tables (we have seen this a couple
of times before) is:

- Small table size.
- Small amount of tuples in the table (almost constant).
- Large amount of updates compared to inserts/deletes and compared to
the amount of tuples in the table.

You that know the interns of postgres :), can you think of anything that
can be causing this behavior? Any more suggestions? do you need more data?

Thanks in advance :)

We are sending all data we had before the last cluster command and after
it.

----------------------------------------------------------------------
**** BEFORE CLUSTER ****
----------------------------------------------------------------------
INFO:  vacuuming "public.hosts"
INFO:  index "hosts_pkey" now contains 99933 row versions in 558 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "hosts": found 0 removable, 99933 nonremovable row versions in
3875 pages
DETAIL:  83623 dead row versions cannot be removed yet.
There were 12079 unused item pointers.
0 pages are entirely empty.
CPU 0.02s/0.03u sec elapsed 0.06 sec.
INFO:  vacuuming "pg_toast.pg_toast_376272"
INFO:  index "pg_toast_376272_index" now contains 133 row versions in 2
pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_376272": found 0 removable, 133 nonremovable row
versions in 65 pages
DETAIL:  2 dead row versions cannot be removed yet.
There were 127 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.hosts"
INFO:  "hosts": scanned 3875 of 3875 pages, containing 16310 live rows
and 83623 dead rows; 16310 rows in sample, 16310 estimated total rows


scanorama=# SELECT age(now(), pg_postmaster_start_time());
           age
-------------------------
 25 days 22:40:01.241036
(1 row)

scanorama=# SELECT pg_size_pretty(pg_relation_size('hosts'));
 pg_size_pretty
----------------
 30 MB
(1 row)

scanorama=# SELECT count(*) from hosts;
 count
-------
 16311
(1 row)

scanorama=# SELECT
relname,relpages,reltuples,reltoastrelid,reltoastidxid from pg_class
where relname = 'hosts';
 relname | relpages | reltuples | reltoastrelid | reltoastidxid
---------+----------+-----------+---------------+---------------
 hosts   |     3875 |    100386 |        376276 |             0
(1 row)

scanorama=# SELECT * from pg_stat_all_tables where schemaname = 'public'
and relname = 'hosts';
 relid  | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del

--------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------
 105805 | public     | hosts   |  2412159 |  39109243131 |  3244406 |
    9870886 |      1208 |   1685525 |      1088
(1 row)

scanorama=# EXPLAIN ANALYZE SELECT * from hosts;
                                                   QUERY PLAN

----------------------------------------------------------------------------------------------------------------
 Seq Scan on hosts  (cost=0.00..4878.86 rows=100386 width=314) (actual
time=0.025..4719.082 rows=16311 loops=1)
 Total runtime: 4742.754 ms
(2 rows)


scanorama=# CLUSTER hosts_pkey ON hosts ;
CLUSTER

----------------------------------------------------------------------
**** AFTER CLUSTER ****
----------------------------------------------------------------------

scanorama=# VACUUM VERBOSE ANALYZE hosts;

INFO:  vacuuming "public.hosts"
INFO:  index "hosts_pkey" now contains 16321 row versions in 65 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "hosts": found 0 removable, 16321 nonremovable row versions in
514 pages
DETAIL:  10 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_383759"
INFO:  index "pg_toast_383759_index" now contains 131 row versions in 2
pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_383759": found 0 removable, 131 nonremovable row
versions in 33 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.hosts"
INFO:  "hosts": scanned 514 of 514 pages, containing 16311 live rows and
10 dead rows; 16311 rows in sample, 16311 estimated total rows
VACUUM


scanorama=# SELECT pg_size_pretty(pg_relation_size('hosts'));
 pg_size_pretty
----------------
 4112 kB
(1 row)

scanorama=# SELECT count(*) from hosts;
 count
-------
 16311
(1 row)

scanorama=#  SELECT
relname,relpages,reltuples,reltoastrelid,reltoastidxid from pg_class
where relname = 'hosts';
 relname | relpages | reltuples | reltoastrelid | reltoastidxid
---------+----------+-----------+---------------+---------------
 hosts   |      514 |     16321 |        383763 |             0
(1 row)

scanorama=# SELECT * from pg_stat_all_tables where schemaname = 'public'
and relname = 'hosts';
 relid  | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del

--------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------
 105805 | public     | hosts   |  2412669 |  39117480187 |  3244962 |
    9887752 |      1208 |   1685857 |      1088
(1 row)

scanorama=# EXPLAIN ANALYZE SELECT * from hosts;
                                                 QUERY PLAN

------------------------------------------------------------------------------------------------------------
 Seq Scan on hosts  (cost=0.00..678.53 rows=16353 width=314) (actual
time=0.006..32.143 rows=16311 loops=1)
 Total runtime: 57.408 ms
(2 rows)
----------------------------------------------------------------------


--
 Rafael Martinez, <r.m.guerrero@usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/