do not sore permanent table in local cache and add limit for table cache
authorTeodor Sigaev <teodor@sigaev.ru>
Thu, 10 Nov 2016 16:36:48 +0000 (19:36 +0300)
committerTeodor Sigaev <teodor@sigaev.ru>
Thu, 10 Nov 2016 16:36:48 +0000 (19:36 +0300)
README.online_analyze
online_analyze.c

index bb4491c..b181da5 100644 (file)
@@ -34,4 +34,7 @@ online_analyze.include_tables = ""
        List of tables which will online analyze
        online_analyze.include_tables overwrites online_analyze.exclude_tables.
 
        List of tables which will online analyze
        online_analyze.include_tables overwrites online_analyze.exclude_tables.
 
+online_analyze.capacity_threshold = 100000
+       Maximum number of temporary tables to store in local cache
+
 Author: Teodor Sigaev <teodor@sigaev.ru>
 Author: Teodor Sigaev <teodor@sigaev.ru>
index d16b233..93276ab 100644 (file)
@@ -61,6 +61,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 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;
 static double online_analyze_min_interval = 10000;
 
 static ExecutorEnd_hook_type oldExecutorEndHook = NULL;
@@ -105,8 +106,11 @@ typedef struct OnlineAnalyzeTableStat {
        TimestampTz             analyze_timestamp;
 } OnlineAnalyzeTableStat;
 
        TimestampTz             analyze_timestamp;
 } OnlineAnalyzeTableStat;
 
+static MemoryContext   onlineAnalyzeMemoryContext = NULL;
 static HTAB    *relstats = NULL;
 
 static HTAB    *relstats = NULL;
 
+static void relstatsInit();
+
 static int
 oid_cmp(const void *a, const void *b)
 {
 static int
 oid_cmp(const void *a, const void *b)
 {
@@ -337,16 +341,17 @@ makeAnalyze(Oid relOid, CmdType operation, int32 naffected)
        TimestampTz                             now = GetCurrentTimestamp();
        Relation                                rel;
        OnlineAnalyzeTableType  reltype;
        TimestampTz                             now = GetCurrentTimestamp();
        Relation                                rel;
        OnlineAnalyzeTableType  reltype;
-       bool                                    found,
+       bool                                    found = false,
                                                        newTable = false;
                                                        newTable = false;
-       OnlineAnalyzeTableStat  *rstat;
+       OnlineAnalyzeTableStat  *rstat,
+                                                       dummyrstat;
        PgStat_StatTabEntry             *tabentry = NULL;
 
        if (relOid == InvalidOid)
                return;
 
        if (naffected == 0)
        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 */
                return;
        else if (naffected < 0)
                /* number if affected rows is unknown */
@@ -401,7 +406,15 @@ makeAnalyze(Oid relOid, CmdType operation, int32 naffected)
                        break;
        }
 
                        break;
        }
 
-       rstat = hash_search(relstats, &relOid, HASH_ENTER, &found);
+       /*
+        * 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 == false || rstat->rereadStat == true || naffected == 0)
        {
@@ -518,8 +531,10 @@ makeAnalyze(Oid relOid, CmdType operation, int32 naffected)
 
                /* update last analyze timestamp in local memory of backend */
                if (tabentry)
 
                /* update last analyze timestamp in local memory of backend */
                if (tabentry)
+               {
                        tabentry->analyze_timestamp = now;
                        tabentry->analyze_timestamp = now;
-
+                       tabentry->changes_since_analyze = 0;
+               }
 #if 0
                /* force reload stat for new table */
                if (newTable)
 #if 0
                /* force reload stat for new table */
                if (newTable)
@@ -529,11 +544,15 @@ makeAnalyze(Oid relOid, CmdType operation, int32 naffected)
        else
        {
 #if PG_VERSION_NUM >= 90000
        else
        {
 #if PG_VERSION_NUM >= 90000
-               if (tabentry != NULL)
+               if (tabentry)
                        tabentry->changes_since_analyze += naffected;
 #endif
                rstat->changes_since_analyze += naffected;
        }
                        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);
 }
 
 extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc);
@@ -636,6 +655,20 @@ relstatsInit()
        hash_ctl.hash = oid_hash;
        flags |= HASH_FUNCTION;
 
        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",
+                                                                 ALLOCSET_DEFAULT_SIZES);
+       }
+
        hash_ctl.hcxt = AllocSetContextCreate(CacheMemoryContext,
                                                                                  "online_analyze storage context",
                                                                                  ALLOCSET_DEFAULT_SIZES);
        hash_ctl.hcxt = AllocSetContextCreate(CacheMemoryContext,
                                                                                  "online_analyze storage context",
                                                                                  ALLOCSET_DEFAULT_SIZES);
@@ -746,6 +779,27 @@ _PG_init(void)
                NULL
        );
 
                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)",
        DefineCustomRealVariable(
                "online_analyze.min_interval",
                "minimum time interval between analyze call (in milliseconds)",