Re: Would my postgresql 8.4.12 profit from doubling RAM?

On Thu, Aug 30, 2012 at 6:42 AM, Alexander Farber
<alexander(dot)farber(at)gmail(dot)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