Thread: Postgres eats all memory
Hi there, we are running a fresh Postgres 8.3 installation with a single database with about 80GB of data. After a while the whole system memory is eaten up and every operation becomes very slow. Shortly after a system reboot and even without sending queries against the database the whole system memory is consumed after some time. Are there any settings that need to be set to avoid this? Currently the default settings are used ... The system is a Suse Enterprise Linux (64bit). Kind regards Eric Bartels
On 2008-08-13 10:06, Bartels, Eric wrote: > After a while the whole system memory is eaten up and every > operation becomes very slow. Show us: - output of "free" command, when server gets slow. - output of "ps v --sort=-size | head -10" - output of "ps auxww | grep postgres" - in terminal start "top", write "fp", enter, "Fp", enter; copy us upper half of your terminal screen. - what options did you change in postgresql.conf? Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh
In response to "Bartels, Eric" <e.bartels@customsoft.de>: > Hi there, > > we are running a fresh Postgres 8.3 installation with a single > database with about 80GB of data. > > After a while the whole system memory is eaten up and every > operation becomes very slow. Shortly after a system reboot > and even without sending queries against the database the > whole system memory is consumed after some time. > > Are there any settings that need to be set to avoid this? > Currently the default settings are used ... > > The system is a Suse Enterprise Linux (64bit). Provide some snapshots of the top command. Default settings for PostgreSQL will not use all system memory, they're actually too memory conservative for most use. You're missing a TON of details here. I recommend you tell the list how _much_ memory your system as, in additional to providing your postgresql.conf file and a top snapshot demonstrating the problem. My suspicion is one or more of the following: 1) You don't have very much RAM in your system and you're overloading it with connections or otherwise 2) You're running things other than PG on this system that are eating RAM. 3) You're being fooled by the fact that Linux will use all the available RAM all the time (which isn't particularly a bad thing) -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
On Wed, Aug 13, 2008 at 10:06:37AM +0200, Bartels, Eric wrote: > After a while the whole system memory is eaten up and every > operation becomes very slow. Shortly after a system reboot > and even without sending queries against the database the > whole system memory is consumed after some time. You don't even show any evidence here that Postgres is using the memory. How do you know that? A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
On Wed, Aug 13, 2008 at 4:06 AM, Bartels, Eric <e.bartels@customsoft.de> wrote: > Hi there, > > we are running a fresh Postgres 8.3 installation with a single > database with about 80GB of data. > > After a while the whole system memory is eaten up and every > operation becomes very slow. Shortly after a system reboot > and even without sending queries against the database the > whole system memory is consumed after some time. Something is very wrong. As others noted, PostgreSQL stock configurations use very little memory. This is mainly because the database relies on the operating system for buffering, less what is in the very conservatively set shared_buffers. In short, it is virtually impossible to run a modern computer out of memory with a stock configuration. That said, OOM culprits include: *) C function or 3rd party library leaking *) massive amounts of sorting going on (on stock config, would take a lot) *) something else on the box is doing it *) you have misdiagnosed the problem somehow merlin
Bartels, Eric wrote: In addition to info others have requested, the output of the following may be useful: select name,setting,source from pg_settings where not source = 'default'; Cheers, Steve
Hi all, thank you very much for your quick response and your support. It seems we found the culprit. The shown memory consumption (top, free -m) was a false trace. As Bill said it is not a bad thing that the systems uses the available memory... The culprit in this case slowing down Postgres was the hard drive itself. The performance of the sata-disks (raid) was very bad. We replaced them with sas-disks (raid) and now everything runs as fast as expected. Greetings Eric Bartels -----Original Message----- From: Bill Moran [mailto:wmoran@collaborativefusion.com] Sent: Wednesday, August 13, 2008 2:44 PM To: Bartels, Eric Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres eats all memory In response to "Bartels, Eric" <e.bartels@customsoft.de>: > Hi there, > > we are running a fresh Postgres 8.3 installation with a single > database with about 80GB of data. > > After a while the whole system memory is eaten up and every > operation becomes very slow. Shortly after a system reboot > and even without sending queries against the database the > whole system memory is consumed after some time. > > Are there any settings that need to be set to avoid this? > Currently the default settings are used ... > > The system is a Suse Enterprise Linux (64bit). Provide some snapshots of the top command. Default settings for PostgreSQL will not use all system memory, they're actually too memory conservative for most use. You're missing a TON of details here. I recommend you tell the list how _much_ memory your system as, in additional to providing your postgresql.conf file and a top snapshot demonstrating the problem. My suspicion is one or more of the following: 1) You don't have very much RAM in your system and you're overloading it with connections or otherwise 2) You're running things other than PG on this system that are eating RAM. 3) You're being fooled by the fact that Linux will use all the available RAM all the time (which isn't particularly a bad thing) -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023