Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Tue, Aug 25, 2015 at 4:31 PM, Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux