Skip site navigation (1) Skip section navigation (2)

Re: Odd blocking (or massively latent) issue - even with EXPLAIN

From: Jim Vanns <james(dot)vanns(at)framestore(dot)com>
To: Martin French <Martin(dot)French(at)romaxtech(dot)com>
Cc: Jim Vanns <james(dot)vanns(at)framestore(dot)com>, pgsql-performance(at)postgresql(dot)org, pgsql-performance-owner(at)postgresql(dot)org
Subject: Re: Odd blocking (or massively latent) issue - even with EXPLAIN
Date: 2012-08-16 16:14:36
Message-ID: 1345133676.8801.52.camel@sys367.ldn.framestore.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello again. So sorry for resurrecting such an old thread but the
problem still persists - I've just had very little to report on, until
now...

> > That latter test - won't that pretty much just read from the page
> cache?
> > 'sync' may well have forced dirty pages to disk but does it actually
> > evict them to?
> 
> Basically, the cache is avoided because of the size of the file.
> 6000000 blocks at 8k exceeds the size of RAM in the machine, so it
> *should* miss the cache and hit the disk directly. :)

OK, I did this during the problematic time and write speeds (sustained)
are in the order of 250MB/s :) It took just 3m21s to write all ~50GB. We
get read speeds of a wonderfully massive ~530MB/s - the whole file read
in just 1m30s. All metrics gathered with iostat -p <devices> -m 1.

Now, what I have noticed is this; we run two databases on this one
machine. One (DB) is completely operable in a normal way during the slow
period and the other is not. This (unresponsive) database has just two
client processes connected - one is writing, the other is (meant to be)
reading.

Neither registers in pg_locks - one does not block the other at the DB
level. However the write process (INSERTs) is writing between 5 and 10
MB/s. The read process (a SELECT or EXPLAIN) just spins the CPU at 100%
and register 0.0 MB/s - yet it should be reading *a lot* of data.

So does PostgreSQL somehow (or have I misconfigured it to) always
prioritise writes over reads?

I'm still at a loss!

Any further pointers would be appreciated.

Jim

PS. This is with the deadline scheduler and with each block device set
with --setra 8192.

> > Anyway, that is off topic... perhaps ;)
> > 
> > Thanks again,
> > 
> > Jim
> > 
> 
> Cheers
> 
> Martin ============================================= Romax Technology
> Limited Rutherford House Nottingham Science & Technology Park
> Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00
> (main) For other office locations see:
> http://www.romaxtech.com/Contact =================================
> =============== E-mail: info(at)romaxtech(dot)com Website: www.romaxtech.com
> ================================= ================ Confidentiality
> Statement This transmission is for the addressee only and contains
> information that is confidential and privileged. Unless you are the
> named addressee, or authorised to receive it on behalf of the
> addressee you may not copy or use it, or disclose it to anyone else.
> If you have received this transmission in error please delete from
> your system and contact the sender. Thank you for your cooperation.
> =================================================
> 

-- 
Jim Vanns
Systems Programmer
Framestore



In response to

pgsql-performance by date

Next:From: Jeff JanesDate: 2012-08-16 16:16:45
Subject: Re: cluster on conditional index?
Previous:From: anarazel@anarazel.deDate: 2012-08-16 16:07:26
Subject: Re: High Disk write and space taken by PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group