X-Git-Url: http://sigaev.ru/git/gitweb.cgi?a=blobdiff_plain;f=online_analyze.c;h=50d8aca9af8819a4a68ce5e99c1bf16a535d3adb;hb=d2be5752026c585a7a10f15936bbf7e89cf07d19;hp=2519b5ec1230261dc6a45524b2b3bf479783bb7d;hpb=ed8cec3c9559df9ea9c70413245e4e0715271adf;p=online_analyze.git diff --git a/online_analyze.c b/online_analyze.c index 2519b5e..50d8aca 100644 --- a/online_analyze.c +++ b/online_analyze.c @@ -6,13 +6,13 @@ * modification, are permitted provided that the following conditions * are met: * 1. Redistributions of source code must retain the above copyright - * notice, this list of conditions and the following disclaimer. + * notice, this list of conditions and the following disclaimer. * 2. Redistributions in binary form must reproduce the above copyright - * notice, this list of conditions and the following disclaimer in the - * documentation and/or other materials provided with the distribution. + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. * 3. Neither the name of the author nor the names of any co-contributors - * may be used to endorse or promote products derived from this software - * without specific prior written permission. + * may be used to endorse or promote products derived from this software + * without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY CONTRIBUTORS ``AS IS'' AND ANY EXPRESS * OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED @@ -37,6 +37,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 @@ -46,6 +48,13 @@ #include "utils/rel.h" #include "utils/relcache.h" #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 #ifdef PG_MODULE_MAGIC @@ -56,6 +65,7 @@ static bool online_analyze_enable = true; 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 ExecutorEnd_hook_type oldExecutorEndHook = NULL; @@ -63,7 +73,7 @@ static ExecutorEnd_hook_type oldExecutorEndHook = NULL; static ProcessUtility_hook_type oldProcessUtilityHook = NULL; #endif -typedef enum +typedef enum { OATT_ALL = 0x03, OATT_PERSISTENT = 0x01, @@ -71,7 +81,7 @@ typedef enum OATT_NONE = 0x00 } OnlineAnalyzeTableType; -static const struct config_enum_entry online_analyze_table_type_options[] = +static const struct config_enum_entry online_analyze_table_type_options[] = { {"all", OATT_ALL, false}, {"persistent", OATT_PERSISTENT, false}, @@ -91,6 +101,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) { @@ -98,15 +123,16 @@ 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) { - char *rawname; - List *namelist; - ListCell *l; - Oid *newOids = NULL; - int nOids = 0, + char *rawname; + List *namelist; + ListCell *l; + Oid *newOids = NULL; + int nOids = 0, i = 0; rawname = pstrdup(newval); @@ -119,17 +145,20 @@ tableListAssign(const char * newval, bool doit, TableList *tbl) nOids = list_length(namelist); newOids = malloc(sizeof(Oid) * (nOids+1)); if (!newOids) - elog(ERROR,"could not allocate %d bytes", (int)(sizeof(Oid) * (nOids+1))); + elog(ERROR,"could not allocate %d bytes", + (int)(sizeof(Oid) * (nOids+1))); } foreach(l, namelist) { - char *curname = (char *) lfirst(l); - Oid relOid = RangeVarGetRelid(makeRangeVarFromNameList(stringToQualifiedNameList(curname)), + char *curname = (char *) lfirst(l); #if PG_VERSION_NUM >= 90200 - NoLock, + Oid relOid = RangeVarGetRelid(makeRangeVarFromNameList( + stringToQualifiedNameList(curname)), NoLock, true); +#else + Oid relOid = RangeVarGetRelid(makeRangeVarFromNameList( + stringToQualifiedNameList(curname)), true); #endif - true); if (relOid == InvalidOid) { @@ -221,7 +250,7 @@ includeTablesAssign(const char *newval, void *extra) tableListAssign(newval, true, &excludeTables); } -#else /* PG_VERSION_NUM < 90100 */ +#else /* PG_VERSION_NUM < 90100 */ static const char * excludeTablesAssign(const char * newval, bool doit, GucSource source) @@ -240,8 +269,8 @@ includeTablesAssign(const char * newval, bool doit, GucSource source) static const char* tableListShow(TableList *tbl) { - char *val, *ptr; - int i, + char *val, *ptr; + int i, len; len = 1 /* \0 */ + tbl->nTables * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */); @@ -249,9 +278,9 @@ tableListShow(TableList *tbl) *ptr ='\0'; for(i=0; inTables; i++) { - char *relname = get_rel_name(tbl->tables[i]); - Oid nspOid = get_rel_namespace(tbl->tables[i]); - char *nspname = get_namespace_name(nspOid); + char *relname = get_rel_name(tbl->tables[i]); + Oid nspOid = get_rel_namespace(tbl->tables[i]); + char *nspname = get_namespace_name(nspOid); if ( relname == NULL || nspOid == InvalidOid || nspname == NULL ) continue; @@ -299,87 +328,162 @@ matchOid(TableList *tbl, Oid oid) return false; } +#if PG_VERSION_NUM >= 90500 +static RangeVar* +makeRangeVarFromOid(Oid relOid) +{ + return makeRangeVar( + get_namespace_name(get_rel_namespace(relOid)), + get_rel_name(relOid), + -1 + ); + +} +#endif + static void -makeAnalyze(Oid relOid, CmdType operation, uint32 naffected) +makeAnalyze(Oid relOid, CmdType operation, int32 naffected) { - PgStat_StatTabEntry *tabentry; - TimestampTz now = GetCurrentTimestamp(); + TimestampTz now = GetCurrentTimestamp(); + Relation rel; + OnlineAnalyzeTableType reltype; + bool found = false, + newTable = false; + OnlineAnalyzeTableStat *rstat, + dummyrstat; + PgStat_StatTabEntry *tabentry = NULL; if (relOid == InvalidOid) return; - tabentry = pgstat_fetch_stat_tabentry(relOid); + if (naffected == 0) + /* return if there is no changes */ + return; + else if (naffected < 0) + /* number if affected rows is unknown */ + naffected = 0; -#if PG_VERSION_NUM >= 90000 -#define changes_since_analyze(t) ((t)->changes_since_analyze) -#else -#define changes_since_analyze(t) ((t)->n_live_tuples + (t)->n_dead_tuples - (t)->last_anl_tuples) -#endif - - 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 - ) - ) + rel = RelationIdGetRelation(relOid); + if (rel->rd_rel->relkind != RELKIND_RELATION) { - VacuumStmt vacstmt; - TimestampTz startStamp, endStamp; + RelationClose(rel); + return; + } - /* - * includeTables overwrites excludeTables - */ - switch(online_analyze_table_type) - { - case OATT_ALL: - if (matchOid(&excludeTables, relOid) == true && matchOid(&includeTables, relOid) == false) - return; - break; - case OATT_NONE: + 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 + */ + switch(online_analyze_table_type) + { + case OATT_ALL: + if (get_rel_relkind(relOid) != RELKIND_RELATION || + (matchOid(&excludeTables, relOid) == true && + matchOid(&includeTables, relOid) == false)) + return; + break; + case OATT_NONE: + if (get_rel_relkind(relOid) != RELKIND_RELATION || + matchOid(&includeTables, relOid) == false) + return; + break; + 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) + { if (matchOid(&includeTables, relOid) == false) return; - break; - case OATT_TEMPORARY: - case OATT_PERSISTENT: - default: - { - Relation rel; - OnlineAnalyzeTableType reltype; - - rel = RelationIdGetRelation(relOid); - reltype = -#if PG_VERSION_NUM >= 90100 - (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP) + } + break; + } + + /* + * 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 */ + + if (found == false || rstat->rereadStat == true || naffected == 0) + { + + if (!found) + { + MemSet(rstat, 0, sizeof(*rstat)); + rstat->tableid = relOid; + } + Assert(rstat->tableid == 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 + tabentry->changes_since_analyze; #else - (rel->rd_istemp || rel->rd_islocaltemp) -#endif - ? OATT_TEMPORARY : OATT_PERSISTENT; - RelationClose(rel); - - /* - * 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; + 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; + rstat->rereadStat = false; } + else + { + newTable = true; + rstat->rereadStat = true; + } + } + + if (newTable || ( + /* 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) && + /* 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; +#else + VacuumParams vacstmt; +#endif + TimestampTz startStamp, endStamp; + + + memset(&startStamp, 0, sizeof(startStamp)); /* keep compiler quiet */ + + memset(&vacstmt, 0, sizeof(vacstmt)); - vacstmt.type = T_VacuumStmt; vacstmt.freeze_min_age = -1; vacstmt.freeze_table_age = -1; /* ??? */ + +#if PG_VERSION_NUM < 90500 + vacstmt.type = T_VacuumStmt; vacstmt.relation = NULL; vacstmt.va_cols = NIL; - #if PG_VERSION_NUM >= 90000 vacstmt.options = VACOPT_ANALYZE; if (online_analyze_verbose) @@ -389,13 +493,29 @@ makeAnalyze(Oid relOid, CmdType operation, uint32 naffected) vacstmt.analyze = true; vacstmt.verbose = online_analyze_verbose; #endif +#else + vacstmt.multixact_freeze_min_age = -1; + vacstmt.multixact_freeze_table_age = -1; + vacstmt.log_min_duration = -1; +#endif if (online_analyze_verbose) startStamp = GetCurrentTimestamp(); - analyze_rel(relOid, &vacstmt, GetAccessStrategy(BAS_VACUUM) -#if (PG_VERSION_NUM < 90004) && (PG_VERSION_NUM >= 90000) + analyze_rel(relOid, +#if PG_VERSION_NUM < 90500 + &vacstmt +#if PG_VERSION_NUM >= 90018 + , true +#endif + , GetAccessStrategy(BAS_VACUUM) +#if (PG_VERSION_NUM >= 90000) && (PG_VERSION_NUM < 90004) , true +#endif +#else + makeRangeVarFromOid(relOid), + VACOPT_ANALYZE | ((online_analyze_verbose) ? VACOPT_VERBOSE : 0), + &vacstmt, NULL, true, GetAccessStrategy(BAS_VACUUM) #endif ); @@ -406,45 +526,57 @@ makeAnalyze(Oid relOid, CmdType operation, uint32 naffected) endStamp = GetCurrentTimestamp(); TimestampDifference(startStamp, endStamp, &secs, µsecs); - elog(INFO, "analyze \"%s\" took %.02f seconds", - get_rel_name(relOid), ((double)secs) + ((double)microsecs)/1.0e6); + elog(INFO, "analyze \"%s\" took %.02f seconds", + get_rel_name(relOid), + ((double)secs) + ((double)microsecs)/1.0e6); } + rstat->autovac_analyze_timestamp = now; + rstat->changes_since_analyze = 0; + rstat->rereadStat = true; - if (tabentry == NULL) + /* update last analyze timestamp in local memory of backend */ + if (tabentry) { - /* new table */ - pgstat_clear_snapshot(); - } - else - { - /* 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 + rstat->changes_since_analyze += naffected; + } + + /* Reset local cache if we are over limit */ + if (hash_get_num_entries(relstats) > online_analyze_capacity_threshold) + relstatsInit(); } extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc); void -onlineAnalyzeHooker(QueryDesc *queryDesc) +onlineAnalyzeHooker(QueryDesc *queryDesc) { - uint32 naffected = 0; + uint32 naffected = -1; if (queryDesc->estate) - naffected = queryDesc->estate->es_processed; + naffected = queryDesc->estate->es_processed; if (online_analyze_enable && queryDesc->plannedstmt && - (queryDesc->operation == CMD_INSERT || + (queryDesc->operation == CMD_INSERT || queryDesc->operation == CMD_UPDATE || queryDesc->operation == CMD_DELETE #if PG_VERSION_NUM < 90200 - || (queryDesc->operation == CMD_SELECT && queryDesc->plannedstmt->intoClause) + || (queryDesc->operation == CMD_SELECT && + queryDesc->plannedstmt->intoClause) #endif )) { @@ -455,7 +587,7 @@ onlineAnalyzeHooker(QueryDesc *queryDesc) makeAnalyze(relOid, queryDesc->operation, naffected); } - else + else #endif if (queryDesc->plannedstmt->resultRelations && queryDesc->plannedstmt->rtable) @@ -464,9 +596,9 @@ onlineAnalyzeHooker(QueryDesc *queryDesc) foreach(l, queryDesc->plannedstmt->resultRelations) { - int n = lfirst_int(l); + int n = lfirst_int(l); RangeTblEntry *rte = list_nth(queryDesc->plannedstmt->rtable, n-1); - + if (rte->rtekind == RTE_RELATION) makeAnalyze(rte->relid, queryDesc->operation, naffected); } @@ -481,31 +613,123 @@ onlineAnalyzeHooker(QueryDesc *queryDesc) #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; +#if PG_VERSION_NUM >= 100000 + Node *parsetree = NULL; + + if (pstmt->commandType == CMD_UTILITY) + parsetree = pstmt->utilityStmt; +#endif - if (IsA(parsetree, CreateTableAsStmt) && ((CreateTableAsStmt*)parsetree)->into) + if (parsetree && IsA(parsetree, CreateTableAsStmt) && + ((CreateTableAsStmt*)parsetree)->into && + online_analyze_enable) tblname = (RangeVar*)copyObject(((CreateTableAsStmt*)parsetree)->into->rel); +#if PG_VERSION_NUM >= 100000 +#define parsetree pstmt +#endif + if (oldProcessUtilityHook) - oldProcessUtilityHook(parsetree, queryString, params, isTopLevel, dest, completionTag); + oldProcessUtilityHook(parsetree, queryString, +#if PG_VERSION_NUM >= 90300 + context, params, +#if PG_VERSION_NUM >= 100000 + queryEnv, +#endif +#else + params, isTopLevel, +#endif + dest, completionTag); else - standard_ProcessUtility(parsetree, queryString, params, isTopLevel, dest, completionTag); + 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 PG_VERSION_NUM >= 100000 +#undef parsetree +#endif if (tblname) { Oid tblOid = RangeVarGetRelid(tblname, NoLock, true); - makeAnalyze(tblOid, CMD_INSERT, 0); + makeAnalyze(tblOid, CMD_INSERT, -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; @@ -555,7 +779,7 @@ _PG_init(void) NULL ); - DefineCustomRealVariable( + DefineCustomRealVariable( "online_analyze.scale_factor", "fraction of table size to start on-line analyze", "fraction of table size to start on-line analyze", @@ -576,7 +800,7 @@ _PG_init(void) NULL ); - DefineCustomIntVariable( + DefineCustomIntVariable( "online_analyze.threshold", "min number of row updates before on-line analyze", "min number of row updates before on-line analyze", @@ -597,11 +821,32 @@ _PG_init(void) NULL ); - DefineCustomRealVariable( + 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)", "minimum time interval between analyze call (in milliseconds)", - &online_analyze_scale_factor, + &online_analyze_min_interval, #if PG_VERSION_NUM >= 80400 online_analyze_min_interval, #endif @@ -629,7 +874,7 @@ _PG_init(void) online_analyze_table_type_options, PGC_USERSET, #if PG_VERSION_NUM >= 80400 - GUC_NOT_IN_SAMPLE, + GUC_NOT_IN_SAMPLE, #if PG_VERSION_NUM >= 90100 NULL, #endif @@ -638,7 +883,7 @@ _PG_init(void) NULL ); - DefineCustomStringVariable( + DefineCustomStringVariable( "online_analyze.exclude_tables", "List of tables which will not online analyze", NULL, @@ -657,7 +902,7 @@ _PG_init(void) excludeTablesShow ); - DefineCustomStringVariable( + DefineCustomStringVariable( "online_analyze.include_tables", "List of tables which will online analyze", NULL,