monitoring sort activities

Hi folks,

I've created a patch to get following TODO item.

> * %Add ability to monitor the use of temporary sort files

This patch provides one system view (pg_stat_sorts) and
one utility function (pg_stat_get_prev_sort_size).

> snaga=# SELECT * from pg_stat_sorts;
>  heap_all | index_all | datum_all | heap_tape | datum_tape | max_size
> ----------+-----------+-----------+-----------+------------+----------
>         4 |         6 |         0 |         4 |          0 | 26400000
> (1 row)
>
> snaga=# select aid from accounts where aid % 2 = 1 order by filler
desc limit 10;
>
> [...snip...]
>
> (10 rows)
>
> snaga=# SELECT pg_stat_get_prev_sort_size();
>  pg_stat_get_prev_sort_size
> ----------------------------
>                    13200000
> (1 row)

Yeah, I don't forget previous discussion with Tom. We also have to
monitor temp files, but I think these view and function will benefit
DBAs on performance tuning.

Any comments or suggestion?
--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
diff -rc postgresql-8.1beta2.orig/src/backend/catalog/system_views.sql
postgresql-8.1beta2/src/backend/catalog/system_views.sql
*** postgresql-8.1beta2.orig/src/backend/catalog/system_views.sql    2005-09-24 11:29:38.000000000 +0900
--- postgresql-8.1beta2/src/backend/catalog/system_views.sql    2005-09-24 14:27:18.000000000 +0900
***************
*** 320,325 ****
--- 320,335 ----
      WHERE pg_stat_get_backend_dbid(S.backendid) = D.oid AND
              pg_stat_get_backend_userid(S.backendid) = U.oid;

+
+ CREATE VIEW pg_stat_sorts AS
+   SELECT pg_stat_get_sort_heap_all() as heap_all,
+          pg_stat_get_sort_index_all() as index_all,
+          pg_stat_get_sort_datum_all() as datum_all,
+          pg_stat_get_sort_heap_tape() as heap_tape,
+          pg_stat_get_sort_datum_tape() as datum_tape,
+          pg_stat_get_max_sort_size() as max_size;
+
+
  CREATE VIEW pg_stat_database AS
      SELECT
              D.oid AS datid,
diff -rc postgresql-8.1beta2.orig/src/backend/postmaster/pgstat.c postgresql-8.1beta2/src/backend/postmaster/pgstat.c
*** postgresql-8.1beta2.orig/src/backend/postmaster/pgstat.c    2005-09-24 11:29:39.000000000 +0900
--- postgresql-8.1beta2/src/backend/postmaster/pgstat.c    2005-09-24 14:42:09.000000000 +0900
***************
*** 120,125 ****
--- 120,132 ----

  static bool pgStatRunningInCollector = FALSE;

