Thread: OSDL Database Test Suite 3 is available on PostgreSQL

OSDL Database Test Suite 3 is available on PostgreSQL

From
Jenny Zhang
Date:
OSDL has ported OSDL Database Test Suite 3(OSDL-DBT3) to PostgreSQL.
It drives the database with an ad-hoc decision support workload.  It
helps database developers and users to identify database performance
issues.

OSDL-DBT3 is derived from TPC-H benchmark.  TPC-H is an ad-hoc decision
support benchmark.  It has 22 complicated queries doing a lot of
table/index scanning, sorting and grouping by.  Details about TPC-H can
be found at: http://www.tpc.org/tpch/

Though OSDL-DBT3 is based on TPC-H, it deviates from TPC-H
significantly.  It skipped many of the requirements for audit purpose,
as well as added flexibility.  OSDL-DBT3 performance test results are
not comparable to TPC-H results.

OSDL-DBT3 tarball can be downloaded from:
http://sourceforge.net/projects/osdldbt/
The source can be downloaded from source forge cvs tree and osdl bk tree
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/osdldbt/dbt3/
bk://developer.osdl.org/dbt3

OSDL-DBT3 is also implemented on OSDL Scalable Test Platform(STP).  The
platform provides a framework where users run a set of performance and
scalability tests on various hardware platforms.  Currently, OSDL-DBT3
is running against PostgreSQL 7.3.3 only.  We plan to improve it so that
it can run against PostgreSQL patches.  To find more information about
STP, visit: http://www.osdl.org/stp/.

A sample OSDL-DBT3 test result report can be found at:
http://khack.osdl.org/stp/276912/

Your comments are welcome,
Regards,
Jenny
--
Jenny Zhang
Open Source Development Lab Inc
12725 SW Millikan Way
Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31


Re: [PERFORM] OSDL Database Test Suite 3 is available on PostgreSQL

From
Manfred Koizar
Date:
On 01 Aug 2003 11:04:10 -0700, Jenny Zhang <jenny@osdl.org> wrote:
>A sample OSDL-DBT3 test result report can be found at:
>http://khack.osdl.org/stp/276912/
>
>Your comments are welcome,

| effective_cache_size           | 1000

