Обсуждение: OSDL Database Test Suite 3 is available on PostgreSQL
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
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
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.
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.
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.
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
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.
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