Re: Postgres.exe Process taking too much memory and CPU usage - making the system extremely slow.

Hi All.

I have a program which import some 6000-10000 records in Postgres
database. However the import of data is successful but the postgres.exe
almost takes up entire memory and CPU.

I could see in the process list there are lot of postgres.exe are
running and one of the postgres.exe has taken up 98% of CPU.Even
stopping the Postgres database service is not killing this processes
running so I have to kill them individual processes.

I have the below parameters set in Postgresql.conf file.

# - Memory -

shared_buffers = 512MB

work_mem = 256MB

# - Free Space Map -

max_fsm_pages = 500000

max_fsm_relations = 5000

Below is the autovacuum settings

autovacuum = on

log_autovacuum_min_duration = 0
.

autovacuum_max_workers = 20

autovacuum_naptime = 5min

autovacuum_vacuum_threshold = 50

autovacuum_analyze_threshold = 50

autovacuum_vacuum_scale_factor = 0.2

autovacuum_analyze_scale_factor = 0.1

autovacuum_freeze_max_age = 200000000

autovacuum_vacuum_cost_delay = 20

I am using Postgres 8.3.5 on windows XP environment 32-bit.

I would really appreciate any help on this as this is one of the show
stopper for us.

A(dot)Bhattacharya(at)sungard(dot)com wrote:
>
> Hi All.
>
>
>
> I have a program which import some 6000-10000 records in Postgres
> database. However the import of data is successful but the
> postgres.exe almost takes up entire memory and CPU.
>
>
>
> I could see in the process list there are lot of postgres.exe are
> running and one of the postgres.exe has taken up 98% of CPU.Even
> stopping the Postgres database service is not killing this processes
> running so I have to kill them individual processes.
>
>
>
> I have the below parameters set in Postgresql.conf file.
>
>
>
> # - Memory -
>
>
>
> shared_buffers = 512MB
>
> work_mem = 256MB
>

