moving pg_xlog -- yeah, it's worth it!

From: Kevin Grittner
Subject: moving pg_xlog -- yeah, it's worth it!
Date: ,
Msg-id: 4B71358E020000250002F0E4@gw.wicourts.gov
(view: Whole thread, Raw)
Responses: Re: moving pg_xlog -- yeah, it's worth it!  (Jesper Krogh)
List: pgsql-performance

Tree view

moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
 Re: moving pg_xlog -- yeah, it's worth it!  (Jesper Krogh, )
  Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
   Re: moving pg_xlog -- yeah, it's worth it!  (Amitabh Kant, )
    Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
   Re: moving pg_xlog -- yeah, it's worth it!  (Bruce Momjian, )
    Re: moving pg_xlog -- yeah, it's worth it!  (Scott Marlowe, )
   Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
    Re: moving pg_xlog -- yeah, it's worth it!  (Alvaro Herrera, )
     Re: moving pg_xlog -- yeah, it's worth it!  (Aidan Van Dyk, )
      Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
 Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
  Re: moving pg_xlog -- yeah, it's worth it!  (Alvaro Herrera, )
   Re: moving pg_xlog -- yeah, it's worth it!  (Alvaro Herrera, )
    Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
     Re: moving pg_xlog -- yeah, it's worth it!  (Alvaro Herrera, )
  Re: moving pg_xlog -- yeah, it's worth it!  (Greg Smith, )
   Re: moving pg_xlog -- yeah, it's worth it!  ("Kevin Grittner", )
    Re: moving pg_xlog -- yeah, it's worth it!  (Ben Chobot, )

Due to an error during an update to the system kernel on a database
server backing a web application, we ran for a month (mid-December
to mid-January) with the WAL files in the pg_xlog subdirectory on
the same 40-spindle array as the data -- only the OS was on a
separate mirrored pair of drives.  When we found the problem, we
moved the pg_xlog directory back to its own mirrored pair of drives
and symlinked to it.  It's a pretty dramatic difference.

Graph attached, this is response time for a URL request (like what a
user from the Internet would issue) which runs 15 queries and
formats the results.  Response time is 85 ms without putting WAL on
its own RAID; 50 ms with it on its own RAID.  This is a real-world,
production web site with 1.3 TB data, millions of hits per day, and
it's an active replication target 24/7.

Frankly, I was quite surprised by this, since some of the benchmarks
people have published on the effects of using a separate RAID for
the WAL files have only shown a one or two percent difference when
using a hardware RAID controller with BBU cache configured for
write-back.

No assistance needed; just posting a performance data point.

-Kevin

Attachment

pgsql-performance by date:

From: Dimi Paun
Date:
Subject: DISTINCT vs. GROUP BY
From: Thom Brown
Date:
Subject: Re: DISTINCT vs. GROUP BY