online_analyze.c

   1 /*
   2  * Copyright (c) 2011 Teodor Sigaev <teodor@sigaev.ru>
   3  * All rights reserved.
   4  *
   5  * Redistribution and use in source and binary forms, with or without
   6  * modification, are permitted provided that the following conditions
   7  * are met:
   8  * 1. Redistributions of source code must retain the above copyright
   9  *        notice, this list of conditions and the following disclaimer.
  10  * 2. Redistributions in binary form must reproduce the above copyright
  11  *        notice, this list of conditions and the following disclaimer in the
  12  *        documentation and/or other materials provided with the distribution.
  13  * 3. Neither the name of the author nor the names of any co-contributors
  14  *        may be used to endorse or promote products derived from this software
  15  *        without specific prior written permission.
  16  *
  17  * THIS SOFTWARE IS PROVIDED BY CONTRIBUTORS ``AS IS'' AND ANY EXPRESS
  18  * OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  19  * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
  20  * ARE DISCLAIMED. IN NO EVENT SHALL CONTRIBUTORS BE LIABLE FOR ANY
  21  * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
  22  * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
  23  * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
  24  * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
  25  * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
  26  * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
  27  * IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  28  */
  29
  30 #include "postgres.h"
  31
  32 #include "pgstat.h"
  33 #include "catalog/namespace.h"
  34 #include "commands/vacuum.h"
  35 #include "executor/executor.h"
  36 #include "nodes/nodes.h"
  37 #include "nodes/parsenodes.h"
  38 #include "storage/bufmgr.h"
  39 #include "utils/lsyscache.h"
  40 #include "utils/guc.h"
  41
  42 #ifdef PG_MODULE_MAGIC
  43 PG_MODULE_MAGIC;
  44 #endif
  45
  46 static bool online_analyze_enable = true;
  47 static bool online_analyze_verbose = true;
  48 static double online_analyze_scale_factor = 0.1;
  49 static int online_analyze_threshold = 50;
  50 static double online_analyze_min_interval = 10000;
  51
  52 static ExecutorEnd_hook_type oldhook = NULL;
  53
  54 static void
  55 makeAnalyze(Oid relOid, CmdType operation, uint32 naffected)
  56 {
  57         PgStat_StatTabEntry             *tabentry;
  58         TimestampTz                     now = GetCurrentTimestamp();
  59
  60         if (relOid == InvalidOid)
  61                 return;
  62
  63         tabentry = pgstat_fetch_stat_tabentry(relOid);
  64
  65 #if PG_VERSION_NUM >= 90000
  66 #define changes_since_analyze(t)        ((t)->changes_since_analyze)
  67 #else
  68 #define changes_since_analyze(t)        ((t)->n_live_tuples + (t)->n_dead_tuples - (t)->last_anl_tuples)
  69 #endif
  70
  71         if (
  72                 tabentry == NULL /* a new table */ ||
  73                 (
  74                         /* do not analyze too often, if both stamps are exceeded the go */
  75                         TimestampDifferenceExceeds(tabentry->analyze_timestamp, now, online_analyze_min_interval) &&
  76                         TimestampDifferenceExceeds(tabentry->autovac_analyze_timestamp, now, online_analyze_min_interval) &&
  77                         /* be in sync with relation_needs_vacanalyze */
  78                         ((double)(changes_since_analyze(tabentry) + naffected)) >=
  79                                 online_analyze_scale_factor * ((double)(tabentry->n_dead_tuples + tabentry->n_live_tuples)) +
  80                                         (double)online_analyze_threshold
  81                 )
  82         )
  83         {
  84                 VacuumStmt                              vacstmt;
  85                 TimestampTz                             startStamp, endStamp;
  86
  87                 vacstmt.type = T_VacuumStmt;
  88                 vacstmt.freeze_min_age = -1;
  89                 vacstmt.freeze_table_age = -1; /* ??? */
  90                 vacstmt.relation = NULL;
  91                 vacstmt.va_cols = NIL;
  92
  93 #if PG_VERSION_NUM >= 90000
  94                 vacstmt.options = VACOPT_ANALYZE;
  95                 if (online_analyze_verbose)
  96                         vacstmt.options |= VACOPT_VERBOSE;
  97 #else
  98                 vacstmt.vacuum = vacstmt.full = false;
  99                 vacstmt.analyze = true;
 100                 vacstmt.verbose = online_analyze_verbose;
 101 #endif
 102
 103                 if (online_analyze_verbose)
 104                         startStamp = GetCurrentTimestamp();
 105
 106                 analyze_rel(relOid, &vacstmt, GetAccessStrategy(BAS_VACUUM)
 107 #if (PG_VERSION_NUM < 90004) && (PG_VERSION_NUM >= 90000)
 108                         , true
 109 #endif
 110                 );
 111
 112                 if (online_analyze_verbose)
 113                 {
 114                         long    secs;
 115                         int             microsecs;
 116
 117                         endStamp = GetCurrentTimestamp();
 118                         TimestampDifference(startStamp, endStamp, &secs, &microsecs);
 119                         elog(INFO, "analyze \"%s\" took %.02f seconds", get_rel_name(relOid), ((double)secs) + ((double)microsecs)/1.0e6);
 120                 }
 121
 122
 123                 if (tabentry == NULL)
 124                 {
 125                         /* new table */
 126                         pgstat_clear_snapshot();
 127                 }
 128                 else
 129                 {
 130                         /* update last analyze timestamp in local memory of backend */
 131                         tabentry->analyze_timestamp = now;
 132                 }
 133         }
 134 #if PG_VERSION_NUM >= 90000
 135         else if (tabentry != NULL)
 136         {
 137                 tabentry->changes_since_analyze += naffected;
 138         }
 139 #endif
 140 }
 141
 142 extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc);
 143 void
 144 onlineAnalyzeHooker(QueryDesc *queryDesc)
 145 {
 146         uint32  naffected = 0;
 147
 148         if (queryDesc->estate)
 149                 naffected = queryDesc->estate->es_processed;
 150
 151         if (online_analyze_enable && queryDesc->plannedstmt &&
 152                         (queryDesc->operation == CMD_INSERT ||
 153                          queryDesc->operation == CMD_UPDATE ||
 154                          queryDesc->operation == CMD_DELETE ||
 155                          (queryDesc->operation == CMD_SELECT && queryDesc->plannedstmt->intoClause)))
 156         {
 157                 if (queryDesc->plannedstmt->intoClause)
 158                 {
 159                         Oid     relOid = RangeVarGetRelid(queryDesc->plannedstmt->intoClause->rel, true);
 160
 161                         makeAnalyze(relOid, queryDesc->operation, naffected);
 162                 }
 163                 else if (queryDesc->plannedstmt->resultRelations &&
 164                                  queryDesc->plannedstmt->rtable)
 165                 {
 166                         ListCell        *l;
 167
 168                         foreach(l, queryDesc->plannedstmt->resultRelations)
 169                         {
 170                                 int                     n = lfirst_int(l);
 171                                 RangeTblEntry   *rte = list_nth(queryDesc->plannedstmt->rtable, n-1);
 172
 173                                 if (rte->rtekind == RTE_RELATION)
 174                                         makeAnalyze(rte->relid, queryDesc->operation, naffected);
 175                         }
 176                 }
 177         }
 178
 179         if (oldhook)
 180                 (*oldhook)(queryDesc);
 181         else
 182                 standard_ExecutorEnd(queryDesc);
 183 }
 184
 185 void _PG_init(void);
 186 void
 187 _PG_init(void)
 188 {
 189         oldhook = ExecutorEnd_hook;
 190
 191         ExecutorEnd_hook = onlineAnalyzeHooker;
 192
 193         DefineCustomBoolVariable(
 194                 "online_analyze.enable",
 195                 "Enable on-line analyze",
 196                 "Enables analyze of table directly after insert/update/delete/select into",
 197                 &online_analyze_enable,
 198 #if PG_VERSION_NUM >= 80400
 199                 online_analyze_enable,
 200 #endif
 201                 PGC_USERSET,
 202 #if PG_VERSION_NUM >= 80400
 203                 GUC_NOT_IN_SAMPLE,
 204 #if PG_VERSION_NUM >= 90100
 205                 NULL,
 206 #endif
 207 #endif
 208                 NULL,
 209                 NULL
 210         );
 211
 212         DefineCustomBoolVariable(
 213                 "online_analyze.verbose",
 214                 "Verbosity of on-line analyze",
 215                 "Make ANALYZE VERBOSE after table's changes",
 216                 &online_analyze_verbose,
 217 #if PG_VERSION_NUM >= 80400
 218                 online_analyze_verbose,
 219 #endif
 220                 PGC_USERSET,
 221 #if PG_VERSION_NUM >= 80400
 222                 GUC_NOT_IN_SAMPLE,
 223 #if PG_VERSION_NUM >= 90100
 224                 NULL,
 225 #endif
 226 #endif
 227                 NULL,
 228                 NULL
 229         );
 230
 231     DefineCustomRealVariable(
 232                 "online_analyze.scale_factor",
 233                 "fraction of table size to start on-line analyze",
 234                 "fraction of table size to start on-line analyze",
 235                 &online_analyze_scale_factor,
 236 #if PG_VERSION_NUM >= 80400
 237                 online_analyze_scale_factor,
 238 #endif
 239                 0.0,
 240                 1.0,
 241                 PGC_USERSET,
 242 #if PG_VERSION_NUM >= 80400
 243                 GUC_NOT_IN_SAMPLE,
 244 #if PG_VERSION_NUM >= 90100
 245                 NULL,
 246 #endif
 247 #endif
 248                 NULL,
 249                 NULL
 250         );
 251
 252     DefineCustomIntVariable(
 253                 "online_analyze.threshold",
 254                 "min number of row updates before on-line analyze",
 255                 "min number of row updates before on-line analyze",
 256                 &online_analyze_threshold,
 257 #if PG_VERSION_NUM >= 80400
 258                 online_analyze_threshold,
 259 #endif
 260                 0,
 261                 0x7fffffff,
 262                 PGC_USERSET,
 263 #if PG_VERSION_NUM >= 80400
 264                 GUC_NOT_IN_SAMPLE,
 265 #if PG_VERSION_NUM >= 90100
 266                 NULL,
 267 #endif
 268 #endif
 269                 NULL,
 270                 NULL
 271         );
 272
 273     DefineCustomRealVariable(
 274                 "online_analyze.min_interval",
 275                 "minimum time interval between analyze call (in milliseconds)",
 276                 "minimum time interval between analyze call (in milliseconds)",
 277                 &online_analyze_scale_factor,
 278 #if PG_VERSION_NUM >= 80400
 279                 online_analyze_min_interval,
 280 #endif
 281                 0.0,
 282                 1e30,
 283                 PGC_USERSET,
 284 #if PG_VERSION_NUM >= 80400
 285                 GUC_NOT_IN_SAMPLE,
 286 #if PG_VERSION_NUM >= 90100
 287                 NULL,
 288 #endif
 289 #endif
 290                 NULL,
 291                 NULL
 292         );
 293
 294 }
 295
 296 void _PG_fini(void);
 297 void
 298 _PG_fini(void)
 299 {
 300         ExecutorEnd_hook = oldhook;
 301 }