file descriptors still open to deleted files

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On a postgres 9.1.3 system we are seeing repeated problems with vanishng disk space. The problem is resolved by restarting the postgres cluster.

This cluster is replicated to another server (which does not show the same problem). It has several databases -- one very low activity currently ( all tables unlogged), one with lots of inserts and some reads (all logged), and one database which is created hourly and then dropped after making a copy.

It is this later operation that I suspect is causing the issues since we did not see this before this database was made conjured up.  

Our monitors show that from inside postgres the database size regularly rises as the transient database is created and drops again when the database is dropped. But monitors that look as disk space as reported by system utilities see a steadily dropping amount of disk space.

I can post further information if you give me some pointers as to what to look for.

Thanks!

Greg Williamson

postgres(at)db11:5432=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
(1 row)

Says one of our sysads:
"These are single disk systems. single non-raid sata. Using xfs as the filesystem like the rest of the dbs." 

The gritty details of before and after as seen through a couple of commands.

BEFORE
$  df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              67G   60G  7.4G  89% /
none                  7.9G  180K  7.9G   1% /dev
none                  7.9G     0  7.9G   0% /dev/shm
none                  7.9G   56K  7.9G   1% /var/run
none                  7.9G     0  7.9G   0% /var/lock
none                  7.9G     0  7.9G   0% /lib/init/rw
/dev/sda1             184M   31M  144M  18% /boot

AFTER:
postgres@db11:~$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              67G   38G   30G  57% /
none                  7.9G  180K  7.9G   1% /dev
none                  7.9G     0  7.9G   0% /dev/shm
none                  7.9G   56K  7.9G   1% /var/run
none                  7.9G     0  7.9G   0% /var/lock
none                  7.9G     0  7.9G   0% /lib/init/rw
/dev/sda1             184M   31M  144M  18% /boot

BEFORE:
postgres@db11:~$ du -h /var/lib/postgresql/9.1/main
480K/var/lib/postgresql/9.1/main/global
28K/var/lib/postgresql/9.1/main/pg_xlog/archive_status
8.0G/var/lib/postgresql/9.1/main/pg_xlog
47M/var/lib/postgresql/9.1/main/pg_clog
8.0K/var/lib/postgresql/9.1/main/pg_notify
0/var/lib/postgresql/9.1/main/pg_serial
16K/var/lib/postgresql/9.1/main/pg_subtrans
0/var/lib/postgresql/9.1/main/pg_twophase
8.0K/var/lib/postgresql/9.1/main/pg_multixact/members
8.0K/var/lib/postgresql/9.1/main/pg_multixact/offsets
16K/var/lib/postgresql/9.1/main/pg_multixact
5.9M/var/lib/postgresql/9.1/main/base/1
5.9M/var/lib/postgresql/9.1/main/base/11938
5.9M/var/lib/postgresql/9.1/main/base/11946
5.0G/var/lib/postgresql/9.1/main/base/16387
5.9M/var/lib/postgresql/9.1/main/base/16392
5.9M/var/lib/postgresql/9.1/main/base/16402
0/var/lib/postgresql/9.1/main/base/pgsql_tmp
520M/var/lib/postgresql/9.1/main/base/16424
5.9M/var/lib/postgresql/9.1/main/base/16449
1.4G/var/lib/postgresql/9.1/main/base/1418400
6.1M/var/lib/postgresql/9.1/main/base/2047839
6.8G/var/lib/postgresql/9.1/main/base
0/var/lib/postgresql/9.1/main/pg_tblspc
216K/var/lib/postgresql/9.1/main/pg_stat_tmp
15G/var/lib/postgresql/9.1/main

AFTER:
postgres@db11:~$ du -h /var/lib/postgresql/9.1/main
480K/var/lib/postgresql/9.1/main/global

28K/var/lib/postgresql/9.1/main/pg_xlog/archive_status
8.0G/var/lib/postgresql/9.1/main/pg_xlog
47M/var/lib/postgresql/9.1/main/pg_clog
8.0K/var/lib/postgresql/9.1/main/pg_notify
0/var/lib/postgresql/9.1/main/pg_serial
64K/var/lib/postgresql/9.1/main/pg_subtrans
0/var/lib/postgresql/9.1/main/pg_twophase
8.0K/var/lib/postgresql/9.1/main/pg_multixact/members
8.0K/var/lib/postgresql/9.1/main/pg_multixact/offsets
16K/var/lib/postgresql/9.1/main/pg_multixact
5.9M/var/lib/postgresql/9.1/main/base/1
5.9M/var/lib/postgresql/9.1/main/base/11938
5.9M/var/lib/postgresql/9.1/main/base/11946
5.0G/var/lib/postgresql/9.1/main/base/16387
5.9M/var/lib/postgresql/9.1/main/base/16392
5.9M/var/lib/postgresql/9.1/main/base/16402
0/var/lib/postgresql/9.1/main/base/pgsql_tmp
520M/var/lib/postgresql/9.1/main/base/16424
5.9M/var/lib/postgresql/9.1/main/base/16449
1.4G/var/lib/postgresql/9.1/main/base/1418400
6.1M/var/lib/postgresql/9.1/main/base/2047839
316M/var/lib/postgresql/9.1/main/base/2297189
7.1G/var/lib/postgresql/9.1/main/base
0/var/lib/postgresql/9.1/main/pg_tblspc
328K/var/lib/postgresql/9.1/main/pg_stat_tmp

