Re: Would my postgresql 8.4.12 profit from doubling RAM? - Mailing list pgsql-general

On Thu, Aug 30, 2012 at 6:42 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> Hello,
>
> I run CentOS 6.3 server with 16 GB RAM and:
>     postgresql-8.4.12-1.el6_2.x86_64
>     pgbouncer-1.3.4-1.rhel6.x86_64
>
> The modified params in postgresql.conf are:
>     max_connections = 100
>     shared_buffers = 4096MB
>
> and the pgbouncer runs with:
>     pool_mode = session
>     server_reset_query = DISCARD ALL;
>
> The main app is a card game with 30-500
> simultaneous users for which I save some
> playing stats into the db +
> PHP scripts to display those stats again.
>
> I have an option to double the RAM for EUR 180,-
> but wonder if it will improve any performance and
> also what to do on the PostgreSQL side once
> I've doubled the RAM (like double shared_buffers?
> but how do I find out if it's needed, maybe they're empty?)
>
> Below is a typical top output, the pref.pl is my game daemon:
>
> top - 13:40:30 up 21 days,  5:11,  1 user,  load average: 0.61, 1.14, 1.31
> Tasks: 232 total,   1 running, 231 sleeping,   0 stopped,   0 zombie
> Cpu0  : 14.6%us,  0.3%sy,  0.0%ni, 84.4%id,  0.3%wa,  0.0%hi,  0.3%si,  0.0%st
> Cpu1  :  3.0%us,  0.0%sy,  0.0%ni, 97.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu2  :  7.3%us,  0.0%sy,  0.0%ni, 92.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu3  :  7.3%us,  0.0%sy,  0.0%ni, 92.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu4  : 10.0%us,  0.0%sy,  0.0%ni, 90.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu5  :  2.3%us,  0.0%sy,  0.0%ni, 97.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu6  :  0.0%us,  0.3%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu7  :  1.7%us,  0.0%sy,  0.0%ni, 98.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
> Mem:  16243640k total, 14091172k used,  2152468k free,   621072k buffers
> Swap:  2096056k total,        0k used,  2096056k free,  8929900k cached
>
>   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
> 19992 postgres  20   0 4378m 782m 775m S 11.0  4.9   2:28.58 postmaster
> 16184 nobody    20   0  116m  21m 3908 S  9.0  0.1  22:01.32 pref.pl
> 16187 postgres  20   0 4375m 502m 497m S  7.3  3.2  37:13.48 postmaster
> 20229 postgres  20   0 4377m 426m 420m S  6.3  2.7   0:07.01 postmaster
> 20201 postgres  20   0 4378m 512m 505m S  4.7  3.2   0:23.65 postmaster
> 20135 postgres  20   0 4378m 771m 764m S  2.7  4.9   2:14.57 postmaster
> 20209 postgres  20   0 4377m 571m 564m S  2.0  3.6   1:14.34 postmaster
> 20030 postgres  20   0 4376m 890m 883m S  1.7  5.6   3:39.64 postmaster
> 20171 apache    20   0  370m  30m  16m S  0.7  0.2   0:01.87 httpd
> 18986 apache    20   0  371m  43m  28m S  0.3  0.3   0:11.47 httpd
> 19523 apache    20   0  370m  32m  18m S  0.3  0.2   0:07.18 httpd
> 19892 apache    20   0  380m  37m  19m S  0.3  0.2   0:04.86 httpd
> 20129 apache    20   0  376m  37m  16m S  0.3  0.2   0:02.39 httpd
> 20335 root      20   0 15148 1416  996 R  0.3  0.0   0:00.13 top

I would say no -- things are fine.  That's a top from a perfectly
healthy server.  Are you experiencing poor performance?  What problem
are you trying to solve exactly?  One thing to possibly explore if
you're seeing unpredictable query latency when lots of users are
logged on is pgbouncer transaction mode.

merlin