Re: [PERFORM] Slow updates, poor IO

[Greg Smith]

On Sat, 27 Sep 2008, John Huttley wrote:


I've got 32M shared on a 1G machine and 16 checkpoint segments.
I'll run some tests against 64 segments and see what happens.


Increase shared_buffers to 256MB as well.  That combination should give 
you much better performance with the type of update you're doing.  Right 
now the database server has to write the index blocks updated to disk all 
the time because it has so little working room to store them in.  If an 
index block is updated but there is room to keep it memory, it doesn't 
have to get written out, which considerably lowers the overhead here.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Slow updates, poor IO

[Scott Marlowe]

On Fri, Sep 26, 2008 at 5:03 PM, John Huttley <[EMAIL PROTECTED]> wrote:
> Hi Andrew,
> There are two problems.
> The first is the that if there is a table with a index and an update is
> performed on a non indexed field,
> the index is still re indexed.

I assume you mean updated, not reindexed, as reindexed has a different
meaning as regards postgresql.  Also, this is no longer true as of
version 8.3.  If you're updating non-indexed fields a lot and you're
not running 8.3 you are doing yourself a huge disservice.

>this is part of the trade-offs of MVCC.

was...  was a part of the trade-offs.

> We should reasonably expect that the total amount of IO will go up, over a
> non-indexed table.
>
> The second thing is that the disk IO throughput goes way down.
>
> This is not an issue with MVCC, as such, except that it exposes the effect
> of a write to an indexed field.

It's really an effect of parallel updates / writes / accesses, and is
always an issue for a database running on a poor storage subsystem.  A
db with a two drive mirror set is always going to be at a disadvantage
to one running on a dozen or so drives in a RAID-10

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance