Re: [GENERAL] Postgres.exe Process taking too much memory and CPU usage - making the system extremely slow.
John R Pierce
>
> 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.
--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
A. Kretschmer
> 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
Massa, Harald Armin
> shared_buffers = 512MB
> work_mem = 256MB
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
Dave Page
>> # - 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
Massa, Harald Armin
>> 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
--
Peter Geoghegan
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
Craig Ringer
> 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
Greg Smith
On Fri, Dec 11, 2009 at 9:02 AM, Massa, Harald Armin <ch...@ghum.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.
-- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support gr...@2ndQuadrant.com www.2ndQuadrant.com
Dave Page
> 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.
John R Pierce
> 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.
Peter Geoghegan
> 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