Postgresql 8.0 beta 1 - strange cpu usage statistics and slow vacuuming - Mailing list pgsql-general

From Shelby Cain
Subject Postgresql 8.0 beta 1 - strange cpu usage statistics and slow vacuuming
Date
Msg-id 20040819154115.63612.qmail@web41607.mail.yahoo.com
Whole thread Raw
Responses Re: Postgresql 8.0 beta 1 - strange cpu usage statistics and slow vacuuming
Re: Postgresql 8.0 beta 1 - strange cpu usage statistics and slow
List pgsql-general
I'm putting 8.0 through its paces and here are a few
things I've noticed on the native win32 port running
on my workstation (2.0g p4 w/256 megs of ram).

Here is the output of "vacuum verbose item":

====================
INFO:  vacuuming "public.item"
INFO:  "item": removed 246381 row versions in 24044
pages
DETAIL:  CPU -1.-1612s/-1.99u sec elapsed 1434.79 sec.
INFO:  "item": found 246381 removable, 492935
nonremovable row versions in 50413 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 100991 unused item pointers.
0 pages are entirely empty.
CPU 1081264882.-821s/0.02u sec elapsed 1682.87 sec.

Query returned successfully with no result in 1683460
ms.
====================

As you can see the cpu statistics are obviously bogus
although the elasped time is correct.

My other concern is the length of time that vacuum
runs when cost based vacuuming is disabled.

Under 8.0, if I run an update statement (update item
where set cost = cost + 0 where country = 'US' [causes
an update w/o really changing data]) that updates half
the rows in the table (~250k out of 500k - average
tuple width is about 500 bytes) and then execute a
regular vacuum it takes approximately 1400 seconds to
complete.  A vacuum full performed immediately after
takes on the order of 2000 seconds to complete.

During the update, I see anywhere from 5 to 10 meg/s
of disk transfer, an average disk queue length of 1-2
and a relatively large number of split I/O operations
per second (as reported via performance monitor).
However, during vacuum and vacuum full (once they
begin in earnest) I see less than 1 meg/s of disk
transfer, an average disk queue length of less than 1
and a virtually no split I/O operations per second.

With the same table data loaded under 7.4.x on cygwin
it takes approximately 100 seconds to complete the
vacuum after the same update.  A vacuum full run
immediately afterwards on the table takes around 250
seconds to complete.

During the update, vacuum and vacuum full I see
anywhere from 5 to 10 meg/s of disk transfer, an
average disk queue length of 1-2 and a relatively
large number of split I/O operations per second (as
reported via performance monitor).

In both cases, cpu usage is nil and the 7.4.x and 8.0
.conf files are virtually indentical.  Can anyone
offer an explanation as to why I'm seeing such a huge
performance difference in vacuum between 7.4.x and
8.0?

Regards,

Shelby Cain




__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Pass parameters to SQL script
Next
From: Tom Lane
Date:
Subject: Re: libpq: passwords WAS: scripting & psql issues