On 03/10/15 21:39, ajaykbs wrote:
I am working in a public company who uses only open source
applications and databases. I have a problem with our critical
database which is write and read intensive. *version:* Postgresql-9.4
*Hardware:* HP DL980 (8-processor, 80 cores w/o hyper threading, 512GB
RAM) *Operating system: *Red Hat Enterprise Linux Server release 6.4
(Santiago) *uname -a* : Linux host1 2.6.32-358.el6.x86_64 #1 SMP Tue
Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux Single
database with separate tablespace for main-data, pg_xlog and indexes I
have a database having 770GB size and expected to grow to 2TB within
the next year. The database was running in a 2processor HP DL560 (16
cores) and as the transactions of the database were found increasing,
we have changed the hardware to DL980 with 8 processors and 512GB RAM.
*Problem* It is observed that at some times during moderate load the
CPU usage goes up to 400% and the users are not able to complete the
queries in expected time. But the load is contributed by some system
process only. The average connections are normally 50. But when this
happens the connections will shoot up to max-connections. *The sar
command output* 07:20:01 IST CPU %user %nice %system %iowait %steal
%idle 07:30:01 IST all 0.73 0.00 0.37 0.58 0.00 98.33 07:40:01 IST all
0.66 0.00 0.38 0.65 0.00 98.31 07:50:01 IST all 0.27 0.00 0.27 0.01
0.00 99.45 08:00:01 IST all 0.52 0.00 0.37 0.01 0.00 99.10 08:10:01
IST all 1.54 0.00 0.70 0.02 0.00 97.74 08:20:01 IST all 1.20 0.00 0.67
0.02 0.00 98.10 08:30:01 IST all 1.48 0.00 0.77 0.03 0.00 97.72
08:40:01 IST all 1.69 0.00 0.89 0.04 0.00 97.39 08:50:01 IST all 1.71
0.00 0.94 0.04 0.00 97.31 09:00:01 IST all 1.74 0.00 0.92 0.03 0.00
97.31 09:10:01 IST all 2.32 0.00 1.06 0.04 0.00 96.58 09:20:01 IST all
2.22 0.00 1.17 0.04 0.00 96.57 09:30:02 IST all 2.20 0.00 6.68 0.06
0.00 91.06 09:40:01 IST all 2.43 0.00 1.37 0.06 0.00 96.14 09:50:01
IST all 3.23 0.00 2.06 0.08 0.00 94.63 10:00:02 IST all 3.15 0.00 6.10
0.07 0.00 90.67 10:10:01 IST all 4.94 0.00 5.20 0.29 0.00 89.57
10:20:01 IST all 5.10 0.00 2.13 0.34 0.00 92.43 10:30:01 IST all 5.60
0.00 2.42 0.18 0.00 91.80 10:40:01 IST all 5.28 0.00 14.37 0.19 0.00
80.16 10:50:01 IST all 4.52 0.00 28.48 0.23 0.00 66.77 11:00:01 IST
all 5.25 0.00 9.02 0.18 0.00 85.55 11:10:01 IST all 5.77 0.00 4.96
0.27 0.00 89.00 11:20:01 IST all 5.70 0.00 2.74 0.19 0.00 91.37
11:30:01 IST all 5.72 0.00 5.91 0.20 0.00 88.17 11:40:01 IST all 5.66
0.00 2.81 0.37 0.00 91.15 11:50:01 IST all 5.90 0.00 8.80 0.10 0.00
85.19 12:00:01 IST all 6.44 0.00 3.40 0.13 0.00 90.03 12:10:01 IST all
7.18 0.00 4.52 0.11 0.00 88.18 12:20:02 IST all 4.40 0.00 37.84 0.07
0.00 57.70 12:30:01 IST all 5.66 0.00 2.98 0.10 0.00 91.26 12:40:01
IST all 5.74 0.00 3.05 0.11 0.00 91.10 Average: all 1.92 0.00 2.28
0.11 0.00 95.69 Postgresql.conf max_connections = 500 (can be reduced)
shared_buffers = 8500MB work_mem = 50MB maintenance_work_mem = 8064MB
checkpoint_segments = 132 checkpoint_timeout = 30min
checkpoint_completion_target = 0.9 This over load happens 5-6 times a
day. How to trace the cause of this problem?. My thoughts. 1. some
thing related to the numa systems memory management. 2. Some thing
related to the size of shared buffers. Please help Ajayakumar.BS
------------------------------------------------------------------------
View this message in context: Multi processor server overloads
occationally with system process while running postgresql-9.4
<http://postgresql.nabble.com/Multi-processor-server-overloads-occationally-with-system-process-while-running-postgresql-9-4-tp5868474.html>
Sent from the PostgreSQL - performance mailing list archive
<http://postgresql.nabble.com/PostgreSQL-performance-f2050081.html> at
Nabble.com.
A little bit of formatting might make the above a bit more readable...
One paragraph is hard to parse.
-Gavin
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
