To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
Date: Tue, 12 Jul 2011 16:43:03 +0200
Lines: 33
Message-ID: <ivhmhn$o9q$1@dough.gmane.org>
References: <4E16479D.3050605@yahoo.com>	<ivhhr6$rin$1@dough.gmane.org>
	<CAHyXU0xBK6+QAm-VCXurMOaSB-iZ_Jtdf_=mc20tetKOenEMdA@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US;
	rv:1.9.2.12) Gecko/20101102 Thunderbird/3.1.6
In-Reply-To: 
 <CAHyXU0xBK6+QAm-VCXurMOaSB-iZ_Jtdf_=mc20tetKOenEMdA@mail.gmail.com>

On 12/07/2011 16:18, Merlin Moncure wrote:
> On Tue, Jul 12, 2011 at 8:22 AM, Ivan Voras<ivoras@freebsd.org>  wrote:
>> On 08/07/2011 01:56, lars wrote:
>>
>>> Setup:
>>> PostgreSQL 9.1beta2 on a high memory (~68gb, 12 cores) EC2 Linux
>>> instance (kernel 2.6.35) with the database and
>>> WAL residing on the same EBS volume with EXT4 (data=ordered, barriers=1)
>>> - yes that is not an ideal setup
>>> (WAL should be on separate drive, EBS is slow to begin, etc), but I am
>>> mostly interested in read performance for a fully cached database.
>>
>> I know you said you know these things - but do you really know the (huge)
>> extent to which all your IO is slowed? Even context switches in a
>> virtualized environment are slowed down by a huge margin - which would make
>> practically all in-memory lock operations very slow - much slower than they
>> would be on "real" hardware, and EBS by definition is even slower then
>> regular private virtual storage environments. I regrettably didn't bookmark
>> the page which did exact measurements of EBS, but
>> http://www.google.com/search?q=how+slow+is+ec2 will illustrate my point. (of
>> course, you may already know all this :) ).
>
> sure, but the OP's question is valid: in postgres, readers don't block
> writers, so why is the reader waiting?

Yes, but I'm suggesting a different question: are we sure we are not 
seeing the influences of the environment (EC2+EBS) instead of the 
software system?

> We need some more information here.

Definitely.