Re: Would my postgresql 8.4.12 profit from doubling RAM?

On Tue, Sep 4, 2012 at 2:59 AM, Alexander Farber
<alexander(dot)farber(at)gmail(dot)com> wrote:
> Hello, thank you for your replies and sorry for the delay in my replying -
>
> On Thu, Aug 30, 2012 at 4:45 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> On Thu, Aug 30, 2012 at 8:42 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>>> users, and currently work_mem is set to 1M (the default.) If you
>>> increase that to 16M, that'd be max 1.6G of memory, which you have
>>> free anyway right now.
>
> I did look at the vmstat output, but can't deduce anything from it:

Looks like you're not real familiar with vmstat. It shows you
averages of things like context switches, blocks read and written and
so on, over time. The first line is the average since the server
booted up so isn't all that useful.

vmstat 10

and let it run for a few minutes during heavy load then cut and paste.

For instance here's some output from a production server:

vmstat 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
6 0 468388 3756476 1445228 94339352 0 0 3 116 0 0
15 2 83 0
4 0 468388 3742288 1445244 94328832 0 0 53 2992 18907
18320 12 2 86 0
5 0 468388 3731272 1445264 94334640 0 0 54 2826 23835
24463 17 3 80 0
2 0 468388 3722556 1445276 94341456 0 0 77 3638 23207
24297 17 3 80 0

Here's a page with a quick description of each field:

http://www.lazysystemadmin.com/2011/04/understanding-vmstat-output-explained.html

The ones to look for are b (high b means blocking IO ops) so/si which
means active swapping, in/cs interrupts / context switches per second.
On big servers numbers into the 10s of k are fine. 100s of k are
getting a bit much. It means how often your machine is switching
tasks. Too much switching and it spends all its time switching and
not doing anything.

us sy id wa are all % of the cpu(s) doing each of: user space work,
system work, idle, or waiting. Note that if you have 4 cores, and are
25% wa(it) that you have one core doing nothing but waiting. I.e. the
% is for the total of cores, not of 1 core.

> # free -m
> total used free shared buffers cached
> Mem: 15862 13289 2573 0 588 8407
> -/+ buffers/cache: 4293 11569
> Swap: 2046 0 2046

So you're not using swap, you have 2.5G free memory and 8.4G cached.
I'm assuming there's nothing to be gained adding memory unless you
could use larger work_mem for some of your work loads.

> I'll try changing work_mem to 2MB first - once I upgrade the RAM.

Yep, small steps, measured afterwards for impact is the way to go.