Re: WAL Bypass for indexes

Simon,

>The WAL becomes more of a hotspot as you scale up numbers of CPUs.

I tend to agree and the original idea came when I was working on a Sun quad-CPU system for a highly parallelized web application. Each page was broken into several dynamic images, each created "on the fly" from dozens of vector objects and all of the data, as well as state information was held in Pg.

As a complex app, each page load would spawn a dozen or so processes, each hitting the DB pretty hard, where all of the business logic resided.

It didn't take too many concurrent users to bring the server to its knees.

Here's the point: some inspection showed that a lot of time was being spent on index output. At that point I realized that there had to be a better way.

My simple home system is not capable of recreating those conditions. If you have an SMP box, please run some tests.

M

_____ Original message _____
Subject: Re: [HACKERS] WAL Bypass for indexes
Author: Simon Riggs <simon(at)2ndquadrant(dot)com>
Date: 05th April 2006 11:0:34 AM

On Wed, 2006-04-05 at 09:40 -0700, Martin Scholes wrote:

> > I will run multiple tests and post the actual numbers.
>
> I did run more extensive tests and did not bother writing down the
> numbers, and here's why: the unmodified Pg ran pgbench with a whopping
> average of 6.3% time in IO wait.
>
> If I was able to totally eliminate that time (which is impossible),
> then the best we could hope for is a 7% increase in performance by
> skipping WAL of indexes.

The WAL becomes more of a hotspot as you scale up numbers of CPUs. I
guess this idea doesn't make much difference for smaller systems.

I think your idea still has merit, Martin. I'll do some tests also.

Best Regards, Simon Riggs

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster