Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UMooz-0004b8-Pu for pgsql-performance@arkaria.postgresql.org; Tue, 02 Apr 2013 00:11:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UMooz-0007AW-8r for pgsql-performance@arkaria.postgresql.org; Tue, 02 Apr 2013 00:11:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UMoox-00079Z-Rq for pgsql-performance@postgresql.org; Tue, 02 Apr 2013 00:11:27 +0000 Received: from [2404:130:0:10::40:2] (helo=mail.catalyst.net.nz) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UMoot-0001BZ-4X for pgsql-performance@postgresql.org; Tue, 02 Apr 2013 00:11:27 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.catalyst.net.nz (Postfix) with ESMTP id 0915D3212A; Tue, 2 Apr 2013 13:11:17 +1300 (NZDT) X-Virus-Scanned: Debian amavisd-new at catalyst.net.nz Received: from mail.catalyst.net.nz ([127.0.0.1]) by localhost (bertrand.catalyst.net.nz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id KGtBjnj8NFiN; Tue, 2 Apr 2013 13:11:16 +1300 (NZDT) Received: from [192.168.1.64] (122-60-64-230.jetstream.xtra.co.nz [122.60.64.230]) (Authenticated sender: mark.kirkwood) by mail.catalyst.net.nz (Postfix) with ESMTPSA id 1F54F31C65; Tue, 2 Apr 2013 13:11:16 +1300 (NZDT) Message-ID: <515A2223.5080204@catalyst.net.nz> Date: Tue, 02 Apr 2013 13:11:15 +1300 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130311 Thunderbird/17.0.4 MIME-Version: 1.0 To: Armand du Plessis CC: pgsql-performance Subject: Re: Problems with pg_locks explosion References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Host-Lookup-Failed: Reverse DNS lookup failed for 2404:130:0:10::40:2 (failed) X-Pg-Spam-Score: -1.1 (-) 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 In addition to tuning the various Postgres config knobs you may need to look at how your AWS server is set up. If your load is causing an IO stall then *symptoms* of this will be lots of locks... You have quite a lot of memory (60G), so look at tuning the vm.dirty_background_ratio, vm.dirty_ratio sysctls to avoid trying to *suddenly* write out many gigs of dirty buffers. Your provisioned volumes are much better than the default AWS ones, but are still not hugely fast (i.e 1000 IOPS is about 8 MB/s worth of Postgres 8k buffers). So you may need to look at adding more volumes into the array, or adding some separate ones and putting pg_xlog directory on 'em. However before making changes I would recommend using iostat or sar to monitor how volumes are handling the load (I usually choose a 1 sec granularity and look for 100% util and high - server hundred ms - awaits). Also iotop could be enlightening. Regards Mark On 02/04/13 11:35, Armand du Plessis wrote: > > It's on Amazon EC2 - > * cc2.8xlarge instance type > * 6 volumes in RAID-0 configuration. (1000 PIOPS) > > 60.5 GiB of memory > 88 EC2 Compute Units (2 x Intel Xeon E5-2670, eight-core) > 3370 GB of instance storage > 64-bit platform > I/O Performance: Very High (10 Gigabit Ethernet) > EBS-Optimized Available: No** > API name: cc2.8xlarge > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance