VACUUM does not update pg_catalog.pg_stat_all_tables views
От | Alex Lai |
---|---|
Тема | VACUUM does not update pg_catalog.pg_stat_all_tables views |
Дата | |
Msg-id | 51ACC815.1000209@sesda3.com обсуждение исходный текст |
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: