[
https://issues.apache.org/jira/browse/DERBY-3788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12629192#action_12629192
]
Mamta A. Satoor commented on DERBY-3788:
----------------------------------------
I now do have the basic framework of using the ThreadPoolExecutor in new
extended class DataDictionary5(DD5). I have called the method in DD5 as
updateStatisticsInBackGround rather than scheduleUpdateStatisticstask. This
method in DD5 right now just queues println tasks everytime an update
statistics request is sent its ways. This is what the method code in DD5 looks
like right now
public void updateStatisticsInBackGround(String schemaName,
String tableName, String indexName) {
System.out.println("came to jdk 1.5 implementation");
if (executorForUpdateStatistics==null)
{
executorForUpdateStatistics = new
ThreadPoolExecutor(5,5,0L,
TimeUnit.MILLISECONDS,
new LinkedBlockingQueue(5));
executorForUpdateStatistics.setRejectedExecutionHandler(
new
ThreadPoolExecutor.CallerRunsPolicy());
}
executorForUpdateStatistics.execute(new
BackgroundUpdateStatisticTask (schemaName,
tableName, indexName));
return;
}
The new class BackgroundUpdateStatisticTask right now just does println as
shown below
class BackgroundUpdateStatisticTask implements Runnable{
private String schemaName;
private String tableName;
private String indexName;
public BackgroundUpdateStatisticTask(String schemaName,
String tableName, String indexName) {
this.schemaName=schemaName;
this.tableName=tableName;
this.indexName=indexName;
}
public void run()
{
System.out.println("Hello World : Updating statistics for "
+ schemaName + ":" + tableName + ":" +
indexName);
}
}
As the next step, I now want to actually do update statistics in
BackgroundUpdateStatisticTask rather than just println. I thought I would be
able to do something like following in the run method in
BackgroundUpdateStatisticTask
public void run()
{
System.out.println("Hello World : Updating statistics for "
+ schemaName + ":" + tableName + ":" +
indexName);
try {
SystemProcedures.SYSCS_UPDATE_STATISTICS(schemaName,
tableName,
indexName);
} catch (SQLException e)
{
System.out.println("got exception");
e.printStackTrace();
}
}
But a call to SystemProcedures.SYSCS_UPDATE_STATISTICS inside of the run method
results in
java.sql.SQLException: No current connection.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:103)
at org.apache.derby.impl.jdbc.Util.noCurrentConnection(Util.java:207)
at
org.apache.derby.catalog.SystemProcedures.getDefaultConn(SystemProcedures.java:185)
at
org.apache.derby.catalog.SystemProcedures.SYSCS_UPDATE_STATISTICS(SystemProcedures.java:737)
at
org.apache.derby.impl.sql.catalog.BackgroundUpdateStatisticTask.run(DataDictionaryImpl5.java:90)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)
So, it appears that I do not have the context setting done correctly in order
to run the stored procedure implementation in
SystemProcedures.SYSCS_UPDATE_STATISTICS. I am currently trying to see how we
do the required setup when a JDBC connection is made but at this point, I am
not clear on how might be able to use some of that code from JDBC connection
time into BackgroundUpdateStatisticTask.run method. I will appreciate any help
I can get to do the context setting so I can run the update statistics. 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: 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.
