From 3bfc9341516bb7d8440691d7594ab1f4d7b0cfe3 Mon Sep 17 00:00:00 2001 From: prabatuty Date: Tue, 13 Oct 2009 12:35:31 +0000 Subject: [PATCH] statement caching for select statement with no parameter and with predicates. Bu t predicates should not involve date, time, timestamp data type. --- csql.conf | 6 ++++++ include/Config.h | 9 +++++++++ include/Parser.h | 7 +++++-- include/SqlConnection.h | 4 ++++ src/sql/SelStatement.cxx | 24 +++++++++++++++--------- src/sql/SqlStatement.cxx | 40 +++++++++++++++++++++++++++++++++++++++- src/sql/dmlyacc.yxx | 1 + src/storage/Config.cxx | 11 +++++++++++ 8 files changed, 90 insertions(+), 12 deletions(-) diff --git a/csql.conf b/csql.conf index 0ab34ceb..4bffd8cb 100644 --- a/csql.conf +++ b/csql.conf @@ -75,6 +75,12 @@ LOCK_TIMEOUT_SECS=0 LOCK_TIMEOUT_USECS=5000 LOCK_TIMEOUT_RETRIES=10 +#Statement Cache Size +STMT_CACHE_SIZE=10 + +#Enable statement cache for statements with no parameters +STMT_CACHE_NOPARAM=false + #####################################Cache Section######################## #Whether to enable caching of tables from target database diff --git a/include/Config.h b/include/Config.h index 11cac25f..d3ab3d86 100644 --- a/include/Config.h +++ b/include/Config.h @@ -69,6 +69,9 @@ class ConfigValues int shmKeyForId; long maxQueueLogs; int noOfProcessors; + int stmtCacheSize; + bool isCacheNoParam; + ConfigValues() { @@ -116,6 +119,9 @@ class ConfigValues shmKeyForId = -1; maxQueueLogs = 100; noOfProcessors = 1; + stmtCacheSize = 10; + isCacheNoParam = false; + } }; @@ -170,6 +176,9 @@ class Config inline int getCacheWaitSecs() { return cVal.cacheWaitSecs; } inline int getLogLevel() { return cVal.logLevel; } inline int getNoOfProcessors() { return cVal.noOfProcessors; } + inline int getStmtCacheSize() { return cVal.stmtCacheSize; } + inline bool useCacheNoParam() { return cVal.isCacheNoParam; } + }; class Conf diff --git a/include/Parser.h b/include/Parser.h index ee95ae3f..6ada73b1 100644 --- a/include/Parser.h +++ b/include/Parser.h @@ -49,7 +49,7 @@ enum StatementType struct FieldValue { char fldName[IDENTIFIER_LENGTH]; - char defValBuf[DEFAULT_VALUE_BUF_LENGTH]; + char defValBuf[DEFAULT_VALUE_BUF_LENGTH]; char *parsedString; void *value; int paramNo; // 0 ->not a param. It stores the param position @@ -227,10 +227,11 @@ class ParsedData bool dsn; int limit; int offset; + bool isWorthyToCache; public: ParsedData() { limit = 0; offset= 0; paramCounter = 0; stmtType = UnknownStatement; isDistinct = false; isExplain=false; isUnique = false; isPrimary = false; isAutoIncrement=false ;indexType = hashIndex; plan = Normal; bucketSize=0; isForeign=false; hCondFld=false; vCondFld=false;pkFld=false;forceOption=false; direct=false; uncache=false; noschema=false; dsn=false; - shouldCreateTbl=false; userNode = NULL; + shouldCreateTbl=false; userNode = NULL; isWorthyToCache=false; } void createUserNode(char *name, char *password); char *getUserName() { return userNode->userName; } @@ -335,6 +336,8 @@ class ParsedData bool getDistinct() { return isDistinct; } void setExplain() { isExplain=true; } bool getExplain() { return isExplain; } + bool getCacheWorthy() { return isWorthyToCache; } + void setCacheWorthy(bool flag) { isWorthyToCache = flag;} void setFldName(char *name); diff --git a/include/SqlConnection.h b/include/SqlConnection.h index c946eab4..40045311 100644 --- a/include/SqlConnection.h +++ b/include/SqlConnection.h @@ -28,6 +28,9 @@ struct CachedStmtNode{ SqlStatement *sqlStmt; int stmtLength; char *sqlString; + int hits; + CachedStmtNode() { sqlStmt = NULL; sqlString=NULL; stmtLength=0; hits=0;} + }; class SqlConnection : public AbsSqlConnection { @@ -95,6 +98,7 @@ class SqlConnection : public AbsSqlConnection SqlStatement* findInCache(char *stmtStr); void flushCacheStmt(); void addToCache(SqlStatement *stmt, char *stmtStr); + void removeLeastUsed(); friend class SqlFactory; }; diff --git a/src/sql/SelStatement.cxx b/src/sql/SelStatement.cxx index cfd93132..56a09a2b 100644 --- a/src/sql/SelStatement.cxx +++ b/src/sql/SelStatement.cxx @@ -402,7 +402,8 @@ DbRetVal SelStatement::resolve() newVal->isDefault = fInfo->isDefault; if (newVal->isDefault) strcpy(newVal->defValBuf, fInfo->defaultValueBuf); - else newVal->defValBuf[0] ='\0'; + else newVal->defValBuf[0]='\0'; + if (name->aType == AGG_COUNT) { newVal->type = typeInt; newVal->length = sizeof(int); @@ -466,12 +467,12 @@ DbRetVal SelStatement::resolve() if (name->aType ==AGG_UNKNOWN && parsedData->getGroupFieldNameList().size()== 0) { rv = table->bindFld(name->fldName, newVal->value); - if (OK !=rv) { - if (newVal->isAllocVal) free(newVal->value); - delete newVal; - delete fInfo; - return rv; - } + if (OK !=rv) { + if (newVal->isAllocVal) free(newVal->value); + delete newVal; + delete fInfo; + return rv; + } } else if (!isSingleTableNoGrp) { @@ -482,7 +483,7 @@ DbRetVal SelStatement::resolve() rv = aggTable->bindFld(name->fldName, name->aType, newVal->value); if (OK !=rv) { if (newVal->isAllocVal) free(newVal->value); - delete newVal; delete fInfo; delete aggTable; + delete newVal; delete fInfo; delete aggTable; aggTable = NULL; table = NULL; return rv; } @@ -554,6 +555,7 @@ DbRetVal SelStatement::resolve() table = NULL; return rv; } + if (NULL == parsedData->getCondition()) parsedData->setCacheWorthy(false); rv = resolveGroupFld(aggTable); if (rv != OK) { @@ -836,6 +838,10 @@ DbRetVal SelStatement::resolveForCondition() } if (!value->paramNo) { // for valid Integer + if (value->type == typeDate || value->type == typeTime || + value->type == typeTimeStamp) + parsedData->setCacheWorthy(false); + if((value->type == typeInt) || (value->type==typeShort) || (value->type==typeByteInt) || (value->type==typeLongLong) || (value->type==typeLong)){ int len=strlen(value->parsedString); for(int n=0;nisDefault = value->isDefault; if (fInfo->aType == AGG_UNKNOWN) { strcpy(fInfo->fldName, value->fldName); - if (fInfo->isDefault) strcpy(fInfo->defaultValueBuf, value->defValBuf); + if (fInfo->isDefault) strcpy(fInfo->defaultValueBuf, value->defValBuf); return OK; } switch(fInfo->aType) diff --git a/src/sql/SqlStatement.cxx b/src/sql/SqlStatement.cxx index 96af9b22..737e57f8 100644 --- a/src/sql/SqlStatement.cxx +++ b/src/sql/SqlStatement.cxx @@ -147,10 +147,21 @@ DbRetVal SqlStatement::prepare(char *stmtstr) //yyrestart(yyin); sysdb->releasePrepareStmtMutex(); isPrepd = true; - if (stmt->noOfParamFields()>0) { + if (Conf::config.getStmtCacheSize()) { + if (stmt->noOfParamFields() > 0) { isCachedStmt = true; sqlCon->addToCache(this, stmtstr); return OK; + } + if (Conf::config.useCacheNoParam()) + { + if (parsedData->getCacheWorthy()) + { + isCachedStmt = true; + sqlCon->addToCache(this, stmtstr); + return OK; + } + } } return OK; } @@ -621,6 +632,7 @@ SqlStatement* SqlConnection::findInCache(char *stmtstr) { logFiner(Conf::logger, "Statement Retrieved From Cache %x\n", node->sqlStmt); + node->hits++; return node->sqlStmt; } } @@ -636,10 +648,36 @@ void SqlConnection::addToCache(SqlStatement *sqlStmt, char* stmtString) node->stmtLength = strlen(stmtString); node->sqlString = (char*)malloc(node->stmtLength+1); strcpy(node->sqlString, stmtString); + if (cachedStmts.size() >= Conf::config.getStmtCacheSize()) + { + removeLeastUsed(); + } cachedStmts.append(node); logFiner(Conf::logger, "Statement added To Cache %x\n", node->sqlStmt); + logFinest(Conf::logger, "Statement added To Cache %s\n", node->sqlString); return ; } +void SqlConnection::removeLeastUsed() +{ + ListIterator iter = cachedStmts.getIterator(); + CachedStmtNode *node = NULL, *toRemove =NULL; + int lowHits = 0; + bool firstCall = true; + while((node = (CachedStmtNode*) iter.nextElement()) != NULL) + { + if (firstCall) { + firstCall = false; + lowHits = node->hits; + toRemove = node; //if cache size is 1 + continue; + } + if (lowHits >= node->hits) toRemove = node; + } + cachedStmts.remove(toRemove); + logFiner(Conf::logger, "Statement removed from Cache %x\n", toRemove->sqlStmt); + logFinest(Conf::logger, "Statement removed from Cache %s\n", toRemove->sqlString); + return; +} SqlConnection::~SqlConnection() { innerConn = NULL; diff --git a/src/sql/dmlyacc.yxx b/src/sql/dmlyacc.yxx index 1b15d471..12312873 100644 --- a/src/sql/dmlyacc.yxx +++ b/src/sql/dmlyacc.yxx @@ -147,6 +147,7 @@ other: ALLTABLE select_statement: opt_explain SELECT opt_distinct field_list FROM table_list where_clause_opt group_by_opt having_opt order_by_opt limit_opt semicolon_opt { parsedData->setStmtType(SelectStatement); + parsedData->setCacheWorthy(true); } ; opt_explain: EXPLAIN PLAN diff --git a/src/storage/Config.cxx b/src/storage/Config.cxx index b9f9dd6a..9d0d0502 100644 --- a/src/storage/Config.cxx +++ b/src/storage/Config.cxx @@ -114,6 +114,11 @@ int Config::storeKeyVal(char *key, char *value) { cVal.isTwoWay = os::atobool(value); } else if (os::strcasestr(key, "CACHE_RECEIVER_WAIT_SECS") != NULL) { cVal.cacheWaitSecs = atoi(value); } + else if (os::strcasestr(key, "STMT_CACHE_SIZE") != NULL) + { cVal.stmtCacheSize = atoi(value); } + else if (os::strcasestr(key, "STMT_CACHE_NOPARAM") != NULL) + { cVal.isCacheNoParam = os::atobool(value); } + else return 1; return 0; } @@ -292,6 +297,12 @@ int Config::validateValues() } return 1; } + if (cVal.stmtCacheSize < 0 || cVal.stmtCacheSize > 1024) + { + printError(ErrBadArg, "STMT_CACHE_SIZE should be >=0 and <1024"); + return 1; + } + return 0; } -- 2.11.4.GIT