Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.6.1.0
    • Component/s: Test, Tools
    • Labels:
      None
    • Bug behavior facts:
      Performance

      Description

      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.

      1. bmWithPatch.out
        6 kB
        Bryan Pendleton
      2. bmTrunk.out
        6 kB
        Bryan Pendleton
      3. benchmark.sh
        2 kB
        Bryan Pendleton
      4. multiColumnBenchmark.diff
        13 kB
        Bryan Pendleton
      5. simpleBenchmark.diff
        10 kB
        Bryan Pendleton

        Activity

        Hide
        Bryan Pendleton added a comment -

        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.

        Show
        Bryan Pendleton added a comment - 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.
        Hide
        Knut Anders Hatlen added a comment -

        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?

        Show
        Knut Anders Hatlen added a comment - 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?
        Hide
        Bryan Pendleton added a comment -

        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.

        Show
        Bryan Pendleton added a comment - 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.
        Hide
        Knut Anders Hatlen added a comment -

        Thanks, Bryan. That sounds like a great plan to me. I'll see if I can find time to run the tests too.

        Show
        Knut Anders Hatlen added a comment - Thanks, Bryan. That sounds like a great plan to me. I'll see if I can find time to run the tests too.
        Hide
        Bryan Pendleton added a comment -

        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.

        Show
        Bryan Pendleton added a comment - 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.
        Hide
        Knut Anders Hatlen added a comment -

        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.

        Show
        Knut Anders Hatlen added a comment - 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.
        Hide
        Bryan Pendleton added a comment -

        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.

        Show
        Bryan Pendleton added a comment - 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.

          People

          • Assignee:
            Bryan Pendleton
            Reporter:
            Bryan Pendleton
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development