Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZiIkp-0000jL-7O for pgsql-performance@arkaria.postgresql.org; Sat, 03 Oct 2015 09:05:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZiIko-00020d-H6 for pgsql-performance@arkaria.postgresql.org; Sat, 03 Oct 2015 09:05:18 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZiIjA-00007a-L2 for pgsql-performance@postgresql.org; Sat, 03 Oct 2015 09:03:36 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ZiIj5-00041R-QV for pgsql-performance@postgresql.org; Sat, 03 Oct 2015 09:03:34 +0000 Received: from [10.1.1.3] ([121.98.97.164]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id t9393Hgn016975 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES128-SHA bits=128 verify=NOT); Sat, 3 Oct 2015 22:03:19 +1300 Subject: Re: Multi processor server overloads occationally with system process while running postgresql-9.4 To: ajaykbs , pgsql-performance@postgresql.org References: <1443861573710-5868474.post@n5.nabble.com> From: Gavin Flower Organization: ArchiDevSys Message-ID: <560F99D4.70309@archidevsys.co.nz> Date: Sat, 3 Oct 2015 22:03:16 +1300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.2.0 MIME-Version: 1.0 In-Reply-To: <1443861573710-5868474.post@n5.nabble.com> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org 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 > > Sent from the PostgreSQL - performance mailing list archive > 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance