Thread: 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 http://www.halifaxbudolife.ca
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@gmail.com> 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 > http://www.halifaxbudolife.ca > -- Jeff MacDonald http://www.halifaxbudolife.ca
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)
I'm using 7.3.4 Jeff. On Mon, 20 Dec 2004 10:48:18 -0300, Alvaro Herrera <alvherre@dcc.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 http://www.halifaxbudolife.ca
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)
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
<alvherre@dcc.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
http://www.halifaxbudolife.ca
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"
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 <alvherre@dcc.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 http://www.halifaxbudolife.ca
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 > <alvherre@dcc.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 > http://www.halifaxbudolife.ca > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > 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.
Attachment
Jeff MacDonald <bignose@gmail.com> 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