+ static long pgStatSortHeapAll   = 0;
+ static long pgStatSortIndexAll  = 0;
+ static long pgStatSortDatumAll  = 0;
+ static long pgStatSortHeapTape  = 0;
+ static long pgStatSortDatumTape = 0;
+ static Size pgStatSortMaxUsedSize = 0;
+
  /*
   * Place where backends store per-table info to be sent to the collector.
   * We store shared relations separately from non-shared ones, to be able to
***************
*** 823,828 ****
--- 830,843 ----
          return;
      }

+     elog(DEBUG1, "report: Sort statistics: [dbentry=%d]", MyDatabaseId);
+     elog(DEBUG1, "report: Sort statistics: heap(all)=%ld, index(all)=%ld, datum(all)=%ld",
+          pgStatSortHeapAll,pgStatSortIndexAll,pgStatSortDatumAll);
+     elog(DEBUG1, "report: Sort statistics: heap(tape)=%ld, datum(tape)=%ld",
+          pgStatSortHeapTape,pgStatSortDatumTape);
+     elog(DEBUG1, "report: Sort statistics: MaxSortSize=%ld",
+          pgStatSortMaxUsedSize);
+
      /*
       * For each message buffer used during the last query set the header
       * fields and send it out.
***************
*** 839,847 ****
--- 854,877 ----

          tsmsg->m_xact_commit = pgStatXactCommit;
          tsmsg->m_xact_rollback = pgStatXactRollback;
+
          pgStatXactCommit = 0;
          pgStatXactRollback = 0;

+         tsmsg->m_sort_heap_all   = pgStatSortHeapAll;
+         tsmsg->m_sort_index_all  = pgStatSortIndexAll;
+         tsmsg->m_sort_datum_all  = pgStatSortDatumAll;
+         tsmsg->m_sort_heap_tape  = pgStatSortHeapTape;
+         tsmsg->m_sort_datum_tape = pgStatSortDatumTape;
+         tsmsg->m_sort_max_used_size = pgStatSortMaxUsedSize;
+
+         pgStatSortHeapAll   = 0;
+         pgStatSortIndexAll  = 0;
+         pgStatSortDatumAll  = 0;
+         pgStatSortHeapTape  = 0;
+         pgStatSortDatumTape = 0;
+         pgStatSortMaxUsedSize = 0;
+
          pgstat_setheader(&tsmsg->m_hdr, PGSTAT_MTYPE_TABSTAT);
          tsmsg->m_databaseid = MyDatabaseId;
          pgstat_send(tsmsg, len);
***************
*** 2218,2223 ****
--- 2248,2260 ----
          result->destroy = 0;
          result->last_autovac_time = 0;

+         result->n_sort_heap_all   = 0;
+         result->n_sort_index_all  = 0;
+         result->n_sort_datum_all  = 0;
+         result->n_sort_heap_tape  = 0;
+         result->n_sort_datum_tape = 0;
+         result->sort_max_used_size = 0;
+
          memset(&hash_ctl, 0, sizeof(hash_ctl));
          hash_ctl.keysize = sizeof(Oid);
          hash_ctl.entrysize = sizeof(PgStat_StatTabEntry);
***************
*** 3050,3055 ****
--- 3087,3101 ----
      dbentry->n_xact_commit += (PgStat_Counter) (msg->m_xact_commit);
      dbentry->n_xact_rollback += (PgStat_Counter) (msg->m_xact_rollback);

+     dbentry->n_sort_heap_all   += (PgStat_Counter) (msg->m_sort_heap_all);
+     dbentry->n_sort_index_all  += (PgStat_Counter) (msg->m_sort_index_all);
+     dbentry->n_sort_datum_all  += (PgStat_Counter) (msg->m_sort_datum_all);
+     dbentry->n_sort_heap_tape  += (PgStat_Counter) (msg->m_sort_heap_tape);
+     dbentry->n_sort_datum_tape += (PgStat_Counter) (msg->m_sort_datum_tape);
+
+     if ( dbentry->sort_max_used_size < msg->m_sort_max_used_size )
+         dbentry->sort_max_used_size = (Size)msg->m_sort_max_used_size;
+
      /*
       * Process all table entries in the message.
       */
***************
*** 3229,3234 ****
--- 3275,3287 ----
      dbentry->n_blocks_hit = 0;
      dbentry->destroy = 0;

+     dbentry->n_sort_heap_all   = 0;
+     dbentry->n_sort_index_all  = 0;
+     dbentry->n_sort_datum_all  = 0;
+     dbentry->n_sort_heap_tape  = 0;
+     dbentry->n_sort_datum_tape = 0;
+     dbentry->sort_max_used_size = 0;
+
      memset(&hash_ctl, 0, sizeof(hash_ctl));
      hash_ctl.keysize = sizeof(Oid);
      hash_ctl.entrysize = sizeof(PgStat_StatTabEntry);
***************
*** 3238,3240 ****
--- 3291,3344 ----
                                    &hash_ctl,
                                    HASH_ELEM | HASH_FUNCTION);
  }
