Skip site navigation (1) Skip section navigation (2)

Re: Performance hit on loading from HD

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Vitaly Belman <vitalyb(at)gmail(dot)com>
Cc: Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Performance hit on loading from HD
Date: 2004-08-28 21:34:23
Message-ID: 200408281434.23950.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Vitaly,

> I have a problem with certain queries performance. Trouble is that
> while their execution plan is pretty good and mostly their execution
> is great as well, their FIRST execution time (that is after you mount
> the database) is abysmal.

This is a well-known problem.   The general approach to this is to run a 
script to do select * queries against all important tables on system 
start-up.

> I realize that it happens due to the loading of data from the HD to
> the memory/swap and it wouldn't be too bad if I just could make the
> data stay in the memory, sadly, after a few minutes the data is back
> on the HD and running the query again results the same bad
> performance.

This could be for a variety of reasons.  On a standard platform (which yours 
most definitely is not), this would be due to database vacuuming, commits of 
large updates to your data, or another application using most of the system 
memory.

> Before going on, I should say that I am running PostgreSQL on CoLinux
> under Windows 2000. From what I read/tested, the CoLinux performance
> on CoLinux are matching to the performance of VMWare. Yet, I'm still
> wondering if it is a side effect of my development setup or if some of
> my settings are indeed wrong.

Probably you will continue to get worse-than-normal performance from both.   
You simply can't expect performance PostgreSQL running on an emulation 
environment.   If you could, we wouldn't have bothered with a Windows port.  
Speaking of which, have you started testing the Windows port?  I'd be 
interested in your comparison of it against running on CoLinux.

> I can live up with the fact that the data has to be loaded the first
> time it is accessed, but is it possible to make it stick longer in the
> memory? Is it the fact that CoLinux gets only 128MB of RAM? Or one of
> my settings should be fixed?

Well, mostly it's that you should start testing 8.0, and the Windows port.  
Not only should running native be better, but 8.0 (thanks to the work of Jan 
Wieck) is now able to take advantage of a large chunk of dedicated memory, 
which earlier versions were not.   Also, "lazy vacuum" and the "background 
writer", also features of 8.0 and Jan's work, should prevent PostgreSQL from 
cleaning out its own cache completely.    You should test this, 
*particularly* on Windows where we could use some more performance testing.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

In response to

pgsql-performance by date

Next:From: Mr PinkDate: 2004-08-29 18:04:48
Subject: Re: Why does a simple query not use an obvious index?
Previous:From: Vitaly BelmanDate: 2004-08-28 17:41:51
Subject: Performance hit on loading from HD

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group