pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Vacuum related question 3+ messages / 3 participants [nested] [flat]
* Vacuum related question @ 2004-09-16 16:17 G. Thomas Yagel, Jr. <tyagel@yahoo.com> 2004-09-18 16:06 ` Re: Vacuum related question Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: G. Thomas Yagel, Jr. @ 2004-09-16 16:17 UTC (permalink / raw) To: pgsql-general I have a postgresql database into which I inserted about 40 million records over a 12 hour period. I did not perform any updates or deleletes on the records. After 12 hours I stopped inserting recods and started a "vacuum verbose". The vacuuming has taken almost 2 hours now and is not even finished. What I don't understand is why there is so much vacuuming to be done on some tables that are only being inserted into. As an example, I have pasted the output from the vacuuming of the pg_largeobject table. Can someone look at this output and tell me why vacuum seems to churn through that table so much and how there could have been 18 million removeable row versions (that number is consistent with how many large objects should be in that table)? Does this have to do with how the indexing algorithm works? Thanks, Thomas INFO: vacuuming "pg_catalog.pg_largeobject" INFO: index "pg_largeobject_loid_pn_index" now contains 38246053 row versions in 153134 pages DETAIL: 1397769 index row versions were removed. 256 index pages have been deleted, 3 are currently reusable. CPU 8.57s/13.76u sec elapsed 255.06 sec. INFO: "pg_largeobject": removed 1397769 row versions in 167552 pages DETAIL: CPU 10.46s/13.97u sec elapsed 133.29 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 36848275 row versions in 153134 pages DETAIL: 1397778 index row versions were removed. 967 index pages have been deleted, 3 are currently reusable. CPU 7.40s/12.99u sec elapsed 169.96 sec. INFO: "pg_largeobject": removed 1397778 row versions in 143887 pages DETAIL: CPU 8.80s/11.92u sec elapsed 103.85 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 35450514 row versions in 153134 pages DETAIL: 1397761 index row versions were removed. 1125 index pages have been deleted, 3 are currently reusable. CPU 7.10s/12.73u sec elapsed 148.63 sec. INFO: "pg_largeobject": removed 1397761 row versions in 156819 pages DETAIL: CPU 8.99s/13.32u sec elapsed 104.23 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 34052746 row versions in 153134 pages DETAIL: 1397768 index row versions were removed. 1226 index pages have been deleted, 3 are currently reusable. CPU 6.99s/12.44u sec elapsed 143.04 sec. INFO: "pg_largeobject": removed 1397768 row versions in 158270 pages DETAIL: CPU 8.78s/13.46u sec elapsed 107.82 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 32654985 row versions in 153134 pages DETAIL: 1397761 index row versions were removed. 1232 index pages have been deleted, 3 are currently reusable. CPU 7.27s/11.77u sec elapsed 142.46 sec. INFO: "pg_largeobject": removed 1397761 row versions in 160051 pages DETAIL: CPU 9.43s/13.19u sec elapsed 116.89 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 31257224 row versions in 153134 pages DETAIL: 1397761 index row versions were removed. 1244 index pages have been deleted, 3 are currently reusable. CPU 7.80s/11.55u sec elapsed 143.49 sec. INFO: "pg_largeobject": removed 1397761 row versions in 159893 pages DETAIL: CPU 9.05s/13.31u sec elapsed 110.53 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 29859458 row versions in 153134 pages DETAIL: 1397766 index row versions were removed. 1297 index pages have been deleted, 3 are currently reusable. CPU 7.29s/11.29u sec elapsed 184.59 sec. INFO: "pg_largeobject": removed 1397766 row versions in 158989 pages DETAIL: CPU 9.07s/13.15u sec elapsed 111.66 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 28461693 row versions in 153134 pages DETAIL: 1397765 index row versions were removed. 1320 index pages have been deleted, 3 are currently reusable. CPU 7.03s/11.01u sec elapsed 153.20 sec. INFO: "pg_largeobject": removed 1397765 row versions in 159645 pages DETAIL: CPU 9.24s/13.24u sec elapsed 116.00 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 27063928 row versions in 153134 pages DETAIL: 1397765 index row versions were removed. 1333 index pages have been deleted, 3 are currently reusable. CPU 7.69s/10.17u sec elapsed 186.98 sec. INFO: "pg_largeobject": removed 1397765 row versions in 159885 pages DETAIL: CPU 9.27s/12.97u sec elapsed 116.60 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 25666165 row versions in 153134 pages DETAIL: 1397763 index row versions were removed. 1338 index pages have been deleted, 3 are currently reusable. CPU 7.19s/9.85u sec elapsed 181.75 sec. INFO: "pg_largeobject": removed 1397763 row versions in 160120 pages DETAIL: CPU 9.21s/13.14u sec elapsed 117.31 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 24268402 row versions in 153134 pages DETAIL: 1397763 index row versions were removed. 1357 index pages have been deleted, 3 are currently reusable. CPU 7.52s/9.49u sec elapsed 145.47 sec. INFO: "pg_largeobject": removed 1397763 row versions in 159804 pages DETAIL: CPU 8.51s/13.00u sec elapsed 114.38 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 22870636 row versions in 153134 pages DETAIL: 1397766 index row versions were removed. 1398 index pages have been deleted, 3 are currently reusable. CPU 7.72s/9.15u sec elapsed 133.68 sec. INFO: "pg_largeobject": removed 1397766 row versions in 159370 pages DETAIL: CPU 9.04s/13.13u sec elapsed 124.29 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 21472870 row versions in 153134 pages DETAIL: 1397766 index row versions were removed. 1426 index pages have been deleted, 3 are currently reusable. CPU 7.13s/8.80u sec elapsed 168.96 sec. INFO: "pg_largeobject": removed 1397766 row versions in 159594 pages DETAIL: CPU 9.48s/13.57u sec elapsed 126.71 sec. INFO: index "pg_largeobject_loid_pn_index" now contains 21326336 row versions in 153134 pages DETAIL: 146534 index row versions were removed. 1426 index pages have been deleted, 3 are currently reusable. CPU 7.25s/5.32u sec elapsed 130.94 sec. INFO: "pg_largeobject": removed 146534 row versions in 16784 pages DETAIL: CPU 0.86s/1.32u sec elapsed 12.44 sec. INFO: "pg_largeobject": found 18317486 removable, 21326336 nonremovable row versions in 2119638 pages DETAIL: 0 dead row versions cannot be removed yet. There were 355487 unused item pointers. 0 pages are entirely empty. CPU 301.74s/343.24u sec elapsed 4871.43 sec. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Vacuum related question 2004-09-16 16:17 Vacuum related question G. Thomas Yagel, Jr. <tyagel@yahoo.com> @ 2004-09-18 16:06 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2004-09-18 16:06 UTC (permalink / raw) To: tyagel@yahoo.com; +Cc: pgsql-general "G. Thomas Yagel, Jr." <tyagel@yahoo.com> writes: > After 12 hours I stopped inserting recods and started a "vacuum verbose". > The vacuuming has taken almost 2 hours now and is not even finished. What I > don't understand is why there is so much vacuuming to be done on some tables > that are only being inserted into. As an example, I have pasted the output > from the vacuuming of the pg_largeobject table. You may think you've only inserted, but this output says differently: > INFO: "pg_largeobject": found 18317486 removable, 21326336 nonremovable row > versions in 2119638 pages Those 18 million dead rows didn't appear out of nowhere; they could only have come from deleting or updating large objects (BLOBs). The reason for the multiple index-cleaning cycles is that vacuum_mem was only high enough to remember about 1.4 million dead rows at a time. You could have sped up the process by setting vacuum_mem higher. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* Vacuum related question @ 2025-03-07 11:16 Bartosz Stalewski <bartekstalewski@gmail.com> 0 siblings, 0 replies; 3+ messages in thread From: Bartosz Stalewski @ 2025-03-07 11:16 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org Hello All! I'm currently using 17.2 version and working on optimizing vacuums. I would be greateful if you could confirm (or deny) my idea. Current situation: Every 4-5 days vacuum to prevent wraparound is running in my DB, which of course is bad. There are a few tables that are constantly being updated (both inserts and updates) - they are quite big - ranging from a few hundred million to over a billion rows. Other tables get only inserts (with similar size - they may reach over a billion rows). Solution: I would like to have a quick vacuum to freeze old tuples - it would be quick because it would omit indexes (because switch INDEX_CLEANUP would be set as false). Additionally for tables with dead tuples I would run vacuum with INDEX_CLEANUP set as true during weekend. Omitting indexes makes a huge difference in terms of vacuum time. Would it be a problem from performance point of view (e.g. accumulating some bloat?) if tables that do not contain dead tuples are vacuumed only with flag INDEX_CLEANUP set as false? I have a feeling that it is fine, but unfortunately I couldn't find any clear evidence when browsing Internet. Thanks in advance, Bartek ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-07 11:16 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-09-16 16:17 Vacuum related question G. Thomas Yagel, Jr. <tyagel@yahoo.com> 2004-09-18 16:06 ` Tom Lane <tgl@sss.pgh.pa.us> 2025-03-07 11:16 Vacuum related question Bartosz Stalewski <bartekstalewski@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox