Thread: PCTFree Results

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

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





--
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 :(




--
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.





--
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