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]