Thread: how much memory to allot to postgres?
What is the suggested rule of thumb in allotting memory to postgres on a production level transactional database? If this was an example config, how much would you suggest allotting? Redhat 7.3 2 Gig EMC Ram Dual Pentium 1.7 mhz Thanks a head of time :-) brian
On 27 Jan 2003 at 13:17, Brian Maguire wrote: > What is the suggested rule of thumb in allotting memory to postgres on a > production level transactional database? Well, that depends upon your database size and data and transaction pattern. It is a very subjective question. > > If this was an example config, how much would you suggest allotting? > > Redhat 7.3 > 2 Gig EMC Ram > Dual Pentium 1.7 mhz 15000 shared buffers + 10Mb of WAL cache + 4MB of sort mem(Note this is per client) should be enough to handle enough data. Bye Shridhar -- Change is the essential process of all existence. -- Spock, "Let That Be Your Last Battlefield", stardate 5730.2
On 29 Jan 2003 at 13:34, Francisco J Reyes wrote: > On Tue, 28 Jan 2003, Shridhar Daithankar wrote: > > > 15000 shared buffers + 10Mb of WAL cache + 4MB of sort mem(Note this is per > > client) should be enough to handle enough data. > > Just checked my values and I have 128MB for vacuum and sort. Is that > overkill? Absolutely. Especially sort mem. If you are allocatingg 128MB sort mem for each connection, you will soon be dry with memory. I would say 8MB to 16MB sort memory depending upon number of conenctions you want to entertain at a time. Just remember it is per connection. Vacuum memory need not be that high. 32MB is like good enough. For vacuum to be very fast and effective, you also need to up the fsm relation pages. Tom has said that already. > 2GB ram also, but usually only have a handfull of connections. > Given that WAL buffer is listed in 8K block (correct?) then 10MB would be > 1310720? <Puzzled> How did you calculate that? If I am right, 10MB should be something like 1280 buffers isn't it? 14K buffers are 117MB or so, last I remember. Just recheck the calculation.. > It seems I only have 2MB of WAL buffer.. I have large daily loads of data. > Will a number above 10MB help? ie 16MB 16MB is good. Most important point is rule of thumb does not serve you anyway. You have to test run and hand tune your database. These are just experiences of other people which might help you. HTH Bye Shridhar -- Majority, n.: That quality that distinguishes a crime from a law.
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> On 29 Jan 2003 at 13:34, Francisco J Reyes wrote:
>> Just checked my values and I have 128MB for vacuum and sort. Is that
>> overkill?
> Absolutely. Especially sort mem. If you are allocatingg 128MB sort mem for each
> connection, you will soon be dry with memory. I would say 8MB to 16MB sort
> memory depending upon number of conenctions you want to entertain at a time.
> Just remember it is per connection.
Worse than that: it's per sort step (or hash step). It's not at all
unlikely that an N-table query would be running N-1 sorts or hashes in
parallel; plus maybe more to implement aggregation, grouping, ORDER BY,
etc. So unless all your queries are very simple, you'd better figure
on some multiple of sort_mem per connection.
>> It seems I only have 2MB of WAL buffer.. I have large daily loads of data.
>> Will a number above 10MB help? ie 16MB
> 16MB is good.
16MB is probably vast overkill. Are you able to measure any change in
performance at all from increasing wal_buffers? I can't see a reason
that more than a dozen or so wal buffers would be useful. Almost
certainly, those megabytes would be more effectively used as
general-purpose kernel disk cache.
regards, tom lane
On Tue, 28 Jan 2003, Shridhar Daithankar wrote: > 15000 shared buffers + 10Mb of WAL cache + 4MB of sort mem(Note this is per > client) should be enough to handle enough data. Just checked my values and I have 128MB for vacuum and sort. Is that overkill? 2GB ram also, but usually only have a handfull of connections. Given that WAL buffer is listed in 8K block (correct?) then 10MB would be 1310720? It seems I only have 2MB of WAL buffer.. I have large daily loads of data. Will a number above 10MB help? ie 16MB