+
+
+ void
+ pgstat_count_sort_heap_all()
+ {
+     pgStatSortHeapAll++;
+
+     elog(DEBUG5, "pgstat_count_sort_heap_all(): %d", pgStatSortHeapAll);
+ }
+
+ void
+ pgstat_count_sort_index_all()
+ {
+     pgStatSortIndexAll++;
+
+     elog(DEBUG5, "pgstat_count_sort_index_all(): %d", pgStatSortIndexAll);
+ }
+
+ void
+ pgstat_count_sort_datum_all()
+ {
+     pgStatSortDatumAll++;
+
+     elog(DEBUG5, "pgstat_count_sort_datum_all(): %d", pgStatSortDatumAll);
+ }
+
+ void
+ pgstat_count_sort_heap_tape()
+ {
+     pgStatSortHeapTape++;
+
+     elog(DEBUG5, "pgstat_count_sort_heap_tape(): %d", pgStatSortHeapTape);
+ }
+
+ void
+ pgstat_count_sort_datum_tape()
+ {
+     pgStatSortDatumTape++;
+
+     elog(DEBUG5, "pgstat_count_sort_datum_tape(): %d", pgStatSortDatumTape);
+ }
+
+ void
+ pgstat_count_sort_max_used_size(Size size)
+ {
+     if ( size>pgStatSortMaxUsedSize )
+         pgStatSortMaxUsedSize = size;
+
+     elog(DEBUG5, "pgstat_count_sort_max_used_size(): %ld",
+          pgStatSortMaxUsedSize);
+ }
diff -rc postgresql-8.1beta2.orig/src/backend/utils/adt/pgstatfuncs.c
postgresql-8.1beta2/src/backend/utils/adt/pgstatfuncs.c
*** postgresql-8.1beta2.orig/src/backend/utils/adt/pgstatfuncs.c    2005-09-24 11:29:42.000000000 +0900
--- postgresql-8.1beta2/src/backend/utils/adt/pgstatfuncs.c    2005-09-24 14:36:27.000000000 +0900
***************
*** 53,58 ****
--- 53,81 ----
  extern Datum pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS);

