X-Git-Url: http://sigaev.ru/git/gitweb.cgi?a=blobdiff_plain;f=online_analyze.c;h=53ffd201f8adc46b8f7d64214606f816cb37abe5;hb=31e2328fdeefe0e5710680e704a3c78a9cadffa4;hp=0d85df6583a8c70fb35547fbb026b28b26a401db;hpb=f0071352047aa4cc7e3822101f9d30a67e12c8e3;p=online_analyze.git diff --git a/online_analyze.c b/online_analyze.c index 0d85df6..53ffd20 100644 --- a/online_analyze.c +++ b/online_analyze.c @@ -30,6 +30,8 @@ #include "postgres.h" #include "pgstat.h" +#include "access/transam.h" +#include "access/xact.h" #include "catalog/namespace.h" #include "commands/vacuum.h" #include "executor/executor.h" @@ -37,6 +39,8 @@ #include "nodes/parsenodes.h" #include "storage/bufmgr.h" #include "utils/builtins.h" +#include "utils/hsearch.h" +#include "utils/memutils.h" #include "utils/lsyscache.h" #include "utils/guc.h" #if PG_VERSION_NUM >= 90200 @@ -48,6 +52,10 @@ #include "utils/timestamp.h" #if PG_VERSION_NUM >= 90500 #include "nodes/makefuncs.h" +#if PG_VERSION_NUM >= 100000 +#include "utils/varlena.h" +#include "utils/regproc.h" +#endif #endif #endif @@ -56,16 +64,33 @@ PG_MODULE_MAGIC; #endif static bool online_analyze_enable = true; +static bool online_analyze_local_tracking = false; static bool online_analyze_verbose = true; static double online_analyze_scale_factor = 0.1; static int online_analyze_threshold = 50; +static int online_analyze_capacity_threshold = 100000; static double online_analyze_min_interval = 10000; +static int online_analyze_lower_limit = 0; static ExecutorEnd_hook_type oldExecutorEndHook = NULL; #if PG_VERSION_NUM >= 90200 static ProcessUtility_hook_type oldProcessUtilityHook = NULL; #endif +typedef enum CmdKind +{ + CK_SELECT = CMD_SELECT, + CK_UPDATE = CMD_UPDATE, + CK_INSERT = CMD_INSERT, + CK_DELETE = CMD_DELETE, + CK_TRUNCATE, + CK_FASTTRUNCATE, + CK_CREATE, + CK_ANALYZE, + CK_VACUUM +} CmdKind; + + typedef enum { OATT_ALL = 0x03, @@ -94,6 +119,21 @@ typedef struct TableList { static TableList excludeTables = {0, NULL, NULL}; static TableList includeTables = {0, NULL, NULL}; +typedef struct OnlineAnalyzeTableStat { + Oid tableid; + bool rereadStat; + PgStat_Counter n_tuples; + PgStat_Counter changes_since_analyze; + TimestampTz autovac_analyze_timestamp; + TimestampTz analyze_timestamp; +} OnlineAnalyzeTableStat; + +static MemoryContext onlineAnalyzeMemoryContext = NULL; +static HTAB *relstats = NULL; + +static void relstatsInit(void); + +#if PG_VERSION_NUM < 100000 static int oid_cmp(const void *a, const void *b) { @@ -101,6 +141,7 @@ oid_cmp(const void *a, const void *b) return 0; return (*(Oid*)a > *(Oid*)b) ? 1 : -1; } +#endif static const char * tableListAssign(const char * newval, bool doit, TableList *tbl) @@ -319,21 +360,44 @@ makeRangeVarFromOid(Oid relOid) #endif static void -makeAnalyze(Oid relOid, CmdType operation, int32 naffected) +makeAnalyze(Oid relOid, CmdKind operation, int64 naffected) { - PgStat_StatTabEntry *tabentry; TimestampTz now = GetCurrentTimestamp(); + Relation rel; + OnlineAnalyzeTableType reltype; + bool found = false, + newTable = false; + OnlineAnalyzeTableStat *rstat, + dummyrstat; + PgStat_StatTabEntry *tabentry = NULL; if (relOid == InvalidOid) return; if (naffected == 0) - /* return if there is not changes */ + /* return if there is no changes */ return; else if (naffected < 0) /* number if affected rows is unknown */ naffected = 0; + rel = RelationIdGetRelation(relOid); + if (rel->rd_rel->relkind != RELKIND_RELATION) + { + RelationClose(rel); + return; + } + + reltype = +#if PG_VERSION_NUM >= 90100 + (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP) +#else + (rel->rd_istemp || rel->rd_islocaltemp) +#endif + ? OATT_TEMPORARY : OATT_PERSISTENT; + + RelationClose(rel); + /* * includeTables overwrites excludeTables */ @@ -353,61 +417,92 @@ makeAnalyze(Oid relOid, CmdType operation, int32 naffected) case OATT_TEMPORARY: case OATT_PERSISTENT: default: + /* + * skip analyze if relation's type doesn't not match + * online_analyze_table_type + */ + if ((online_analyze_table_type & reltype) == 0 || + matchOid(&excludeTables, relOid) == true) { - Relation rel; - OnlineAnalyzeTableType reltype; - - rel = RelationIdGetRelation(relOid); - - if (rel->rd_rel->relkind != RELKIND_RELATION) - { - RelationClose(rel); + if (matchOid(&includeTables, relOid) == false) return; - } + } + break; + } - reltype = -#if PG_VERSION_NUM >= 90100 - (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP) -#else - (rel->rd_istemp || rel->rd_islocaltemp) -#endif - ? OATT_TEMPORARY : OATT_PERSISTENT; - RelationClose(rel); + /* + * Do not store data about persistent table in local memory because we + * could not track changes of them: they could be changed by another + * backends. So always get a pgstat table entry. + */ + if (reltype == OATT_TEMPORARY) + rstat = hash_search(relstats, &relOid, HASH_ENTER, &found); + else + rstat = &dummyrstat; /* found == false for following if */ - /* - * skip analyze if relation's type doesn't not match - * online_analyze_table_type - */ - if ((online_analyze_table_type & reltype) == 0 || - matchOid(&excludeTables, relOid) == true) - { - if (matchOid(&includeTables, relOid) == false) - return; - } - break; - } + if (!found) + { + MemSet(rstat, 0, sizeof(*rstat)); + rstat->tableid = relOid; + newTable = true; + } + else if (operation == CK_VACUUM) + { + /* force reread becouse vacuum could change n_tuples */ + rstat->rereadStat = true; + return; } + else if (operation == CK_ANALYZE) + { + /* only analyze */ + rstat->changes_since_analyze = 0; + rstat->analyze_timestamp = now; + return; + } + + Assert(rstat->tableid == relOid); + + if ( + /* do not reread data if it was a truncation */ + operation != CK_TRUNCATE && operation != CK_FASTTRUNCATE && + /* read for persistent table and for temp teble if it allowed */ + (reltype == OATT_PERSISTENT || online_analyze_local_tracking == false) && + /* read only for new table or we know that it's needed */ + (newTable == true || rstat->rereadStat == true) + ) + { + rstat->rereadStat = false; - tabentry = pgstat_fetch_stat_tabentry(relOid); + tabentry = pgstat_fetch_stat_tabentry(relOid); + if (tabentry) + { + rstat->n_tuples = tabentry->n_dead_tuples + tabentry->n_live_tuples; + rstat->changes_since_analyze = #if PG_VERSION_NUM >= 90000 -#define changes_since_analyze(t) ((t)->changes_since_analyze) + tabentry->changes_since_analyze; #else -#define changes_since_analyze(t) ((t)->n_live_tuples + (t)->n_dead_tuples - (t)->last_anl_tuples) + tabentry->n_live_tuples + tabentry->n_dead_tuples - + tabentry->last_anl_tuples; #endif + rstat->autovac_analyze_timestamp = + tabentry->autovac_analyze_timestamp; + rstat->analyze_timestamp = tabentry->analyze_timestamp; + } + } - if ( - tabentry == NULL /* a new table */ || - ( - /* do not analyze too often, if both stamps are exceeded the go */ - TimestampDifferenceExceeds(tabentry->analyze_timestamp, now, online_analyze_min_interval) && - TimestampDifferenceExceeds(tabentry->autovac_analyze_timestamp, now, online_analyze_min_interval) && - /* be in sync with relation_needs_vacanalyze */ - ((double)(changes_since_analyze(tabentry) + naffected)) >= - online_analyze_scale_factor * ((double)(tabentry->n_dead_tuples + tabentry->n_live_tuples)) + - (double)online_analyze_threshold - ) - ) + if (newTable || + /* force analyze after truncate, fasttruncate already did analyze */ + operation == CK_TRUNCATE || ( + /* do not analyze too often, if both stamps are exceeded the go */ + TimestampDifferenceExceeds(rstat->analyze_timestamp, now, online_analyze_min_interval) && + TimestampDifferenceExceeds(rstat->autovac_analyze_timestamp, now, online_analyze_min_interval) && + /* do not analyze too small tables */ + rstat->n_tuples + rstat->changes_since_analyze + naffected > online_analyze_lower_limit && + /* be in sync with relation_needs_vacanalyze */ + ((double)(rstat->changes_since_analyze + naffected)) >= + online_analyze_scale_factor * ((double)rstat->n_tuples) + + (double)online_analyze_threshold)) { #if PG_VERSION_NUM < 90500 VacuumStmt vacstmt; @@ -416,6 +511,7 @@ makeAnalyze(Oid relOid, CmdType operation, int32 naffected) #endif TimestampTz startStamp, endStamp; + memset(&startStamp, 0, sizeof(startStamp)); /* keep compiler quiet */ memset(&vacstmt, 0, sizeof(vacstmt)); @@ -474,34 +570,145 @@ makeAnalyze(Oid relOid, CmdType operation, int32 naffected) ((double)secs) + ((double)microsecs)/1.0e6); } - if (tabentry == NULL) + rstat->autovac_analyze_timestamp = now; + rstat->changes_since_analyze = 0; + + switch(operation) { - /* new table */ - pgstat_clear_snapshot(); + case CK_CREATE: + case CK_INSERT: + case CK_UPDATE: + rstat->n_tuples += naffected; + case CK_DELETE: + rstat->rereadStat = (reltype == OATT_PERSISTENT); + break; + case CK_TRUNCATE: + case CK_FASTTRUNCATE: + rstat->rereadStat = false; + rstat->n_tuples = 0; + break; + default: + break; } - else + + /* update last analyze timestamp in local memory of backend */ + if (tabentry) { - /* update last analyze timestamp in local memory of backend */ tabentry->analyze_timestamp = now; + tabentry->changes_since_analyze = 0; } +#if 0 + /* force reload stat for new table */ + if (newTable) + pgstat_clear_snapshot(); +#endif } -#if PG_VERSION_NUM >= 90000 - else if (tabentry != NULL) + else { - tabentry->changes_since_analyze += naffected; - } +#if PG_VERSION_NUM >= 90000 + if (tabentry) + tabentry->changes_since_analyze += naffected; #endif + switch(operation) + { + case CK_CREATE: + case CK_INSERT: + rstat->changes_since_analyze += naffected; + rstat->n_tuples += naffected; + break; + case CK_UPDATE: + rstat->changes_since_analyze += 2 * naffected; + rstat->n_tuples += naffected; + case CK_DELETE: + rstat->changes_since_analyze += naffected; + break; + case CK_TRUNCATE: + case CK_FASTTRUNCATE: + rstat->changes_since_analyze = 0; + rstat->n_tuples = 0; + break; + default: + break; + } + } + + /* Reset local cache if we are over limit */ + if (hash_get_num_entries(relstats) > online_analyze_capacity_threshold) + relstatsInit(); +} + +static Const* +isFastTruncateCall(QueryDesc *queryDesc) +{ + TargetEntry *te; + FuncExpr *fe; + Const *constval; + + if (!( + queryDesc->plannedstmt && + queryDesc->operation == CMD_SELECT && + queryDesc->plannedstmt->planTree && + queryDesc->plannedstmt->planTree->targetlist && + list_length(queryDesc->plannedstmt->planTree->targetlist) == 1 + )) + return NULL; + + te = linitial(queryDesc->plannedstmt->planTree->targetlist); + + if (!IsA(te, TargetEntry)) + return NULL; + + fe = (FuncExpr*)te->expr; + + if (!( + fe && IsA(fe, FuncExpr) && + fe->funcid >= FirstNormalObjectId && + fe->funcretset == false && + fe->funcresulttype == VOIDOID && + fe->funcvariadic == false && + list_length(fe->args) == 1 + )) + return NULL; + + constval = linitial(fe->args); + + if (!( + IsA(constval,Const) && + constval->consttype == TEXTOID && + strcmp(get_func_name(fe->funcid), "fasttruncate") == 0 + )) + return NULL; + + return constval; } + extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc); void onlineAnalyzeHooker(QueryDesc *queryDesc) { - uint32 naffected = -1; + int64 naffected = -1; + Const *constval; if (queryDesc->estate) naffected = queryDesc->estate->es_processed; +#if PG_VERSION_NUM >= 90200 + if (online_analyze_enable && + (constval = isFastTruncateCall(queryDesc)) != NULL) + { + Datum tblnamed = constval->constvalue; + char *tblname = text_to_cstring(DatumGetTextP(tblnamed)); + RangeVar *tblvar = + makeRangeVarFromNameList(stringToQualifiedNameList(tblname)); + + makeAnalyze(RangeVarGetRelid(tblvar, + NoLock, + false), + CK_FASTTRUNCATE, -1); + } +#endif + if (online_analyze_enable && queryDesc->plannedstmt && (queryDesc->operation == CMD_INSERT || queryDesc->operation == CMD_UPDATE || @@ -532,7 +739,7 @@ onlineAnalyzeHooker(QueryDesc *queryDesc) RangeTblEntry *rte = list_nth(queryDesc->plannedstmt->rtable, n-1); if (rte->rtekind == RTE_RELATION) - makeAnalyze(rte->relid, queryDesc->operation, naffected); + makeAnalyze(rte->relid, (CmdKind)queryDesc->operation, naffected); } } } @@ -543,24 +750,126 @@ onlineAnalyzeHooker(QueryDesc *queryDesc) standard_ExecutorEnd(queryDesc); } +static List *toremove = NIL; + +/* + * removeTable called on transaction end, see call RegisterXactCallback() below + */ +static void +removeTable(XactEvent event, void *arg) +{ + ListCell *cell; + + switch(event) + { + case XACT_EVENT_COMMIT: + break; + case XACT_EVENT_ABORT: + toremove = NIL; + default: + return; + } + + foreach(cell, toremove) + { + Oid relOid = lfirst_oid(cell); + + hash_search(relstats, &relOid, HASH_REMOVE, NULL); + } + + toremove = NIL; +} + + #if PG_VERSION_NUM >= 90200 static void -onlineAnalyzeHookerUtility(Node *parsetree, const char *queryString, +onlineAnalyzeHookerUtility( +#if PG_VERSION_NUM >= 100000 + PlannedStmt *pstmt, +#else + Node *parsetree, +#endif + const char *queryString, #if PG_VERSION_NUM >= 90300 ProcessUtilityContext context, ParamListInfo params, +#if PG_VERSION_NUM >= 100000 + QueryEnvironment *queryEnv, +#endif #else ParamListInfo params, bool isTopLevel, #endif DestReceiver *dest, char *completionTag) { - RangeVar *tblname = NULL; + List *tblnames = NIL; + CmdKind op = CK_INSERT; +#if PG_VERSION_NUM >= 100000 + Node *parsetree = NULL; + + if (pstmt->commandType == CMD_UTILITY) + parsetree = pstmt->utilityStmt; +#endif + + if (parsetree && online_analyze_enable) + { + if (IsA(parsetree, CreateTableAsStmt) && + ((CreateTableAsStmt*)parsetree)->into) + { + tblnames = + list_make1((RangeVar*)copyObject(((CreateTableAsStmt*)parsetree)->into->rel)); + op = CK_CREATE; + } + else if (IsA(parsetree, TruncateStmt)) + { + tblnames = list_copy(((TruncateStmt*)parsetree)->relations); + op = CK_TRUNCATE; + } + else if (IsA(parsetree, DropStmt) && + ((DropStmt*)parsetree)->removeType == OBJECT_TABLE) + { + ListCell *cell; + + foreach(cell, ((DropStmt*)parsetree)->objects) + { + List *relname = (List *) lfirst(cell); + RangeVar *rel = makeRangeVarFromNameList(relname); + Oid relOid = RangeVarGetRelid(rel, NoLock, true); - if (IsA(parsetree, CreateTableAsStmt) && ((CreateTableAsStmt*)parsetree)->into) - tblname = (RangeVar*)copyObject(((CreateTableAsStmt*)parsetree)->into->rel); + if (OidIsValid(relOid)) + { + MemoryContext ctx; + + ctx = MemoryContextSwitchTo(TopTransactionContext); + toremove = lappend_oid(toremove, relOid); + MemoryContextSwitchTo(ctx); + } + } + } + else if (IsA(parsetree, VacuumStmt)) + { + VacuumStmt *vac = (VacuumStmt*)parsetree; + + tblnames = list_make1(vac->relation); + + if (vac->options & (VACOPT_VACUUM | VACOPT_FULL | VACOPT_FREEZE)) + /* optionally with analyze */ + op = CK_VACUUM; + else if (vac->options & VACOPT_ANALYZE) + op = CK_ANALYZE; + else + tblnames = NIL; + } + } + +#if PG_VERSION_NUM >= 100000 +#define parsetree pstmt +#endif if (oldProcessUtilityHook) oldProcessUtilityHook(parsetree, queryString, #if PG_VERSION_NUM >= 90300 context, params, +#if PG_VERSION_NUM >= 100000 + queryEnv, +#endif #else params, isTopLevel, #endif @@ -569,23 +878,81 @@ onlineAnalyzeHookerUtility(Node *parsetree, const char *queryString, standard_ProcessUtility(parsetree, queryString, #if PG_VERSION_NUM >= 90300 context, params, +#if PG_VERSION_NUM >= 100000 + queryEnv, +#endif #else params, isTopLevel, #endif dest, completionTag); - if (tblname) { - Oid tblOid = RangeVarGetRelid(tblname, NoLock, true); +#if PG_VERSION_NUM >= 100000 +#undef parsetree +#endif + + if (tblnames) { + ListCell *l; + + foreach(l, tblnames) + { + RangeVar *tblname = (RangeVar*)lfirst(l); + Oid tblOid = RangeVarGetRelid(tblname, NoLock, true); - makeAnalyze(tblOid, CMD_INSERT, -1); + makeAnalyze(tblOid, op, -1); + } } } #endif +static void +relstatsInit(void) +{ + HASHCTL hash_ctl; + int flags = 0; + + MemSet(&hash_ctl, 0, sizeof(hash_ctl)); + + hash_ctl.hash = oid_hash; + flags |= HASH_FUNCTION; + + if (onlineAnalyzeMemoryContext) + { + Assert(relstats != NULL); + MemoryContextReset(onlineAnalyzeMemoryContext); + } + else + { + Assert(relstats == NULL); + onlineAnalyzeMemoryContext = + AllocSetContextCreate(CacheMemoryContext, + "online_analyze storage context", +#if PG_VERSION_NUM < 90600 + ALLOCSET_DEFAULT_MINSIZE, + ALLOCSET_DEFAULT_INITSIZE, + ALLOCSET_DEFAULT_MAXSIZE +#else + ALLOCSET_DEFAULT_SIZES +#endif + ); + } + + hash_ctl.hcxt = onlineAnalyzeMemoryContext; + flags |= HASH_CONTEXT; + + hash_ctl.keysize = sizeof(Oid); + + hash_ctl.entrysize = sizeof(OnlineAnalyzeTableStat); + flags |= HASH_ELEM; + + relstats = hash_create("online_analyze storage", 1024, &hash_ctl, flags); +} + void _PG_init(void); void _PG_init(void) { + relstatsInit(); + oldExecutorEndHook = ExecutorEnd_hook; ExecutorEnd_hook = onlineAnalyzeHooker; @@ -616,6 +983,25 @@ _PG_init(void) NULL ); + DefineCustomBoolVariable( + "online_analyze.local_tracking", + "Per backend tracking", + "Per backend tracking for temp tables (do not use system statistic)", + &online_analyze_local_tracking, +#if PG_VERSION_NUM >= 80400 + online_analyze_local_tracking, +#endif + PGC_USERSET, +#if PG_VERSION_NUM >= 80400 + GUC_NOT_IN_SAMPLE, +#if PG_VERSION_NUM >= 90100 + NULL, +#endif +#endif + NULL, + NULL + ); + DefineCustomBoolVariable( "online_analyze.verbose", "Verbosity of on-line analyze", @@ -677,6 +1063,27 @@ _PG_init(void) NULL ); + DefineCustomIntVariable( + "online_analyze.capacity_threshold", + "Max local cache table capacity", + "Max local cache table capacity", + &online_analyze_capacity_threshold, +#if PG_VERSION_NUM >= 80400 + online_analyze_capacity_threshold, +#endif + 0, + 0x7fffffff, + PGC_USERSET, +#if PG_VERSION_NUM >= 80400 + GUC_NOT_IN_SAMPLE, +#if PG_VERSION_NUM >= 90100 + NULL, +#endif +#endif + NULL, + NULL + ); + DefineCustomRealVariable( "online_analyze.min_interval", "minimum time interval between analyze call (in milliseconds)", @@ -755,6 +1162,29 @@ _PG_init(void) #endif includeTablesShow ); + + DefineCustomIntVariable( + "online_analyze.lower_limit", + "min number of rows in table to analyze", + "min number of rows in table to analyze", + &online_analyze_lower_limit, +#if PG_VERSION_NUM >= 80400 + online_analyze_lower_limit, +#endif + 0, + 0x7fffffff, + PGC_USERSET, +#if PG_VERSION_NUM >= 80400 + GUC_NOT_IN_SAMPLE, +#if PG_VERSION_NUM >= 90100 + NULL, +#endif +#endif + NULL, + NULL + ); + + RegisterXactCallback(removeTable, NULL); } void _PG_fini(void);