[PERFORM] Newbie performance problem - semop taking most of time ?

2012-09-21 Thread mal.oracledba 
Using postgreSQL 9.2 with the following settings:

max_connections = 1000  # (change requires restart)
shared_buffers = 65536MB# min 128kB
work_mem = 16MB # min 64kB
effective_io_concurrency = 48   # 1-1000; 0 disables prefetching
wal_buffers = 16MB  # min 32kB, -1 sets based on
shared_buffers
commit_delay = 1# range 0-10, in microseconds
commit_siblings = 100   # range 1-1000
checkpoint_segments = 256   # in logfile segments, min 1, 16MB
each
checkpoint_timeout = 10min  # range 30s-1h
checkpoint_completion_target = 0.9  # checkpoint target duration, 0.0 -
1.0
effective_cache_size = 65536MB

Server spec is Xeon 8 Core/16threads, 512Gb memory. Database size is 100G. 
Underlying SAN is raid 1/0.
OS is Linux Redhat 6.2. Kernel 2.6.32

Running hammer ora TPC-C type load. Under 20 user load (no key and think)
getting approx 180,000 TPM - which is about half of what I get with another
database vendor. 

tracing the process (strace -r) I get outtput like that below - a lot of the
time seems to be doing semop type operations (eg 0.001299 semop(13369414,
{{3, -1, 0}}, 1) = 0)

Just wondered if anyone could tell me what is going on there and any
possibilities that I might have to decrease this wait time ?


 0.000176 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.31 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.001102 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.49 semop(13369414, {{4, 1, 0}}, 1) = 0
 0.000405 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.49 semop(13369414, {{10, 1, 0}}, 1) = 0
 0.000337 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.57 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.74 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.000779 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.000847 sendto(10,
"T\0\0\0\37\0\1neword\0\0\0\0\0\0\0\0\0\6\244\377\377\377\377\377\377\0\0"...,
63, 0, NULL, 0) = 63
 0.63 recvfrom(10, "Q\0\0\0(select neword(142,1001,8,23"..., 8192,
0, NULL, NULL) = 41
 0.000463 lseek(12, 0, SEEK_END)= 52486144
 0.57 lseek(13, 0, SEEK_END)= 6356992
 0.001299 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.000864 semop(13402183, {{2, 1, 0}}, 1) = 0
 0.000420 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.000675 semop(13402183, {{7, 1, 0}}, 1) = 0
 0.000445 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.000156 semop(13369414, {{3, -1, 0}}, 1) = 0
 0.001458 semop(13369414, {{6, 1, 0}}, 1) = 0

Cheers,

Mal





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Newbie-performance-problem-semop-taking-most-of-time-tp5724566.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Newbie performance problem - semop taking most of time ?

2012-09-23 Thread mal.oracledba 

CPU on the server shows approx 60% used/40 % idle


 vmstat 5 5
procs ---memory-- ---swap-- -io --system--
-cpu-
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id
wa st
18  0  0 160465072 455200 3521852480037   42120  4 
1 95  0  0
14  1  0 160321088 455200 35231504000 0 30559 30496 48583 48 
4 46  1  0
 1  0  0 160153584 455204 3524666240013 57266 35147 56949 56 
5 38  2  0
19  1  0 160030544 455204 3525775040013 27765 27924 41981 41 
3 54  1  0
10  0  0 159862800 455204 3527317760029 37807 35591 58193 57 
5 37  2  0



A snapshot of 'perf' output for one of the users below. It doesnt change
much from that profile.



   PerfTop: 319 irqs/sec  kernel: 7.5%  exact:  0.0% [1000Hz cycles], 
(target_pid: 18450)
-

 samples  pcnt functionDSO
 ___ _ ___


  235.00  7.5% AllocSetAlloc  
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
  155.00  5.0% SearchCatCache 
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
  134.00  4.3% hash_search_with_hash_value
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
  128.00  4.1% LWLockAcquire  
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
  110.00  3.5% _bt_compare
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
  106.00  3.4% __memcpy_ssse3_back /lib64/libc-2.12.so
  106.00  3.4% XLogInsert 
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
   92.00  2.9% cmp_numerics   
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
   73.00  2.3% ExecInitExpr   
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
   64.00  2.0% MemoryContextAlloc 
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
   61.00  1.9% GetSnapshotData
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
   60.00  1.9% _int_malloc /lib64/libc-2.12.so
   58.00  1.9% nocache_index_getattr  
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
   49.00  1.6% PinBuffer  
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres
   49.00  1.6% cmp_abs_common 
/post/PostgreSQL2/postgresql-9.2rc1/bin/postgres



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Newbie-performance-problem-semop-taking-most-of-time-tp5724566p5725062.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance