Re: Why does splitting $PGDATA and xlog yield a performance benefit?

On Tue, Aug 25, 2015 at 4:31 PM, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz
> wrote:

> On 26/08/15 05:54, David Kerr wrote:
>
>> On Tue, Aug 25, 2015 at 10:16:37AM PDT, Andomar wrote:
>>
>>> However, I know from experience that's not entirely true, (although it's
>>>> not always easy to measure all aspects of your I/O bandwith).
>>>>
>>>> Am I missing something?
>>>>
>>>> Two things I can think of:
>>>
>>> Transaction writes are entirely sequential. If you have disks
>>> assigned for just this purpose, then the heads will always be in the
>>> right spot, and the writes go through more quickly.
>>>
>>> A database server process waits until the transaction logs are
>>> written and then returns control to the client. The data writes can
>>> be done in the background while the client goes on to do other
>>> things. Splitting up data and logs mean that there is less chance
>>> the disk controller will cause data writes to interfere with log
>>> files.
>>>
>>> Kind regards,
>>> Andomar
>>>
>>> hmm, yeah those are both what I'd lump into "I/O bandwith".
>> If your disk subsystem is fast enough, or you're on a RAIDd SAN
>> or EBS you'd either overcome that, or not neccssarily be able to.
>>
>>
>>
>> Back when I actually understood the various timings of disc accessing on
> a MainFrame system, back in the 1980's (disc layout & accessing, is way
> more complicated now!), I found that there was a considerable difference
> between mainly sequential & mostly random access - easily greater than a
> factor of 5 (from memory) in terms of throughput.
>
> Considering the time to move heads between tracks and rotational latency
> (caused by not reading sequential blocks on the same track). There are
> other complications, which I have glossed over!
>
>
It can go even further now with the use of SSDs. You can put the xlogs on
an SSD and the rest of the database on a mechanical drive. Same can be said
about partitions, you can place the most accessed partition on an SSD and
the rest of the db on a mechanical drive.

-Joseph Kregloh

>
> Cheers,
> Gavin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>