[jira] Updated: (DERBY-3024) Validation of shared plans hurts scalability

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-3024?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen updated DERBY-3024:
--------------------------------------

    Attachment: patch-1a.png
                patch-1a.diff

I ran the Values1 test on a Sun Fire T2000 with 32 virtual processors (running
Solaris 10 and Java version 1.6.0_15) and noticed that there was a simple
change in BaseActivation.checkStatementValidity() that improved the situation
somewhat. As mentioned in the previous comments, there's a synchronized block
in checkStatementValidity() where a lot of time is spent waiting:

    synchronized (preStmt) {
        if ((gc == preStmt.getActivationClass()) && preStmt.upToDate())
            return;
    }

If the (gc == preStmt.getActivationClass()) check is moved inside
preStmt.upToDate(), which is also synchronized on preStmt, we avoid a double
synchronization. This appears to take some of the pressure off the monitor and
allows the Values1 test to scale better. The preStmt monitor is still very hot,
though, so the performance still breaks down when too many threads are added,
but it is able to handle more threads than before before it breaks down.

The attached patch and graph (patch-1a.diff and patch-1a.png) show the change
and its effect on the scalability. Whereas trunk maxes out on 5 threads and
305K tx/s, the patched version maxes out on 7 threads and 520K tx/s. After both
trunk and the patched version have collapsed because of too many threads, the
patched version seems to stabilize on a level 30% higher than trunk.

For comparison, the graph also shows the results for trunk with separate plans
for each thread. Its throughput grows steadily for each thread added until the
number of threads reaches the number of virtual processors (32), which is still
far better than with shared plans, so it's clear that the patch is not a full
solution to this issue. It doesn't do anything with the underlying problem,
which is that upToDate() is called way too frequently during execution, but it
may be a good first step to remove the overhead of shared plans.

One may perhaps expect the JVM to be able to eliminate double synchronization,
so that such a change should not be necessary. Anyhow, I think the change would
make sense even without any performance benefit, as it hides some of
GenericPreparedStatement's internal synchronization details from users of the
PreparedStatement interface.

> Validation of shared plans hurts scalability
> --------------------------------------------
>
>                 Key: DERBY-3024
>                 URL: https://issues.apache.org/jira/browse/DERBY-3024
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.4.1.3
>         Environment: Sun Java SE 6, Solaris 10, Sun Fire V880 (8 CPUs)
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>         Attachments: patch-1a.diff, patch-1a.png, 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.