You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bryan Pendleton (JIRA)" <ji...@apache.org> on 2009/08/29 17:47:32 UTC
[jira] Created: (DERBY-4363) Add simple benchmark for measuring
GROUP BY performance
Add simple benchmark for measuring GROUP BY performance
-------------------------------------------------------
Key: DERBY-4363
URL: https://issues.apache.org/jira/browse/DERBY-4363
Project: Derby
Issue Type: Sub-task
Components: Test, Tools
Reporter: Bryan Pendleton
Assignee: Bryan Pendleton
Priority: Minor
As part of ROLLUP implementation (DERBY-3002), it will be helpful to be able to measure the performance of GROUP BY.
Using the o.a.dT.perf.clients framework, this sub-task proposes to add a GroupByClient to the performance runner
library; the GroupByClient will run GROUP BY statements against the Wisconsin benchmark database.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4363) Add simple benchmark for measuring
GROUP BY performance
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12752394#action_12752394 ]
Knut Anders Hatlen commented on DERBY-4363:
-------------------------------------------
Thanks, Bryan. That sounds like a great plan to me. I'll see if I can find time to run the tests too.
> Add simple benchmark for measuring GROUP BY performance
> -------------------------------------------------------
>
> Key: DERBY-4363
> URL: https://issues.apache.org/jira/browse/DERBY-4363
> Project: Derby
> Issue Type: Sub-task
> Components: Test, Tools
> Reporter: Bryan Pendleton
> Assignee: Bryan Pendleton
> Priority: Minor
> Attachments: multiColumnBenchmark.diff, simpleBenchmark.diff
>
>
> As part of ROLLUP implementation (DERBY-3002), it will be helpful to be able to measure the performance of GROUP BY.
> Using the o.a.dT.perf.clients framework, this sub-task proposes to add a GroupByClient to the performance runner
> library; the GroupByClient will run GROUP BY statements against the Wisconsin benchmark database.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4363) Add simple benchmark for measuring
GROUP BY performance
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4363?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-4363:
-----------------------------------
Attachment: multiColumnBenchmark.diff
Attached is 'multiColumnBenchmark, an enhanced version of the GroupByClient proposal
which can now generate a richer variety of GROUP BY statements.
It also only executes a single statement per run, since I agree with the
observation that it is hard to interpret the results of running a mixture
of statements in the same run.
I put a lot of comments into the GroupByClient header which should explain
how to invoke the benchmark to run a richer set of statements.
I gave getLoadOpt package visibility so that the GroupByClient could
interrogate the -load_opts settings in a more convenient fashion.
Continued suggestions and comments would be greatly appreciated.
Soon, I hope to find the time to run this benchmark against the current trunk,
as well as against the DERBY-3002 patch proposal, to get a first set of
numbers to explore the overall performance characteristics in a coarse fashion.
I'm hoping it will be sufficient to perform, say, 5 different GROUP BY statements
against each version of the code, at scales 10 thousand, 100 thousand, and
250 thousand rows. That will give us 15 numbers for each branch, and
maybe we can see some results from that data.
I should be able to post those runs as a "script" of 18 perf.clients.Runner statements
to be run in sequence against each code branch.
> Add simple benchmark for measuring GROUP BY performance
> -------------------------------------------------------
>
> Key: DERBY-4363
> URL: https://issues.apache.org/jira/browse/DERBY-4363
> Project: Derby
> Issue Type: Sub-task
> Components: Test, Tools
> Reporter: Bryan Pendleton
> Assignee: Bryan Pendleton
> Priority: Minor
> Attachments: multiColumnBenchmark.diff, simpleBenchmark.diff
>
>
> As part of ROLLUP implementation (DERBY-3002), it will be helpful to be able to measure the performance of GROUP BY.
> Using the o.a.dT.perf.clients framework, this sub-task proposes to add a GroupByClient to the performance runner
> library; the GroupByClient will run GROUP BY statements against the Wisconsin benchmark database.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4363) Add simple benchmark for measuring
GROUP BY performance
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4363?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-4363:
-----------------------------------
Attachment: simpleBenchmark.diff
Attached 'simpleBenchmark.diff' is a patch proposal for a new benchmark
in the o.a.dT.perf.clients package.
This benchmark runs 3 GROUP BY statements against the scalable
Wisconsin table TENKTUP1. The statements partition the entire table
into (a) 10 groups, (b) 100 groups, and (c) 1000 groups.
Sample ways to run this benchmark:
1) java org.apache.derbyTesting.perf.clients.Runner -load group_by -init
initializes the database and runs the benchmark against the 10,000 row
default Wisconsin database.
2) java org.apache.derbyTesting.perf.clients.Runner -load group_by -init -load_opts numRows=100000
Reloads the DB and runs the benchmark against a 100,000 row table.
3) java org.apache.derbyTesting.perf.clients.Runner -load group_by -init -load_opts numRows=1000000 -rt 300
Reloads the DB and runs the benchmark against a 1,000,000 row table. Since
the runtimes are much slower against a giant table, lets the benchmark run 5 minutes.
Comments and suggestions would be wonderful.
> Add simple benchmark for measuring GROUP BY performance
> -------------------------------------------------------
>
> Key: DERBY-4363
> URL: https://issues.apache.org/jira/browse/DERBY-4363
> Project: Derby
> Issue Type: Sub-task
> Components: Test, Tools
> Reporter: Bryan Pendleton
> Assignee: Bryan Pendleton
> Priority: Minor
> Attachments: simpleBenchmark.diff
>
>
> As part of ROLLUP implementation (DERBY-3002), it will be helpful to be able to measure the performance of GROUP BY.
> Using the o.a.dT.perf.clients framework, this sub-task proposes to add a GroupByClient to the performance runner
> library; the GroupByClient will run GROUP BY statements against the Wisconsin benchmark database.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4363) Add simple benchmark for measuring
GROUP BY performance
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4363?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-4363:
-----------------------------------
Attachment: bmWithPatch.out
bmTrunk.out
benchmark.sh
Attached is 'benchmark.sh', which contains the simple script that I ran to
conduct a 15-item benchmark of the GROUP BY statement with and without
the DERBY-3002 patch applied.
Also attached is 'bmTrunk.out' and 'bmWithPatch.out', which are the benchmark
results of the script on a clean trunk build, and on a build with the DERBY-3002 patch.
Unfortunately, the benchmark appears to show substantial performance
problems with the DERBY-3002 patch, so I'll have to look into the results more deeply.
I suspect that this means that the DERBY-3002 patch cannot simply eliminate
the code patch which performs aggregation within the sort observer, because
the performance wins from doing such aggregation are substantial.
> Add simple benchmark for measuring GROUP BY performance
> -------------------------------------------------------
>
> Key: DERBY-4363
> URL: https://issues.apache.org/jira/browse/DERBY-4363
> Project: Derby
> Issue Type: Sub-task
> Components: Test, Tools
> Reporter: Bryan Pendleton
> Assignee: Bryan Pendleton
> Priority: Minor
> Attachments: benchmark.sh, bmTrunk.out, bmWithPatch.out, multiColumnBenchmark.diff, simpleBenchmark.diff
>
>
> As part of ROLLUP implementation (DERBY-3002), it will be helpful to be able to measure the performance of GROUP BY.
> Using the o.a.dT.perf.clients framework, this sub-task proposes to add a GroupByClient to the performance runner
> library; the GroupByClient will run GROUP BY statements against the Wisconsin benchmark database.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4363) Add simple benchmark for measuring
GROUP BY performance
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12749431#action_12749431 ]
Knut Anders Hatlen commented on DERBY-4363:
-------------------------------------------
Hi Bryan,
Thanks for writing the test. It looks good to me, and I think it will be helpful in evaluating whether DERBY-3002 affects plain GROUP BY statements in any way.
Currently, the test uses a mix of three different statements with different characteristics. This means that the reported results only show the average, and some effects may be hidden if the performance of one of the statements is improved whereas another one performs worse. Perhaps we should have an option to run with just one of the statements?
Do you think it would add value to have a test case with multiple GROUP BY columns?
> Add simple benchmark for measuring GROUP BY performance
> -------------------------------------------------------
>
> Key: DERBY-4363
> URL: https://issues.apache.org/jira/browse/DERBY-4363
> Project: Derby
> Issue Type: Sub-task
> Components: Test, Tools
> Reporter: Bryan Pendleton
> Assignee: Bryan Pendleton
> Priority: Minor
> Attachments: simpleBenchmark.diff
>
>
> As part of ROLLUP implementation (DERBY-3002), it will be helpful to be able to measure the performance of GROUP BY.
> Using the o.a.dT.perf.clients framework, this sub-task proposes to add a GroupByClient to the performance runner
> library; the GroupByClient will run GROUP BY statements against the Wisconsin benchmark database.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-4363) Add simple benchmark for measuring
GROUP BY performance
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4363?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton resolved DERBY-4363.
------------------------------------
Resolution: Fixed
Fix Version/s: 10.6.0.0
I fixed a small typo in wisconsin.java (the 'compress' argument was always being
set to 'false', ran a complete set of regression tests without problem, and submitted
this benchmark as revision 814237.
We may end up adding more types of GROUP BY statements to this benchmark
over time, but for now it seems like the work on this task is done.
> Add simple benchmark for measuring GROUP BY performance
> -------------------------------------------------------
>
> Key: DERBY-4363
> URL: https://issues.apache.org/jira/browse/DERBY-4363
> Project: Derby
> Issue Type: Sub-task
> Components: Test, Tools
> Reporter: Bryan Pendleton
> Assignee: Bryan Pendleton
> Priority: Minor
> Fix For: 10.6.0.0
>
> Attachments: benchmark.sh, bmTrunk.out, bmWithPatch.out, multiColumnBenchmark.diff, simpleBenchmark.diff
>
>
> As part of ROLLUP implementation (DERBY-3002), it will be helpful to be able to measure the performance of GROUP BY.
> Using the o.a.dT.perf.clients framework, this sub-task proposes to add a GroupByClient to the performance runner
> library; the GroupByClient will run GROUP BY statements against the Wisconsin benchmark database.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4363) Add simple benchmark for measuring
GROUP BY performance
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12753600#action_12753600 ]
Knut Anders Hatlen commented on DERBY-4363:
-------------------------------------------
I've only run the tests in one configuration (-load group_by -load_opts numGroupingCols=2,numGroups1=10,numGroups2=100), but my runs confirm what you're seeing. I had a quick look at some profiling results, but nothing jumped at me. The runs with the DERBY-3002 patch did seem to spend more time copying values, though (ValueRow.getClone() and SQLInteger.getClone() in particular). Not sure if that helps, but I thought I'd mention it.
> Add simple benchmark for measuring GROUP BY performance
> -------------------------------------------------------
>
> Key: DERBY-4363
> URL: https://issues.apache.org/jira/browse/DERBY-4363
> Project: Derby
> Issue Type: Sub-task
> Components: Test, Tools
> Reporter: Bryan Pendleton
> Assignee: Bryan Pendleton
> Priority: Minor
> Attachments: benchmark.sh, bmTrunk.out, bmWithPatch.out, multiColumnBenchmark.diff, simpleBenchmark.diff
>
>
> As part of ROLLUP implementation (DERBY-3002), it will be helpful to be able to measure the performance of GROUP BY.
> Using the o.a.dT.perf.clients framework, this sub-task proposes to add a GroupByClient to the performance runner
> library; the GroupByClient will run GROUP BY statements against the Wisconsin benchmark database.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.