Message-ID: <54F902F6.6040608@BlueTreble.com>
Date: Thu, 5 Mar 2015 19:29:26 -0600
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Soni M <diptatapa@gmail.com>, "pgsql-performance@postgresql.org"
 <pgsql-performance@postgresql.org>
Subject: Re: slow server : s_lock and _bt_checkkeys on perf top
References: 
 <CAAMgDX=xTxCV0DMTt5VYGus-LO=1n9wNO7RQni0yvWmbVgD-kQ@mail.gmail.com>
In-Reply-To: 
 <CAAMgDX=xTxCV0DMTt5VYGus-LO=1n9wNO7RQni0yvWmbVgD-kQ@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 06 Mar 2015 01:29:35.7657 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN3PR11MB0338
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 3/4/15 1:31 PM, Soni M wrote:
> Hello All,
>
> Master db size 1.5 TB
> All postgres 9.1.13 installed from RHEL package.
> It has streaming replica and slony replica to another servers.
>
> Server performance is slower than usual, before that, there's a big
> query got cancelled and then performance get slow.
>
> No sign of IO wait.
>
> on sar, it's %user and %system dominate the cpu usage
> 01:25:04 PM     CPU     %user     %nice   %system   %iowait    %steal
>    %idle
> Average:        all     51.91      0.00     12.03      0.66      0.00
>    35.39
>
> on perf top, i saw
>   18.93%  postgres                                  [.] s_lock
>   10.72%  postgres                                  [.] _bt_checkkeys
> almost always at top.

_bt_checkkeys is the function that compares a row in a btree index to a 
condition. s_lock is a spinlock; the high CPU usage in there indicates 
there's heavy lock contention somewhere.

Is there one PG process that's clearly using more CPU than the others? 
What else is running in the database? Are there any unusual data types 
involved?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


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