Re: UPDATEDs slowing SELECTs in a fully cached database

On Tue, Jul 12, 2011 at 2:08 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> lars <lhofhansl(at)yahoo(dot)com> wrote:
>
>> select count(*) from test where tenant = $1 and created_date = $2
>
> Ah, that might be a major clue -- prepared statements.

I'm really skeptical that this is the case -- the table is 100m and
there is no way you are banging through 100m in 500ms records
particularly if doing i/o.

Also regarding the page cleanup, IIRC the optimization only takes
place if the dead tuples are HOT -- when the OP opened the thread he
stated it was happening with update on indexed field (and later tested
it on unindexed field).

Something is not adding up here. Perhaps there is an alternate route
to WAL logged activity from selects I'm not thinking of. Right now
I'm thinking to run the selects on table 'a' and the inserts
concurrently on table 'b' and seeing how that behaves. Another way to
get to the bottom is to oprofile the selecting-during-load backend to
see where the time is getting spent. Alternative way to do this is
to strace attach to the selecting-during-load backend to see if it's
really writing to the WAL (I'm really curious about this).

Another interesting test would be to try and reproduce the results on
native machine. It should be possible to do this on your workstation
with a more modestly sized scaling factor.

merlin