Re: UPDATEDs slowing SELECTs in a fully cached database

On Tue, Jul 12, 2011 at 8:22 AM, Ivan Voras <ivoras(at)freebsd(dot)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? I'd like to know definitively:
*) is the reader bottlenecked on disk i/o (it seems yes)
*) is that disk i/o heap or wal (it seems wal)
*) is that disk i/o reading/writing (it seems writing)
*) given the above, why is this happening (presumably disk page tidying)?

We need some more information here. I'd like to see the table
information -- at least the average width of the record both pre/post
update, if it is or is not toasted, and the number of size and indexes
pre/post update. I'm really suspicious of the virtualization tech as
well -- is it possible to run this test on at least semi decent native
hardware?

merlin