Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Rob Wultsch
On Fri, Oct 22, 2010 at 1:15 PM, Kevin Grittner wrote: > Rob Wultsch wrote: > >> not needing full_page_writes would make geographically dispersed >> replication possible for certain cases where it is not currently >> (or at least rather painful). > > Do you have any hard numbers on WAL file size

Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Kevin Grittner
Rob Wultsch wrote: > not needing full_page_writes would make geographically dispersed > replication possible for certain cases where it is not currently > (or at least rather painful). Do you have any hard numbers on WAL file size impact? How much does pglesslog help in a file-based WAL trans

Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Rob Wultsch
On Fri, Oct 22, 2010 at 12:05 PM, Kevin Grittner wrote: > Rob Wultsch wrote: > >> I would think full_page_writes=off + double write buffer should be >> far superior, particularly given that the WAL is shipped over the >> network to slaves. > > For a reasonably brief description of InnoDB double w

Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Kevin Grittner
Rob Wultsch wrote: > I would think full_page_writes=off + double write buffer should be > far superior, particularly given that the WAL is shipped over the > network to slaves. For a reasonably brief description of InnoDB double write buffers, I found this: http://www.mysqlperformanceblog.co

Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Rob Wultsch
On Fri, Oct 22, 2010 at 10:28 AM, Kevin Grittner wrote: > Rob Wultsch wrote: > >> has PG considered using a double write buffer similar to InnodB? > > That seems inferior to the full_page_writes strategy, where you only > write a page twice the first time it is written after a checkpoint. > We're

Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Kevin Grittner
Rob Wultsch wrote: > has PG considered using a double write buffer similar to InnodB? That seems inferior to the full_page_writes strategy, where you only write a page twice the first time it is written after a checkpoint. We're talking about when we might be able to write *less*, not more.

Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Rob Wultsch
On Fri, Oct 22, 2010 at 8:37 AM, Greg Smith wrote: > Tom Lane wrote: >> >> You've got entirely too simplistic a view of what the "delta" might be, >> I fear.  In particular there are various sorts of changes that involve >> inserting the data carried in the WAL record and shifting pre-existing >>

Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Jesper Krogh
On 2010-10-22 17:37, Greg Smith wrote: I think that most people who have thought they were safe to turn off full_page_writes in the past did so because they believed they were in category (1) here. I've never advised anyone to do that, because it's so difficult to validate the truth of. Jus

Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Greg Smith
Tom Lane wrote: You've got entirely too simplistic a view of what the "delta" might be, I fear. In particular there are various sorts of changes that involve inserting the data carried in the WAL record and shifting pre-existing data around to make room, or removing an item and moving remaining

Re: [PERFORM] Periodically slow inserts

2010-10-22 Thread Gael Le Mignot
Hello, Thanks to everyone who gave me hints and feedbacks. I managed to solve the problem. My understanding of what was happening is the following : - The gin index (as explained on [1]), stores temporary list, and when they grow big enough, those are dispatched into the real index. Vacuum

Re: [PERFORM] BBU Cache vs. spindles

2010-10-22 Thread Kevin Grittner
Greg Smith wrote: > I think Kevin's point here may be that if your fsync isn't > reliable, you're always in trouble. But if your fsync is good, > even torn pages should be repairable by the deltas written to the > WAL I was actually just arguing that a BBU doesn't eliminate a risk here; if th

Re: [PERFORM] Periodically slow inserts

2010-10-22 Thread Leonardo Francalanci
>>> I think this may be caused by GIN's habit of queuing index insertions >>> until it's accumulated a reasonable-size batch: So the fact that it takes 21s to sync 365 buffers in this case is normal? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make chan