Thread: table growing
Hi
I have a growing database problem… I insert rows in a table automatically(1500 by hour approx) and I use this table like a temporary table 24/7…All rows inserted are
deleted(maybe 12-24 hours later)but the database grow all the time I tried vacuum,analyze,vacuum full, reindex, max_fsm_pages =100000,1000000 etc…
the only way to reduce space is dump table /drop table/create table and insert after but after 12-24 hours… the table is very big and request are very slow
12G before the drop and 250m after the drop/create/insert…
ideas?
Thanks!
Stephane
On Mon, 4 Nov 2002, Stephane Tessier wrote: > Hi > > I have a growing database problem. I insert rows in a table > automatically(1500 by hour approx) and I use this table like a temporary > table 24/7.All rows inserted are > deleted(maybe 12-24 hours later)but the database grow all the time I > tried vacuum,analyze,vacuum full, reindex, max_fsm_pages =100000,1000000 > etc. > > the only way to reduce space is dump table /drop table/create table and > insert after but after 12-24 hours. the table is very big and request > are very slow > > 12G before the drop and 250m after the drop/create/insert. Have you been running vacuum and vacuum full? What version of Postgresql are you running, and what OS are you on?
The database version is Postgresql 7.2.1 And I run 4 time/day vacuum but I cannot lock tables so I don't want to use a vacuum full.... the question is:is it a problem with pgsql or Is it normal to have to stop the database to do maintenance... My boss WANT (!!!!!) that we find the trouble but is there a trouble???? If it's normal... we'll have to live with it or migrate to Oracle... Thanks a lot! -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: 7 novembre, 2002 12:18 To: Stephane Tessier Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] table growing On Mon, 4 Nov 2002, Stephane Tessier wrote: > Hi > > I have a growing database problem. I insert rows in a table > automatically(1500 by hour approx) and I use this table like a temporary > table 24/7.All rows inserted are > deleted(maybe 12-24 hours later)but the database grow all the time I > tried vacuum,analyze,vacuum full, reindex, max_fsm_pages =100000,1000000 > etc. > > the only way to reduce space is dump table /drop table/create table and > insert after but after 12-24 hours. the table is very big and request > are very slow > > 12G before the drop and 250m after the drop/create/insert. Have you been running vacuum and vacuum full? What version of Postgresql are you running, and what OS are you on?
Stephane Tessier <stephane@info-logika.com> writes: > The database version is Postgresql 7.2.1 > And I run 4 time/day vacuum but I cannot lock tables so I don't want to > use a vacuum full.... the question is:is it a problem with pgsql or > Is it normal to have to stop the database to do maintenance... > My boss WANT (!!!!!) that we find the trouble but is there a trouble???? Is it the table or an index that's growing? If it's the table, it may be growing too fast for VACUUM to clean up. If you have heavy update/delete-insert activity, try running VACUUM more often (you can run it every few minutes if you have to, since it doesn't block anything). If there's any way you can run VACUUM FULL at least once a day (during non-peak hours) it might help as well. If it's an index that's growing, right now REINDEX is the only way to reclaim that space. There are plans to hopefully fix VACUUM to do it, but it's not currently done. -Doug
I think 7.2 allows simultaneous VACUUM-ing along with normal operations.... Stephane Tessier wrote: >The database version is Postgresql 7.2.1 >And I run 4 time/day vacuum but I cannot lock tables so I don't want to >use a vacuum full.... the question is:is it a problem with pgsql or >Is it normal to have to stop the database to do maintenance... >My boss WANT (!!!!!) that we find the trouble but is there a trouble???? > >If it's normal... we'll have to live with it or migrate to Oracle... > >Thanks a lot! > > >-----Original Message----- >From: scott.marlowe [mailto:scott.marlowe@ihs.com] >Sent: 7 novembre, 2002 12:18 >To: Stephane Tessier >Cc: pgsql-general@postgresql.org >Subject: Re: [GENERAL] table growing > >On Mon, 4 Nov 2002, Stephane Tessier wrote: > > > >>Hi >> >>I have a growing database problem. I insert rows in a table >>automatically(1500 by hour approx) and I use this table like a >> >> >temporary > > >>table 24/7.All rows inserted are >>deleted(maybe 12-24 hours later)but the database grow all the time I >>tried vacuum,analyze,vacuum full, reindex, max_fsm_pages >> >> >=100000,1000000 > > >>etc. >> >>the only way to reduce space is dump table /drop table/create table >> >> >and > > >>insert after but after 12-24 hours. the table is very big and request >>are very slow >> >>12G before the drop and 250m after the drop/create/insert. >> >> > >Have you been running vacuum and vacuum full? > >What version of Postgresql are you running, and what OS are you on? > > > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html > >
On Thu, 7 Nov 2002, Stephane Tessier wrote: > The database version is Postgresql 7.2.1 > And I run 4 time/day vacuum but I cannot lock tables so I don't want to > use a vacuum full.... the question is:is it a problem with pgsql or > Is it normal to have to stop the database to do maintenance... > My boss WANT (!!!!!) that we find the trouble but is there a trouble???? > > If it's normal... we'll have to live with it or migrate to Oracle... Well, that depends. You don't have to stop the database, but you might have to stop all transactions to get the vacuuming to work. Have you figured out which exact table or index is the one that keeps on growing? See this section of the manual (the 7.3 beta manual has this stuff, it's not in the 7.2.x manual) http://developer.postgresql.org/docs/postgres/diskusage.html See if you can figure out which exact table or index is causing the problem, that may help a bit. > -----Original Message----- > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > Sent: 7 novembre, 2002 12:18 > To: Stephane Tessier > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] table growing > > On Mon, 4 Nov 2002, Stephane Tessier wrote: > > > Hi > > > > I have a growing database problem. I insert rows in a table > > automatically(1500 by hour approx) and I use this table like a > temporary > > table 24/7.All rows inserted are > > deleted(maybe 12-24 hours later)but the database grow all the time I > > tried vacuum,analyze,vacuum full, reindex, max_fsm_pages > =100000,1000000 > > etc. > > > > the only way to reduce space is dump table /drop table/create table > and > > insert after but after 12-24 hours. the table is very big and request > > are very slow > > > > 12G before the drop and 250m after the drop/create/insert. > > Have you been running vacuum and vacuum full? > > What version of Postgresql are you running, and what OS are you on? > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >