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 = 10000 # range 0-100000, 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.000031 semop(13369414, {{3, -1, 0}}, 1) = 0
0.001102 semop(13369414, {{3, -1, 0}}, 1) = 0
0.000049 semop(13369414, {{4, 1, 0}}, 1) = 0
0.000405 semop(13369414, {{3, -1, 0}}, 1) = 0
0.000049 semop(13369414, {{10, 1, 0}}, 1) = 0
0.000337 semop(13369414, {{3, -1, 0}}, 1) = 0
0.000057 semop(13369414, {{3, -1, 0}}, 1) = 0
0.000074 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.000063 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.000057 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.
