Обсуждение: 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

От
Andrzej Zawadzki
Дата:
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

От
Michael Fuhr
Дата:
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

От
Tom Lane
Дата:
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

От
Markus Schaber
Дата:
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