Message-ID: <51ACC815.1000209@sesda3.com>
Date: Mon, 03 Jun 2013 12:45:09 -0400
From: Alex Lai <mlai@sesda3.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:17.0) Gecko/20130514 Thunderbird/17.0.6
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: VACUUM does not update pg_catalog.pg_stat_all_tables views
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

I have an issue on VACUUM one of a table with 45 million rows with 6 
indexes.  Somehow vacuum others large table without problem.
This is the table definition:

      Column     |            Type             | 
Modifiers                  | Storage  | Stats target | Description
----------------+-----------------------------+--------------------------------------------+----------+--------------+-------------
  fileid         | integer                     | not 
null                                   | plain    |              |
  filetype       | character varying           | not 
null                                   | extended |              |
  filename       | character varying(255)      | not 
null                                   | extended |              |
  filesizebytes  | bigint                      | not 
null                                   | plain    |              |
  compressedsize | bigint                      | not null default 
(0)::bigint               | plain    |              |
  ingesttime     | timestamp without time zone | not null default 
now()                     | plain    |              |
  archivemethod  | character varying(30)       | not null default 
'Copy'::character varying | extended |              |
  md5            | character(32)               | not 
null                                   | extended |              |
Indexes:
     "pk_file" PRIMARY KEY, btree (fileid)
     "uk_file_filename" UNIQUE, btree (filename)
     "ak_file_filename" btree (filename)
     "ak_file_filename_varchar" btree (filename varchar_pattern_ops)
     "ak_file_filetype" btree (filetype)
     "ak_file_ingesttime" btree (ingesttime)
vacuum does not update pg_catalog.pg_stat_all_tables for one of the 
large table "file".  All other tables updated last_autovacuum and reset 
n_dead_tup without problem.

Before vacuum
=============

select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum 
from pg_catalog.pg_stat_all_tables where relname = 'file' and schemaname 
= 'public';
  relname | n_live_tup | n_dead_tup |          last_vacuum          | 
last_autovacuum
---------+------------+------------+-------------------------------+-----------------
  file    |   47424031 |       4662 | 2013-05-31 11:43:14.681605-04 |
(1 row)

omiops=# vacuum analyze verbose file;
INFO:  vacuuming "public.file"
INFO:  scanned index "pk_file" to remove 33 row versions
DETAIL:  CPU 1.30s/6.59u sec elapsed 36.60 sec.
INFO:  scanned index "ak_file_filename" to remove 33 row versions
DETAIL:  CPU 4.95s/7.01u sec elapsed 63.91 sec.
INFO:  scanned index "ak_file_filename_varchar" to remove 33 row versions
DETAIL:  CPU 4.88s/6.90u sec elapsed 55.07 sec.
INFO:  scanned index "ak_file_filetype" to remove 33 row versions
DETAIL:  CPU 1.36s/5.16u sec elapsed 31.19 sec.
INFO:  scanned index "ak_file_ingesttime" to remove 33 row versions
DETAIL:  CPU 1.02s/3.84u sec elapsed 25.52 sec.
INFO:  scanned index "uk_file_filename" to remove 33 row versions
DETAIL:  CPU 5.07s/6.68u sec elapsed 61.60 sec.
INFO:  "file": removed 33 row versions in 20 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.08 sec.
INFO:  index "pk_file" now contains 49832372 row versions in 138054 pages
DETAIL:  10 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ak_file_filename" now contains 49832388 row versions in 
508815 pages
DETAIL:  27 index row versions were removed.
3 index pages have been deleted, 3 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.13 sec.
INFO:  index "ak_file_filename_varchar" now contains 49832400 row 
versions in 508138 pages
DETAIL:  27 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.14 sec.
INFO:  index "ak_file_filetype" now contains 49832421 row versions in 
157031 pages
DETAIL:  33 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO:  index "ak_file_ingesttime" now contains 49832451 row versions in 
137147 pages
DETAIL:  7 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "uk_file_filename" now contains 49832488 row versions in 
508805 pages
DETAIL:  33 index row versions were removed.
3 index pages have been deleted, 3 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.11 sec.
INFO:  "file": found 22 removable, 251831 nonremovable row versions in 
5026 out of 981495 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 251 unused item pointers.
0 pages are entirely empty.
CPU 18.68s/36.36u sec elapsed 278.45 sec.
INFO:  vacuuming "pg_toast.pg_toast_20603"
INFO:  index "pg_toast_20603_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_20603": found 0 removable, 0 nonremovable row versions 
in 0 out of 0 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.
VACUUM

After vacuum
============

select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum 
from pg_catalog.pg_stat_all_tables where relname = 'file' and schemaname 
= 'public';
  relname | n_live_tup | n_dead_tup |          last_vacuum          | 
last_autovacuum
---------+------------+------------+-------------------------------+-----------------
  file    |   47424031 |       4662 | 2013-05-31 11:43:14.681605-04 |
(1 row)

I did not see any vacuum error in postgres log.
I am not sure what I miss that cause the pg_catalog.pg_stat_all_tables 
not update last_autovacuum and reset n_dead_tup columns.
Any help will be greatly appreciated.

-- 
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
mlai@sesda3.com


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin