Message-ID: <534C5E6E.1080604@nasby.net>
Date: Mon, 14 Apr 2014 17:17:18 -0500
From: Jim Nasby <jim@nasby.net>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: Andres Freund <andres@2ndquadrant.com>,
 Joe Conway <mail@joeconway.com>
CC: "Hackers (PostgreSQL)" <pgsql-hackers@postgresql.org>
Subject: Re: Excessive WAL generation and related performance issue
References: <534C540F.4030905@joeconway.com>
 <20140414215059.GA4161@awork2.anarazel.de>
In-Reply-To: <20140414215059.GA4161@awork2.anarazel.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 4/14/14, 4:50 PM, Andres Freund wrote:
> Hi,
>
> On 2014-04-14 14:33:03 -0700, Joe Conway wrote:
>> checkpoint_segments = 96
>> checkpoint_timeout = 10min
>
>> I realize there are many things that can be done to improve my
>> specific scenario, e.g. drop indexes before loading, change various
>> configs, etc. My purpose for this post is to ask if it is really
>> expected to get over 20 times as much WAL as heap data?
>
> I'd bet a large percentage of this will be full page images of the
> index. The values you index are essentially distributed over the whole
> index, so you'll modifiy the same indx values repeatedly. But often
> enough it won't be in the same checkpoint and thus will create full page
> images.

My thought exactly...

ISTM that we should be able to push all the index inserts to the end of the transaction. That should greatly reduce the amount of full page writes. That would also open the door for doing all the index inserts in parallel.
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net


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