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 } OnlineAnalyzeTableType;
  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                                         OnlineAnalyzeTableType  reltype;
 339
 340                                         rel = RelationIdGetRelation(relOid);
 341                                         reltype =
 342 #if PG_VERSION_NUM >= 90100
 343                                                 (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
 344 #else
 345                                                 (rel->rd_istemp || rel->rd_islocaltemp)
 346 #endif
 347                                                         ? OATT_TEMPORARY : OATT_PERSISTENT;
 348                                         RelationClose(rel);
 349
 350                                         /*
 351                                          * skip analyze if relation's type doesn't not match online_analyze_table_type
 352                                          */
 353                                         if ((online_analyze_table_type & reltype) == 0 || matchOid(&excludeTables, relOid) == true)
 354                                         {
 355                                                 if (matchOid(&includeTables, relOid) == false)
 356                                                         return;
 357                                         }
 358                                 }
 359                                 break;
 360                 }
 361
 362                 vacstmt.type = T_VacuumStmt;
 363                 vacstmt.freeze_min_age = -1;
 364                 vacstmt.freeze_table_age = -1; /* ??? */
 365                 vacstmt.relation = NULL;
 366                 vacstmt.va_cols = NIL;
 367
 368 #if PG_VERSION_NUM >= 90000
 369                 vacstmt.options = VACOPT_ANALYZE;
 370                 if (online_analyze_verbose)
 371                         vacstmt.options |= VACOPT_VERBOSE;
 372 #else
 373                 vacstmt.vacuum = vacstmt.full = false;
 374                 vacstmt.analyze = true;
 375                 vacstmt.verbose = online_analyze_verbose;
 376 #endif
 377
 378                 if (online_analyze_verbose)
 379                         startStamp = GetCurrentTimestamp();
 380
 381                 analyze_rel(relOid, &vacstmt, GetAccessStrategy(BAS_VACUUM)
 382 #if (PG_VERSION_NUM < 90004) && (PG_VERSION_NUM >= 90000)
 383                         , true
 384 #endif
 385                 );
 386
 387                 if (online_analyze_verbose)
 388                 {
 389                         long    secs;
 390                         int             microsecs;
 391
 392                         endStamp = GetCurrentTimestamp();
 393                         TimestampDifference(startStamp, endStamp, &secs, &microsecs);
 394                         elog(INFO, "analyze \"%s\" took %.02f seconds",
 395                                 get_rel_name(relOid), ((double)secs) + ((double)microsecs)/1.0e6);
 396                 }
 397
 398
 399                 if (tabentry == NULL)
 400                 {
 401                         /* new table */
 402                         pgstat_clear_snapshot();
 403                 }
 404                 else
 405                 {
 406                         /* update last analyze timestamp in local memory of backend */
 407                         tabentry->analyze_timestamp = now;
 408                 }
 409         }
 410 #if PG_VERSION_NUM >= 90000
 411         else if (tabentry != NULL)
 412         {
 413                 tabentry->changes_since_analyze += naffected;
 414         }
 415 #endif
 416 }
 417
 418 extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc);
 419 void
 420 onlineAnalyzeHooker(QueryDesc *queryDesc)
 421 {
 422         uint32  naffected = 0;
 423
 424         if (queryDesc->estate)
 425                 naffected = queryDesc->estate->es_processed;
 426
 427         if (online_analyze_enable && queryDesc->plannedstmt &&
 428                         (queryDesc->operation == CMD_INSERT ||
 429                          queryDesc->operation == CMD_UPDATE ||
 430                          queryDesc->operation == CMD_DELETE ||
 431                          (queryDesc->operation == CMD_SELECT && queryDesc->plannedstmt->intoClause)))
 432         {
 433                 if (queryDesc->plannedstmt->intoClause)
 434                 {
 435                         Oid     relOid = RangeVarGetRelid(queryDesc->plannedstmt->intoClause->rel, true);
 436
 437                         makeAnalyze(relOid, queryDesc->operation, naffected);
 438                 }
 439                 else if (queryDesc->plannedstmt->resultRelations &&
 440                                  queryDesc->plannedstmt->rtable)
 441                 {
 442                         ListCell        *l;
 443
 444                         foreach(l, queryDesc->plannedstmt->resultRelations)
 445                         {
 446                                 int                     n = lfirst_int(l);
 447                                 RangeTblEntry   *rte = list_nth(queryDesc->plannedstmt->rtable, n-1);
 448
 449                                 if (rte->rtekind == RTE_RELATION)
 450                                         makeAnalyze(rte->relid, queryDesc->operation, naffected);
 451                         }
 452                 }
 453         }
 454
 455         if (oldhook)
 456                 (*oldhook)(queryDesc);
 457         else
 458                 standard_ExecutorEnd(queryDesc);
 459 }
 460
 461 void _PG_init(void);
 462 void
 463 _PG_init(void)
 464 {
 465         oldhook = ExecutorEnd_hook;
 466
 467         ExecutorEnd_hook = onlineAnalyzeHooker;
 468
 469         DefineCustomBoolVariable(
 470                 "online_analyze.enable",
 471                 "Enable on-line analyze",
 472                 "Enables analyze of table directly after insert/update/delete/select into",
 473                 &online_analyze_enable,
 474 #if PG_VERSION_NUM >= 80400
 475                 online_analyze_enable,
 476 #endif
 477                 PGC_USERSET,
 478 #if PG_VERSION_NUM >= 80400
 479                 GUC_NOT_IN_SAMPLE,
 480 #if PG_VERSION_NUM >= 90100
 481                 NULL,
 482 #endif
 483 #endif
 484                 NULL,
 485                 NULL
 486         );
 487
 488         DefineCustomBoolVariable(
 489                 "online_analyze.verbose",
 490                 "Verbosity of on-line analyze",
 491                 "Make ANALYZE VERBOSE after table's changes",
 492                 &online_analyze_verbose,
 493 #if PG_VERSION_NUM >= 80400
 494                 online_analyze_verbose,
 495 #endif
 496                 PGC_USERSET,
 497 #if PG_VERSION_NUM >= 80400
 498                 GUC_NOT_IN_SAMPLE,
 499 #if PG_VERSION_NUM >= 90100
 500                 NULL,
 501 #endif
 502 #endif
 503                 NULL,
 504                 NULL
 505         );
 506
 507     DefineCustomRealVariable(
 508                 "online_analyze.scale_factor",
 509                 "fraction of table size to start on-line analyze",
 510                 "fraction of table size to start on-line analyze",
 511                 &online_analyze_scale_factor,
 512 #if PG_VERSION_NUM >= 80400
 513                 online_analyze_scale_factor,
 514 #endif
 515                 0.0,
 516                 1.0,
 517                 PGC_USERSET,
 518 #if PG_VERSION_NUM >= 80400
 519                 GUC_NOT_IN_SAMPLE,
 520 #if PG_VERSION_NUM >= 90100
 521                 NULL,
 522 #endif
 523 #endif
 524                 NULL,
 525                 NULL
 526         );
 527
 528     DefineCustomIntVariable(
 529                 "online_analyze.threshold",
 530                 "min number of row updates before on-line analyze",
 531                 "min number of row updates before on-line analyze",
 532                 &online_analyze_threshold,
 533 #if PG_VERSION_NUM >= 80400
 534                 online_analyze_threshold,
 535 #endif
 536                 0,
 537                 0x7fffffff,
 538                 PGC_USERSET,
 539 #if PG_VERSION_NUM >= 80400
 540                 GUC_NOT_IN_SAMPLE,
 541 #if PG_VERSION_NUM >= 90100
 542                 NULL,
 543 #endif
 544 #endif
 545                 NULL,
 546                 NULL
 547         );
 548
 549     DefineCustomRealVariable(
 550                 "online_analyze.min_interval",
 551                 "minimum time interval between analyze call (in milliseconds)",
 552                 "minimum time interval between analyze call (in milliseconds)",
 553                 &online_analyze_scale_factor,
 554 #if PG_VERSION_NUM >= 80400
 555                 online_analyze_min_interval,
 556 #endif
 557                 0.0,
 558                 1e30,
 559                 PGC_USERSET,
 560 #if PG_VERSION_NUM >= 80400
 561                 GUC_NOT_IN_SAMPLE,
 562 #if PG_VERSION_NUM >= 90100
 563                 NULL,
 564 #endif
 565 #endif
 566                 NULL,
 567                 NULL
 568         );
 569
 570 DefineCustomEnumVariable(
 571                 "online_analyze.table_type",
 572                 "Type(s) of table for online analyze: all(default), persistent, temporary, none",
 573                 NULL,
 574                 &online_analyze_table_type,
 575 #if PG_VERSION_NUM >= 80400
 576                 online_analyze_table_type,
 577 #endif
 578                 online_analyze_table_type_options,
 579                 PGC_USERSET,
 580 #if PG_VERSION_NUM >= 80400
 581         GUC_NOT_IN_SAMPLE,
 582 #if PG_VERSION_NUM >= 90100
 583                 NULL,
 584 #endif
 585 #endif
 586                 NULL,
 587                 NULL
 588         );
 589
 590     DefineCustomStringVariable(
 591                 "online_analyze.exclude_tables",
 592                 "List of tables which will not online analyze",
 593                 NULL,
 594                 &excludeTables.tableStr,
 595 #if PG_VERSION_NUM >= 80400
 596                 "",
 597 #endif
 598                 PGC_USERSET,
 599                 0,
 600 #if PG_VERSION_NUM >= 90100
 601                 excludeTablesCheck,
 602                 excludeTablesAssign,
 603 #else
 604                 excludeTablesAssign,
 605 #endif
 606                 excludeTablesShow
 607         );
 608
 609     DefineCustomStringVariable(
 610                 "online_analyze.include_tables",
 611                 "List of tables which will online analyze",
 612                 NULL,
 613                 &includeTables.tableStr,
 614 #if PG_VERSION_NUM >= 80400
 615                 "",
 616 #endif
 617                 PGC_USERSET,
 618                 0,
 619 #if PG_VERSION_NUM >= 90100
 620                 includeTablesCheck,
 621                 includeTablesAssign,
 622 #else
 623                 includeTablesAssign,
 624 #endif
 625                 includeTablesShow
 626         );
 627 }
 628
 629 void _PG_fini(void);
 630 void
 631 _PG_fini(void)
 632 {
 633         ExecutorEnd_hook = oldhook;
 634
 635         if (excludeTables.tables)
 636                 free(excludeTables.tables);
 637         if (includeTables.tables)
 638                 free(includeTables.tables);
 639
 640         excludeTables.tables = includeTables.tables = NULL;
 641         excludeTables.nTables = includeTables.nTables = 0;
 642 }