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 Olav Sandstaa <Ol...@Sun.COM> on 2007/04/26 15:00:12 UTC
Performance regression after check-in on DERBY 2537 (SVN 531971)
The two last days I have seen a performance regression in some of the
performance tests I run. For some of the tests the reduction in
throughput is about 15 percent. It seems like the regression is
introduced by the latest check-in on DERBY-2537, svn 531971.
I have attached a graph showing the throughput I get when running
single-record select operation on a table. The queries use a secondary
index for finding the record to select. The test has been run with 1 to
20 concurrent client against embedded Derby. I have run the test with
and without SVN 531971. As the graph shows, the performance reduction is
between 12 and 15 percent for all runs.
The schema looks like this:
CREATE TABLE t1 id INTEGER sec_id INTEGER data CHARACTER(100) PRIMARY
KEY(id))
CREATE INDEX nonprimary_index ON t1 (sec_id)
Each query do the select on the secondary index and retrieves the data
field (a CHARACTER(100) field).
I would expect some of the changes done in the patch to have some
influence on the performance, but not in the order of 10-15 percent.
The tests are run on a 2 CPU Opteron server running Solaris 10 and JDK 6.
Olav
Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Olav Sandstaa <Ol...@Sun.COM> writes:
> Attached is a slightly modified version of the test client found in
> DERBY-1961. The main change I have done is to add a secondary index to
> it that is used for the queries. Running this with two clients on a
> dual CPU machine running Linux and using IBM JVM 1.5 I see a drop in
> throughput of about 6 percent (down from 16656 tps to 15706 tps -
> average of five runs).
Thanks for the test client, Olav.
I ran the test on my single-CPU Athlon 64 running OpenSolaris and got
almost the same results as you. When moving from revision 531970 to
revision 531971, the throughput dropped by between 6% and 22%. I used
Sun Java 1.6 (server VM) and derby.storage.pageCacheSize=12500 for the
tests, and 1 to 20 concurrent clients.
More interestingly, I applied the derby827_update920.txt patch attached
to DERBY-827 and reran the tests. With that patch applied, there was no
difference between the two revisions as far as I could see. The results
from the test runs are attached as select.png.
I'm just guessing here, but could the DERBY-2537 check-in have pushed
the amount of object allocations over some threshold that made the gc
cost much higher, whereas the DERBY-827 patch reduced the total amount
of allocations by so much that gc is able to cope with the small
increase from DERBY-2537?
--
Knut Anders
Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
Posted by Olav Sandstaa <Ol...@Sun.COM>.
Attached is a slightly modified version of the test client found in
DERBY-1961. The main change I have done is to add a secondary index to
it that is used for the queries. Running this with two clients on a dual
CPU machine running Linux and using IBM JVM 1.5 I see a drop in
throughput of about 6 percent (down from 16656 tps to 15706 tps -
average of five runs).
To run this client do:
1. Create and initialize database:
java TestClient2 -a initdb -u "jdbc:derby:/tmp/tulldb;create=true"
2. Run test (which does SELECT * FROM ... WHERE SEC_ID= X) with two
client threads:
java TestClient2 -a select -r 60 -c 2 -u "jdbc:derby:/tmp/tulldb"
Olav
Mike Matrigali wrote:
> Are these tests checked in so that I can run them in my environment?
>
> Olav Sandstaa wrote:
>> The two last days I have seen a performance regression in some of the
>> performance tests I run. For some of the tests the reduction in
>> throughput is about 15 percent. It seems like the regression is
>> introduced by the latest check-in on DERBY-2537, svn 531971.
>>
>> I have attached a graph showing the throughput I get when running
>> single-record select operation on a table. The queries use a
>> secondary index for finding the record to select. The test has been
>> run with 1 to 20 concurrent client against embedded Derby. I have run
>> the test with and without SVN 531971. As the graph shows, the
>> performance reduction is between 12 and 15 percent for all runs.
>>
>> The schema looks like this:
>>
>> CREATE TABLE t1 id INTEGER sec_id INTEGER data CHARACTER(100)
>> PRIMARY KEY(id))
>> CREATE INDEX nonprimary_index ON t1 (sec_id)
>>
>> Each query do the select on the secondary index and retrieves the
>> data field (a CHARACTER(100) field).
>>
>> I would expect some of the changes done in the patch to have some
>> influence on the performance, but not in the order of 10-15 percent.
>>
>> The tests are run on a 2 CPU Opteron server running Solaris 10 and
>> JDK 6.
>>
>> Olav
>>
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>
Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
Posted by Olav Sandstaa <Ol...@Sun.COM>.
I have re-run the same test using IBM JVM 1.5 on Linux. The resulting
graphs are attached. Compared to the results when running on Sun JDK 6
the regression seems to influence the performance less on IMB JBM/Linux,
but it is still in the order of 8-10% in my test.
I have also re-run the test using Sun JDK 5 VM and this also shows that
there seems to be a regression (see attached graph).
Olav
Olav Sandstaa wrote:
> To see the effect of this in a different environment I started a
> similar test running with IBM 1.5 VM on Linux. Unfortunately, the
> machine I decided to use was a shared server so during part of the
> test run some other testing have been running in parallel. I plan to
> try to re-run these tomorrow.
>
> But anyway, I am attaching the graph even if it is not correct. It
> seems that the negative effect of this patch is much less when running
> on the IBM VM/Linux (the last part of the second curve I expect was
> run in parallel with some other testing). So if anyone is looking into
> this and using a different VM (or OS) than I did they might not see
> the large drop I see in my tests.
>
> Olav
>
>
> Mike Matrigali wrote:
>> Are these tests checked in so that I can run them in my environment?
>>
>> Olav Sandstaa wrote:
>>> The two last days I have seen a performance regression in some of
>>> the performance tests I run. For some of the tests the reduction in
>>> throughput is about 15 percent. It seems like the regression is
>>> introduced by the latest check-in on DERBY-2537, svn 531971.
>>>
>>> I have attached a graph showing the throughput I get when running
>>> single-record select operation on a table. The queries use a
>>> secondary index for finding the record to select. The test has been
>>> run with 1 to 20 concurrent client against embedded Derby. I have
>>> run the test with and without SVN 531971. As the graph shows, the
>>> performance reduction is between 12 and 15 percent for all runs.
>>>
>>> The schema looks like this:
>>>
>>> CREATE TABLE t1 id INTEGER sec_id INTEGER data CHARACTER(100)
>>> PRIMARY KEY(id))
>>> CREATE INDEX nonprimary_index ON t1 (sec_id)
>>>
>>> Each query do the select on the secondary index and retrieves the
>>> data field (a CHARACTER(100) field).
>>>
>>> I would expect some of the changes done in the patch to have some
>>> influence on the performance, but not in the order of 10-15 percent.
>>>
>>> The tests are run on a 2 CPU Opteron server running Solaris 10 and
>>> JDK 6.
>>>
>>> Olav
>>>
>>>
>>>
>>>
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>
>
>
> ------------------------------------------------------------------------
>
Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
Posted by Olav Sandstaa <ol...@sun.com>.
To see the effect of this in a different environment I started a similar
test running with IBM 1.5 VM on Linux. Unfortunately, the machine I
decided to use was a shared server so during part of the test run some
other testing have been running in parallel. I plan to try to re-run
these tomorrow.
But anyway, I am attaching the graph even if it is not correct. It seems
that the negative effect of this patch is much less when running on the
IBM VM/Linux (the last part of the second curve I expect was run in
parallel with some other testing). So if anyone is looking into this and
using a different VM (or OS) than I did they might not see the large
drop I see in my tests.
Olav
Mike Matrigali wrote:
> Are these tests checked in so that I can run them in my environment?
>
> Olav Sandstaa wrote:
>> The two last days I have seen a performance regression in some of the
>> performance tests I run. For some of the tests the reduction in
>> throughput is about 15 percent. It seems like the regression is
>> introduced by the latest check-in on DERBY-2537, svn 531971.
>>
>> I have attached a graph showing the throughput I get when running
>> single-record select operation on a table. The queries use a
>> secondary index for finding the record to select. The test has been
>> run with 1 to 20 concurrent client against embedded Derby. I have run
>> the test with and without SVN 531971. As the graph shows, the
>> performance reduction is between 12 and 15 percent for all runs.
>>
>> The schema looks like this:
>>
>> CREATE TABLE t1 id INTEGER sec_id INTEGER data CHARACTER(100)
>> PRIMARY KEY(id))
>> CREATE INDEX nonprimary_index ON t1 (sec_id)
>>
>> Each query do the select on the secondary index and retrieves the
>> data field (a CHARACTER(100) field).
>>
>> I would expect some of the changes done in the patch to have some
>> influence on the performance, but not in the order of 10-15 percent.
>>
>> The tests are run on a 2 CPU Opteron server running Solaris 10 and
>> JDK 6.
>>
>> Olav
>>
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>
Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
Posted by Olav Sandstaa <ol...@sun.com>.
Hi Mike,
No, unfortunately these tests are not checked in. But using the test
client attached to DERBY-1961 should generate similar load.
Olav
Mike Matrigali wrote:
> Are these tests checked in so that I can run them in my environment?
>
> Olav Sandstaa wrote:
>> The two last days I have seen a performance regression in some of the
>> performance tests I run. For some of the tests the reduction in
>> throughput is about 15 percent. It seems like the regression is
>> introduced by the latest check-in on DERBY-2537, svn 531971.
>>
>> I have attached a graph showing the throughput I get when running
>> single-record select operation on a table. The queries use a
>> secondary index for finding the record to select. The test has been
>> run with 1 to 20 concurrent client against embedded Derby. I have run
>> the test with and without SVN 531971. As the graph shows, the
>> performance reduction is between 12 and 15 percent for all runs.
>>
>> The schema looks like this:
>>
>> CREATE TABLE t1 id INTEGER sec_id INTEGER data CHARACTER(100)
>> PRIMARY KEY(id))
>> CREATE INDEX nonprimary_index ON t1 (sec_id)
>>
>> Each query do the select on the secondary index and retrieves the
>> data field (a CHARACTER(100) field).
>>
>> I would expect some of the changes done in the patch to have some
>> influence on the performance, but not in the order of 10-15 percent.
>>
>> The tests are run on a 2 CPU Opteron server running Solaris 10 and
>> JDK 6.
>>
>> Olav
>>
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>
Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Are these tests checked in so that I can run them in my environment?
Olav Sandstaa wrote:
> The two last days I have seen a performance regression in some of the
> performance tests I run. For some of the tests the reduction in
> throughput is about 15 percent. It seems like the regression is
> introduced by the latest check-in on DERBY-2537, svn 531971.
>
> I have attached a graph showing the throughput I get when running
> single-record select operation on a table. The queries use a secondary
> index for finding the record to select. The test has been run with 1 to
> 20 concurrent client against embedded Derby. I have run the test with
> and without SVN 531971. As the graph shows, the performance reduction is
> between 12 and 15 percent for all runs.
>
> The schema looks like this:
>
> CREATE TABLE t1 id INTEGER sec_id INTEGER data CHARACTER(100) PRIMARY
> KEY(id))
> CREATE INDEX nonprimary_index ON t1 (sec_id)
>
> Each query do the select on the secondary index and retrieves the data
> field (a CHARACTER(100) field).
>
> I would expect some of the changes done in the patch to have some
> influence on the performance, but not in the order of 10-15 percent.
>
> The tests are run on a 2 CPU Opteron server running Solaris 10 and JDK 6.
>
> Olav
>
>
>
>
>
>
> ------------------------------------------------------------------------
>
Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
Posted by Olav Sandstaa <ol...@sun.com>.
Knut.Hatlen@Sun.COM wrote:
>
> Olav, could you please file a JIRA issue for the regression so that it
> is not forgotten? A JIRA issue would also be a good place to upload the
> test client that you posted in this thread.
>
Thanks for the suggestion. I have filed DERBY-2657 for this issue and
attached the test client.
Olav
Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
Posted by Kn...@Sun.COM.
Olav Sandstaa <Ol...@Sun.COM> writes:
> The two last days I have seen a performance regression in some of the
> performance tests I run. For some of the tests the reduction in
> throughput is about 15 percent. It seems like the regression is
> introduced by the latest check-in on DERBY-2537, svn 531971.
Olav, could you please file a JIRA issue for the regression so that it
is not forgotten? A JIRA issue would also be a good place to upload the
test client that you posted in this thread.
Thanks,
--
Knut Anders
Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
Posted by Olav Sandstaa <ol...@sun.com>.
The actual performance data from the regression test that made me
suspect there had been a regression are available here:
http://home.online.no/~olmsan/derby/perf/select_sec_index_30d.html
These are produced using the same test client as in the graphs I sent
out earlier. In addition to include the throughput numbers, CPU numbers
are presented that also indicate increased CPU usage.
These tests are run with JDK6 on Solaris 10.
Olav
Olav Sandstaa wrote:
> The two last days I have seen a performance regression in some of the
> performance tests I run. For some of the tests the reduction in
> throughput is about 15 percent. It seems like the regression is
> introduced by the latest check-in on DERBY-2537, svn 531971.
>
> I have attached a graph showing the throughput I get when running
> single-record select operation on a table. The queries use a secondary
> index for finding the record to select. The test has been run with 1
> to 20 concurrent client against embedded Derby. I have run the test
> with and without SVN 531971. As the graph shows, the performance
> reduction is between 12 and 15 percent for all runs.
>
> The schema looks like this:
>
> CREATE TABLE t1 id INTEGER sec_id INTEGER data CHARACTER(100)
> PRIMARY KEY(id))
> CREATE INDEX nonprimary_index ON t1 (sec_id)
>
> Each query do the select on the secondary index and retrieves the data
> field (a CHARACTER(100) field).
>
> I would expect some of the changes done in the patch to have some
> influence on the performance, but not in the order of 10-15 percent.
>
> The tests are run on a 2 CPU Opteron server running Solaris 10 and JDK 6.
>
> Olav
>
>
>
>
>
>
> ------------------------------------------------------------------------
>