pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Does VACUUMLO result in lots of writes in the file system? 2+ messages / 1 participants [nested] [flat]
* Does VACUUMLO result in lots of writes in the file system? @ 2022-07-18 08:14 Thorsten Schöning <tschoening@am-soft.de> 0 siblings, 1 reply; 2+ messages in thread From: Thorsten Schöning @ 2022-07-18 08:14 UTC (permalink / raw) To: Pgsql-admin <pgsql-admin@lists.postgresql.org> Hi everyone, I have some Postgres 11.16 hosted by some Ubuntu 18.04 LTS using BTRFS as file system. Am running a script named btrfs-auto-snapshot creating snapshots on a regular basis, like every 15 minutes, hourly etc. My databases are used by some custom application storing files as-is as large objects in those databases for historical reasons. Those files might be some KiB, some MiB or even a few GiB in size, though the latter is pretty seldom. That custom app provides some cleanup daemon doing three things: 1. checking some flag if some file has been deleted or not and if so, deleting some row with the corresponding file info. 2. as that doesn't remove the LO itself, the shell app VACUUMLO is called after removing all rows to remove all LOs. 3. after everthing finished, VACUUM ANALYZE is called on pg_largeobject to updates stats. Though, it's important to note that FULL is NOT used regularly. During the last few weeks I see that lots of free space gets consumed during those operations, dozens of GiBs for some reason. Up to a point where the file system free space gets exhausted, which shouldn't happen too easily according to the database sizes. Many of them aren't even the half of the size of free space. I know that VACUUM FULL rewrites files and might temporarily consume as much space as a database is in size, maybe a little more. I don't have that much free space for some databases, so am not doing this. But what's with the tool VACUUMLO? Does it rewrite some files as well, like only some instead of all of those 1 GiB data files? I always expected that it manages flags and free blocks infos only, like if LOs are deleted manually using SQL, without actually rewriting data files. In the end, there's some process creating dozens of GiBs of data which are preserved by the automatically created snapshots and I don't understand where this is coming from. After deleting those snapshots manually, the free space is back again. It might be additionally important to note that regarding runtime logs of my cleanup daemons, snapshots always happen BEFORE and AFTER those daemons only, not in the middle of their processing. So it looks to me that during the cleanup really new files get created which are used afterwards. If it was temporary data only, that shouldn't show up too much in the snapshots or get deleted with the frequently ones. Any further ideas? Thanks! Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151- 9468-55 Mobil: 0178-8 9468-04 AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska Für Rückfragen stehe ich Ihnen jederzeit zur Verfügung. Mit freundlichen Grüßen, Thorsten Schöning Telefon: +49 (0)515 94 68 - 0 Fax: E-Mail: TSchoening@am-soft.de AM-Soft IT-Service - Bitstore Hameln GmbH Brandenburger Straße 7c 31789 Hameln Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen und ist ausschliesslich für den Adressaten bestimmt. Jeglicher Zugriff auf diese E-Mail durch andere Personen als den Adressaten ist untersagt. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Sollten Sie nicht der für diese E-Mail bestimmte Adressat sein, ist Ihnen jede Veröffentlichung, Vervielfältigung oder Weitergabe wie auch das Ergreifen oder Unterlassen von Massnahmen im Vertrauen auf erlangte Information untersagt. This e-mail may contain confidential and/or privileged information and is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. Hinweise zum Datenschutz: bitstore.group/datenschutz ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Does VACUUMLO result in lots of writes in the file system? @ 2022-07-18 08:45 Thorsten Schöning <tschoening@am-soft.de> parent: Thorsten Schöning <tschoening@am-soft.de> 0 siblings, 0 replies; 2+ messages in thread From: Thorsten Schöning @ 2022-07-18 08:45 UTC (permalink / raw) To: Pgsql-admin <pgsql-admin@lists.postgresql.org> Guten Tag Thorsten Schöning, am Montag, 18. Juli 2022 um 10:14 schrieben Sie: > Any further ideas? Thanks! During some additional debugging, I see the following: > pg_largeobject 285 GB > pg_largeobject_loid_pn_index 4628 MB Might it be that this index is completely rewritten when deleting LOs? This might easily sum up in my case. Claimed storage for all other tables is far less: > elrev_schriftsaetze 115 MB > pg_toast_6166080 105 MB > elrev_protokoll 78 MB > elrev_statistik 52 MB > elrev_zugrifflog 46 MB > pg_largeobject_metadata 15 MB > label_dokument 15 MB > pg_largeobject_metadata_oid_index 13 MB > elrev_protokoll_pkey 13 MB > elrev_protokoll_nummer_key 13 MB Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151- 9468-55 Mobil: 0178-8 9468-04 AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska Für Rückfragen stehe ich Ihnen jederzeit zur Verfügung. Mit freundlichen Grüßen, Thorsten Schöning Telefon: +49 (0)515 94 68 - 0 Fax: E-Mail: TSchoening@am-soft.de AM-Soft IT-Service - Bitstore Hameln GmbH Brandenburger Straße 7c 31789 Hameln Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen und ist ausschliesslich für den Adressaten bestimmt. Jeglicher Zugriff auf diese E-Mail durch andere Personen als den Adressaten ist untersagt. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Sollten Sie nicht der für diese E-Mail bestimmte Adressat sein, ist Ihnen jede Veröffentlichung, Vervielfältigung oder Weitergabe wie auch das Ergreifen oder Unterlassen von Massnahmen im Vertrauen auf erlangte Information untersagt. This e-mail may contain confidential and/or privileged information and is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. Hinweise zum Datenschutz: bitstore.group/datenschutz ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-07-18 08:45 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-07-18 08:14 Does VACUUMLO result in lots of writes in the file system? Thorsten Schöning <tschoening@am-soft.de> 2022-07-18 08:45 ` Thorsten Schöning <tschoening@am-soft.de>
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