Thread: Disk is filling up with large files. How can I clean?
Hi,
I find our disk is filling up.
sudo find /var/lib -type f -size +100M -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'
/var/lib/rpm/Packages: 278M
/var/lib/pgsql/12/data/base/16384/16583: 392M
/var/lib/pgsql/12/data/base/16384/16586: 1.0G
/var/lib/pgsql/12/data/base/16384/16588: 173M
/var/lib/pgsql/12/data/base/16384/16633: 106M
/var/lib/pgsql/12/data/base/16384/16644: 179M
/var/lib/pgsql/12/data/base/16384/16659: 1.0G
/var/lib/pgsql/12/data/base/16384/16662: 438M
/var/lib/pgsql/12/data/base/16384/16665: 1.0G
/var/lib/pgsql/12/data/base/16384/16667: 1.0G
/var/lib/pgsql/12/data/base/16384/16668: 1.0G
/var/lib/pgsql/12/data/base/16384/16780: 466M
/var/lib/pgsql/12/data/base/16384/16786: 182M
/var/lib/pgsql/12/data/base/16384/16788: 163M
/var/lib/pgsql/12/data/base/16384/16789: 315M
/var/lib/pgsql/12/data/base/16384/16790: 126M
/var/lib/pgsql/12/data/base/16384/16665.2: 403M
/var/lib/pgsql/12/data/base/16384/16586.7: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.6: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.9: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.8: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.6: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.4: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.5: 1.0G
/var/lib/pgsql/12/data/base/16384/16668.3: 586M
/var/lib/pgsql/12/data/base/16384/16586.10: 458M
/var/lib/pgsql/12/data/base/16384/16659.1: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.2: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.2: 1.0G
/var/lib/pgsql/12/data/base/16384/16668.1: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.3: 1.0G
/var/lib/pgsql/12/data/base/16384/16659.3: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.4: 1.0G
/var/lib/pgsql/12/data/base/16384/16665.1: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.5: 1.0G
/var/lib/pgsql/12/data/base/16384/16586.1: 1.0G
/var/lib/pgsql/12/data/base/16384/16668.2: 1.0G
/var/lib/pgsql/12/data/base/16384/16667.1: 741M
/var/lib/pgsql/12/data/base/16384/4652676: 502M
/var/lib/pgsql/12/data/base/16384/4652688: 155M
How can I clean up?
What can I do to clean up. We are using pgsql 12.
Br,
//Mikael
Filenames like 16665, 16665.1, 16665.2 etc all represent the same table (or similar). The number 16665 is called the file node.
To get a list of file nodes for a specific database you can run:
SELECT oid::regclass::text, relfilenode FROM pg_class;
The /16384/ in the path represents the database. To decipher that you can run:
SELECT datname, oid FROM pg_database;
Once you have all that information, you know which database to connect to and which tables are big. Then you can DROP/DELETE/TRUNCATE or so.
Useful functions in that context are pg_relation_size(), pg_table_size(), pg_total_relation_size() and some more. Check out the manual for more information.
Example:
SELECT oid::regclass::text, relfilenode, pg_relation_size(oid) as size FROM pg_class ORDER BY size DESC;
On Wed, Oct 9, 2024 at 10:10 AM Mikael Petterson <mikaelpetterson@hotmail.com> wrote:
Hi,I find our disk is filling up.sudo find /var/lib -type f -size +100M -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'/var/lib/rpm/Packages: 278M/var/lib/pgsql/12/data/base/16384/16583: 392M/var/lib/pgsql/12/data/base/16384/16586: 1.0G/var/lib/pgsql/12/data/base/16384/16588: 173M/var/lib/pgsql/12/data/base/16384/16633: 106M/var/lib/pgsql/12/data/base/16384/16644: 179M/var/lib/pgsql/12/data/base/16384/16659: 1.0G/var/lib/pgsql/12/data/base/16384/16662: 438M/var/lib/pgsql/12/data/base/16384/16665: 1.0G/var/lib/pgsql/12/data/base/16384/16667: 1.0G/var/lib/pgsql/12/data/base/16384/16668: 1.0G/var/lib/pgsql/12/data/base/16384/16780: 466M/var/lib/pgsql/12/data/base/16384/16786: 182M/var/lib/pgsql/12/data/base/16384/16788: 163M/var/lib/pgsql/12/data/base/16384/16789: 315M/var/lib/pgsql/12/data/base/16384/16790: 126M/var/lib/pgsql/12/data/base/16384/16665.2: 403M/var/lib/pgsql/12/data/base/16384/16586.7: 1.0G/var/lib/pgsql/12/data/base/16384/16586.6: 1.0G/var/lib/pgsql/12/data/base/16384/16586.9: 1.0G/var/lib/pgsql/12/data/base/16384/16586.8: 1.0G/var/lib/pgsql/12/data/base/16384/16659.6: 1.0G/var/lib/pgsql/12/data/base/16384/16659.4: 1.0G/var/lib/pgsql/12/data/base/16384/16659.5: 1.0G/var/lib/pgsql/12/data/base/16384/16668.3: 586M/var/lib/pgsql/12/data/base/16384/16586.10: 458M/var/lib/pgsql/12/data/base/16384/16659.1: 1.0G/var/lib/pgsql/12/data/base/16384/16586.2: 1.0G/var/lib/pgsql/12/data/base/16384/16659.2: 1.0G/var/lib/pgsql/12/data/base/16384/16668.1: 1.0G/var/lib/pgsql/12/data/base/16384/16586.3: 1.0G/var/lib/pgsql/12/data/base/16384/16659.3: 1.0G/var/lib/pgsql/12/data/base/16384/16586.4: 1.0G/var/lib/pgsql/12/data/base/16384/16665.1: 1.0G/var/lib/pgsql/12/data/base/16384/16586.5: 1.0G/var/lib/pgsql/12/data/base/16384/16586.1: 1.0G/var/lib/pgsql/12/data/base/16384/16668.2: 1.0G/var/lib/pgsql/12/data/base/16384/16667.1: 741M/var/lib/pgsql/12/data/base/16384/4652676: 502M/var/lib/pgsql/12/data/base/16384/4652688: 155MHow can I clean up?What can I do to clean up. We are using pgsql 12.Br,//Mikael
> On Oct 9, 2024, at 5:52 AM, Torsten Förtsch <tfoertsch123@gmail.com> wrote: > > Filenames like 16665, 16665.1, 16665.2 etc all represent the same table (or similar). The number 16665 is called the filenode. > > To get a list of file nodes for a specific database you can run: > > SELECT oid::regclass::text, relfilenode FROM pg_class; > > The /16384/ in the path represents the database. To decipher that you can run: > > SELECT datname, oid FROM pg_database; > > Once you have all that information, you know which database to connect to and which tables are big. Then you can DROP/DELETE/TRUNCATEor so. Mikael, if you’re unaware of VACUUM FULL (as opposed to just VACUUM), you should read about that too. Hope that helps, Philip
On Wed, Oct 9, 2024 at 9:02 AM Philip Semanchuk <philip@americanefficient.com> wrote:
> On Oct 9, 2024, at 5:52 AM, Torsten Förtsch <tfoertsch123@gmail.com> wrote:
>
> Filenames like 16665, 16665.1, 16665.2 etc all represent the same table (or similar). The number 16665 is called the file node.
>
> To get a list of file nodes for a specific database you can run:
>
> SELECT oid::regclass::text, relfilenode FROM pg_class;
>
> The /16384/ in the path represents the database. To decipher that you can run:
>
> SELECT datname, oid FROM pg_database;
>
> Once you have all that information, you know which database to connect to and which tables are big. Then you can DROP/DELETE/TRUNCATE or so.
Mikael, if you’re unaware of VACUUM FULL (as opposed to just VACUUM), you should read about that too.
VACUUM FULL with an almost-full disk probably isn't the wisest idea.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!
On 10/8/24 04:06, Mikael Petterson wrote: > Hi, > I find our disk is filling up. Query the pg_stat_activity view: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW and look for rows that have with state showing 'idle in transaction'. Report back what you find. You should also check out this post: https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/ > What can I do to clean up. We are using pgsql 12. > Br, > //Mikael > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Oct 9, 2024 at 4:10 AM Mikael Petterson <mikaelpetterson@hotmail.com> wrote:
Hi,I find our disk is filling up.sudo find /var/lib -type f -size +100M -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'
...
Those files only add up to about 30GB. That's pretty small these days : time for a bigger disk? Or perhaps the space is elsewhere: probably want to do something like
sudo find / -xdev -maxdepth 2 -exec du -sh {} \; | grep -E 'G|M' | sort -g
Cheers,
Greg
Another possibility is orphaned files. See https://www.dbi-services.com/blog/can-there-be-orphaned-data-files-in-postgresql/ and https://github.com/bdrouvot/pg_orphaned -- Sergey On Wed, 9 Oct 2024 at 19:02, Greg Sabino Mullane <htamfids@gmail.com> wrote: > > On Wed, Oct 9, 2024 at 4:10 AM Mikael Petterson <mikaelpetterson@hotmail.com> wrote: >> >> Hi, >> >> I find our disk is filling up. >> >> sudo find /var/lib -type f -size +100M -exec ls -lh {} \; | awk '{ print $9 ": " $5 }' > > ... > > Those files only add up to about 30GB. That's pretty small these days : time for a bigger disk? Or perhaps the space iselsewhere: probably want to do something like > sudo find / -xdev -maxdepth 2 -exec du -sh {} \; | grep -E 'G|M' | sort -g > > Cheers, > Greg > -- Sergey