+ typedef enum SORT_TYPE {
+     HEAP_ALL,
+     INDEX_ALL,
+     DATUM_ALL,
+     HEAP_TAPE,
+     DATUM_TAPE
+ } SORT_TYPE;
+
+ PG_FUNCTION_INFO_V1(pg_stat_get_sort_heap_all);
+ PG_FUNCTION_INFO_V1(pg_stat_get_sort_index_all);
+ PG_FUNCTION_INFO_V1(pg_stat_get_sort_datum_all);
+ PG_FUNCTION_INFO_V1(pg_stat_get_sort_heap_tape);
+ PG_FUNCTION_INFO_V1(pg_stat_get_sort_datum_tape);
+ PG_FUNCTION_INFO_V1(pg_stat_get_max_sort_size);
+ PG_FUNCTION_INFO_V1(pg_stat_get_prev_sort_size);
+
+ extern Datum pg_stat_get_sort_heap_all(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_sort_index_all(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_sort_datum_all(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_sort_heap_tape(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_sort_datum_tape(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_max_sort_size(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_prev_sort_size(PG_FUNCTION_ARGS);

  Datum
  pg_stat_get_numscans(PG_FUNCTION_ARGS)
***************
*** 562,564 ****
--- 585,693 ----

      PG_RETURN_INT64(result);
  }
+
+
+ static int64
+ get_sort_stat_internal(SORT_TYPE type)
+ {
+     PgStat_StatDBEntry *dbentry;
+     int64           result = 0;
+
+     Oid dbid = MyDatabaseId;
+
+     if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+         result = -1;
+     else
+         switch (type)
+         {
+             case HEAP_ALL:
+                 result = (int64) (dbentry->n_sort_heap_all);
+                 break;
+             case INDEX_ALL:
+                 result = (int64) (dbentry->n_sort_index_all);
+                 break;
+             case DATUM_ALL:
+                 result = (int64) (dbentry->n_sort_datum_all);
+                 break;
+             case HEAP_TAPE:
+                 result = (int64) (dbentry->n_sort_heap_tape);
+                 break;
+             case DATUM_TAPE:
+                 result = (int64) (dbentry->n_sort_datum_tape);
+                 break;
+             default:
+                 elog(ERROR, "get_sort_stat_internal: unexpected type.");
+                 break;
+         }
+
+     if ( dbentry )
+     {
+             elog(DEBUG5, "get_sort_stat_internal(): [dbentry=%d]", dbentry->databaseid);
+             elog(DEBUG5, "get_sort_stat_internal(): heap(all)=%ld, index(all)=%ld, datum(all)=%ld",
+                  dbentry->n_sort_heap_all,
+                  dbentry->n_sort_index_all,
+                  dbentry->n_sort_datum_all);
+             elog(DEBUG5, "get_sort_stat_internal(): heap(tape)=%ld, datum(tape)=%ld",
+                  dbentry->n_sort_heap_tape,
+                  dbentry->n_sort_datum_tape);
+             elog(DEBUG5, "get_sort_stat_internal(): MaxSortSize=%ld",
+                  dbentry->sort_max_used_size);
+     }
+
+     return result;
+ }
+
+
+ Datum
+ pg_stat_get_sort_heap_all(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_INT64(get_sort_stat_internal(HEAP_ALL));
+ }
+
+ Datum
+ pg_stat_get_sort_index_all(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_INT64(get_sort_stat_internal(INDEX_ALL));
+ }
+
+ Datum
+ pg_stat_get_sort_datum_all(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_INT64(get_sort_stat_internal(DATUM_ALL));
+ }
+
+ Datum
+ pg_stat_get_sort_heap_tape(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_INT64(get_sort_stat_internal(HEAP_TAPE));
+ }
+
+ Datum
+ pg_stat_get_sort_datum_tape(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_INT64(get_sort_stat_internal(DATUM_TAPE));
+ }
+
+ Datum
+ pg_stat_get_max_sort_size(PG_FUNCTION_ARGS)
+ {
+     PgStat_StatDBEntry *dbentry;
+     Oid dbid;
+     int64           result;
+
+ //    dbid = PG_GETARG_OID(0);
+     dbid = MyDatabaseId;
+
+     if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+         result = -1;
+     else
+         result = (int64) (dbentry->sort_max_used_size);
+
+     PG_RETURN_INT64(result);
+ }
+
+ Datum
+ pg_stat_get_prev_sort_size(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_INT64(tuplesort_get_prev_size());
+ }
diff -rc postgresql-8.1beta2.orig/src/backend/utils/sort/tuplesort.c
postgresql-8.1beta2/src/backend/utils/sort/tuplesort.c
*** postgresql-8.1beta2.orig/src/backend/utils/sort/tuplesort.c    2005-09-24 11:29:46.000000000 +0900
--- postgresql-8.1beta2/src/backend/utils/sort/tuplesort.c    2005-09-24 14:35:27.000000000 +0900
***************
*** 283,288 ****
--- 283,290 ----
      /* we need typelen and byval in order to know how to copy the Datums. */
      int            datumTypeLen;
      bool        datumTypeByVal;
+
+     Size        usedSize;
  };

  #define COMPARETUP(state,a,b)    ((*(state)->comparetup) (state, a, b))
***************
*** 395,400 ****
--- 397,404 ----
  static Tuplesortstate *qsort_tuplesortstate;


+ static Size prevSortSize = 0;
+
  /*
   *        tuplesort_begin_xxx
   *
***************
*** 442,447 ****
--- 446,453 ----
      state->result_tape = -1;    /* flag that result tape has not been
                                   * formed */

+     state->usedSize = 0;
+
      return state;
  }

***************
*** 489,494 ****
--- 495,502 ----
                      (Datum) 0);
      }

+     pgstat_count_sort_heap_all();
+
      return state;
  }

***************
*** 509,514 ****
--- 517,524 ----
      state->indexScanKey = _bt_mkscankey_nodata(indexRel);
      state->enforceUnique = enforceUnique;

+     pgstat_count_sort_index_all();
+
      return state;
  }

***************
*** 540,545 ****
--- 550,557 ----
      state->datumTypeLen = typlen;
      state->datumTypeByVal = typbyval;

+     pgstat_count_sort_datum_all();
+
      return state;
  }

***************
*** 553,558 ****
--- 565,577 ----
  {
      int            i;

+     pgstat_count_sort_max_used_size(state->usedSize);
+
+     elog(DEBUG5, "tuplesort_end: usedSize=%ld, tape=%d",
+          state->usedSize, (state->tapeset ? 1 : 0));
+
+     prevSortSize = state->usedSize;
+
      if (state->tapeset)
          LogicalTapeSetClose(state->tapeset);
      if (state->memtuples)
***************
*** 1027,1032 ****
--- 1046,1057 ----
      state->Level = 1;
      state->destTape = 0;

+     /* If not a datum sort, it's a heap sort  */
+     if ( state->datumType == InvalidOid )
+         pgstat_count_sort_heap_tape();
+     else
+         pgstat_count_sort_datum_tape();
+
      state->status = TSS_BUILDRUNS;
  }

***************
*** 1952,1957 ****
--- 1977,1983 ----

      tuple = heap_copytuple(tuple);
      USEMEM(state, GetMemoryChunkSpace(tuple));
+     state->usedSize += GetMemoryChunkSpace(tuple);
      return (void *) tuple;
  }

***************
*** 2111,2116 ****
--- 2137,2143 ----

      newtuple = (IndexTuple) palloc(tuplen);
      USEMEM(state, GetMemoryChunkSpace(newtuple));
+     state->usedSize += GetMemoryChunkSpace(newtuple);

      memcpy(newtuple, tuple, tuplen);

***************
*** 2228,2230 ****
--- 2255,2264 ----
                                       MAXALIGN(sizeof(DatumTuple)));
      return (void *) tuple;
  }
+
+ Size
+ tuplesort_get_prev_size()
+ {
+     return prevSortSize;
+ }
+
diff -rc postgresql-8.1beta2.orig/src/include/catalog/pg_proc.h postgresql-8.1beta2/src/include/catalog/pg_proc.h
*** postgresql-8.1beta2.orig/src/include/catalog/pg_proc.h    2005-09-24 11:29:48.000000000 +0900
--- postgresql-8.1beta2/src/include/catalog/pg_proc.h    2005-09-24 14:32:59.000000000 +0900
***************
*** 2863,2868 ****
--- 2863,2883 ----
  DATA(insert OID = 1945 (  pg_stat_get_db_blocks_hit        PGNSP PGUID 12 f f t f s 1 20 "26" _null_ _null_ _null_
pg_stat_get_db_blocks_hit- _null_ )); 
  DESCR("Statistics: Blocks found in cache for database");

