Message-ID: <48DEB4D4.1020100@mib-infotech.co.nz>
Date: Sun, 28 Sep 2008 11:33:56 +1300
From: John Huttley <John@mib-infotech.co.nz>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Slow updates, poor IO
References: <48DBE587.3050406@mib-infotech.co.nz>	
	<20080926131556.GA1005@commandprompt.com>	
	<48DD6A4A.10209@mib-infotech.co.nz>
	<dcc563d10809270809g17938b7fvf28a3624028e0f3b@mail.gmail.com>
In-Reply-To: <dcc563d10809270809g17938b7fvf28a3624028e0f3b@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------060407060708080905000208"

This is a multi-part message in MIME format.
--------------060407060708080905000208
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


Scott Marlowe wrote:
> On Fri, Sep 26, 2008 at 5:03 PM, John Huttley <John@mib-infotech.co.nz> 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.
>
>   

Yes sorry, I mean all indexes are updated even when the updated field is 
not indexed.
I'm running 8.3.3
>> this is part of the trade-offs of MVCC.
>>     
>
> was...  was a part of the trade-offs.
>
>   
You are thinking of HOT?
I don't think it applies in the case of full table updates??

>> 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
>
>   
Oh well, I'm forever going to be disadvantaged.


--------------060407060708080905000208
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
Scott Marlowe wrote:
<blockquote
 cite="mid:dcc563d10809270809g17938b7fvf28a3624028e0f3b@mail.gmail.com"
 type="cite">
  <pre wrap="">On Fri, Sep 26, 2008 at 5:03 PM, John Huttley <a class="moz-txt-link-rfc2396E" href="mailto:John@mib-infotech.co.nz">&lt;John@mib-infotech.co.nz&gt;</a> wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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.

  </pre>
</blockquote>
<br>
Yes sorry, I mean all indexes are updated even when the updated field
is not indexed.<br>
I'm running 8.3.3<br>
<blockquote
 cite="mid:dcc563d10809270809g17938b7fvf28a3624028e0f3b@mail.gmail.com"
 type="cite">
  <pre wrap=""></pre>
  <blockquote type="cite">
    <pre wrap="">this is part of the trade-offs of MVCC.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
was...  was a part of the trade-offs.

  </pre>
</blockquote>
You are thinking of HOT?<br>
I don't think it applies in the case of full table updates??<br>
<br>
<blockquote
 cite="mid:dcc563d10809270809g17938b7fvf28a3624028e0f3b@mail.gmail.com"
 type="cite">
  <pre wrap=""></pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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

  </pre>
</blockquote>
Oh well, I'm forever going to be disadvantaged.<br>
<br>
<br>
</body>
</html>

--------------060407060708080905000208--