Thread: vacuum and 24/7 uptime
Is Postgresql ready for 24/7 uptime? Our tests have shown that vacuumdb requires downtime, and if one does this nightly as suggested, well, one has downtime, 40+ minutes in our case. My company wants to replace MS SQL Server with PostgreSQL, but we can't afford downtime to do database maintenance. Is it possible that we are doing something wrong? What are the plans for future versions of pgsql? Will vacuum be optomized or otherwise enhanced to execute more quickly and/or not lock tables? Thanks, Mark PS I posted more details to the hackers list.
Hi Mark, This is being worked on now. I believe the 7.2 release will have enable you to run a vacuum with no downtime. -r At 03:39 PM 7/11/01 -0600, Mark wrote: >Is Postgresql ready for 24/7 uptime? Our tests have shown that vacuumdb >requires downtime, and if one does this nightly as suggested, well, one has >downtime, 40+ minutes in our case. > >My company wants to replace MS SQL Server with PostgreSQL, but we can't >afford downtime to do database maintenance. Is it possible that we are doing >something wrong? > >What are the plans for future versions of pgsql? Will vacuum be optomized or >otherwise enhanced to execute more quickly and/or not lock tables? > >Thanks, > >Mark > >PS >I posted more details to the hackers list. > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://www.postgresql.org/search.mpl > > > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
We increased shared memory in the linux kernel, which decreased the vacuumdb time from 40 minutes to 14 minutes on a 450 mhz processor. We calculate that on our dual 1ghz box with ghz ethernet san connection this will go down to under 5 minutes. This is acceptable to us. Sorry about the unnecessary post. On Wednesday 11 July 2001 15:39, Mark wrote: > Is Postgresql ready for 24/7 uptime? Our tests have shown that vacuumdb > requires downtime, and if one does this nightly as suggested, well, one has > downtime, 40+ minutes in our case. > > My company wants to replace MS SQL Server with PostgreSQL, but we can't > afford downtime to do database maintenance. Is it possible that we are > doing something wrong? > > What are the plans for future versions of pgsql? Will vacuum be optomized > or otherwise enhanced to execute more quickly and/or not lock tables? > > Thanks, > > Mark > > PS > I posted more details to the hackers list. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl
On Mié 11 Jul 2001 13:46, Ryan Mahoney wrote:
> Hi Mark, This is being worked on now. I believe the 7.2 release will have
> enable you to run a vacuum with no downtime.
AFAIK, if UNDO is implemented, no vacuum will be needed to free space.
It would be great if UNDO would clean indexes.
Saludos.... :-)
--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques | mmarques@unl.edu.ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
> > Is Postgresql ready for 24/7 uptime? Our tests have shown that vacuumdb > requires downtime, and if one does this nightly as suggested, well, one has > downtime, 40+ minutes in our case. > > My company wants to replace MS SQL Server with PostgreSQL, but we can't > afford downtime to do database maintenance. Is it possible that we are doing > something wrong? > > What are the plans for future versions of pgsql? Will vacuum be optomized or > otherwise enhanced to execute more quickly and/or not lock tables? We hope to eliminate that downtime in 7.2. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Any thoughts as to when 7.2 is targeted for release? Tim ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: "Mark" <mark@ldssingles.com> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, July 11, 2001 3:06 PM Subject: Re: [GENERAL] vacuum and 24/7 uptime > > > > Is Postgresql ready for 24/7 uptime? Our tests have shown that vacuumdb > > requires downtime, and if one does this nightly as suggested, well, one has > > downtime, 40+ minutes in our case. > > > > My company wants to replace MS SQL Server with PostgreSQL, but we can't > > afford downtime to do database maintenance. Is it possible that we are doing > > something wrong? > > > > What are the plans for future versions of pgsql? Will vacuum be optomized or > > otherwise enhanced to execute more quickly and/or not lock tables? > > We hope to eliminate that downtime in 7.2. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
> Any thoughts as to when 7.2 is targeted for release? > The fall, 2001. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> On Mi? 11 Jul 2001 13:46, Ryan Mahoney wrote: > > Hi Mark, This is being worked on now. I believe the 7.2 release will have > > enable you to run a vacuum with no downtime. > > AFAIK, if UNDO is implemented, no vacuum will be needed to free space. > > It would be great if UNDO would clean indexes. UNDO is for rolling back aborted transactions. I have not heard it used to eliminate vacuum. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Wed, Jul 11, 2001 at 03:55:46PM -0600, Mark wrote: : : We increased shared memory in the linux kernel, which decreased the vacuumdb : time from 40 minutes to 14 minutes on a 450 mhz processor. We calculate that : on our dual 1ghz box with ghz ethernet san connection this will go down to : under 5 minutes. This is acceptable to us. Sorry about the unnecessary post. The other thing you can do is run vacuum more frequently. The less it has to do, the less time it takes (it appears). We run vacuum on an active system every half-hour with little to no delay (the tables aren't very big, though, like 10000 or 100000 rows, depending on the table). We also recreate the indices on the most frequent table every 12 hours, since vacuum doesn't clean up after indices (again, it appears). Of course, we're still looking forward to 7.2 where some of this will hopefully be unnecessary. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * philip@datafoundry.net
Greetings, Bruce! At 12.07.2001, 03:49, you wrote: >> Any thoughts as to when 7.2 is targeted for release? >> BM> The fall, 2001. Will you be even kinder and enlighten us, mere users, on the features planned for this release? ; -- Yours, Alexey V. Borzov, Webmaster of RDW.ru
On Thu, Jul 12, 2001 at 11:04:11AM +0400, Alexey Borzov <borz_off@rdw.ru> wrote: > > Will you be even kinder and enlighten us, mere users, on the features > planned for this release? ; http://www.postgresql.org/docs/todo.html
> Greetings, Bruce! > > At 12.07.2001, 03:49, you wrote: > > >> Any thoughts as to when 7.2 is targeted for release? > >> > > BM> The fall, 2001. > > Will you be even kinder and enlighten us, mere users, on the features > planned for this release? ; Light-weight vacuum with light locking and statistics of queries. All items marked with a dash on the TODO list will be in there. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruno Wolff III writes: > On Thu, Jul 12, 2001 at 11:04:11AM +0400, > Alexey Borzov <borz_off@rdw.ru> wrote: > > > > Will you be even kinder and enlighten us, mere users, on the features > > planned for this release? ; > > http://www.postgresql.org/docs/todo.html Those are not the features planned for this release. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Alexey Borzov writes: > Will you be even kinder and enlighten us, mere users, on the features > planned for this release? ; National language support, if I can get some people to do the translating. (Hint, hint.) -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter Eisentraut wrote: >Alexey Borzov writes: > >>Will you be even kinder and enlighten us, mere users, on the features >>planned for this release? ; >> > >National language support, if I can get some people to do the translating. >(Hint, hint.) > Ill be happy to help with hebrew. >
> Bruno Wolff III writes: > > > On Thu, Jul 12, 2001 at 11:04:11AM +0400, > > Alexey Borzov <borz_off@rdw.ru> wrote: > > > > > > Will you be even kinder and enlighten us, mere users, on the features > > > planned for this release? ; > > > > http://www.postgresql.org/docs/todo.html > > Those are not the features planned for this release. Yes, only the ones marked with dashes are done for 7.2 so far. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
What type of reads/writes actually have to take place during a vacuum? Does it make a difference if there is enough shared memory to fit the entire table being vacuumed? After I insert the records, the table size is about 685 M. I calculate that to be about 86K pages. The max we have tried on shared memory is about 12288 buffers ( 96M ). If buffers ~ pages then this table takes many more than we have tried so far. Am I totally off on this or is there merit to my logic? The following may help shed light on the subject. When I insert about 1 million records into a table and then vacuum that table, even after I've increased the shared memory, Vacuum still takes a tremendous amount of time on that table. It doesn't take near the time after I've done the initial vacuum but it is still very much more than is an acceptable time to lock that table in our system. After I the perform the initial vacuum ( which has taken as long as an 2 or more hours for that one table ) I notice two behaviors regarding the shared memory. First, if I left postmaster with the default shared memory. Vacuuming the table took a considerable amount of time. Then, increasing the shared memory to about 64 megs sped the process up quite a bit. I tried 96 megs and vacuum didn't seem to change much. The first time I vacuum after doing the insert though, doesn't seem to be effected by the amount of shared memory. PG lists a little vacuum information about the table and then prints the following for a very long time. DEBUG: removing transaction log file 0000000B00000085 DEBUG: removing transaction log file 0000000B00000083 DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: removing transaction log file 0000000B00000087 DEBUG: removing transaction log file 0000000B00000088 DEBUG: removing transaction log file 0000000B00000086 DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: removing transaction log file 0000000B0000008A DEBUG: removing transaction log file 0000000B0000008B DEBUG: removing transaction log file 0000000B00000089 DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES (DEBUG continues this way for approx 1 to 2 hours ). Finally, vacuum finishes. This behavior is specific to 7.2devel compiled from CVS on 7/11 When we tried it with REL7.1.2, the timing was similar, but the DEBUG statements only had create WAL_FILES and never removed them. We ran out of disk space before vacuum completed. We hope this is fixed in 7.2 but we also would like to know if there is anyway we can get around it now. Are we doing something wrong that is making vacuum take so long? I thought vacuum should never take 2.5 hours even if the table was pretty big. Some more info if your still interesed. The table that has 1 million rows has four ints, four varchars(20) and one text and a boolean. It has Primary key and two indexes each with two fields. We have one more table that has 2.8 million records. This one only has two fields, one index and the size of the table is only 180 Megs as compared to the 685 in the first table. Vacuumb takes care of this table in a reasonable amount of time. Maybe number of pages in the table is the real key here?? Any suggestions much appreciated :-) Thanks Dennis > > otherwise enhanced to execute more quickly and/or not lock tables? > > We hope to eliminate that downtime in 7.2. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Mié 11 Jul 2001 20:49, Bruce Momjian wrote:
> > On Mi? 11 Jul 2001 13:46, Ryan Mahoney wrote:
> > > Hi Mark, This is being worked on now. I believe the 7.2 release will
> > > have enable you to run a vacuum with no downtime.
> >
> > AFAIK, if UNDO is implemented, no vacuum will be needed to free space.
> >
> > It would be great if UNDO would clean indexes.
>
> UNDO is for rolling back aborted transactions. I have not heard it used
> to eliminate vacuum.
I most have read badly, but I'm almost sure that I heard that UNDO was gonna
solve the overload that vacuum does on big DB.
Which is, in that case, the real status of the future of UNDO?
Saludos.... :-)
--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques | mmarques@unl.edu.ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
> On Mi? 11 Jul 2001 20:49, Bruce Momjian wrote: > > > On Mi? 11 Jul 2001 13:46, Ryan Mahoney wrote: > > > > Hi Mark, This is being worked on now. I believe the 7.2 release will > > > > have enable you to run a vacuum with no downtime. > > > > > > AFAIK, if UNDO is implemented, no vacuum will be needed to free space. > > > > > > It would be great if UNDO would clean indexes. > > > > UNDO is for rolling back aborted transactions. I have not heard it used > > to eliminate vacuum. > > I most have read badly, but I'm almost sure that I heard that UNDO was gonna > solve the overload that vacuum does on big DB. > Which is, in that case, the real status of the future of UNDO? UNDO was discussed as a solution for subtransactions and for row reuse of aborted transactions. I have not heard it for clearing out expire rows caused by DELETE or UPDATE. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Jue 12 Jul 2001 16:42, Bruce Momjian wrote: > > On Mi? 11 Jul 2001 20:49, Bruce Momjian wrote: > > > > I most have read badly, but I'm almost sure that I heard that UNDO was > > gonna solve the overload that vacuum does on big DB. > > Which is, in that case, the real status of the future of UNDO? > > UNDO was discussed as a solution for subtransactions and for row reuse > of aborted transactions. I have not heard it for clearing out expire > rows caused by DELETE or UPDATE. But the discussion started with a long mail from Tom who dropped the a posible solution of the row reuse of the ones which have been expired by DELETE or UPDATE. Saludos... :-) P.D.: If there is someone interested in the mail Tom send: http://fts.postgresql.org/db/mw/msg.html?mid=118484 -- Cualquiera administra un NT. Ese es el problema, que cualquiera administre. ----------------------------------------------------------------- Martin Marques | mmarques@unl.edu.ar Programador, Administrador | Centro de Telematica Universidad Nacional del Litoral -----------------------------------------------------------------
Philip Molter wrote: > > table). We also recreate the indices on the most frequent table every > 12 hours, since vacuum doesn't clean up after indices (again, it > appears). > Are you sure about that? Since an index grows with each insert or update they would soon take up the whole disk for any active databases. I see in my postgres log: DEBUG: Index ml_pkey: Pages 882; Tuples 174067: Deleted 18. CPU 0.07s/0.62u sec. -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Mark wrote: > > Is Postgresql ready for 24/7 uptime? Our tests have shown that vacuumdb > requires downtime, and if one does this nightly as suggested, well, one has > downtime, 40+ minutes in our case. > > My company wants to replace MS SQL Server with PostgreSQL, but we can't > afford downtime to do database maintenance. Is it possible that we are doing > something wrong? > > What are the plans for future versions of pgsql? Will vacuum be optomized or > otherwise enhanced to execute more quickly and/or not lock tables? > > Thanks, > > Mark > > PS > I posted more details to the hackers list. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl Hi Have you tried creating a duplicating each table then drop the original and rename the new one ? I don't know if that is what you are looking for but dropping a table and renaming a table is very quick. The new table should not need to be vacuumed because all the tuples are in use. A quick little script can have this done automatically at an appropriate time of the day or night. I don't have any databases that can't stand a few seconds of down time around 03:00. Simple solutions can be more effective than complex solutions {Keep It Simple Smart}. Guy Fraser -- There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on.