+ DATA(insert OID = 1971 (  pg_stat_get_sort_heap_all PGNSP PGUID 12 f f t f s 0 20 "" _null_ _null_ _null_
pg_stat_get_sort_heap_all- _null_ )); 
+ DESCR("Statistics: pg_stat_get_sort_heap_all");
+ DATA(insert OID = 1972 (  pg_stat_get_sort_index_all PGNSP PGUID 12 f f t f s 0 20 "" _null_ _null_ _null_
pg_stat_get_sort_index_all- _null_ )); 
+ DESCR("Statistics: pg_stat_get_sort_index_all");
+ DATA(insert OID = 1973 (  pg_stat_get_sort_datum_all PGNSP PGUID 12 f f t f s 0 20 "" _null_ _null_ _null_
pg_stat_get_sort_datum_all- _null_ )); 
+ DESCR("Statistics: pg_stat_get_sort_datum_all");
+ DATA(insert OID = 1974 (  pg_stat_get_sort_heap_tape PGNSP PGUID 12 f f t f s 0 20 "" _null_ _null_ _null_
pg_stat_get_sort_heap_tape- _null_ )); 
+ DESCR("Statistics: pg_stat_get_sort_heap_tape");
+ DATA(insert OID = 1975 (  pg_stat_get_sort_datum_tape PGNSP PGUID 12 f f t f s 0 20 "" _null_ _null_ _null_
pg_stat_get_sort_datum_tape- _null_ )); 
+ DESCR("Statistics: pg_stat_get_sort_datum_tape");
+ DATA(insert OID = 1976 (  pg_stat_get_max_sort_size PGNSP PGUID 12 f f t f s 0 20 "" _null_ _null_ _null_
pg_stat_get_max_sort_size- _null_ )); 
+ DESCR("Statistics: pg_stat_get_prev_sort_size");
+ DATA(insert OID = 1977 (  pg_stat_get_prev_sort_size PGNSP PGUID 12 f f t f s 0 20 "" _null_ _null_ _null_
pg_stat_get_prev_sort_size- _null_ )); 
+ DESCR("Statistics: pg_stat_get_prev_sort_size");
+
  DATA(insert OID = 1946 (  encode                        PGNSP PGUID 12 f f t f i 2 25 "17 25" _null_ _null_ _null_
binary_encode- _null_ )); 
  DESCR("Convert bytea value into some ascii-only text string");
  DATA(insert OID = 1947 (  decode                        PGNSP PGUID 12 f f t f i 2 17 "25 25" _null_ _null_ _null_
binary_decode- _null_ )); 
diff -rc postgresql-8.1beta2.orig/src/include/pgstat.h postgresql-8.1beta2/src/include/pgstat.h
*** postgresql-8.1beta2.orig/src/include/pgstat.h    2005-09-24 11:29:48.000000000 +0900
--- postgresql-8.1beta2/src/include/pgstat.h    2005-09-24 13:49:38.000000000 +0900
***************
*** 189,194 ****
--- 189,203 ----
      int            m_nentries;
      int            m_xact_commit;
      int            m_xact_rollback;
+
+     int            m_sort_heap_all;
+     int            m_sort_index_all;
+     int            m_sort_datum_all;
+     int            m_sort_heap_tape;
+     int            m_sort_datum_tape;
+
+     Size        m_sort_max_used_size;
+
      PgStat_TableEntry m_entry[PGSTAT_NUM_TABENTRIES];
  } PgStat_MsgTabstat;

***************
*** 276,281 ****
--- 285,299 ----
      PgStat_Counter n_xact_rollback;
      PgStat_Counter n_blocks_fetched;
      PgStat_Counter n_blocks_hit;
+
+     PgStat_Counter n_sort_heap_all;
+     PgStat_Counter n_sort_index_all;
+     PgStat_Counter n_sort_datum_all;
+     PgStat_Counter n_sort_heap_tape;
+     PgStat_Counter n_sort_datum_tape;
+
+     Size           sort_max_used_size;
+
      int            destroy;
      TimestampTz    last_autovac_time;
  } PgStat_StatDBEntry;
***************
*** 492,495 ****
--- 510,521 ----
  extern PgStat_StatBeEntry *pgstat_fetch_stat_beentry(int beid);
  extern int    pgstat_fetch_stat_numbackends(void);

+
+ extern void pgstat_count_sort_heap_all(void);
+ extern void pgstat_count_sort_index_all(void);
+ extern void pgstat_count_sort_datum_all(void);
+ extern void pgstat_count_sort_heap_tape(void);
+ extern void pgstat_count_sort_datum_tape(void);
+ extern void pgstat_count_sort_max_used_size(Size size);
+
  #endif   /* PGSTAT_H */
diff -rc postgresql-8.1beta2.orig/src/include/utils/tuplesort.h postgresql-8.1beta2/src/include/utils/tuplesort.h
*** postgresql-8.1beta2.orig/src/include/utils/tuplesort.h    2005-09-24 11:29:48.000000000 +0900
--- postgresql-8.1beta2/src/include/utils/tuplesort.h    2005-09-24 14:36:07.000000000 +0900
***************
*** 102,105 ****
--- 102,107 ----
                    Datum datum1, bool isNull1,
                    Datum datum2, bool isNull2);

+ extern Size tuplesort_get_prev_size();
+
  #endif   /* TUPLESORT_H */