db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Manish Khettry (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3024) Validation of shared plans hurts scalability
Date Thu, 23 Aug 2007 17:50:30 GMT

    [ 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:
>         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
> 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.

View raw message