Re: Simplifying wal_sync_method

In summary, we added all those wal_sync_method values in hopes of
getting some data on which is best on which platform, but having gone
several years with few reports, I am thinking we should just choose the
best ones we can and move on, rather than expose a confusing API to the
users.

Does anyone show a platform where the *data* options are slower than the
non-*data* ones?

---------------------------------------------------------------------------

pgman wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Currently, here are the options available for wal_sync_method:
> > > #wal_sync_method = fsync # the default varies across platforms:
> > > # fsync, fdatasync, fsync_writethrough,
> > > # open_sync, open_datasync
> >
> > > I don't understand why we support so many values.
> >
> > Because there are so many platforms with different subsets of these APIs
> > and different performance characteristics for the ones they do have.
>
> Right, and our current behavior makes it harder for people to even know
> the supported options.
>
> > > It seems 'fsync' should be fdatasync(), and if that is not available,
> > > fsync().
> >
> > I have yet to see anyone do any systematic testing of the different
> > options on different platforms. In the absence of hard data, proposing
> > that we don't need some of the options is highly premature.
>
> No one is every going to do it, so we might as well make the best guess
> we have. I think any platform where the *data* options are slower than
> the non-*data* options is broken, and if that logic holds, we might as
> well just use *data* by default if we can, which is my proposal.
>
> > > In fact, 8.1 uses O_DIRECT if available,
> >
> > That's a decision that hasn't got a shred of evidence to justify
> > imposing it on every platform.
>
> Right, and there is no evidence it hurts, so we do our best until
> someone comes up with data to suggest we are wrong. The same should be
> done with *data*.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Mon, 2005-08-08 at 17:44 -0400, Bruce Momjian wrote:
> In summary, we added all those wal_sync_method values in hopes of
> getting some data on which is best on which platform, but having gone
> several years with few reports, I am thinking we should just choose the
> best ones we can and move on, rather than expose a confusing API to the
> users.

I agree this should be attempted over the 8.1 beta period.

This is a good case for having a Port Coordinator assigned for each
port, so we could ask them to hunt out the solution for their platform.
Maybe this is something that we can broadcast to the BuildFarm team, so
each person can reflect on the appropriate settings?

Best Regards, Simon Riggs

Simon Riggs wrote:

>On Mon, 2005-08-08 at 17:44 -0400, Bruce Momjian wrote:
>
>
>>In summary, we added all those wal_sync_method values in hopes of
>>getting some data on which is best on which platform, but having gone
>>several years with few reports, I am thinking we should just choose the
>>best ones we can and move on, rather than expose a confusing API to the
>>users.
>>
>>
>
>I agree this should be attempted over the 8.1 beta period.
>
>This is a good case for having a Port Coordinator assigned for each
>port, so we could ask them to hunt out the solution for their platform.
>Maybe this is something that we can broadcast to the BuildFarm team, so
>each person can reflect on the appropriate settings?
>
>
>
>

It might be possible to build a new set of tests that we could perform.
That would have to be built into the buildfarm script, as the PL tests
were, but they were picked up pretty quickly by the community.
Unfortunately it doesn't sound like these would fit into the pg_regress
setup, so we'll have to devise a different test harness - probably not a
bad idea for automated performance testing anyway.

So the short answer is possibly "You build the tests and we'll run 'em."

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> So the short answer is possibly "You build the tests and we'll run 'em."

The availability of the buildfarm certainly makes it a lot more feasible
to do performance tests on a variety of platforms. So, who wants to
knock something together?

I suppose we would usually be interested in one-time tests, rather than
something repeated every time CVS is touched. How might that sort of
requirement fit into the buildfarm software design?

regards, tom lane

Tom Lane said:
> Kris Jurka <books(at)ejurka(dot)com> writes:
>> Automated performance testing seems like a bad idea for the buildfarm.
>> Consider in my particular case I've got three members that all
>> happen to be running in virtual machines on the same host. What
>> virtualization does for performance and what happens when all three
>> members are running at the same time renders any results beyond
>> useless.
>
> Certainly a good point --- but as I noted to Andrew, we'd probably be
> more interested in one-off tests than repetitive testing anyway. So
> possibly this could be handled with a different protocol, and buildfarm
> machine owners could be careful to schedule slots for such tests at
> times when their machine is otherwise idle.
>
> Anyway it all needs some thought ...
>

Well, of course running tests would be optional.

But it's also possible that we would create a similar but separate setup to
run performance tests. Creating it would be lots easier this time around ;-)

Let's come up with something we can run by hand, decide the parameters, and
set set about automating and distributing it.

cheers

andrew

On Mon, 8 Aug 2005, Andrew Dunstan wrote:

> So the short answer is possibly "You build the tests and we'll run 'em."
>

