Re: Inserting 8MB bytea: just 25% of disk perf used?

On Jan 25, 2010, at 6:55 AM, fkater(at)googlemail(dot)com wrote:

> Scott Carey:
>
>>> (2) The tests:
>>>
>>> Note: The standard speed was about 800MB/40s, so 20MB/s.
>>>
>>>
>>> a)
>>> What I changed: fsync=off
>>> Result: 35s, so 5s faster.
>>>
>>>
>>> b) like a) but:
>>> checkpoint_segments=128 (was 3)
>>> autovacuum=off
>>>
>>> Result: 35s (no change...?!)
>>>
>>
>> yes, more checkpoint_segments will help if your
>> shared_buffers is larger, it won't do a whole lot
>> otherwise. Generally, I like to keep these roughly equal
>> sized as a starting point for any small to medium sized
>> configuration. So if shared_buffers is 1GB, that takes 64
>> checkpoint segments to hold for heavy write scenarios.
>
> (1)
>
> Ok, that's what I tested: 1024 MB shared_buffers, 64
> checkpoint segments.
>
> Unfortunatelly I could not run it on the same hardware
> anymore: The data is written to a single disk now, not raid
> anymore. So with the default shared_buffers of 8 MB (?) we
> should expect 45s for writing the 800 MB. With the large
> shared_buffers and checkpoints (mentioned above) I got this:
>
> 1. run (right after postgres server (re-)start): 28s (!)
> 2. run: 44s
> 3. run: 42s
>
> So, roughly the same as with small buffers.
>
>
> (2)
> Then I switched again from 8.2.4 to 8.4.2:
>
> 1. run (after server start): 25s.
> 2. run: 38s
> 3. run: 38s
>

If you expect to typically only run a batch of these large inserts occasionally, hopefully the 25s performance will be what you get.

> So, 8.4 helped a bit over 8.2.
>
>
> (3) All in all
>
> By (1) + (2) the performance bottleneck has, however,
> changed a lot (as shown here by the performance monitor):
>
> Now, the test system is definitly disk bound. Roughly
> speaking, at the middle of the whole test, for about 40-50%
> of the time, the 'data' disk was at 100% (and the 'WAL' at
> 20%), while before and after that the 'WAL' disk had a lot
> of peaks at 100% (and 'data' disk at 30%).
>
> The average MB/s of the 'data' disk was 40 MB/s (WAL:
> 20MB/s) -- while the raw performance is 800MB/40s = 20MB/s,
> so still *half* what the disk does.
>
> So, this remains as the last open question to me: It seems
> the data is doubly written to the 'data' disk, although WAL
> is written to the separate 'WAL' disk.
>

It appears as though there is clear evidence that the system is writing data twice (excluding WAL). This is where my Postgres knowledge ends and someone else will have to comment. Why would it write the TOAST data twice?