Re: Problems with pg_locks explosion

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
>