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 <[EMAIL PROTECTED]>
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 */
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
