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 connections all 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
