473,320 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Postgresql 8.0 beta 1 - strange cpu usage statistics and slow vacuuming

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
2 1807
Shelby Cain <al******@yahoo.com> writes:
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": DETAIL: CPU -1.-1612s/-1.99u sec elapsed 1434.79 sec.
...
CPU 1081264882.-821s/0.02u sec elapsed 1682.87 sec.
Hmm ... something broken about getrusage() on Windows?
CC'd to pgsql-hackers-win32 for comment.
My other concern is the length of time that vacuum
runs when cost based vacuuming is disabled.


Are you sure you had cost-based vac disabled? I tried to reproduce
your experiment here. I saw some degradation in vacuuming speed
but not nearly as large as you're reporting (85 vs 73 seconds),
and as far as I could tell it was still maxing out my disk.
But the behavior you're describing is exactly what I'd expect if
cost-based vac was on.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #2
Shelby Cain wrote:
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.


On Windows XP with 8.0beta1 I'm experiencing different
values instead, after updating 800K rows the plain vacuum
takes 200 seconds and the vacuum full immediately after
takes 620 seconds.

In both case the cpu usage was near zero.
I'm using a 2.2GHZ 1GB di RAM and I'm using 64MB to workmem.

Regards
Gaetano Mendola


Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: John H | last post by:
Can anyone help with why our pages are slow to load during peak-use hours? We run a database site getting 4 million hits a month which becomes painfully slow during peak hours. The slowness is...
26
by: jini us | last post by:
Hi, I am starting a new project where I intend to use embedded database server in my win32 application. I intend to use VC++ microsoft studio 6.0 as my development environment. The...
59
by: Jeff Bowden | last post by:
For ease of configuration and other reasons, I would like for my single-user GUI app to be able to use postgresql in-process as a library accessing a database created in the users home directory. ...
1
by: Campano, Troy | last post by:
Do you know what I did wrong? I'm trying to enable some logging features in postgresql.conf, but now I get this message when I try to connect to the postgresql server... WARNING: ...
12
by: jao | last post by:
I have an application with a table that tracks objects with a "size" attribute. What we want to do is to periodically report on the number of these objects and the sum of the object sizes. The...
6
by: Jean-Guillaume LALANNE | last post by:
Hi, I am pretty new to the postgresql community. My question may seem a little bit strange but I'd like to know if it is possible to run 2 postgresql processes with 2 separate databases on the...
1
by: Shelby Cain | last post by:
When I enable log_statement_stats AND log_parser_stats in my postgresql.conf file and attempt the start the service I receive the following error: "Could not start the PostgreSQL Database SErver...
10
by: Henk Ernst Blok | last post by:
Hi Posgres users/developers, Can anyone explain why PosgreSQL (version 7.4.5 on Linux) does a full table scan to compute a count(*) on a base table after a vacuum analyze has been done with no...
0
by: Patvs | last post by:
I use poker software (HoldemManager) to keep track of the statistics (and show nice graphs) of millions of poker hand histories. This software (also PokerTracker 3) imports all the poker hands in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.