[
https://issues.apache.org/jira/browse/DERBY-3024?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12522204
]
Manish Khettry commented on DERBY-3024:
---------------------------------------
That is very interesting. A couple of thoughts on this.
First, the point of sharing plans is to avoid doing potentially expensive
compilation. By choosing a really simple query which is cheap both to compile
and execute you are effectively measuring only the cost of sharing plans. If
you had even a slightly more expensive query, I doubt you would see such a huge
disparity between the two cases.
That having been said, the lack of any speedup is troubling. I ran the same
query to see how many times the routines you mentioned
(GenericPreparedStatement#upToDate and BaseActivation#checkStatementValdity)
are executed. The first one is called *five* times per query and the second one
*once*. I haven't looked at the code too closely but it does seem excessive and
could be a starting point to investigate contention.
Also, there are two other routines GPS#finish and GPS#getActivation which
synchronize on the GPS and are called once per statement so these routines add
to the contention as well.
> Validation of shared plans hurts scalability
> --------------------------------------------
>
> Key: DERBY-3024
> URL: https://issues.apache.org/jira/browse/DERBY-3024
> Project: Derby
> Issue Type: Improvement
> Components: Performance, SQL
> Affects Versions: 10.4.0.0
> Environment: Sun Java SE 6, Solaris 10, Sun Fire V880 (8 CPUs)
> Reporter: Knut Anders Hatlen
> Priority: Minor
> Attachments: Values.java, values1.png
>
>
> To investigate whether there was anything in the SQL execution layer that
> prevented scaling on a multi-CPU machine, I wrote a multi-threaded test which
> continuously executed "VALUES 1" using a PreparedStatement. I ran the test on
> a machine with 8 CPUs and expected the throughput to be proportional to the
> number of concurrent clients up to 8 clients (the same as the number of
> CPUs). However, the throughput only had a small increase from 1 to 2 clients,
> and adding more clients did not increase the throughput. Looking at the test
> in a profiler, it seems like the threads are spending a lot of time waiting
> to enter synchronization blocks in GenericPreparedStatement.upToDate() and
> BaseActivation.checkStatementValidity() (both of which are synchronized on
> the a GenericPreparedStatement object).
> I then changed the test slightly, appending a comment with a unique thread id
> to the "VALUES 1" statement. That means the threads still did the same work,
> but each thread got its own plan (GenericPreparedStatement object) since the
> statement cache didn't regard the SQL text strings as identical. When I made
> that change, the test scaled more or less perfectly up to 8 concurrent
> threads.
> We should try to find a way to make the scalability the same regardless of
> whether or not the threads share the same plan.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