Automated performance testing seems like a bad idea for the buildfarm.
Consider in my particular case I've got three members that all happen to
be running in virtual machines on the same host. What virtualization does
for performance and what happens when all three members are running at the
same time renders any results beyond useless. Certainly soliciting the
pgbuildfarm-members(at)pgfoundry(dot)org list is good idea, but I don't think
automating this testing is a good idea without more knowledge of the
machines and their other workloads.

Kris Jurka

Tom Lane wrote:

>Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>
>>So the short answer is possibly "You build the tests and we'll run 'em."
>>
>>
>
>The availability of the buildfarm certainly makes it a lot more feasible
>to do performance tests on a variety of platforms. So, who wants to
>knock something together?
>
>I suppose we would usually be interested in one-time tests, rather than
>something repeated every time CVS is touched. How might that sort of
>requirement fit into the buildfarm software design?
>
>
>
>

I'll give it some thought. Maybe a unique name would do the trick.

cheers

andrew

Kris Jurka <books(at)ejurka(dot)com> writes:
> Automated performance testing seems like a bad idea for the buildfarm.
> Consider in my particular case I've got three members that all happen to
> be running in virtual machines on the same host. What virtualization does
> for performance and what happens when all three members are running at the
> same time renders any results beyond useless.

Certainly a good point --- but as I noted to Andrew, we'd probably be
more interested in one-off tests than repetitive testing anyway. So
possibly this could be handled with a different protocol, and buildfarm
machine owners could be careful to schedule slots for such tests at
times when their machine is otherwise idle.

Anyway it all needs some thought ...

regards, tom lane

I was recently witness to a benchmark of 7.4.5 on Solaris 9 wherein
it was apparently demonstrated that fsync was the fastest option
among the 7.4.x wal_sync_method options.

If there's a way to make this information more useful by providing
more data, please let me know, and I'll see what I can do.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Aug 8, 2005, at 4:44 PM, Bruce Momjian wrote:

> In summary, we added all those wal_sync_method values in hopes of
> getting some data on which is best on which platform, but having gone
> several years with few reports, I am thinking we should just choose
> the
> best ones we can and move on, rather than expose a confusing API to
> the
> users.
>
> Does anyone show a platform where the *data* options are slower
> than the
> non-*data* ones?

On Wed, Aug 10, 2005 at 02:11:48AM -0500, Thomas F. O'Connell wrote:
> I was recently witness to a benchmark of 7.4.5 on Solaris 9 wherein
> it was apparently demonstrated that fsync was the fastest option
> among the 7.4.x wal_sync_method options.
>
> If there's a way to make this information more useful by providing
> more data, please let me know, and I'll see what I can do.

What would be really interesting to me to know is what Sun did
between 8 and 9 to make that so. We don't use Solaris for databases
any more, but fsync was a lot slower than whatever we ended up using
on 8. I wouldn't be surprised if they'd wired fsync directly to
something else; but I can hardly believe it'd be faster than any
other option. (Mind, we were using Veritas filesyste with this, as
well, which was at least half the headache.)

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

UFS was the filesystem on the Solaris 9 box.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Aug 11, 2005, at 4:18 PM, Andrew Sullivan wrote:

> On Wed, Aug 10, 2005 at 02:11:48AM -0500, Thomas F. O'Connell wrote:
>
>> I was recently witness to a benchmark of 7.4.5 on Solaris 9 wherein
>> it was apparently demonstrated that fsync was the fastest option
>> among the 7.4.x wal_sync_method options.
>>
>> If there's a way to make this information more useful by providing
>> more data, please let me know, and I'll see what I can do.
>>
>
> What would be really interesting to me to know is what Sun did
> between 8 and 9 to make that so. We don't use Solaris for databases
> any more, but fsync was a lot slower than whatever we ended up using
> on 8. I wouldn't be surprised if they'd wired fsync directly to
> something else; but I can hardly believe it'd be faster than any
> other option. (Mind, we were using Veritas filesyste with this, as
> well, which was at least half the headache.)
>
> A

On Mon, Aug 08, 2005 at 07:45:38PM -0400, Andrew Dunstan wrote:
> So the short answer is possibly "You build the tests and we'll run 'em."

Would some version of dbt2/3 work for this?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461

On Sun, 21 Aug 2005 19:27:35 -0500
"Jim C. Nasby" <jnasby(at)pervasive(dot)com> wrote:

> On Mon, Aug 08, 2005 at 07:45:38PM -0400, Andrew Dunstan wrote:
> > So the short answer is possibly "You build the tests and we'll run 'em."
>
> Would some version of dbt2/3 work for this?

Yeah, trying... On the larger system I'm using I'm not seeing much of a
performance difference but I'm looking for a way to see if we can
identify any benefit to bypassing the kernel cache. I've been
re-arranging disks due to failures and trying to tweak a couple of
profiling things, but I'll try to get some data to share within a few
days.

Mark