BEFORE:
$ sudo lsof -p 31356 -a | more
COMMAND    PID     USER   FD   TYPE             DEVICE SIZE/OFF      NODE NAME
postgres 31356 postgres  cwd    DIR                8,3     4096 269834294 /var/lib/postgresql/9.1/main
postgres 31356 postgres  rtd    DIR                8,3     4096       128 /
postgres 31356 postgres  txt    REG                8,3  5430480  37149754 /usr/lib/postgresql/9.1/bin/postgres
postgres 31356 postgres  mem    REG                8,3    51712    772341 /lib/libnss_files-2.11.1.so
postgres 31356 postgres  DEL    REG                0,4             819200 /SYSV0052e2c1
postgres 31356 postgres  mem    REG                8,3    14344      2282 /lib/libgpg-error.so.0.4.0
postgres 31356 postgres  mem    REG                8,3   491000      1699 /lib/libgcrypt.so.11.5.2
postgres 31356 postgres  mem    REG                8,3    67896 268818565 /usr/lib/libtasn1.so.3.1.7
postgres 31356 postgres  DEL    REG                8,3          273434018 /usr/lib/libgnutls.so.26.14.12
postgres 31356 postgres  mem    REG                8,3   105232 268940921 /usr/lib/libsasl2.so.2.0.23
postgres 31356 postgres  DEL    REG                8,3          268574412 /usr/lib/liblber-2.4.so.2.5.4
postgres 31356 postgres  mem    REG                8,3   135745    772336 /lib/libpthread-2.11.1.so
postgres 31356 postgres  mem    REG                8,3    93000    772321 /lib/libresolv-2.11.1.so
postgres 31356 postgres  mem    REG                8,3    10224      2300 /lib/libkeyutils-1.2.so
postgres 31356 postgres  mem    REG                8,3    31168 268572617 /usr/lib/libkrb5support.so.0.1
postgres 31356 postgres  mem    REG                8,3   154048 268807360 /usr/lib/libk5crypto.so.3.1
postgres 31356 postgres  mem    REG                8,3    43296    614766 /lib/libcrypt-2.11.1.so
postgres 31356 postgres  mem    REG                8,3    92752     79191 /lib/libz.so.1.2.3.3
postgres 31356 postgres  mem    REG                8,3  1572232    772338 /lib/libc-2.11.1.so
postgres 31356 postgres  DEL    REG                8,3          268574415 /usr/lib/libldap_r-2.4.so.2.5.4
postgres 31356 postgres  mem    REG                8,3   534832     60426 /lib/libm-2.11.1.so
postgres 31356 postgres  mem    REG                8,3    14696    772342 /lib/libdl-2.11.1.so
postgres 31356 postgres  mem    REG                8,3   213784 268572618 /usr/lib/libgssapi_krb5.so.2.2
postgres 31356 postgres  mem    REG                8,3    14584       495 /lib/libcom_err.so.2.1
postgres 31356 postgres  mem    REG                8,3   803192 268807473 /usr/lib/libkrb5.so.3.3
postgres 31356 postgres  mem    REG                8,3  1626400       772 /lib/libcrypto.so.0.9.8
postgres 31356 postgres  mem    REG                8,3   333904   2849994 /lib/libssl.so.0.9.8
postgres 31356 postgres  mem    REG                8,3    51776      1263 /lib/libpam.so.0.82.2
postgres 31356 postgres  mem    REG                8,3  1372344 268818714 /usr/lib/libxml2.so.2.7.6
postgres 31356 postgres  mem    REG                8,3   136936     60428 /lib/ld-2.11.1.so
postgres 31356 postgres  mem    REG                8,3     2459 402716943 /usr/lib/locale/en_US/LC_TIME
postgres 31356 postgres  mem    REG                8,3       59 402716942 /usr/lib/locale/en_US/LC_NUMERIC
postgres 31356 postgres  mem    REG                8,3      291 402716945 /usr/lib/locale/en_US/LC_MONETARY
postgres 31356 postgres  mem    REG                8,3       62      4366 /usr/lib/locale/en_US/LC_MESSAGES/SYS_LC_MESSAGES
postgres 31356 postgres  mem    REG                8,3   221612 402716941 /usr/lib/locale/en_US/LC_CTYPE
postgres 31356 postgres  mem    REG                8,3    19459 402716944 /usr/lib/locale/en_US/LC_COLLATE
postgres 31356 postgres    0r   CHR                1,3      0t0      1344 /dev/null
postgres 31356 postgres    1u   CHR              136,1      0t0         4 /dev/pts/1 (deleted)
postgres 31356 postgres    2u   CHR              136,1      0t0         4 /dev/pts/1 (deleted)
postgres 31356 postgres    3u  IPv4          392650125      0t0       TCP *:postgresql (LISTEN)
postgres 31356 postgres    4u  IPv6          392650126      0t0       TCP *:postgresql (LISTEN)
postgres 31356 postgres    5u  unix 0xffff88005142e100      0t0 392650128 /var/run/postgresql/.s.PGSQL.5432
postgres 31356 postgres    6u  IPv6          392650136      0t0       UDP localhost:21771->localhost:21771 
postgres 31356 postgres    7u  unix 0xffff88005142db00      0t0 392679151 socket

