Re: [ADMIN] postgres 6.2 vacuum

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

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.

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.

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.
>

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(at)postgresql(dot)org)
>

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

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(at)postgresql(dot)org)
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>

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

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
>

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

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

Lamar Owen <lowen(at)pari(dot)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

On Friday 26 September 2003 10:52, Tom Lane wrote:
> Lamar Owen <lowen(at)pari(dot)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

Tom Lane wrote:

> Lamar Owen <lowen(at)pari(dot)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(at)Yahoo(dot)com #

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