current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am running postgresql 9.2 on a 32-bit machine running windows xp with 4 gb of memory and I am getting out of memory errors. I suspect it has something to do with these two parameters I have changed related to memory:

shared_buffers = 1024 MB
maintenance_work_mem = 1024 MB

Before changing these parameters, I was not getting out-of-memory errors. Is this too much for a 32-bit machine?

share|improve this question
    
Operating system? Size of physical memory? Swap? Other software running on the same box? Without knowing these we could blame the aliens as well :) –  dezso Jan 29 '13 at 20:58
    
Sorry for the lack of detail. Added the OS and physical memory. There is other software running but they are not using a lot of resources. –  user972276 Jan 29 '13 at 21:03
    
Check the maintenance_work_mem description in the documentation. This size (relative to the remaining portion) seems overkill. How do your typical queries look like? How much memory do they consume? –  dezso Jan 29 '13 at 21:15
    
I am restoring from a dump file to an empty database. It looks like I am running out of memory during index creation –  user972276 Jan 29 '13 at 21:18
1  
Also, on Windows, large values for shared_buffers aren't as effective. You may find better results keeping the setting relatively low and using the operating system cache more instead. The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB. I'd start from lower, with both values. If and only if I hit the limits of the settings I'd raise those. If your biggest table is 200 MB, then 1 G for maintenance_work_mem sound like a waste. –  dezso Jan 29 '13 at 21:54

1 Answer 1

up vote 1 down vote

I usually prefer to start with lower shared buffers on both Linux and Windows, and keep work mem low also. Maintenance_work_mem can be left generally the default too. Keep in mind that too low a maintenance_work_mem may slow down db restores but it won't stop it. Generally you are better off starting low and then tweaking upward to find sweet spots than starting too high and working your way down.

Secondly you are reserving half of the memory on your box for PostgreSQL and only PostgreSQL. This can have very unfortunate side effects if you are doing anything else. Since this is Windows XP I am assuming it is not a dedicated server, and you are going to do far better with conservative settings here.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.