PCTFree Results

Seems like this was to be somewhat expected. Was there any stats/diagnostics
included in the patch to show the effectiveness of PCTFREE?

On 9/22/05, Josh Berkus <josh@agliodbs.com> wrote:

Folks,

Well, it took a while but I finally have the results of Satoshi's PCTFree
patch back from the STP. Bad news about the STP, see below ...

Anyway, a series of DBT2 runs doesn't seem to show any advantage to
PCTFree
over a 3-hour run with no vacuums:

test# pctfree full_page_writes notpm
303164 off off 1803
303165 on on 1847
303166 on off 1860
303167 off on 1801
303168 off off 1838
303169 on on 1821
303170 on off 1846

(again, check all results at http://www.testing.osdl.org/stp/######/)

The differences above are small enough to be in the "noise" factor of DBT2
execution. The good news is that it appears that stuff which has been
done since July has lessened the penalty for checkpoints somewhat; while
the maximum response time is still better on the full_page_writes=off
systems, the average throughput is no longer substantially different.
Either that, or full_page_writes=off isn't working properly anymore.

If anyone has suggestions on different tests to run, or better stats to
compile, please speak up.

Now, the bad news: the STP has had some failures and is down to *one*
usable machine for testing. I have, like, 160 performance tests backed up
which are never going to get run before we release 8.1. I'm going to be
hitting up some major PostgreSQL sponsors for hardware donations, any help
is welcome.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/

Jonah,

Seems like this was to be somewhat expected. Was there any
stats/diagnostics included in the patch to show the effectiveness of
PCTFREE?

Lots, look up the tests on OSDL, per link.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh,

Sorry, duh. I'll check it out. It has been a long day and I totally missed
the URL :(

On 9/22/05, Josh Berkus <josh@agliodbs.com> wrote:

Jonah,

Seems like this was to be somewhat expected. Was there any
stats/diagnostics included in the patch to show the effectiveness of
PCTFREE?

Lots, look up the tests on OSDL, per link.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/

Josh Berkus <josh@agliodbs.com> writes:

... The good news is that it appears that stuff which has been
done since July has lessened the penalty for checkpoints somewhat; while
the maximum response time is still better on the full_page_writes=off
systems, the average throughput is no longer substantially different.
Either that, or full_page_writes=off isn't working properly anymore.

I dug through the CVS logs since 5-July (when full_page_writes was
added, so I suppose that's before your unspecified "July" test).
AFAICS the only changes that might possibly affect xlog/checkpoint
performance were these:

Use O_DIRECT if available when using O_SYNC for wal_sync_method.

Also, write multiple WAL buffers out in one write() operation.

Was your test set up so that it would have used O_DIRECT?

With respect to the original point, I'm pretty nervous about either
accepting or rejecting a performance-oriented patch on the strength
of a single test case. This report certainly doesn't favor the PCTFREE
patch, but it probably shouldn't kill it either. Anyone want to try it
on some other test cases?

regards, tom lane

Tom,

I dug through the CVS logs since 5-July (when full_page_writes was
added, so I suppose that's before your unspecified "July" test).
AFAICS the only changes that might possibly affect xlog/checkpoint
performance were these:

Use O_DIRECT if available when using O_SYNC for wal_sync_method.

Also, write multiple WAL buffers out in one write() operation.

Was your test set up so that it would have used O_DIRECT?

Yes, actually. It's Linux, and we used the defaults.

It would be interesting to test on a system that doesn't support the above
and see if full_page_writes=off still makes a significant difference.

With respect to the original point, I'm pretty nervous about either
accepting or rejecting a performance-oriented patch on the strength
of a single test case. This report certainly doesn't favor the PCTFREE
patch, but it probably shouldn't kill it either. Anyone want to try it
on some other test cases?

Right, this doesn't kill PCTFree, what it does is fail to make a case for
it.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Thu, Sep 22, 2005 at 10:05:57PM -0400, Tom Lane wrote:

With respect to the original point, I'm pretty nervous about either
accepting or rejecting a performance-oriented patch on the strength
of a single test case. This report certainly doesn't favor the PCTFREE
patch, but it probably shouldn't kill it either. Anyone want to try it
on some other test cases?

I *think* that a better test would be a table that is seeing a lot of
'update churn', or one that's seeing a lot of insert and delete activity
spread randomly around. It's very possible that dbt2 doesn't put enough
activity on each page to make any real difference, especially if the old
behaviour was to leave 10% free by default.

But it's been quite some time since the patch was discussed and I don't
remember specifics. Hopefully the author will speak up.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Has there been any movement on this? If not, I finally have some time to
look at it.

On 9/23/05, Jim C. Nasby <jnasby@pervasive.com> wrote:

On Thu, Sep 22, 2005 at 10:05:57PM -0400, Tom Lane wrote:

With respect to the original point, I'm pretty nervous about either
accepting or rejecting a performance-oriented patch on the strength
of a single test case. This report certainly doesn't favor the PCTFREE
patch, but it probably shouldn't kill it either. Anyone want to try it
on some other test cases?

I *think* that a better test would be a table that is seeing a lot of
'update churn', or one that's seeing a lot of insert and delete activity
spread randomly around. It's very possible that dbt2 doesn't put enough
activity on each page to make any real difference, especially if the old
behaviour was to leave 10% free by default.

But it's been quite some time since the patch was discussed and I don't
remember specifics. Hopefully the author will speak up.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/

On Thu, Sep 29, 2005 at 11:05:47AM -0400, Jonah H. Harris wrote:

Has there been any movement on this? If not, I finally have some time to
look at it.

Well, here's the original thread:
http://archives.postgresql.org/pgsql-hackers/2005-08/msg00637.php

I think the problem is that the testing isn't going to show us much (if
anything) based on applying PCTFREE across all tables. It only makes
sense for tables that see a lot of updates, or Index Organized Tables
(to use Oracle vernacular) with indexes spread throughout the table (I
don't think there's any other way to get indexes forcibly spread
throughout a table, and in any case it doesn't matter for PostgreSQL
right now).

So, the NOTPM numbers are probably pretty meaningless. One of the other
metrics that's essentially just updating might be more interesting.

Josh mentioned that there were additional stats produced by the patch,
but the URL seems to be down so I can't go and check. :(

It would be really useful if someone with a real-life heavy-update
workload stepped up.

I'm going to cc Satoshi; maybe he's not on the list.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Josh,

Do you have an 8.1 patch for this or only the 8.0.x?

Jonah,

Do you have an 8.1 patch for this or only the 8.0.x?

Nope, this is Satoshi's code, ask him.

BTW, I'm not sure that the DBT2 test is ideal for this sort of thing anyway.
We really need a few tests that are heavier on UPDATEs than on INSERTS.
Maybe a few data warehousing-style merges.

--
Josh Berkus
Aglio Database Solutions
San Francisco