Postgres Pro
Downloads
Home   >   mailing lists

Thread: VACUUM FULL ANALYZE on 8.1.4 is slower then on 8.0

VACUUM FULL ANALYZE on 8.1.4 is slower then on 8.0

From
Andrzej Zawadzki
Date:
Hello!

After upgrade PostgreSQL from 8.0 to 8.1.4 a VACUUM FULL ANALYZE
process is much slower, from logs:

8.0
[13666][postgres][2006-10-06 01:13:38 CEST][1340121452] LOG:  statement: VACUUM FULL ANALYZE;
[13666][postgres][2006-10-06 01:39:15 CEST][0] LOG:  duration: 1536862.425 ms


8.1
[4535][postgres][2006-10-10 01:08:51 CEST][6144112] LOG:  statement: VACUUM FULL ANALYZE;
[4535][postgres][2006-10-10 02:04:23 CEST][0] LOG:  duration: 3332128.332 ms

Databases are equal.
I'm not using autovacuum.

Linux kernel is the same in two cases.
$uname -a
Linux zamczysko 2.6.13.4 #2 SMP Tue Oct 18 21:19:23 UTC 2005 x86_64 Dual_Core_AMD_Opteron(tm)_Processor_875 unknown PLD
Linux

Why new PostgreSQL is slower?

--
Andrzej Zawadzki

Re: VACUUM FULL ANALYZE on 8.1.4 is slower then on 8.0

From
Michael Fuhr
Date:
On Thu, Oct 19, 2006 at 03:30:35PM +0200, Andrzej Zawadzki wrote:
> After upgrade PostgreSQL from 8.0 to 8.1.4 a VACUUM FULL ANALYZE
> process is much slower, from logs:

Are you sure you need VACUUM FULL?  If you're vacuuming often enough
and your free space map settings are adequate then plain VACUUM
(without FULL) should suffice for routine use.

> 8.0
> [13666][postgres][2006-10-06 01:13:38 CEST][1340121452] LOG:  statement: VACUUM FULL ANALYZE;
> [13666][postgres][2006-10-06 01:39:15 CEST][0] LOG:  duration: 1536862.425 ms
>
>
> 8.1
> [4535][postgres][2006-10-10 01:08:51 CEST][6144112] LOG:  statement: VACUUM FULL ANALYZE;
> [4535][postgres][2006-10-10 02:04:23 CEST][0] LOG:  duration: 3332128.332 ms
>
> Databases are equal.

Equal how?  Number of tables?  Number of tuples?  Disk space used?
Activity, especially updates and deletes?  All of the above?

Have you used VACUUM VERBOSE to see how much work each VACUUM is doing?

Is it possible that 8.1 was built with --enable-cassert and 8.0
wasn't?  What does "SHOW debug_assertions" show on each server?

--
Michael Fuhr

Re: VACUUM FULL ANALYZE on 8.1.4 is slower then on 8.0

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> On Thu, Oct 19, 2006 at 03:30:35PM +0200, Andrzej Zawadzki wrote:
>> After upgrade PostgreSQL from 8.0 to 8.1.4 a VACUUM FULL ANALYZE
>> process is much slower, from logs:

> Is it possible that 8.1 was built with --enable-cassert and 8.0
> wasn't?  What does "SHOW debug_assertions" show on each server?

Different vacuum delay settings maybe?

            regards, tom lane

Re: VACUUM FULL ANALYZE on 8.1.4 is slower then on 8.0

From
Markus Schaber
Date:
Hi, Andrzej,

Andrzej Zawadzki wrote:
> After upgrade PostgreSQL from 8.0 to 8.1.4 a VACUUM FULL ANALYZE
> process is much slower, from logs:
>
> 8.0
> [13666][postgres][2006-10-06 01:13:38 CEST][1340121452] LOG:  statement: VACUUM FULL ANALYZE;
> [13666][postgres][2006-10-06 01:39:15 CEST][0] LOG:  duration: 1536862.425 ms
>
>
> 8.1
> [4535][postgres][2006-10-10 01:08:51 CEST][6144112] LOG:  statement: VACUUM FULL ANALYZE;
> [4535][postgres][2006-10-10 02:04:23 CEST][0] LOG:  duration: 3332128.332 ms
>
> Databases are equal.

Are they on equal disks? And in the same areas of those disks? Some
current disks tend to drop down their speed at the "end" of the LBA
address space drastically.

HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org