[jira] Updated: (DERBY-3788) Provide a zero-admin way of updating the statisitcs of an index

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

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

Mamta A. Satoor updated DERBY-3788:
-----------------------------------

    Attachment: DERBY3788_patch3_stat.txt
                DERBY3788_patch3_diff.txt

I am attaching a new patch which incorporates following comments from Knut.
2)Are the calls to EmbedConnection30.setupContextStack() and restoreContextStack() needed around the call to execute()? I thought execute() would call setup/restoreContextStack() itself. 
I removed the calls to setup and restore the stack since as Knut pointed out, they are unnecessary.
3)Creating an EmbedConnection30 object directly breaks the modularity. Unless the calls to the internal methods are necessary, it may be better to use InternalDriver.activeDriver().connect(url, info) instead. 
I removed the direct creation of EmbedConnection30 and instead use InternalDriver.activeDriver().connect(url, info) 
5)There's a comment in DDImpl5.updateStatisticsInBackGround() saying that "cm is null the very first time, and whenever we aren't actually nested." I'm not sure I understand that comment. Why is it null the first time? And isn't the method always called in a nested context? And if it is null, wouldn't that cause a NullPointerException in EmbedConnection's constructor when url=null is passed in? 
In the case that ContextManager may be null, I do not fire statistics creation in background because that will cause NPE later on because url is null.
6) DDImpl5.updateStatisticsInBackGround() updates the shared variable executorForUpdateStatistics if it is null. But it is not protected by synchronization, so race conditions are possible. 
I now have synchronization code around the code that can result in race conditions.
7) DDImpl5.stop() should call super.stop(). 
Did this.
8) In BackgroundUpdateStatisticsTask, using a prepared statement with the table name and the index name parametrized would be better because it would handle quoting special characters correctly (not handled in 
the current patch) and it would reduce the number of entries in the statement cache. 
Now using PreparedStatement rather than Statement.

The 2 comments from Knut that are not addressed in this patch are as follows
4) I think that the creation of a new connection will reboot the database if it has been shut down in the user thread, which may lead to unpredictable behaviour. It also seems like it will preserve all connection attributes, like attributes to reencrypt the database or to start replication master. 
I think there might be an issue as raised by Knut's comment 4). I still have to trace down completely why some of the junit test failures with my patch say Database "Failed to start(/create) database". It might be because of the properties that are used to create the new Connection object to run the statistics in background. I am not sure yet if it happens during every run of the test jdbcapi.DriverMgrAuthenticationTest but I have seen that test to be one of the tests that fails with Failed to create database. I will work on trying to figure out at what point does the test fail with these errors. If anyone has any ideas on the failures or how we should create the new Connection object,
I will highly appreciate that.
9) As to the locking issues, I would have tried to call Connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED) in the background thread to see if that solved/reduced the issues. 
I am not 100% about this but I this using isolation Connection.TRANSACTION_READ_UNCOMMITTED had slowed down test runs on my machine. I would like to address number 4 first so that at least the test failures go down and then I can focus on usage of isolation level 
Connection.TRANSACTION_READ_UNCOMMITTED. There ARE few tests that fail because of lock time out and may be using the isolation level recommended by Knut will help.

So, at this point, I will focus on 4) to try to narrow down where are the failures coming from. In the mean time, if community has time to run the junit tests to see if they notice noticeable performance problems with my patch, I will appreciate that feedback. Thanks

> Provide a zero-admin way of updating the statisitcs of an index
> ---------------------------------------------------------------
>
>                 Key: DERBY-3788
>                 URL: https://issues.apache.org/jira/browse/DERBY-3788
>             Project: Derby
>          Issue Type: New Feature
>          Components: Performance
>    Affects Versions: 10.5.0.0
>            Reporter: Mamta A. Satoor
>            Assignee: Mamta A. Satoor
>         Attachments: DERBY3788_patch1_diff.txt, DERBY3788_patch1_stat.txt, DERBY3788_patch2_diff.txt, DERBY3788_patch2_stat.txt, DERBY3788_patch3_diff.txt, DERBY3788_patch3_stat.txt, DERBY_3788_Mgr.java, DERBY_3788_Repro.java
>
>
> DERBY-269 provided a manual way of updating the statistics using the new system stored procedure SYSCS_UTIL.SYSCS_UPDATE_STATISTICS. It will be good for Derby to provide an automatic way of updating the statistics without requiring to run the stored procedure manually. There was some discussion on DERBY-269 about providing the 0-admin way. I have copied it here for reference.
> *********************
> Kathey Marsden - 22/May/05 03:53 PM 
> Some sort of zero admin solution for updating statistics would be prefferable to the manual 'update statistics' 
> *********************
> *********************
> Mike Matrigali - 11/Jun/08 12:37 PM 
> I have not seen any other suggestions, how about the following zero admin solution? It is not perfect - suggestions welcome. 
> Along with the statistics storing, save how many rows were in the table when exact statistics were calculated. This number is 0 if none have been calculated because index creation happened on an empty table. At query compile time when we look up statistics we automatically recalculate the statistics at certain threshholds - say something like row count growing past next threshhold : 10, 100, 1000, 100000 - with upper limit being somewhere around how many rows we can process in some small amount of time - like 1 second on a modern laptop. If we are worried about response time, maybe we background queue the stat gathering rather than waiting with maybe some quick load if no stat has ever been gathered. The background gathering could be optimized to not interfere with locks by using read uncommitted. 
> I think it would be useful to also have the manual call just to make it easy to support customers and debug issues in the field. There is proably always some dynamic data distribution change that in some case won't be picked up by the automatic algorithm. Also just very useful for those who have complete control of the create ddl, load data, run stats, deliver application process. 
> *********************

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.