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