I have a situation where a VACUUM VERBOSE ANALYZE of table
loops on the table and its indexes.
There are inserts happening on the table while the vacuum
is going on. We sure that there are no other vacuums or
re-indexing going on.
>From the archives, the only similar case
http://archives.postgresql.org/pgsql-novice/2004-10/msg00104.php
had Tom suggesting to increase vacuum mem.
Our vacuum mem is very high.
The log is attached. Any insight would be helpful.
My ideas are to 1) turn off verbose (but then we can't
see the looping) 2) possibly use vacuum full so that
it locks the table and goes ideally quickly once through.
--elein
[EMAIL PROTECTED]
INFO: vacuuming "public.NCA_log_head"
INFO: index "NCA_log_head_pkey" now contains 2795382 row versions in 8178 pages
DETAIL: 0 index row versions were removed.
83 index pages have been deleted, 83 are currently reusable.
CPU 0.38s/0.42u sec elapsed 0.88 sec.
INFO: index "NCA_appliance_idx" now contains 2795384 row versions in 8062 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.25s/0.52u sec elapsed 0.81 sec.
INFO: index "NCA_log_attack_vuln_id_id" now contains 2795388 row versions in
6464 pages
DETAIL: 0 index row versions were removed.
65 index pages have been deleted, 65 are currently reusable.
CPU 0.17s/0.42u sec elapsed 0.60 sec.
INFO: index "NCA_log_dest_ip_idx" now contains 2795394 row versions in 9674
pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.19s/0.53u sec elapsed 0.72 sec.
INFO: index "NCA_log_head_host_id_idx" now contains 2795398 row versions in
8111 pages
DETAIL: 0 index row versions were removed.
47 index pages have been deleted, 47 are currently reusable.
CPU 0.21s/0.46u sec elapsed 0.68 sec.
INFO: index "NCA_log_os_id_idx" now contains 2795401 row versions in 8077 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.31s/0.49u sec elapsed 1.95 sec.
INFO: index "NCA_log_source_ip_idx" now contains 2795428 row versions in 9660
pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.45s/0.46u sec elapsed 4.99 sec.
INFO: index "NCA_log_time_stamp_idx" now contains 2795442 row versions in 8107
pages
DETAIL: 0 index row versions were removed.
78 index pages have been deleted, 78 are currently reusable.
CPU 0.28s/0.40u sec elapsed 0.69 sec.
INFO: index "NCA_log_network_id_idx" now contains 2795444 row versions in 8070
pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.47s/0.50u sec elapsed 9.28 sec.
-------------------
INFO: "NCA_log_head": removed 61 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "NCA_log_head_pkey" now contains 2795430 row versions in 8178 pages
DETAIL: 61 index row versions were removed.
83 index pages have been deleted, 83 are currently reusable.
CPU 0.58s/0.45u sec elapsed 14.60 sec.
INFO: index "NCA_appliance_idx" now contains 2795444 row versions in 8063 pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.70s/0.44u sec elapsed 15.30 sec.
INFO: index "NCA_log_attack_vuln_id_id" now contains 2795481 row versions in
6464 pages
DETAIL: 61 index row versions were removed.
65 index pages have been deleted, 65 are currently reusable.
CPU 0.83s/0.36u sec elapsed 14.88 sec.
INFO: index "NCA_log_dest_ip_idx" now contains 2795544 row versions in 9676
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.74s/0.55u sec elapsed 14.00 sec.
INFO: index "NCA_log_head_host_id_idx" now contains 2795604 row versions in
8111 pages
DETAIL: 61 index row versions were removed.
46 index pages have been deleted, 46 are currently reusable.
CPU 0.70s/0.61u sec elapsed 12.42 sec.
INFO: index "NCA_log_os_id_idx" now contains 2795636 row versions in 8077 pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.58s/0.43u sec elapsed 19.18 sec.
INFO: index "NCA_log_source_ip_idx" now contains 2795674 row versions in 9661
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.57s/0.48u sec elapsed 22.13 sec.
INFO: index "NCA_log_time_stamp_idx" now contains 2795695 row versions in 8107
pages
DETAIL: 61 index row versions were removed.
77 index pages have been deleted, 77 are currently reusable.
CPU 0.38s/0.43u sec elapsed 3.11 sec.
INFO: index "NCA_log_network_id_idx" now contains 2795696 row versions in 8071
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.35s/0.52u sec elapsed 0.97 sec.
-------------------
INFO: "NCA_log_head": removed 61 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "NCA_log_head_pkey" now contains 2795653 row versions in 8178 pages
DETAIL: 61 index row versions were removed.
82 index pages have been deleted, 82 are currently reusable.
CPU 0.43s/0.44u sec elapsed 12.00 sec.
INFO: index "NCA_appliance_idx" now contains 2795662 row versions in 8063 pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.65s/0.47u sec elapsed 38.77 sec.
INFO: index "NCA_log_attack_vuln_id_id" now contains 2795699 row versions in
6464 pages
DETAIL: 61 index row versions were removed.
65 index pages have been deleted, 64 are currently reusable.
CPU 0.51s/0.43u sec elapsed 18.40 sec.
INFO: index "NCA_log_dest_ip_idx" now contains 2795755 row versions in 9676
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.46s/0.51u sec elapsed 12.36 sec.
INFO: index "NCA_log_head_host_id_idx" now contains 2795774 row versions in
8111 pages
DETAIL: 61 index row versions were removed.
46 index pages have been deleted, 46 are currently reusable.
CPU 0.22s/0.51u sec elapsed 0.95 sec.
INFO: index "NCA_log_os_id_idx" now contains 2795778 row versions in 8077 pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.44s/0.55u sec elapsed 7.59 sec.
INFO: index "NCA_log_source_ip_idx" now contains 2795809 row versions in 9662
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.61s/0.45u sec elapsed 6.21 sec.
INFO: index "NCA_log_time_stamp_idx" now contains 2795853 row versions in 8107
pages
DETAIL: 61 index row versions were removed.
76 index pages have been deleted, 76 are currently reusable.
CPU 0.61s/0.49u sec elapsed 11.43 sec.
INFO: index "NCA_log_network_id_idx" now contains 2795861 row versions in 8072
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.64s/0.54u sec elapsed 12.87 sec.
-------------------
INFO: "NCA_log_head": removed 61 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 1.21 sec.
INFO: index "NCA_log_head_pkey" now contains 2795841 row versions in 8178 pages
DETAIL: 61 index row versions were removed.
81 index pages have been deleted, 81 are currently reusable.
CPU 0.42s/0.45u sec elapsed 2.49 sec.
INFO: index "NCA_appliance_idx" now contains 2795854 row versions in 8064 pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.47s/0.61u sec elapsed 12.67 sec.
INFO: index "NCA_log_attack_vuln_id_id" now contains 2795882 row versions in
6464 pages
DETAIL: 61 index row versions were removed.
64 index pages have been deleted, 63 are currently reusable.
CPU 0.40s/0.48u sec elapsed 7.50 sec.
INFO: index "NCA_log_dest_ip_idx" now contains 2795923 row versions in 9679
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.68s/0.51u sec elapsed 7.92 sec.
INFO: index "NCA_log_head_host_id_idx" now contains 2795962 row versions in
8111 pages
DETAIL: 61 index row versions were removed.
44 index pages have been deleted, 44 are currently reusable.
CPU 0.49s/0.43u sec elapsed 5.47 sec.
INFO: index "NCA_log_os_id_idx" now contains 2795982 row versions in 8080 pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.33s/0.54u sec elapsed 7.15 sec.
INFO: index "NCA_log_source_ip_idx" now contains 2796020 row versions in 9662
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.49s/0.44u sec elapsed 6.20 sec.
INFO: index "NCA_log_time_stamp_idx" now contains 2796089 row versions in 8107
pages
DETAIL: 61 index row versions were removed.
74 index pages have been deleted, 74 are currently reusable.
CPU 0.52s/0.48u sec elapsed 13.98 sec.
INFO: index "NCA_log_network_id_idx" now contains 2796092 row versions in 8073
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.36s/0.51u sec elapsed 1.50 sec.
-------------------
INFO: "NCA_log_head": removed 61 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "NCA_log_head_pkey" now contains 2796051 row versions in 8178 pages
DETAIL: 61 index row versions were removed.
80 index pages have been deleted, 80 are currently reusable.
CPU 0.42s/0.49u sec elapsed 7.04 sec.
INFO: index "NCA_appliance_idx" now contains 2796067 row versions in 8064 pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.40s/0.60u sec elapsed 7.93 sec.
INFO: index "NCA_log_attack_vuln_id_id" now contains 2796080 row versions in
6464 pages
DETAIL: 61 index row versions were removed.
64 index pages have been deleted, 63 are currently reusable.
CPU 0.37s/0.47u sec elapsed 8.25 sec.
INFO: index "NCA_log_dest_ip_idx" now contains 2796120 row versions in 9682
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.73s/0.43u sec elapsed 8.12 sec.
INFO: index "NCA_log_head_host_id_idx" now contains 2796168 row versions in
8111 pages
DETAIL: 61 index row versions were removed.
44 index pages have been deleted, 44 are currently reusable.
CPU 0.56s/0.48u sec elapsed 8.87 sec.
INFO: index "NCA_log_os_id_idx" now contains 2796193 row versions in 8082 pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.37s/0.52u sec elapsed 8.00 sec.
INFO: index "NCA_log_source_ip_idx" now contains 2796215 row versions in 9664
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.39s/0.43u sec elapsed 4.51 sec.
INFO: index "NCA_log_time_stamp_idx" now contains 2796293 row versions in 8107
pages
DETAIL: 61 index row versions were removed.
73 index pages have been deleted, 73 are currently reusable.
CPU 0.59s/0.44u sec elapsed 12.56 sec.
INFO: index "NCA_log_network_id_idx" now contains 2796311 row versions in 8074
pages
DETAIL: 61 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.40s/0.54u sec elapsed 6.95 sec.
-------------------
INFO: "NCA_log_head": removed 61 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "NCA_log_head_pkey" now contains 2796271 row versions in 8178 pages
DETAIL: 61 index row versions were removed.
80 index pages have been deleted, 79 are currently reusable.
CPU 0.34s/0.44u sec elapsed 1.82 sec.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