With 4GB of memory this is definitely too low and *can* (note that I
don't say *must*) lead the planner to wrong decisions.

| shared_buffers                 | 15200

... looks reasonable.  Did you test with other values?

| sort_mem                       | 524288

This is a bit high, IMHO, but might be ok given that DBT3 is not run
with many concurrent sessions (right?).
http://khack.osdl.org/stp/276912/results/plot/vmstat_swap.png shows
some swapping activity towards the end of the run which could be
caused by a too high sort_mem setting.

Servus
 Manfred

Re: [PERFORM] OSDL Database Test Suite 3 is available on PostgreSQL

From
"scott.marlowe"
Date:
On Mon, 4 Aug 2003, Manfred Koizar wrote:

> On 01 Aug 2003 11:04:10 -0700, Jenny Zhang <jenny@osdl.org> wrote:
> >A sample OSDL-DBT3 test result report can be found at:
> >http://khack.osdl.org/stp/276912/
> >
> >Your comments are welcome,
>
> | effective_cache_size           | 1000
>
> With 4GB of memory this is definitely too low and *can* (note that I
> don't say *must*) lead the planner to wrong decisions.
>
> | shared_buffers                 | 15200
>
> ... looks reasonable.  Did you test with other values?
>
> | sort_mem                       | 524288
>
> This is a bit high, IMHO, but might be ok given that DBT3 is not run
> with many concurrent sessions (right?).
> http://khack.osdl.org/stp/276912/results/plot/vmstat_swap.png shows
> some swapping activity towards the end of the run which could be
> caused by a too high sort_mem setting.

And, as always, don't forget to set effect_cache_size.


Re: [PERFORM] OSDL Database Test Suite 3 is available on PostgreSQL

From
"scott.marlowe"
Date:
On Mon, 4 Aug 2003, Manfred Koizar wrote:

> On 01 Aug 2003 11:04:10 -0700, Jenny Zhang <jenny@osdl.org> wrote:
> >A sample OSDL-DBT3 test result report can be found at:
> >http://khack.osdl.org/stp/276912/
> >
> >Your comments are welcome,
>
> | effective_cache_size           | 1000
>
> With 4GB of memory this is definitely too low and *can* (note that I
> don't say *must*) lead the planner to wrong decisions.
>
> | shared_buffers                 | 15200
>
> ... looks reasonable.  Did you test with other values?
>
> | sort_mem                       | 524288
>
> This is a bit high, IMHO, but might be ok given that DBT3 is not run
> with many concurrent sessions (right?).
> http://khack.osdl.org/stp/276912/results/plot/vmstat_swap.png shows
> some swapping activity towards the end of the run which could be
> caused by a too high sort_mem setting.

Sorry, my last email shot off out of the gun before it was completed...

To repeat:  Don't forget to set effective_cache_size the same way as
shared buffers (i.e. it's in 8k blocks for most systems.)  If you have a
machine with 4 gig ram, and 3 gigs is available as disk cache, then
divide out 3 gigs by 8k to get the right number.  My quick calculation
shows that being about 393216 blocks.


Re: [PERFORM] OSDL Database Test Suite 3 is available on PostgreSQL

From
"Shridhar Daithankar"
Date:
On 4 Aug 2003 at 15:33, Manfred Koizar wrote:

> On 01 Aug 2003 11:04:10 -0700, Jenny Zhang <jenny@osdl.org> wrote:
> >A sample OSDL-DBT3 test result report can be found at:
> >http://khack.osdl.org/stp/276912/
> >
> >Your comments are welcome,

I could not get postgresql .conf so I will combine the comments.

1. Effective cache size, already mentioned
2. Sort memory already mentioned.
3. Was WAL put on different drive?
4. Can you try with autovacuum daemon and 7.4beta when it comes out..
5. What was the file system? Ext2/Ext3/reiser/XFS?

<Scratching head>

Is there any comparison available for other databases.. Could be interesting to
see..:-)

</Scratching head>

Thanks for the good work. I understand it must have been quite an effort to run
it..

Keep it up..

Bye
 Shridhar

--
Fourth Law of Revision:    It is usually impractical to worry beforehand about
interferences -- if you have none, someone will make one for you.


Re: [osdldbt-general] Re: [PERFORM] OSDL Database Test Suite 3 is

From
Jenny Zhang
Date:
Thanks all for your feedback.

I think I should explain more about how to use this test kit.

The main purpose of putting the test kit on Scalability Test
Platform(STP) is that testers can run the workload against the database
with different parameters and Linux kernels to see performance
differences.  Though the test kit picks up default parameters if they
are not provided, the command line parameters overwrite the default
ones.  Currently, the following parameters are supported:
-s <scale_factor> -n <number of streams> -d '<database parameters>' -r
<{0|1}> -x <{0|1}>

where:
-s <scale_factor> is tpc-h database scale factor, right now, only SF=1
is available.

-n <number of streams> is the number of throughput test streams, which
corresponds number of simultaneous database connections during
throughput test.

-d <database parameters> is the database parameters used when starting
postmaster.  for example:
-B 120000 -c effective_cache_size=393216 -c sort_mem=524288 -c
stats_command_string=true -c stats_row_level=true -c
stats_block_level=true

-r {0|1}: indicates if the database dir base/<database dir>/pgsql_tmp is
put on a separate disk drive

-x {0|1}: indicates if the WAL is put on a separate disk drive.

The other comments are in-lined:

On Mon, 2003-08-04 at 06:33, Manfred Koizar wrote:
> | effective_cache_size           | 1000
>
> With 4GB of memory this is definitely too low and *can* (note that I
> don't say *must*) lead the planner to wrong decisions.
>
I changed the default to effective_cache_size=393216 as calculated by
Scott.  Another way to check the execution plan is to go to the results
dir:
http://khack.osdl.org/stp/276917/results
There is a 'power_plan.out' file to record the execution plan.  I am
running a test with the changed effective_cache_size, I will see how it
affect the plan.

> | shared_buffers                 | 15200
>
> ... looks reasonable.  Did you test with other values?
I have only one with shared_buffers=1200000 at:
http://khack.osdl.org/stp/276847/
The performance degraded.
>
> | sort_mem                       | 524288
>
> This is a bit high, IMHO, but might be ok given that DBT3 is not run
> with many concurrent sessions (right?).
> http://khack.osdl.org/stp/276912/results/plot/vmstat_swap.png shows
> some swapping activity towards the end of the run which could be
> caused by a too high sort_mem setting.
Right, I run only 4 streams.  Setting this parameter lower caused more
reading/writing to the pgsql/tmp.  I guess the database has to do it if
it can not do sorting in memory.

On 4 Aug 2003 at 15:33, Manfred Koizar wrote:
>
> I could not get postgresql .conf so I will combine the comments.
It is under database monitor data: database parameters
>
> 1. Effective cache size, already mentioned
> 2. Sort memory already mentioned.
> 3. Was WAL put on different drive?
That run did not put WAL on different drive.  I changed it this morning
so that it is configurable.  Also I changed the result page so that the
testers can tell from the result page.
> 4. Can you try with autovacuum daemon and 7.4beta when it comes out..
I'd be happy to run it.  We would like to improve out Patch Life
Management(PLM) system so that it can accept PG patches and run
performance tests on those patches.  Right now PLM only manages Linux
Kernel patches.  I would like to ask the PostgreSQL community if this
kind of tools is of interest.
> 5. What was the file system? Ext2/Ext3/reiser/XFS?
>
> <Scratching head>
>
It is Ext2.  Yeah, it is not reported on the page.
> Is there any comparison available for other databases.. Could be interesting to
> see..:-)
>
> </Scratching head>
>

Let me know if you have any suggestions about how to improve the test
kit (parameters, reported information, etc.), or how to make it more
useful to PG community.

Thanks,
--
Jenny Zhang
Open Source Development Lab Inc
12725 SW Millikan Way
Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31


Re: [osdldbt-general] Re: [PERFORM] OSDL Database Test Suite 3 is

From
"scott.marlowe"
Date:
On 4 Aug 2003, Jenny Zhang wrote:

> On Mon, 2003-08-04 at 06:33, Manfred Koizar wrote:
> > | effective_cache_size           | 1000
> >
> > With 4GB of memory this is definitely too low and *can* (note that I
> > don't say *must*) lead the planner to wrong decisions.
> >
> I changed the default to effective_cache_size=393216 as calculated by
> Scott.  Another way to check the execution plan is to go to the results
> dir:
> http://khack.osdl.org/stp/276917/results
> There is a 'power_plan.out' file to record the execution plan.  I am
> running a test with the changed effective_cache_size, I will see how it
> affect the plan.
>
> > | shared_buffers                 | 15200
> >
> > ... looks reasonable.  Did you test with other values?
> I have only one with shared_buffers=1200000 at:
> http://khack.osdl.org/stp/276847/
> The performance degraded.

Well, that's truly huge, even for a machine with lots-o-ram.  Most tests
find that once the shared_buffers are big enough to use more than about 25
to 33% of RAM, they're too big, as you get little return.

> > | sort_mem                       | 524288
> >
> > This is a bit high, IMHO, but might be ok given that DBT3 is not run
> > with many concurrent sessions (right?).
> > http://khack.osdl.org/stp/276912/results/plot/vmstat_swap.png shows
> > some swapping activity towards the end of the run which could be
> > caused by a too high sort_mem setting.
> Right, I run only 4 streams.  Setting this parameter lower caused more
> reading/writing to the pgsql/tmp.  I guess the database has to do it if
> it can not do sorting in memory.

Note that IF your sortmem really is 1/2 gig, then you'll likely have LESS
than 3 gigs left for OS system cache.  About how big does top show buff
and cached to be on that box under load?  Not that it's a big deal if you
get the effective cache size off by a little bit, it's more of a rubber
mallet setting than a jeweler's screw driver setting.


Thanks a bunch for all the great testing.  It's a very nice tool to have
for convincing the bosses to go with Postgresql.


Re: [osdldbt-general] Re: [PERFORM] OSDL Database Test Suite 3 is

From
Craig Thomas
Date:
On Mon, 2003-08-04 at 09:39, Shridhar Daithankar wrote:
> On 4 Aug 2003 at 15:33, Manfred Koizar wrote:
>
> > On 01 Aug 2003 11:04:10 -0700, Jenny Zhang <jenny@osdl.org> wrote:
> > >A sample OSDL-DBT3 test result report can be found at:
> > >http://khack.osdl.org/stp/276912/
> > >
> > >Your comments are welcome,
>
> I could not get postgresql .conf so I will combine the comments.
>
> 1. Effective cache size, already mentioned
> 2. Sort memory already mentioned.
> 3. Was WAL put on different drive?
> 4. Can you try with autovacuum daemon and 7.4beta when it comes out..
> 5. What was the file system? Ext2/Ext3/reiser/XFS?
>
> <Scratching head>
>
> Is there any comparison available for other databases.. Could be interesting to
> see..:-)

OSDL has run workloads using the SAP DB and PostgreSQL.  However, each
of the workloads have been tweaked to work around deficiencies of each
database with respect to the TPC benchmarks from with the DBT workloads
are derrived.  Since there are extensive modifications to each workload
an that the fact that each workload operates under different situations
(SAP uses raw disk, PostgreSQL uses a file system), it is not beneficial
to compare numbers between different databases.

Remember, the intent is to provide a tool kit that can be used to
benefit the community.  From other postings, it appears that these
workloads we have available can be used to help the PostgreSQl community
develop a better database; one that is better able to handle the kinds
of stress these workloads can produce when scaled to large database
sizes.

We have been using these kits to characterize the abilities of the Linux
kernel.  To show that these workloads work with two different databases
implies that Linux is capable of supporting these two databases.

The other tool kits, by the way, are being ported to PostgreSQL as well.
Help is needed to tune the workloads to exercise PostgreSQL better.  It
would be great if you could get involved with the porting efforts and
assist with the tuning of the PostgreSQL kit.


>
> </Scratching head>
>
> Thanks for the good work. I understand it must have been quite an effort to run
> it..
>
> Keep it up..
>
> Bye
>  Shridhar
>
> --
> Fourth Law of Revision:    It is usually impractical to worry beforehand about
> interferences -- if you have none, someone will make one for you.
>
>
>
> -------------------------------------------------------
> This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> Data Reports, E-commerce, Portals, and Forums are available now.
> Download today and enter to win an XBOX or Visual Studio .NET.
> http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> _______________________________________________
> osdldbt-general mailing list
> osdldbt-general@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/osdldbt-general
--
Craig Thomas
craiger@osdl.org