Professional Web Applications Themes

Diskspace - PostgreSQL / PGSQL

In article <bg6juc$q4p$03$1news.t-online.com>, Thomas Musall <muesli.pcmt-online.de> wrote: >I have deleted a great file from the filesystem, but >the disk is near 94% of diskspace. How will the >system update this information, or must I do something. Most likely the files you deleted are still in use by some processes. This is most common when you delete log files that are still being written to. To clear up the space, you have to get the processes to close the files. If they're being written by syslogd, sending a SIGHUP to syslogd will do this (you should first recreate the new log ...

  1. #1

    Default Re: Diskspace

    In article <bg6juc$q4p$03$1news.t-online.com>,
    Thomas Musall <muesli.pcmt-online.de> wrote:
    >I have deleted a great file from the filesystem, but
    >the disk is near 94% of diskspace. How will the
    >system update this information, or must I do something.
    Most likely the files you deleted are still in use by some processes. This
    is most common when you delete log files that are still being written to.

    To clear up the space, you have to get the processes to close the files.
    If they're being written by syslogd, sending a SIGHUP to syslogd will do
    this (you should first recreate the new log files for it to start using).
    For other files, you'll need to do some searching for the processes that
    need to be killed.

    If you can't figure out which processes they are, you may need to reboot.

    --
    Barry Margolin, [email]barry.margolinlevel3.com[/email]
    Level(3), Woburn, MA
    *** DON'T SEND TECHNICAL QUESTIONS DIRECTLY TO ME, post them to newsgroups.
    Please DON'T copy followups to me -- I'll assume it wasn't posted to the group.
    Barry Margolin Guest

  2. #2

    Default Re: Diskspace

    The system update immediately when you remove a file.
    I think you have anothers files which fill up your file system.
    See the directory /usr/tmp. Sometime the system create files "wscon...." .
    You can remove these files ( logs of cde).
    You can see also in the var directory (/var)

    Vince

    "Thomas Musall" <muesli.pcmt-online.de> a écrit dans le message de news:
    bg6juc$q4p$03$1news.t-online.com...
    > Hy,
    >
    > I have deleted a great file from the filesystem, but
    > the disk is near 94% of diskspace. How will the
    > system update this information, or must I do something.
    >
    > Thanks
    >
    > Tom
    >
    >

    ragnus2003 Guest

  3. #3

    Default Re: Diskspace

    Maybe a process still has the file open you deleted, in which case the
    space will not be given back to the filesystem. You need to run pfiles
    to find out which process to terminate.

    Regards

    Duncan Baillie

    ragnus2003 wrote:
    >The system update immediately when you remove a file.
    >I think you have anothers files which fill up your file system.
    >See the directory /usr/tmp. Sometime the system create files "wscon...." .
    >You can remove these files ( logs of cde).
    >You can see also in the var directory (/var)
    >
    >Vince
    >
    >"Thomas Musall" <muesli.pcmt-online.de> a écrit dans le message de news:
    >bg6juc$q4p$03$1news.t-online.com...
    >
    >
    >>Hy,
    >>
    >>I have deleted a great file from the filesystem, but
    >>the disk is near 94% of diskspace. How will the
    >>system update this information, or must I do something.
    >>
    >>Thanks
    >>
    >>Tom
    >>
    >>
    >>
    >>
    >
    >
    >
    >

    Duncan Baillie Guest

  4. #4

    Default Re: Diskspace

    Thomas Musall wrote:
    > I have deleted a great file from the filesystem, but
    > the disk is near 94% of diskspace. How will the
    > system update this information, or must I do something.
    More than likely another process still has this file open. So, even
    though its been deleted, its still there. You can confirm this by
    checking for the said process and terminating it. You could also
    reboot if you can't find it. Alternately, you can run find across
    the filesystem to check for other large files or check overall
    usage with du.

    -am © 2003
    Anthony Mandic Guest

  5. #5

    Default Diskspace

    Hi,

    I have a database that is about 3.5 gigs big. And I have a pretty
    serious hunch that there isn't that much data.

    I did a "du -s *|sort -n " in /usr/local/pgsql/data/base/9039913

    And got a list that ends with these entries.

    55648 18070582
    137296 13312252
    294736 13312279
    845648 13312283.2
    1049104 13312283
    1049104 13312283.1

    There are pleanty of other tables, but these are teh big ones.

    So I tried this

    select * from pg_statio_user_tables where relid = 13312283;

    and got no row returned, when i did

    select * from pg_statio_user_tables where relid = 13312279;

    i told me which table i was looking for.

    So I guess my question is, how do i find out what 13312283.* are, are
    they safe to delete ?

    Will a dump/reload fix things up ? I'm trying a vacuum now, as well i
    checked the queries in pg_statio_all_tables too.

    Thanks.

    --
    Jeff MacDonald
    [url]http://www.halifaxbudolife.ca[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Jeff MacDonald Guest

  6. #6

    Default Re: Diskspace

    Hi,

    We after some more reading I learned that this huge file is my TOAST table..

    Is there a way to schrink that down ?

    Thanks.

    Jeff.


    On Mon, 20 Dec 2004 09:39:15 -0400, Jeff MacDonald <bignose> wrote:
    > Hi,
    >
    > I have a database that is about 3.5 gigs big. And I have a pretty
    > serious hunch that there isn't that much data.
    >
    > I did a "du -s *|sort -n " in /usr/local/pgsql/data/base/9039913
    >
    > And got a list that ends with these entries.
    >
    > 55648 18070582
    > 137296 13312252
    > 294736 13312279
    > 845648 13312283.2
    > 1049104 13312283
    > 1049104 13312283.1
    >
    > There are pleanty of other tables, but these are teh big ones.
    >
    > So I tried this
    >
    > select * from pg_statio_user_tables where relid = 13312283;
    >
    > and got no row returned, when i did
    >
    > select * from pg_statio_user_tables where relid = 13312279;
    >
    > i told me which table i was looking for.
    >
    > So I guess my question is, how do i find out what 13312283.* are, are
    > they safe to delete ?
    >
    > Will a dump/reload fix things up ? I'm trying a vacuum now, as well i
    > checked the queries in pg_statio_all_tables too.
    >
    > Thanks.
    >
    > --
    > Jeff MacDonald
    > [url]http://www.halifaxbudolife.ca[/url]
    >

    --
    Jeff MacDonald
    [url]http://www.halifaxbudolife.ca[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Jeff MacDonald Guest

  7. #7

    Default Re: Diskspace

    On Mon, Dec 20, 2004 at 09:39:15AM -0400, Jeff MacDonald wrote:

    Hi,
    > select * from pg_statio_user_tables where relid = 13312283;
    See the pg_class table, using the relfilenode column.
    > So I guess my question is, how do i find out what 13312283.* are, are
    > they safe to delete ?
    Probably none. You'd have to VACUUM FULL or maybe REINDEX, depending on
    which version you are using.

    --
    Alvaro Herrera (<alvherre[]dcc.uchile.cl>)
    "I suspect most samba developers are already technically insane...
    Of course, since many of them are Australians, you can't tell." (L. Torvalds)

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Alvaro Herrera Guest

  8. #8

    Default Re: Diskspace

    I'm using 7.3.4

    Jeff.


    On Mon, 20 Dec 2004 10:48:18 -0300, Alvaro Herrera
    <alvherredcc.uchile.cl> wrote:
    > On Mon, Dec 20, 2004 at 09:39:15AM -0400, Jeff MacDonald wrote:
    >
    > Hi,
    >
    > > select * from pg_statio_user_tables where relid = 13312283;
    >
    > See the pg_class table, using the relfilenode column.
    >
    > > So I guess my question is, how do i find out what 13312283.* are, are
    > > they safe to delete ?
    >
    > Probably none. You'd have to VACUUM FULL or maybe REINDEX, depending on
    > which version you are using.
    >
    > --
    > Alvaro Herrera (<alvherre[]dcc.uchile.cl>)
    > "I suspect most samba developers are already technically insane...
    > Of course, since many of them are Australians, you can't tell." (L. Torvalds)
    >

    --
    Jeff MacDonald
    [url]http://www.halifaxbudolife.ca[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Jeff MacDonald Guest

  9. #9

    Default Re: Diskspace

    On Mon, Dec 20, 2004 at 09:48:57AM -0400, Jeff MacDonald wrote:
    > I'm using 7.3.4
    And what about pg_class?

    --
    Alvaro Herrera (<alvherre[]dcc.uchile.cl>)
    "Industry suffers from the managerial dogma that for the sake of stability
    and continuity, the company should be independent of the competence of
    individual employees." (E. Dijkstra)

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Alvaro Herrera Guest

  10. #10

    Default Re: Diskspace

    pg_class tells me

    select relname,relfilenode,relpages from pg_class where relfilenode = 13312283;
    relname | relfilenode | relpages
    -------------------+-------------+----------
    pg_toast_13312279 | 13312283 | 367639

    So now I guess I have to find out what 13312279 is..

    Ah HA !

    # select relname,relfilenode,relpages from pg_class where relfilenode
    = 13312279;
    relname | relfilenode | relpages
    -----------+-------------+----------
    email_log | 13312279 | 36821

    It just so happens that email_log has around 700,000 rows, that would
    explain the space issues.

    I do know that email_log is 100% deleteable, so I'll proceed with hosing that.

    Does all this sound reasonable ?

    Jeff.

    On Mon, 20 Dec 2004 10:51:32 -0300, Alvaro Herrera
    <alvherredcc.uchile.cl> wrote:
    > On Mon, Dec 20, 2004 at 09:48:57AM -0400, Jeff MacDonald wrote:
    > > I'm using 7.3.4
    >
    > And what about pg_class?
    >
    > --
    > Alvaro Herrera (<alvherre[]dcc.uchile.cl>)
    > "Industry suffers from the managerial dogma that for the sake of stability
    > and continuity, the company should be independent of the competence of
    > individual employees." (E. Dijkstra)
    >

    --
    Jeff MacDonald
    [url]http://www.halifaxbudolife.ca[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Jeff MacDonald Guest

  11. #11

    Default Re: Diskspace

    On Mon, Dec 20, 2004 at 09:57:35AM -0400, Jeff MacDonald wrote:
    > # select relname,relfilenode,relpages from pg_class where relfilenode
    > = 13312279;
    > relname | relfilenode | relpages
    > -----------+-------------+----------
    > email_log | 13312279 | 36821
    >
    > It just so happens that email_log has around 700,000 rows, that would
    > explain the space issues.
    >
    > I do know that email_log is 100% deleteable, so I'll proceed with hosing that.
    >
    > Does all this sound reasonable ?
    Certainly.

    --
    Alvaro Herrera (<alvherre[]dcc.uchile.cl>)
    "Para tener más hay que desear menos"

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Alvaro Herrera Guest

  12. #12

    Default Re: Diskspace

    well, i did a "delete from email_log" and then a vacuum and the files
    are still lingering around...

    still huge. the postmaster did die due to a diskspace issue, so i
    wonder if it's still just keeping a wierd lock on those files or
    something.. idea's ?


    On Mon, 20 Dec 2004 11:01:32 -0300, Alvaro Herrera
    <alvherredcc.uchile.cl> wrote:
    > On Mon, Dec 20, 2004 at 09:57:35AM -0400, Jeff MacDonald wrote:
    >
    > > # select relname,relfilenode,relpages from pg_class where relfilenode
    > > = 13312279;
    > > relname | relfilenode | relpages
    > > -----------+-------------+----------
    > > email_log | 13312279 | 36821
    > >
    > > It just so happens that email_log has around 700,000 rows, that would
    > > explain the space issues.
    > >
    > > I do know that email_log is 100% deleteable, so I'll proceed with hosing that.
    > >
    > > Does all this sound reasonable ?
    >
    > Certainly.
    >
    > --
    > Alvaro Herrera (<alvherre[]dcc.uchile.cl>)
    > "Para tener más hay que desear menos"
    >

    --
    Jeff MacDonald
    [url]http://www.halifaxbudolife.ca[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Jeff MacDonald Guest

  13. #13

    Default Re: Diskspace

    VACUUM or VACUUM FULL. Only the second actually reclaims diskspace...

    On Mon, Dec 20, 2004 at 03:21:51PM -0400, Jeff MacDonald wrote:
    > well, i did a "delete from email_log" and then a vacuum and the files
    > are still lingering around...
    >
    > still huge. the postmaster did die due to a diskspace issue, so i
    > wonder if it's still just keeping a wierd lock on those files or
    > something.. idea's ?
    >
    >
    > On Mon, 20 Dec 2004 11:01:32 -0300, Alvaro Herrera
    > <alvherredcc.uchile.cl> wrote:
    > > On Mon, Dec 20, 2004 at 09:57:35AM -0400, Jeff MacDonald wrote:
    > >
    > > > # select relname,relfilenode,relpages from pg_class where relfilenode
    > > > = 13312279;
    > > > relname | relfilenode | relpages
    > > > -----------+-------------+----------
    > > > email_log | 13312279 | 36821
    > > >
    > > > It just so happens that email_log has around 700,000 rows, that would
    > > > explain the space issues.
    > > >
    > > > I do know that email_log is 100% deleteable, so I'll proceed with hosing that.
    > > >
    > > > Does all this sound reasonable ?
    > >
    > > Certainly.
    > >
    > > --
    > > Alvaro Herrera (<alvherre[]dcc.uchile.cl>)
    > > "Para tener más hay que desear menos"
    > >
    >
    >
    > --
    > Jeff MacDonald
    > [url]http://www.halifaxbudolife.ca[/url]
    >
    > ---------------------------(end of broadcast)---------------------------
    > TIP 4: Don't 'kill -9' the postmaster
    --
    Martijn van Oosterhout <kleptogsvana.org> [url]http://svana.org/kleptog/[/url]
    > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
    > tool for doing 5% of the work and then sitting around waiting for someone
    > else to do the other 95% so you can sue them.
    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.0.6 (GNU/Linux)
    Comment: For info see [url]http://www.gnupg.org[/url]

    iD8DBQFBxyuLY5Twig3Ge+YRAqfNAKCIexZOHk9HxCI1QzmtqQ v07GRGsQCfStTn
    MFwOL32iF8F7wwQ1eqYQCmA=
    =7pnx
    -----END PGP SIGNATURE-----

    Martijn van Oosterhout Guest

  14. #14

    Default Re: Diskspace

    Jeff MacDonald <bignose> writes:
    > well, i did a "delete from email_log" and then a vacuum and the files
    > are still lingering around...
    TRUNCATE would be better. A VACUUM FULL would shrink the tables all
    right, but probably not do much for the indexes.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    Tom Lane Guest

Similar Threads

  1. Tablespace, Diskspace or sql abuse..
    By nikey11 in forum Oracle Server
    Replies: 10
    Last Post: June 25th, 03:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139