Hi all!

We have a database on postgreSQL 6.2 and it is extremely slow, so we
started vacuum on it. I know it locks the tables, so clients can not use
it until the process is finished, but it is extremely slow on a 1.800.000
record table and we don't know how to make it faster. Can anybody help me?

It seems it is writing an index file, but it grows very slowly.

I know we should use 7.3 at least, we are working on it, but we need to
survive this day with 6.2 :(

Thanks in advance

Laszlo Hornyak


Mon, 22 Sep 2003 18:07:01 +0200 (CEST)
Hornyak Laszlo <kocka@tigrasoft.hu>
postgres 6.2 vacuum

On Mon, 22 Sep 2003, Hornyak Laszlo wrote:

> Hi all!
>
> We have a database on postgreSQL 6.2 and it is extremely slow, so we
> started vacuum on it. I know it locks the tables, so clients can not use
> it until the process is finished, but it is extremely slow on a 1.800.000
> record table and we don't know how to make it faster. Can anybody help me?
>
> It seems it is writing an index file, but it grows very slowly.

You might be better off dropping indexes, vacuuming and then recreating
the indexes.

Mon, 22 Sep 2003 09:16:19 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: postgres 6.2 vacuum
On Mon, 22 Sep 2003, Hornyak Laszlo wrote:

> Hi all!
> 
> We have a database on postgreSQL 6.2 and it is extremely slow, so we
> started vacuum on it. I know it locks the tables, so clients can not use
> it until the process is finished, but it is extremely slow on a 1.800.000
> record table and we don't know how to make it faster. Can anybody help me?
> 
> It seems it is writing an index file, but it grows very slowly.
> 
> I know we should use 7.3 at least, we are working on it, but we need to
> survive this day with 6.2 :(

In all honesty, it'd probably be faster to convert than to wait for that 
vacuum to finish.

seriously.  

6.2 is like the model A of Postgresql versions.


Tue, 23 Sep 2003 10:53:36 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: postgres 6.2 vacuum

Yes, at the end we did the port at night, and in the morning the system
started without any problem. Some of the dumps from pg 6.2 was not realy
acceptable by 7.3, but it was easy to fix.

Thank you for your help!

Laszlo Hornyak

On Tue, 23 Sep 2003, scott.marlowe wrote:

> On Mon, 22 Sep 2003, Hornyak Laszlo wrote:
> 
> > Hi all!
> > 
> > We have a database on postgreSQL 6.2 and it is extremely slow, so we
> > started vacuum on it. I know it locks the tables, so clients can not use
> > it until the process is finished, but it is extremely slow on a 1.800.000
> > record table and we don't know how to make it faster. Can anybody help me?
> > 
> > It seems it is writing an index file, but it grows very slowly.
> > 
> > I know we should use 7.3 at least, we are working on it, but we need to
> > survive this day with 6.2 :(
> 
> In all honesty, it'd probably be faster to convert than to wait for that 
> vacuum to finish.
> 
> seriously.  
> 
> 6.2 is like the model A of Postgresql versions.
> 


Wed, 24 Sep 2003 10:00:12 +0200 (CEST)
Hornyak Laszlo <kocka@tigrasoft.hu>
Re: postgres 6.2 vacuum

I wonder if we should have an auto-responder so when someone says they
are running 6.5, we can reply --- Yikes, upgrade.

In fact, we could go with a little chart:

	7.3.4	great
	7.3.0-3 please upgrade, it is easy
	7.2	consider upgrading
	7.1	wow, that is old
	7.0	you need an upgrade, pal
	<=6.5	run, don't walk, to the nearest PostgreSQL ftp server


---------------------------------------------------------------------------

Hornyak Laszlo wrote:
> 
> Yes, at the end we did the port at night, and in the morning the system
> started without any problem. Some of the dumps from pg 6.2 was not realy
> acceptable by 7.3, but it was easy to fix.
> 
> Thank you for your help!
> 
> Laszlo Hornyak
> 
> On Tue, 23 Sep 2003, scott.marlowe wrote:
> 
> > On Mon, 22 Sep 2003, Hornyak Laszlo wrote:
> > 
> > > Hi all!
> > > 
> > > We have a database on postgreSQL 6.2 and it is extremely slow, so we
> > > started vacuum on it. I know it locks the tables, so clients can not use
> > > it until the process is finished, but it is extremely slow on a 1.800.000
> > > record table and we don't know how to make it faster. Can anybody help me?
> > > 
> > > It seems it is writing an index file, but it grows very slowly.
> > > 
> > > I know we should use 7.3 at least, we are working on it, but we need to
> > > survive this day with 6.2 :(
> > 
> > In all honesty, it'd probably be faster to convert than to wait for that 
> > vacuum to finish.
> > 
> > seriously.  
> > 
> > 6.2 is like the model A of Postgresql versions.
> > 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 24 Sep 2003 18:08:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [ADMIN] postgres 6.2 vacuum

I think it is not that simple. How should I explain the company leaders
why I must stop the system. It may risk their bussiness success too. I can
tell them that the new db is more stable, but until the old one does the
job, it is still acceptable for them (it served the system for 5-6 years
or so). Once it crashes, it is a good reason to do the move.

Laszlo

On Wed, 24 Sep 2003, Bruce Momjian wrote:

> 
> I wonder if we should have an auto-responder so when someone says they
> are running 6.5, we can reply --- Yikes, upgrade.
> 
> In fact, we could go with a little chart:
> 
> 	7.3.4	great
> 	7.3.0-3 please upgrade, it is easy
> 	7.2	consider upgrading
> 	7.1	wow, that is old
> 	7.0	you need an upgrade, pal
> 	<=6.5	run, don't walk, to the nearest PostgreSQL ftp server
> 
> 
> ---------------------------------------------------------------------------
> 
> Hornyak Laszlo wrote:
> > 
> > Yes, at the end we did the port at night, and in the morning the system
> > started without any problem. Some of the dumps from pg 6.2 was not realy
> > acceptable by 7.3, but it was easy to fix.
> > 
> > Thank you for your help!
> > 
> > Laszlo Hornyak
> > 
> > On Tue, 23 Sep 2003, scott.marlowe wrote:
> > 
> > > On Mon, 22 Sep 2003, Hornyak Laszlo wrote:
> > > 
> > > > Hi all!
> > > > 
> > > > We have a database on postgreSQL 6.2 and it is extremely slow, so we
> > > > started vacuum on it. I know it locks the tables, so clients can not use
> > > > it until the process is finished, but it is extremely slow on a 1.800.000
> > > > record table and we don't know how to make it faster. Can anybody help me?
> > > > 
> > > > It seems it is writing an index file, but it grows very slowly.
> > > > 
> > > > I know we should use 7.3 at least, we are working on it, but we need to
> > > > survive this day with 6.2 :(
> > > 
> > > In all honesty, it'd probably be faster to convert than to wait for that 
> > > vacuum to finish.
> > > 
> > > seriously.  
> > > 
> > > 6.2 is like the model A of Postgresql versions.
> > > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > 
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 


Thu, 25 Sep 2003 08:36:05 +0200 (CEST)
Hornyak Laszlo <kocka@tigrasoft.hu>
Re: [ADMIN] postgres 6.2 vacuum
On Thursday 25 September 2003 07:36, Hornyak Laszlo wrote:
> I think it is not that simple. How should I explain the company leaders
> why I must stop the system. It may risk their bussiness success too. I can
> tell them that the new db is more stable, but until the old one does the
> job, it is still acceptable for them (it served the system for 5-6 years
> or so).

Here, I agree with you Hornyak - you've got 5+ years real-world experience 
with this version and it does what you want (mostly). I'm half tempted to 
downgrade myself ;-)

By the way - are you saying the your system has been running *without 
interruption* for 5 years?

Having said that, when (and it's when, not if) you upgrade, you'll be looking 
at some major changes in PG, so it's probably a good idea to test 7.3.4 and 
see what changes are necessary now, before you need to. Short-term, could you 
stop the system long enough to pg_dump it and restore? Again, test the 
process first.

> Once it crashes, it is a good reason to do the move.

And just *before* it crashes is the best time.

-- 
  Richard Huxton
  Archonet Ltd

Thu, 25 Sep 2003 20:57:27 +0100
Richard Huxton <dev@archonet.com>
Re: [ADMIN] postgres 6.2 vacuum

No, I got this job 2 months ago, I don`t know who managed it before, and I
don`t know why didn`t he upgrade. Until this week I didn`t have the chance
to upgrade.
But it runs now on 7.3.4.


On Thu, 25 Sep 2003, Richard Huxton wrote:

> On Thursday 25 September 2003 07:36, Hornyak Laszlo wrote:
> > I think it is not that simple. How should I explain the company leaders
> > why I must stop the system. It may risk their bussiness success too. I can
> > tell them that the new db is more stable, but until the old one does the
> > job, it is still acceptable for them (it served the system for 5-6 years
> > or so).
> 
> Here, I agree with you Hornyak - you've got 5+ years real-world experience 
> with this version and it does what you want (mostly). I'm half tempted to 
> downgrade myself ;-)

<OFF>
If you think you can do it better, the job is yours.
</OFF>

Laszlo Hornyak

> 
> By the way - are you saying the your system has been running *without 
> interruption* for 5 years?
> 
> Having said that, when (and it's when, not if) you upgrade, you'll be looking 
> at some major changes in PG, so it's probably a good idea to test 7.3.4 and 
> see what changes are necessary now, before you need to. Short-term, could you 
> stop the system long enough to pg_dump it and restore? Again, test the 
> process first.
> 
> > Once it crashes, it is a good reason to do the move.
> 
> And just *before* it crashes is the best time.
> 
> -- 
>   Richard Huxton
>   Archonet Ltd
> 


Fri, 26 Sep 2003 02:18:48 +0200 (CEST)
Hornyak Laszlo <kocka@tigrasoft.hu>
Re: [ADMIN] postgres 6.2 vacuum
Hornyak Laszlo wrote:

> I think it is not that simple. How should I explain the company leaders
> why I must stop the system. It may risk their bussiness success too. I can
> tell them that the new db is more stable, but until the old one does the
> job, it is still acceptable for them (it served the system for 5-6 years
> or so). Once it crashes, it is a good reason to do the move.

Well, I am sure there are data corruption bugs fixed between 6.2 and current CVS 
head which would count as large impact in terms of numbers and severity.

If your client business depends upon it, that is in fact a better reason to 
upgrade. If postgresql developers tells you to upgrade, that does count as 
recommendation.

Its not like oracle upgrade where you have to move the OS, hardware and spend a 
large amount of money. The impact of migration is restricted to downtime of 
servers and cleaning up any applications that depend upon any incorrect 
behaviour supported in past.

IMO you should move in all scenarios.

  Shridhar


Fri, 26 Sep 2003 11:59:58 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: [ADMIN] postgres 6.2 vacuum
On Friday 26 September 2003 02:29, Shridhar Daithankar wrote:
> Well, I am sure there are data corruption bugs fixed between 6.2 and
> current CVS head which would count as large impact in terms of numbers and
> severity.

Indeed there are.

> Its not like oracle upgrade where you have to move the OS, hardware and
> spend a large amount of money. The impact of migration is restricted to
> downtime of servers and cleaning up any applications that depend upon any
> incorrect behaviour supported in past.

This isn't necessarily true.  That old of a version of PostgreSQL is probably 
running on a quite out-of-date OS -- for instance, if the OS was Red Hat 
Linux, then the point at which 6.2.1 was shipped was RHL 5.0.  Can you even 
compile PostgreSQL 7.3.x on RHL 5.0 or its contemporaries?

I have had this problem, and still, at one client, have a box running 
PostgreSQL 6.5.3 because later PostgreSQL's haven't been well tested on RHL 
5.2.  There is a binary-only closed source app running on the box that won't 
run on even a Linux 2.2 kernel, much less a 2.4 kernel.  The 5.2 box is 
running the latest 2.0.x kernel.  That client depends upon behaviors of the 
older version of that application that the newer version of that application 
doesn't perform.  So they are quite literally stuck at 6.5.3.  I would love 
to get them up to something better, but, it's not at the moment worth enough 
to them to do it.  When the cost-benefit balance swings to the benefit side, 
things will change.

If I could even get the box up to RHL 6.2 I'd be better off, because 
PostgreSQL 7.3.x builds and runs well there.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu

Fri, 26 Sep 2003 09:22:26 -0400
Lamar Owen <lowen@pari.edu>
Re: [ADMIN] postgres 6.2 vacuum
Lamar Owen <lowen@pari.edu> writes:
> This isn't necessarily true.  That old of a version of PostgreSQL is probably
> running on a quite out-of-date OS -- for instance, if the OS was Red Hat 
> Linux, then the point at which 6.2.1 was shipped was RHL 5.0.  Can you even 
> compile PostgreSQL 7.3.x on RHL 5.0 or its contemporaries?

Surely.  We still support other platforms that make RHL 5.0 look like
the new kid on the block.  There might not be RPMs available, but I
can't believe it wouldn't compile from source.

I do agree that people running that old a Linux distro need to think
about updating more than just Postgres, though.  They have kernel bugs
as well as PG bugs to fear :-(

			regards, tom lane

Fri, 26 Sep 2003 10:52:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [ADMIN] postgres 6.2 vacuum 
On Friday 26 September 2003 10:52, Tom Lane wrote:
> Lamar Owen <lowen@pari.edu> writes:
> > This isn't necessarily true.  That old of a version of PostgreSQL is
> > probably running on a quite out-of-date OS -- for instance, if the OS was
> > Red Hat Linux, then the point at which 6.2.1 was shipped was RHL 5.0. 
> > Can you even compile PostgreSQL 7.3.x on RHL 5.0 or its contemporaries?

> Surely.  We still support other platforms that make RHL 5.0 look like
> the new kid on the block.  There might not be RPMs available, but I
> can't believe it wouldn't compile from source.

I think I tried a 7.1.x on 5.2 a long time ago, and it didn't build for some 
reason.  But that has been some time ago.  I might just build up a 5.2 system 
(plus errata) to see.

> I do agree that people running that old a Linux distro need to think
> about updating more than just Postgres, though.  They have kernel bugs
> as well as PG bugs to fear :-(

2.0 happily doesn't have many new bugs, and it is being maintained, IIRC.  
Just not by Red Hat.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu

Fri, 26 Sep 2003 12:11:28 -0400
Lamar Owen <lowen@pari.edu>
Re: [ADMIN] postgres 6.2 vacuum
Tom Lane wrote:

> Lamar Owen <lowen@pari.edu> writes:
>> This isn't necessarily true.  That old of a version of PostgreSQL is probably
>> running on a quite out-of-date OS -- for instance, if the OS was Red Hat 
>> Linux, then the point at which 6.2.1 was shipped was RHL 5.0.  Can you even 
>> compile PostgreSQL 7.3.x on RHL 5.0 or its contemporaries?
> 
> Surely.  We still support other platforms that make RHL 5.0 look like
> the new kid on the block.  There might not be RPMs available, but I
> can't believe it wouldn't compile from source.

It's not that simple. At some point we decide to support newer bison, 
flex, ant, jdk, tcl ... you go through some chain of upgrades.

> I do agree that people running that old a Linux distro need to think
> about updating more than just Postgres, though.  They have kernel bugs
> as well as PG bugs to fear :-(

Plus all the well known vulnerabilities used by worms and root kits ...


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Mon, 29 Sep 2003 11:41:38 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: [ADMIN] postgres 6.2 vacuum
On Monday 29 September 2003 11:41 am, Jan Wieck wrote:
> Tom Lane wrote:
> > I do agree that people running that old a Linux distro need to think
> > about updating more than just Postgres, though.  They have kernel bugs
> > as well as PG bugs to fear :-(

> Plus all the well known vulnerabilities used by worms and root kits ...

Assuming the db server is exposed directly to the Internet.  I know of old, 
obscurity-secured systems with none of the development tools necessary to use 
a rootkit (and rootkits are extremely rare in precompiled form for things 
that old and uncommon), and running none of the traditionally exploited 
services.  A Red Hat 5.2 server running only PostgreSQL 6.3.2, for instance, 
can be made very secure without upgrades by disposing of vulnerable services 
and running the latest and greatest 2.0.x series kernel (2.0.40, IIRC).  And 
once such a server is running on, say, a dual PPro 200 and serving up queries 
at the design rate, what is the impetus and motivation to upgrade?  

Furthermore, if one were leery of the SCO business with Linux 2.4.x and later, 
then one would be running a 2.0.x or 2.2.x kernel based system anyway, where 
SCO has not made any claims.  This brings us back to a Red Hat 5.2 for 2.0.x 
or Red Hat 7.0 (not 7.1 or later) for 2.2.x.  Although Red Hat 6.2 is a safer 
bet for a 2.2.x based system.  Just make sure to update it before connecting 
it to the Internet, if it is to be connected to the Internet.  Or don't run 
the rootable services that 6.2 has out of the box.

7.3.4 is buildable on 6.2, which makes it a nice balance point for those who 
want to do this sort of thing. 
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu


Mon, 29 Sep 2003 13:59:09 -0400
Lamar Owen <lowen@pari.edu>
Re: [ADMIN] postgres 6.2 vacuum