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@xxxxxxxxxx
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