in 32bit XP, a user process gets a MAXIMUM of 2gb of address space (the
other 2gb of the 32bit address space is reserved by the kernel.
Further, you are limited to typically 3.1-3.3GB of physical memory

you are using 512MB of this for the shared_buffers, *AND* every postgres
connection process will use 256MB of workmem each time it needs to do a
sort or index shuffle or similar operation. thats going to hurt.

on a XP machine, I'd keep shared_buffers down around 256MB max (assuming
you have 3-4GB of physical memory, and I'd keep work_mem down around
32MB. maybe less if you expect more than a couple connecctions.

In response to A(dot)Bhattacharya(at)sungard(dot)com :
> Hi All.
>
>
>
> I have a program which import some 6000-10000 records in Postgres database.

How? With COPY or with INSERT? Within one single transaction or every
Insert in an own transaction?

> I could see in the process list there are lot of postgres.exe are running and
> one of the postgres.exe has taken up 98% of CPU.Even stopping the Postgres
> database service is not killing this processes running so I have to kill them
> individual processes.

Really bad idea...

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

> # - Memory -
> shared_buffers = 512MB
> work_mem = 256MB
this settings would be good for PostgreSQL on Unix and Unixlike systems.

I got MUCH better results by drastically lowering shared_buffers on
Windows. Drastically = 8MB.

please try it. (there is no clear understanding WHY shared buffers on
Windows most often behave totally different then on unix)

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality

On Fri, Dec 11, 2009 at 9:02 AM, Massa, Harald Armin <chef(at)ghum(dot)de> wrote:
>> # - Memory -
>> shared_buffers = 512MB
>> work_mem = 256MB
> this settings would be good for PostgreSQL on Unix and Unixlike systems.
>
> I got MUCH better results by drastically lowering shared_buffers on
> Windows. Drastically = 8MB.

Wow - really? Greg and I did some rough pgbench experiments last year
and were finding the on a 4GB machine, running XP Pro, 512MB seemed to
be optimal, but it was only marginally better than 128 or 256MB. Going
lower than that made a noticable difference, and going higher we saw
performance dropping off again as well.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

>> I got MUCH better results by drastically lowering shared_buffers on
>> Windows. Drastically = 8MB.
>
> Wow - really? Greg and I did some rough pgbench experiments last year
> and were finding the on a 4GB machine, running XP Pro, 512MB seemed to
> be optimal, but it was only marginally better than 128 or 256MB. Going
> lower than that made a noticable difference, and going higher we saw
> performance dropping off again as well.

Yes, really, I am astonished too. Necessary information: it is a very
low concurrency workload, and system is as low as 1GB.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality

Incidentally, I seem to recall that there is a way of using greater
than 2GB of memory on 32-bit XP; It's a matter of turning on the
so-called "3gb switch":

http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx

Regards,
Peter Geoghegan

Peter Geoghegan wrote:
> Incidentally, I seem to recall that there is a way of using greater
> than 2GB of memory on 32-bit XP; It's a matter of turning on the
> so-called "3gb switch":
>
> http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx

Using 3GB user address space may have side effects. Some drivers
(particularly graphics drivers for high-end graphics cards) may not be
happy, for example. It's best used with caution if at all.

These days, if you need it, you're better off just going 64-bit.

--
Craig Ringer

Dave Page wrote:
> On Fri, Dec 11, 2009 at 9:02 AM, Massa, Harald Armin <chef(at)ghum(dot)de> wrote:
>
>> I got MUCH better results by drastically lowering shared_buffers on
>> Windows. Drastically = 8MB.
>>
>
> Wow - really? Greg and I did some rough pgbench experiments last year
> and were finding the on a 4GB machine, running XP Pro, 512MB seemed to
> be optimal, but it was only marginally better than 128 or 256MB. Going
> lower than that made a noticable difference, and going higher we saw
> performance dropping off again as well.
>
Dave is talking about Greg Stark here, lest anyone credit me for
something I wasn't involved in. I just updated the shared_buffers
section of "Tuning Your PostgreSQL Server" to reflect Dave's comments
and to generally clean up the Windows suggestions here. I recall seeing
some comments in the past that suggested earlier systems started to fall
off at closer to 64MB rather than 128MB, tweaked the wording there
accordingly. 8MB working out best is really unexpected though; I'd like
to know what you were doing where *that* was the optimal setting.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com

On Fri, Dec 11, 2009 at 3:11 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> Dave is talking about Greg Stark here, lest anyone credit me for something I

Err, yes indeed. Sorry. Keep forgetting that postgresql.org is the
only place in the world with more Gregs than Daves...

> wasn't involved in.  I just updated the shared_buffers section of "Tuning
> Your PostgreSQL Server" to reflect Dave's comments and to generally clean up
> the Windows suggestions here.  I recall seeing some comments in the past
> that suggested earlier systems started to fall off at closer to 64MB rather
> than 128MB, tweaked the wording there accordingly.  8MB working out best is
> really unexpected though; I'd like to know what you were doing where *that*
> was the optimal setting.

Thanks.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Greg,

>8MB working out best is
> really unexpected though; I'd like to know what you were doing where *that*
> was the optimal setting.

Developer machine, XP professional, 1 Gig memory. other applications
are running, ~2-4 concurrent acceses to PostgreSQL. Value was in the
200MB range, system trashed the harddrive badly. Setting down to 8MB,
and DB acesses AND the rest of the system run much smoother. Did not
benchmark queries; difference between settings was too obvious (from
many seconds down to <2 seconds)

A server (2Gig, win2003, 8.4) is set to 32MB and runs as smoothly as with 128.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality

Peter Geoghegan wrote:
> Incidentally, I seem to recall that there is a way of using greater
> than 2GB of memory on 32-bit XP; It's a matter of turning on the
> so-called "3gb switch":
>
> http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx
>

which is suicide on the typical x86 32bit system that has max 3.2GB
(+/-) addressable physical memory[1], never mind the issues it causes
with reduced space for page tables and such.

if you read through all the microsoft KB articles and stuff on that
flag, it was recommended for use only under tightly controlled
circumstances, like a 32bit windows server that was dedicated to running
1-2 specific apps that were OK with it,.

[1] yes the hardware probably supports PAE to access physical memory
above 4GB via page table extensions, but XP 32bit doesn't.

> which is suicide on the typical x86 32bit system that has max 3.2GB (+/-)
> addressable physical memory[1], never mind the issues it causes with reduced
> space for page tables and such.
>
> if you read through all the microsoft KB articles and stuff on that flag, it
> was recommended for use only under tightly controlled circumstances, like a
> 32bit windows server that was dedicated to running 1-2 specific apps that
> were OK with it,.

Fair enough, but I wasn't actually advocating that as a solution to
the poster's problem. I was merely pointing out that it isn't strictly
true that there is an absolute limit of 2GB of addressable memory for
user processes in 32-bit XP.

Regards,
Peter Geoghegan