Re: Memory Issue - Mailing list pgsql-general

On Thu, Nov 3, 2011 at 7:34 AM, Ioana Danes <ioanasoftware@yahoo.ca> wrote:
> Hello Everyone,
>
> I have a performance test running with 1200 clients performing this transaction every second:
>
>
> begin transaction
> select nextval('sequence1');
> select nextval('sequence2');
> insert into table1;
> insert into table2;
> commit;
>
> Table1 and table2 have no foreign keys and no triggers. There are 13 indexes on table1 and 5 indexes on table2.
>
> We do use connection pooling but because the clients commit this transaction every second I basically have 1200
connectionsall the time. 
>
> The db server is dedicated running on
>
> SUSE Linux Enterprise Server 11 (x86_64)
> VERSION = 11
> PATCHLEVEL = 1
>
> Postgres 9.0.3 (Same behaviour on Postgres 9.1.1):
>
>
> The server has 16GB of RAM and the postgres parameters are:
>
> shared_buffers = 4GB
> work_mem = 1MB
> maintenance_work_mem = 2GB
> effective_cache_size = 8GB
>
> wal_level = minimal
> wal_buffers = 1MB
>
> checkpoint_segments =16
> checkpoint_warning = 30s
> archive_mode = off
>
> autovacuum = off
>
> kernel.shmmax=5368709120 (5GB)
> kernel.shmall=5368709120 (5GB)
>
> The test performs well for about an hour with 1150 TPS and then the TPS goes down really bad and the clients
timeout...
> I watched the memory usage and the slowness is caused by swapping:
>
> vmstat
>
> procs -----------memory---------- ---swap-- -----io---- -system--
>      -----cpu------
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs
>      us sy id wa st
> 33  1 1149892  77484    436 3289824 1012 2700  1016  5844 12285
>      35318 43 32  1  0 25
> 28  1 1150348  73996    432 3291632  908 1300   908  4684 13668
>      29100 43 31  1  0 24
>  8  1 1151704  75212    440 3292756 1048 2260  1056 10300 13844
>      18628 39 33  6  1 22
>  8  1 1152716  75364    428 3294028 1640 1932  1640  6780 15325
>      17785 38 34  5  1 22
> 11  1 1154024  94356    444 3278828 1260 2328  1276  6752 15171
>      15538 40 30  7  1 22
>  1  0 1154876  98156    480 3281456 1572 1844  1596  8260 14690
>      14451 32 32 13  2 19
>  0  0 1154892 100588    492 3281636   56  108    68   932 2744
>      2082  2  8 88  1  1
>
>
> free
> --------------------------------
>              total       used       free     shared    buffers     cached
> Mem:      16790144   16710092      80052          0       1724
>      3337172
> -/+ buffers/cache:   13371196    3418948
> Swap:      4194296    1162980    3031316
>
>
> top:
> --------------------------------
> top - 12:12:00 up  1:54,  6 users,  load average: 37.57, 41.52, 31.24
> Tasks: 1309 total,  42 running, 1267 sleeping,   0 stopped,   0
>      zombie
> Cpu(s): 29.4%us, 13.8%sy,  0.0%ni, 23.8%id, 12.8%wa,  0.0%hi,
>      5.3%si, 14.8%st
> Mem:     16396M total,    16310M used,       85M free,        2M
>      buffers
> Swap:     4095M total,     1187M used,     2908M free,     3213M
>      cached
>
>   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+
>      COMMAND
>    39 root      20   0     0    0    0 S    4  0.0   3:42.18
>      kswapd0
>  2282 postgres  20   0 57628  608  332 R    3  0.0   2:18.42
>      postmaster
>  9722 postgres  20   0 4308m 220m 215m S    2  1.3   0:03.01
>      postmaster
> 10483 postgres  20   0 4303m 164m 160m S    2  1.0   0:02.02
>      postmaster
> 10520 postgres  20   0 4303m 158m 155m R    2  1.0   0:02.24
>      postmaster
>  9005 postgres  20   0 4308m 298m 294m S    2  1.8   0:04.52
>      postmaster
>
>
> After another half an hour almost the entire swap is used and the system performs really bad 100 TPS or lower.
> It never runs out of memory though!
>
> I would like to ask for your opinion on this issue.
> My concerns are why the memory is not reused earlier and it is using the swapping when the system does only these 2
inserts.
> Is this an OS issue, postgres issue, configuration issue?
> Your advice is greatly appreciated.

You can try a few things.

1: lower your shared_buffers.  It's unlikely you really need 4G for
them.  A few hundred megs is probably plenty for the type of work
you're doing.  Let the kernel cache the data you're not hitting right
this second.

2: Set swappiness to 0.    I.e. edit /etc/sysctl.conf and add a line
like vm.swappiness = 0 then run sudo sysctl -p

3: Turn off overcommit.  Same as number 2, set vm.overcommit_memory =
2 which will turn off the ability of linux to overcommit memory and
should then turn off the OOM killer.

4: just turn off swap. With only 16Gigs this is a tad dangerous,
especially if you haven't turned off the OOM in step 3.  Memory is
cheap, throw 32G at least into the machine.  With 1200 users, you
really need plenty of memory.  To turn off swap add something like
/sbin/swapoff -a to the /etc/rc.local file (before the exit line
natch)