Обсуждение: 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.Bhattacharya@sungard.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.
Re: Postgres.exe Process taking too much memory and CPU usage - making the system extremely slow.
In response to A.Bhattacharya@sungard.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
Re: Postgres.exe Process taking too much memory and CPU usage - making the system extremely slow.
> # - 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@ghum.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
Re: Postgres.exe Process taking too much memory and CPU usage - making the system extremely slow.
>> 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
Re: Postgres.exe Process taking too much memory and CPU usage - making the system extremely slow.
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 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.On Fri, Dec 11, 2009 at 9:02 AM, Massa, Harald Armin <chef@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 greg@2ndQuadrant.com www.2ndQuadrant.com
On Fri, Dec 11, 2009 at 3:11 PM, Greg Smith <greg@2ndquadrant.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
Re: Postgres.exe Process taking too much memory and CPU usage - making the system extremely slow.
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.
Re: Postgres.exe Process taking too much memory and CPU usage - making the system extremely slow.
> 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