[
https://issues.apache.org/jira/browse/DERBY-3024?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12538150
]
Daniel John Debrunner commented on DERBY-3024:
----------------------------------------------
GPS#getActivation & GPS#finish will not be called per execution (except when
using a Statement).
The upToDate() check interacts with the table locking of any DDL that lead to
the invalidation.
When a table T is modified via DDL there is an exclusive lock held on T.
This lock is obtained and then plans dependent on that table are modified.
Thus if a statement has obtained an intent lock on T and it is valid
(upToDate()) then it can complete its execution knowing that no DDL can proceed
and invalidate it since it holds an intent table lock that will block any DDL's
exclusive lock.
So ideally a plan will check that it's up to date once all of its table locks
are obtained, in Derby this is not centralized. Some DBMS's as part of their
compilation setup a list of table intent locks and obtain them at the start of
execution. In Derby this is handled by calling checkStatementValdity() in
*each* open of a ResultSet (possibly regardless of it it obtains a table lock
or not).
Ideally this would be in one place, maybe after the open of the top level
(language) ResultSet and thus executed once per-plan. I'm not sure though if
the top-level open is guaranteed to open all the tables that the plan requires.
There's room for improvement here, not least by writing up & understanding all
the interactions.
> 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.