AFTER:
$ sudo lsof -p 4382 -a
COMMAND   PID     USER   FD   TYPE             DEVICE SIZE/OFF      NODE NAME
postgres 4382 postgres  cwd    DIR                8,3     4096 269834294 /var/lib/postgresql/9.1/main
postgres 4382 postgres  rtd    DIR                8,3     4096       128 /
postgres 4382 postgres  txt    REG                8,3  5430480  37149754 /usr/lib/postgresql/9.1/bin/postgres
postgres 4382 postgres  mem    REG                8,3    51712    772341 /lib/libnss_files-2.11.1.so
postgres 4382 postgres  DEL    REG                0,4             884736 /SYSV0052e2c1
postgres 4382 postgres  mem    REG                8,3    14344      2282 /lib/libgpg-error.so.0.4.0
postgres 4382 postgres  mem    REG                8,3   491000      1699 /lib/libgcrypt.so.11.5.2
postgres 4382 postgres  mem    REG                8,3    67896 268818565 /usr/lib/libtasn1.so.3.1.7
postgres 4382 postgres  mem    REG                8,3   663816 268573983 /usr/lib/libgnutls.so.26.14.12
postgres 4382 postgres  mem    REG                8,3   105232 268940921 /usr/lib/libsasl2.so.2.0.23
postgres 4382 postgres  mem    REG                8,3    55568 268573988 /usr/lib/liblber-2.4.so.2.5.4
postgres 4382 postgres  mem    REG                8,3   135745    772336 /lib/libpthread-2.11.1.so
postgres 4382 postgres  mem    REG                8,3    93000    772321 /lib/libresolv-2.11.1.so
postgres 4382 postgres  mem    REG                8,3    10224      2300 /lib/libkeyutils-1.2.so
postgres 4382 postgres  mem    REG                8,3    31168 268572617 /usr/lib/libkrb5support.so.0.1
postgres 4382 postgres  mem    REG                8,3   154048 268807360 /usr/lib/libk5crypto.so.3.1
postgres 4382 postgres  mem    REG                8,3    43296    614766 /lib/libcrypt-2.11.1.so
postgres 4382 postgres  mem    REG                8,3    92752     79191 /lib/libz.so.1.2.3.3
postgres 4382 postgres  mem    REG                8,3  1572232    772338 /lib/libc-2.11.1.so
postgres 4382 postgres  mem    REG                8,3   302464 268573990 /usr/lib/libldap_r-2.4.so.2.5.4
postgres 4382 postgres  mem    REG                8,3   534832     60426 /lib/libm-2.11.1.so
postgres 4382 postgres  mem    REG                8,3    14696    772342 /lib/libdl-2.11.1.so
postgres 4382 postgres  mem    REG                8,3   213784 268572618 /usr/lib/libgssapi_krb5.so.2.2
postgres 4382 postgres  mem    REG                8,3    14584       495 /lib/libcom_err.so.2.1
postgres 4382 postgres  mem    REG                8,3   803192 268807473 /usr/lib/libkrb5.so.3.3
postgres 4382 postgres  mem    REG                8,3  1626400       772 /lib/libcrypto.so.0.9.8
postgres 4382 postgres  mem    REG                8,3   333904   2849994 /lib/libssl.so.0.9.8
postgres 4382 postgres  mem    REG                8,3    51776      1263 /lib/libpam.so.0.82.2
postgres 4382 postgres  mem    REG                8,3  1372344 268818714 /usr/lib/libxml2.so.2.7.6
postgres 4382 postgres  mem    REG                8,3   136936     60428 /lib/ld-2.11.1.so
postgres 4382 postgres  mem    REG                8,3     2459 402716943 /usr/lib/locale/en_US/LC_TIME
postgres 4382 postgres  mem    REG                8,3       59 402716942 /usr/lib/locale/en_US/LC_NUMERIC
postgres 4382 postgres  mem    REG                8,3      291 402716945 /usr/lib/locale/en_US/LC_MONETARY
postgres 4382 postgres  mem    REG                8,3       62      4366 /usr/lib/locale/en_US/LC_MESSAGES/SYS_LC_MESSAGES
postgres 4382 postgres  mem    REG                8,3   221612 402716941 /usr/lib/locale/en_US/LC_CTYPE
postgres 4382 postgres  mem    REG                8,3    19459 402716944 /usr/lib/locale/en_US/LC_COLLATE
postgres 4382 postgres    0r   CHR                1,3      0t0      1344 /dev/null
postgres 4382 postgres    1u   CHR              136,2      0t0         5 /dev/pts/2
postgres 4382 postgres    2u   CHR              136,2      0t0         5 /dev/pts/2
postgres 4382 postgres    3u  IPv4          502242690      0t0       TCP *:postgresql (LISTEN)
postgres 4382 postgres    4u  IPv6          502242691      0t0       TCP *:postgresql (LISTEN)
postgres 4382 postgres    5u  unix 0xffff88042b7f8600      0t0 502242693 /var/run/postgresql/.s.PGSQL.5432
postgres 4382 postgres    6u  IPv6          502242701      0t0       UDP localhost:18554->localhost:18554 
postgres 4382 postgres    7u  unix 0xffff88042a250000      0t0 502242789 socket



-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux