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/builtins.h"
  40 #include "utils/lsyscache.h"
  41 #include "utils/guc.h"
  42
  43 #ifdef PG_MODULE_MAGIC
  44 PG_MODULE_MAGIC;
  45 #endif
  46
  47 static bool online_analyze_enable = true;
  48 static bool online_analyze_verbose = true;
  49 static double online_analyze_scale_factor = 0.1;
  50 static int online_analyze_threshold = 50;
  51 static double online_analyze_min_interval = 10000;
  52
  53 typedef enum
  54 {
  55         OATT_ALL                = 0x03,
  56         OATT_PERSISTENT = 0x01,
  57         OATT_TEMPORARY  = 0x02,
  58         OATT_NONE               = 0x00
  59 } OnlyneAnalyzeTableType;
  60
  61 static const struct config_enum_entry online_analyze_table_type_options[] =
  62 {
  63         {"all", OATT_ALL, false},
  64         {"persistent", OATT_PERSISTENT, false},
  65         {"temporary", OATT_TEMPORARY, false},
  66         {"none", OATT_NONE, false},
  67         {NULL, 0, false},
  68 };
  69
  70 static int online_analyze_table_type = (int)OATT_ALL;
  71
  72 typedef struct TableList {
  73         int             nTables;
  74         Oid             *tables;
  75         char    *tableStr;
  76 } TableList;
  77
  78 static TableList excludeTables = {0, NULL, NULL};
  79 static TableList includeTables = {0, NULL, NULL};
  80
  81 static int
  82 oid_cmp(const void *a, const void *b)
  83 {
  84         if (*(Oid*)a == *(Oid*)b)
  85                 return 0;
  86         return (*(Oid*)a > *(Oid*)b) ? 1 : -1;
  87 }
  88
  89 static const char *
  90 tableListAssign(const char * newval, bool doit, TableList *tbl)
  91 {
  92         char       *rawname;
  93         List       *namelist;
  94         ListCell   *l;
  95         Oid         *newOids = NULL;
  96         int         nOids = 0,
  97                                 i = 0;
  98
  99         rawname = pstrdup(newval);
 100
 101         if (!SplitIdentifierString(rawname, ',', &namelist))
 102                 goto cleanup;
 103
 104         if (doit)
 105         {
 106                 nOids = list_length(namelist);
 107                 newOids = malloc(sizeof(Oid) * (nOids+1));
 108                 if (!newOids)
 109                         elog(ERROR,"could not allocate %d bytes", (int)(sizeof(Oid) * (nOids+1)));
 110         }
 111
 112         foreach(l, namelist)
 113         {
 114                 char        *curname = (char *) lfirst(l);
 115                 Oid         relOid = RangeVarGetRelid(makeRangeVarFromNameList(stringToQualifiedNameList(curname)), true);
 116
 117                 if (relOid == InvalidOid)
 118                 {
 119 #if PG_VERSION_NUM >= 90100
 120                         if (doit == false)
 121 #endif
 122                         elog(WARNING,"'%s' does not exist", curname);
 123                         continue;
 124                 }
 125                 else if ( get_rel_relkind(relOid) != RELKIND_RELATION )
 126                 {
 127 #if PG_VERSION_NUM >= 90100
 128                         if (doit == false)
 129 #endif
 130                                 elog(WARNING,"'%s' is not an table", curname);
 131                         continue;
 132                 }
 133                 else if (doit)
 134                 {
 135                         newOids[i++] = relOid;
 136                 }
 137         }
 138
 139         if (doit)
 140         {
 141                 tbl->nTables = i;
 142                 if (tbl->tables)
 143                         free(tbl->tables);
 144                 tbl->tables = newOids;
 145                 if (tbl->nTables > 1)
 146                         qsort(tbl->tables, tbl->nTables, sizeof(tbl->tables[0]), oid_cmp);
 147         }
 148
 149         pfree(rawname);
 150         list_free(namelist);
 151
 152         return newval;
 153
 154 cleanup:
 155         if (newOids)
 156                 free(newOids);
 157         pfree(rawname);
 158         list_free(namelist);
 159         return NULL;
 160 }
 161
 162 #if PG_VERSION_NUM >= 90100
 163 static bool
 164 excludeTablesCheck(char **newval, void **extra, GucSource source)
 165 {
 166         char *val;
 167
 168         val = (char*)tableListAssign(*newval, false, &excludeTables);
 169
 170         if (val)
 171         {
 172                 *newval = val;
 173                 return true;
 174         }
 175
 176         return false;
 177 }
 178
 179 static void
 180 excludeTablesAssign(const char *newval, void *extra)
 181 {
 182         tableListAssign(newval, true, &excludeTables);
 183 }
 184
 185 static bool
 186 includeTablesCheck(char **newval, void **extra, GucSource source)
 187 {
 188         char *val;
 189
 190         val = (char*)tableListAssign(*newval, false, &includeTables);
 191
 192         if (val)
 193         {
 194                 *newval = val;
 195                 return true;
 196         }
 197
 198         return false;
 199 }
 200
 201 static void
 202 includeTablesAssign(const char *newval, void *extra)
 203 {
 204         tableListAssign(newval, true, &excludeTables);
 205 }
 206
 207 #else /* PG_VERSION_NUM < 90100 */
 208
 209 static const char *
 210 excludeTablesAssign(const char * newval, bool doit, GucSource source)
 211 {
 212         return tableListAssign(newval, doit, &excludeTables);
 213 }
 214
 215 static const char *
 216 includeTablesAssign(const char * newval, bool doit, GucSource source)
 217 {
 218         return tableListAssign(newval, doit, &includeTables);
 219 }
 220
 221 #endif
 222
 223 static const char*
 224 tableListShow(TableList *tbl)
 225 {
 226         char    *val, *ptr;
 227         int     i,
 228                         len;
 229
 230         len = 1 /* \0 */ + tbl->nTables * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
 231         ptr = val = palloc(len);
 232         *ptr ='\0';
 233         for(i=0; i<tbl->nTables; i++)
 234         {
 235                 char    *relname = get_rel_name(tbl->tables[i]);
 236                 Oid     nspOid = get_rel_namespace(tbl->tables[i]);
 237                 char    *nspname = get_namespace_name(nspOid);
 238
 239                 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
 240                         continue;
 241
 242                 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
 243                                                                                                         (i==0) ? "" : ", ",
 244                                                                                                         nspname, relname);
 245         }
 246
 247         return val;
 248 }
 249
 250 static const char*
 251 excludeTablesShow(void)
 252 {
 253         return tableListShow(&excludeTables);
 254 }
 255
 256 static const char*
 257 includeTablesShow(void)
 258 {
 259         return tableListShow(&includeTables);
 260 }
 261
 262 static bool
 263 matchOid(TableList *tbl, Oid oid)
 264 {
 265         Oid     *StopLow = tbl->tables,
 266                 *StopHigh = tbl->tables + tbl->nTables,
 267                 *StopMiddle;
 268
 269         /* Loop invariant: StopLow <= val < StopHigh */
 270         while (StopLow < StopHigh)
 271         {
 272                 StopMiddle = StopLow + ((StopHigh - StopLow) >> 1);
 273
 274                 if (*StopMiddle == oid)
 275                         return true;
 276                 else  if (*StopMiddle < oid)
 277                         StopLow = StopMiddle + 1;
 278                 else
 279                         StopHigh = StopMiddle;
 280         }
 281
 282         return false;
 283 }
 284
 285 static ExecutorEnd_hook_type oldhook = NULL;
 286
 287 static void
 288 makeAnalyze(Oid relOid, CmdType operation, uint32 naffected)
 289 {
 290         PgStat_StatTabEntry             *tabentry;
 291         TimestampTz                     now = GetCurrentTimestamp();
 292
 293         if (relOid == InvalidOid)
 294                 return;
 295
 296         tabentry = pgstat_fetch_stat_tabentry(relOid);
 297
 298 #if PG_VERSION_NUM >= 90000
 299 #define changes_since_analyze(t)        ((t)->changes_since_analyze)
 300 #else
 301 #define changes_since_analyze(t)        ((t)->n_live_tuples + (t)->n_dead_tuples - (t)->last_anl_tuples)
 302 #endif
 303
 304         if (
 305                 tabentry == NULL /* a new table */ ||
 306                 (
 307                         /* do not analyze too often, if both stamps are exceeded the go */
 308                         TimestampDifferenceExceeds(tabentry->analyze_timestamp, now, online_analyze_min_interval) &&
 309                         TimestampDifferenceExceeds(tabentry->autovac_analyze_timestamp, now, online_analyze_min_interval) &&
 310                         /* be in sync with relation_needs_vacanalyze */
 311                         ((double)(changes_since_analyze(tabentry) + naffected)) >=
 312                                 online_analyze_scale_factor * ((double)(tabentry->n_dead_tuples + tabentry->n_live_tuples)) +
 313                                         (double)online_analyze_threshold
 314                 )
 315         )
 316         {
 317                 VacuumStmt                              vacstmt;
 318                 TimestampTz                             startStamp, endStamp;
 319
 320                 /*
 321                  * includeTables overwrites excludeTables
 322                  */
 323                 switch(online_analyze_table_type)
 324                 {
 325                         case OATT_ALL:
 326                                 if (matchOid(&excludeTables, relOid) == true && matchOid(&includeTables, relOid) == false)
 327                                         return;
 328                                 break;
 329                         case OATT_NONE:
 330                                 if (matchOid(&includeTables, relOid) == false)
 331                                         return;
 332                                 break;
 333                         case OATT_TEMPORARY:
 334                         case OATT_PERSISTENT:
 335                         default:
 336                                 {
 337                                         Relation                                rel;
 338                                         OnlyneAnalyzeTableType  reltype;
 339
 340                                         rel = RelationIdGetRelation(relOid);
 341                                         reltype = (rel->rd_istemp || rel->rd_islocaltemp) ? OATT_TEMPORARY : OATT_PERSISTENT;
 342                                         RelationClose(rel);
 343
 344                                         /*
 345                                          * skip analyze if relation's type doesn't not match online_analyze_table_type
 346                                          */
 347                                         if ((online_analyze_table_type & reltype) == 0 || matchOid(&excludeTables, relOid) == true)
 348                                         {
 349                                                 if (matchOid(&includeTables, relOid) == false)
 350                                                         return;
 351                                         }
 352                                 }
 353                                 break;
 354                 }
 355
 356                 vacstmt.type = T_VacuumStmt;
 357                 vacstmt.freeze_min_age = -1;
 358                 vacstmt.freeze_table_age = -1; /* ??? */
 359                 vacstmt.relation = NULL;
 360                 vacstmt.va_cols = NIL;
 361
 362 #if PG_VERSION_NUM >= 90000
 363                 vacstmt.options = VACOPT_ANALYZE;
 364                 if (online_analyze_verbose)
 365                         vacstmt.options |= VACOPT_VERBOSE;
 366 #else
 367                 vacstmt.vacuum = vacstmt.full = false;
 368                 vacstmt.analyze = true;
 369                 vacstmt.verbose = online_analyze_verbose;
 370 #endif
 371
 372                 if (online_analyze_verbose)
 373                         startStamp = GetCurrentTimestamp();
 374
 375                 analyze_rel(relOid, &vacstmt, GetAccessStrategy(BAS_VACUUM)
 376 #if (PG_VERSION_NUM < 90004) && (PG_VERSION_NUM >= 90000)
 377                         , true
 378 #endif
 379                 );
 380
 381                 if (online_analyze_verbose)
 382                 {
 383                         long    secs;
 384                         int             microsecs;
 385
 386                         endStamp = GetCurrentTimestamp();
 387                         TimestampDifference(startStamp, endStamp, &secs, &microsecs);
 388                         elog(INFO, "analyze \"%s\" took %.02f seconds",
 389                                 get_rel_name(relOid), ((double)secs) + ((double)microsecs)/1.0e6);
 390                 }
 391
 392
 393                 if (tabentry == NULL)
 394                 {
 395                         /* new table */
 396                         pgstat_clear_snapshot();
 397                 }
 398                 else
 399                 {
 400                         /* update last analyze timestamp in local memory of backend */
 401                         tabentry->analyze_timestamp = now;
 402                 }
 403         }
 404 #if PG_VERSION_NUM >= 90000
 405         else if (tabentry != NULL)
 406         {
 407                 tabentry->changes_since_analyze += naffected;
 408         }
 409 #endif
 410 }
 411
 412 extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc);
 413 void
 414 onlineAnalyzeHooker(QueryDesc *queryDesc)
 415 {
 416         uint32  naffected = 0;
 417
 418         if (queryDesc->estate)
 419                 naffected = queryDesc->estate->es_processed;
 420
 421         if (online_analyze_enable && queryDesc->plannedstmt &&
 422                         (queryDesc->operation == CMD_INSERT ||
 423                          queryDesc->operation == CMD_UPDATE ||
 424                          queryDesc->operation == CMD_DELETE ||
 425                          (queryDesc->operation == CMD_SELECT && queryDesc->plannedstmt->intoClause)))
 426         {
 427                 if (queryDesc->plannedstmt->intoClause)
 428                 {
 429                         Oid     relOid = RangeVarGetRelid(queryDesc->plannedstmt->intoClause->rel, true);
 430
 431                         makeAnalyze(relOid, queryDesc->operation, naffected);
 432                 }
 433                 else if (queryDesc->plannedstmt->resultRelations &&
 434                                  queryDesc->plannedstmt->rtable)
 435                 {
 436                         ListCell        *l;
 437
 438                         foreach(l, queryDesc->plannedstmt->resultRelations)
 439                         {
 440                                 int                     n = lfirst_int(l);
 441                                 RangeTblEntry   *rte = list_nth(queryDesc->plannedstmt->rtable, n-1);
 442
 443                                 if (rte->rtekind == RTE_RELATION)
 444                                         makeAnalyze(rte->relid, queryDesc->operation, naffected);
 445                         }
 446                 }
 447         }
 448
 449         if (oldhook)
 450                 (*oldhook)(queryDesc);
 451         else
 452                 standard_ExecutorEnd(queryDesc);
 453 }
 454
 455 void _PG_init(void);
 456 void
 457 _PG_init(void)
 458 {
 459         oldhook = ExecutorEnd_hook;
 460
 461         ExecutorEnd_hook = onlineAnalyzeHooker;
 462
 463         DefineCustomBoolVariable(
 464                 "online_analyze.enable",
 465                 "Enable on-line analyze",
 466                 "Enables analyze of table directly after insert/update/delete/select into",
 467                 &online_analyze_enable,
 468 #if PG_VERSION_NUM >= 80400
 469                 online_analyze_enable,
 470 #endif
 471                 PGC_USERSET,
 472 #if PG_VERSION_NUM >= 80400
 473                 GUC_NOT_IN_SAMPLE,
 474 #if PG_VERSION_NUM >= 90100
 475                 NULL,
 476 #endif
 477 #endif
 478                 NULL,
 479                 NULL
 480         );
 481
 482         DefineCustomBoolVariable(
 483                 "online_analyze.verbose",
 484                 "Verbosity of on-line analyze",
 485                 "Make ANALYZE VERBOSE after table's changes",
 486                 &online_analyze_verbose,
 487 #if PG_VERSION_NUM >= 80400
 488                 online_analyze_verbose,
 489 #endif
 490                 PGC_USERSET,
 491 #if PG_VERSION_NUM >= 80400
 492                 GUC_NOT_IN_SAMPLE,
 493 #if PG_VERSION_NUM >= 90100
 494                 NULL,
 495 #endif
 496 #endif
 497                 NULL,
 498                 NULL
 499         );
 500
 501     DefineCustomRealVariable(
 502                 "online_analyze.scale_factor",
 503                 "fraction of table size to start on-line analyze",
 504                 "fraction of table size to start on-line analyze",
 505                 &online_analyze_scale_factor,
 506 #if PG_VERSION_NUM >= 80400
 507                 online_analyze_scale_factor,
 508 #endif
 509                 0.0,
 510                 1.0,
 511                 PGC_USERSET,
 512 #if PG_VERSION_NUM >= 80400
 513                 GUC_NOT_IN_SAMPLE,
 514 #if PG_VERSION_NUM >= 90100
 515                 NULL,
 516 #endif
 517 #endif
 518                 NULL,
 519                 NULL
 520         );
 521
 522     DefineCustomIntVariable(
 523                 "online_analyze.threshold",
 524                 "min number of row updates before on-line analyze",
 525                 "min number of row updates before on-line analyze",
 526                 &online_analyze_threshold,
 527 #if PG_VERSION_NUM >= 80400
 528                 online_analyze_threshold,
 529 #endif
 530                 0,
 531                 0x7fffffff,
 532                 PGC_USERSET,
 533 #if PG_VERSION_NUM >= 80400
 534                 GUC_NOT_IN_SAMPLE,
 535 #if PG_VERSION_NUM >= 90100
 536                 NULL,
 537 #endif
 538 #endif
 539                 NULL,
 540                 NULL
 541         );
 542
 543     DefineCustomRealVariable(
 544                 "online_analyze.min_interval",
 545                 "minimum time interval between analyze call (in milliseconds)",
 546                 "minimum time interval between analyze call (in milliseconds)",
 547                 &online_analyze_scale_factor,
 548 #if PG_VERSION_NUM >= 80400
 549                 online_analyze_min_interval,
 550 #endif
 551                 0.0,
 552                 1e30,
 553                 PGC_USERSET,
 554 #if PG_VERSION_NUM >= 80400
 555                 GUC_NOT_IN_SAMPLE,
 556 #if PG_VERSION_NUM >= 90100
 557                 NULL,
 558 #endif
 559 #endif
 560                 NULL,
 561                 NULL
 562         );
 563
 564 DefineCustomEnumVariable(
 565                 "online_analyze.table_type",
 566                 "Type(s) of table for onlyne analyze: all(default), persistent, temporary, none",
 567                 NULL,
 568                 &online_analyze_table_type,
 569 #if PG_VERSION_NUM >= 80400
 570                 online_analyze_table_type,
 571 #endif
 572                 online_analyze_table_type_options,
 573                 PGC_USERSET,
 574 #if PG_VERSION_NUM >= 80400
 575         GUC_NOT_IN_SAMPLE,
 576 #if PG_VERSION_NUM >= 90100
 577                 NULL,
 578 #endif
 579 #endif
 580                 NULL,
 581                 NULL
 582         );
 583
 584     DefineCustomStringVariable(
 585                 "online_analyze.exclude_tables",
 586                 "List of tables which will not online analyze",
 587                 NULL,
 588                 &excludeTables.tableStr,
 589 #if PG_VERSION_NUM >= 80400
 590                 "",
 591 #endif
 592                 PGC_USERSET,
 593                 0,
 594 #if PG_VERSION_NUM >= 90100
 595                 excludeTablesCheck,
 596                 excludeTablesAssign,
 597 #else
 598                 excludeTablesAssign,
 599 #endif
 600                 excludeTablesShow
 601         );
 602
 603     DefineCustomStringVariable(
 604                 "online_analyze.include_tables",
 605                 "List of tables which will online analyze",
 606                 NULL,
 607                 &includeTables.tableStr,
 608 #if PG_VERSION_NUM >= 80400
 609                 "",
 610 #endif
 611                 PGC_USERSET,
 612                 0,
 613 #if PG_VERSION_NUM >= 90100
 614                 includeTablesCheck,
 615                 includeTablesAssign,
 616 #else
 617                 includeTablesAssign,
 618 #endif
 619                 includeTablesShow
 620         );
 621 }
 622
 623 void _PG_fini(void);
 624 void
 625 _PG_fini(void)
 626 {
 627         ExecutorEnd_hook = oldhook;
 628
 629         if (excludeTables.tables)
 630                 free(excludeTables.tables);
 631         if (includeTables.tables)
 632                 free(includeTables.tables);
 633
 634         excludeTables.tables = includeTables.tables = NULL;
 635         excludeTables.nTables = includeTables.nTables = 0;
 636 }