Memory Issue - Mailing list pgsql-general

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.

Thank you for your time and help,
Ioana