8f965c14e7fc119777ac77b6910312aa4533d75c
[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, GetAccessStrategy(BAS_VACUUM)
404 #if (PG_VERSION_NUM < 90004) && (PG_VERSION_NUM >= 90000)
405                         , true
406 #endif
407                 );
408
409                 if (online_analyze_verbose)
410                 {
411                         long    secs;
412                         int             microsecs;
413
414                         endStamp = GetCurrentTimestamp();
415                         TimestampDifference(startStamp, endStamp, &secs, &microsecs);
416                         elog(INFO, "analyze \"%s\" took %.02f seconds", 
417                                 get_rel_name(relOid), ((double)secs) + ((double)microsecs)/1.0e6);
418                 }
419
420
421                 if (tabentry == NULL)
422                 {
423                         /* new table */
424                         pgstat_clear_snapshot();
425                 }
426                 else
427                 {
428                         /* update last analyze timestamp in local memory of backend */
429                         tabentry->analyze_timestamp = now;
430                 }
431         }
432 #if PG_VERSION_NUM >= 90000
433         else if (tabentry != NULL)
434         {
435                 tabentry->changes_since_analyze += naffected;
436         }
437 #endif
438 }
439
440 extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc);
441 void
442 onlineAnalyzeHooker(QueryDesc *queryDesc) 
443 {
444         uint32  naffected = 0;
445
446         if (queryDesc->estate)
447                 naffected = queryDesc->estate->es_processed;    
448
449         if (online_analyze_enable && queryDesc->plannedstmt &&
450                         (queryDesc->operation == CMD_INSERT || 
451                          queryDesc->operation == CMD_UPDATE ||
452                          queryDesc->operation == CMD_DELETE
453 #if PG_VERSION_NUM < 90200
454                          || (queryDesc->operation == CMD_SELECT && queryDesc->plannedstmt->intoClause)
455 #endif
456                          ))
457         {
458 #if PG_VERSION_NUM < 90200
459                 if (queryDesc->operation == CMD_SELECT)
460                 {
461                         Oid     relOid = RangeVarGetRelid(queryDesc->plannedstmt->intoClause->rel, true);
462
463                         makeAnalyze(relOid, queryDesc->operation, naffected);
464                 }
465                 else 
466 #endif
467                 if (queryDesc->plannedstmt->resultRelations &&
468                                  queryDesc->plannedstmt->rtable)
469                 {
470                         ListCell        *l;
471
472                         foreach(l, queryDesc->plannedstmt->resultRelations)
473                         {
474                                 int                     n = lfirst_int(l);
475                                 RangeTblEntry   *rte = list_nth(queryDesc->plannedstmt->rtable, n-1);
476                 
477                                 if (rte->rtekind == RTE_RELATION)
478                                         makeAnalyze(rte->relid, queryDesc->operation, naffected);
479                         }
480                 }
481         }
482
483         if (oldExecutorEndHook)
484                 oldExecutorEndHook(queryDesc);
485         else
486                 standard_ExecutorEnd(queryDesc);
487 }
488
489 #if PG_VERSION_NUM >= 90200
490 static void
491 onlineAnalyzeHookerUtility(Node *parsetree, const char *queryString,
492 #if PG_VERSION_NUM >= 90300
493                                                         ProcessUtilityContext context, ParamListInfo params,
494 #else
495                                                         ParamListInfo params, bool isTopLevel,
496 #endif
497                                                         DestReceiver *dest, char *completionTag) {
498         RangeVar        *tblname = NULL;
499
500         if (IsA(parsetree, CreateTableAsStmt) && ((CreateTableAsStmt*)parsetree)->into)
501                 tblname = (RangeVar*)copyObject(((CreateTableAsStmt*)parsetree)->into->rel);
502
503         if (oldProcessUtilityHook)
504                 oldProcessUtilityHook(parsetree, queryString, 
505 #if PG_VERSION_NUM >= 90300
506                                                           context, params,
507 #else
508                                                           params, isTopLevel,
509 #endif
510                                                           dest, completionTag);
511         else
512                 standard_ProcessUtility(parsetree, queryString, 
513 #if PG_VERSION_NUM >= 90300
514                                                                 context, params,
515 #else
516                                                                 params, isTopLevel,
517 #endif
518                                                                 dest, completionTag);
519
520         if (tblname) {
521                 Oid     tblOid = RangeVarGetRelid(tblname, NoLock, true);
522
523                 makeAnalyze(tblOid, CMD_INSERT, 0); 
524         }
525 }
526 #endif
527
528 void _PG_init(void);
529 void
530 _PG_init(void)
531 {
532         oldExecutorEndHook = ExecutorEnd_hook;
533
534         ExecutorEnd_hook = onlineAnalyzeHooker;
535
536 #if PG_VERSION_NUM >= 90200
537         oldProcessUtilityHook = ProcessUtility_hook;
538
539         ProcessUtility_hook = onlineAnalyzeHookerUtility;
540 #endif
541
542
543         DefineCustomBoolVariable(
544                 "online_analyze.enable",
545                 "Enable on-line analyze",
546                 "Enables analyze of table directly after insert/update/delete/select into",
547                 &online_analyze_enable,
548 #if PG_VERSION_NUM >= 80400
549                 online_analyze_enable,
550 #endif
551                 PGC_USERSET,
552 #if PG_VERSION_NUM >= 80400
553                 GUC_NOT_IN_SAMPLE,
554 #if PG_VERSION_NUM >= 90100
555                 NULL,
556 #endif
557 #endif
558                 NULL,
559                 NULL
560         );
561
562         DefineCustomBoolVariable(
563                 "online_analyze.verbose",
564                 "Verbosity of on-line analyze",
565                 "Make ANALYZE VERBOSE after table's changes",
566                 &online_analyze_verbose,
567 #if PG_VERSION_NUM >= 80400
568                 online_analyze_verbose,
569 #endif
570                 PGC_USERSET,
571 #if PG_VERSION_NUM >= 80400
572                 GUC_NOT_IN_SAMPLE,
573 #if PG_VERSION_NUM >= 90100
574                 NULL,
575 #endif
576 #endif
577                 NULL,
578                 NULL
579         );
580
581     DefineCustomRealVariable(
582                 "online_analyze.scale_factor",
583                 "fraction of table size to start on-line analyze",
584                 "fraction of table size to start on-line analyze",
585                 &online_analyze_scale_factor,
586 #if PG_VERSION_NUM >= 80400
587                 online_analyze_scale_factor,
588 #endif
589                 0.0,
590                 1.0,
591                 PGC_USERSET,
592 #if PG_VERSION_NUM >= 80400
593                 GUC_NOT_IN_SAMPLE,
594 #if PG_VERSION_NUM >= 90100
595                 NULL,
596 #endif
597 #endif
598                 NULL,
599                 NULL
600         );
601
602     DefineCustomIntVariable(
603                 "online_analyze.threshold",
604                 "min number of row updates before on-line analyze",
605                 "min number of row updates before on-line analyze",
606                 &online_analyze_threshold,
607 #if PG_VERSION_NUM >= 80400
608                 online_analyze_threshold,
609 #endif
610                 0,
611                 0x7fffffff,
612                 PGC_USERSET,
613 #if PG_VERSION_NUM >= 80400
614                 GUC_NOT_IN_SAMPLE,
615 #if PG_VERSION_NUM >= 90100
616                 NULL,
617 #endif
618 #endif
619                 NULL,
620                 NULL
621         );
622
623     DefineCustomRealVariable(
624                 "online_analyze.min_interval",
625                 "minimum time interval between analyze call (in milliseconds)",
626                 "minimum time interval between analyze call (in milliseconds)",
627                 &online_analyze_min_interval,
628 #if PG_VERSION_NUM >= 80400
629                 online_analyze_min_interval,
630 #endif
631                 0.0,
632                 1e30,
633                 PGC_USERSET,
634 #if PG_VERSION_NUM >= 80400
635                 GUC_NOT_IN_SAMPLE,
636 #if PG_VERSION_NUM >= 90100
637                 NULL,
638 #endif
639 #endif
640                 NULL,
641                 NULL
642         );
643
644         DefineCustomEnumVariable(
645                 "online_analyze.table_type",
646                 "Type(s) of table for online analyze: all(default), persistent, temporary, none",
647                 NULL,
648                 &online_analyze_table_type,
649 #if PG_VERSION_NUM >= 80400
650                 online_analyze_table_type,
651 #endif
652                 online_analyze_table_type_options,
653                 PGC_USERSET,
654 #if PG_VERSION_NUM >= 80400
655         GUC_NOT_IN_SAMPLE,
656 #if PG_VERSION_NUM >= 90100
657                 NULL,
658 #endif
659 #endif
660                 NULL,
661                 NULL
662         );
663
664     DefineCustomStringVariable(
665                 "online_analyze.exclude_tables",
666                 "List of tables which will not online analyze",
667                 NULL,
668                 &excludeTables.tableStr,
669 #if PG_VERSION_NUM >= 80400
670                 "",
671 #endif
672                 PGC_USERSET,
673                 0,
674 #if PG_VERSION_NUM >= 90100
675                 excludeTablesCheck,
676                 excludeTablesAssign,
677 #else
678                 excludeTablesAssign,
679 #endif
680                 excludeTablesShow
681         );
682
683     DefineCustomStringVariable(
684                 "online_analyze.include_tables",
685                 "List of tables which will online analyze",
686                 NULL,
687                 &includeTables.tableStr,
688 #if PG_VERSION_NUM >= 80400
689                 "",
690 #endif
691                 PGC_USERSET,
692                 0,
693 #if PG_VERSION_NUM >= 90100
694                 includeTablesCheck,
695                 includeTablesAssign,
696 #else
697                 includeTablesAssign,
698 #endif
699                 includeTablesShow
700         );
701 }
702
703 void _PG_fini(void);
704 void
705 _PG_fini(void)
706 {
707         ExecutorEnd_hook = oldExecutorEndHook;
708 #if PG_VERSION_NUM >= 90200
709         ProcessUtility_hook = oldProcessUtilityHook;
710 #endif
711
712         if (excludeTables.tables)
713                 free(excludeTables.tables);
714         if (includeTables.tables)
715                 free(includeTables.tables);
716
717         excludeTables.tables = includeTables.tables = NULL;
718         excludeTables.nTables = includeTables.nTables = 0;
719 }