Hi Rajeev,
(2014/01/29 17:31), Rajeev rastogi wrote:
No Issue, you can share me the test cases, I will take the performance report.
Attached patch is supported to latest pg_stat_statements. It includes min, max,
and stdev statistics. Could you run compiling test on your windows enviroments?
I think compiling error was fixed.
We had disscuttion about which is needed useful statistics in community, I think
both of statistics have storong and weak point. When we see the less(2 or 3)
executed statement, stdev will be meaningless because it cannot calculate
estimated value precisely very much, however in this situation, min and max will
be propety work well because it isn't estimated value but fact value. On the
other hand, when we see the more frequency executed statement, they will be
contrary position
statistics, stdev will be very useful statistics for estimating whole statements,
and min and max might be extremely value.
At the end of the day, these value were needed each other for more useful
statistics when we want to see several actual statments. And past my experience
showed no performance problems in this patch. So I'd like to implements all these
values in pg_stat_statements.
Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center
*** a/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql
--- b/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql
***************
*** 19,24 **** CREATE FUNCTION pg_stat_statements(IN showtext boolean,
--- 19,27 ----
OUT query text,
OUT calls int8,
OUT total_time float8,
+ OUT min_time float8,
+ OUT max_time float8,
+ OUT stdev_time float8,
OUT rows int8,
OUT shared_blks_hit int8,
OUT shared_blks_read int8,
***************
*** 41,43 **** CREATE VIEW pg_stat_statements AS
--- 44,51 ----
SELECT * FROM pg_stat_statements(true);
GRANT SELECT ON pg_stat_statements TO PUBLIC;
+
+ CREATE FUNCTION pg_stat_statements_reset_time()
+ RETURNS void
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C;
*** a/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
--- b/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
***************
*** 9,14 **** RETURNS void
--- 9,19 ----
AS 'MODULE_PATHNAME'
LANGUAGE C;
+ CREATE FUNCTION pg_stat_statements_reset_time()
+ RETURNS void
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C;
+
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
OUT userid oid,
OUT dbid oid,
***************
*** 16,21 **** CREATE FUNCTION pg_stat_statements(IN showtext boolean,
--- 21,29 ----
OUT query text,
OUT calls int8,
OUT total_time float8,
+ OUT min_time float8,
+ OUT max_time float8,
+ OUT stdev_time float8,
OUT rows int8,
OUT shared_blks_hit int8,
OUT shared_blks_read int8,
***************
*** 42,44 **** GRANT SELECT ON pg_stat_statements TO PUBLIC;
--- 50,53 ----
-- Don't want this to be available to non-superusers.
REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;
+ REVOKE ALL ON FUNCTION pg_stat_statements_reset_time() FROM PUBLIC;
*** a/contrib/pg_stat_statements/pg_stat_statements--unpackaged--1.0.sql
--- b/contrib/pg_stat_statements/pg_stat_statements--unpackaged--1.0.sql
***************
*** 4,8 ****
--- 4,9 ----
\echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit
ALTER EXTENSION pg_stat_statements ADD function pg_stat_statements_reset();
+ ALTER EXTENSION pg_stat_statements ADD function pg_stat_statements_reset_time();
ALTER EXTENSION pg_stat_statements ADD function pg_stat_statements();
ALTER EXTENSION pg_stat_statements ADD view pg_stat_statements;
*** a/contrib/pg_stat_statements/pg_stat_statements.c
--- b/contrib/pg_stat_statements/pg_stat_statements.c
***************
*** 106,111 **** static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
--- 106,113 ----
#define USAGE_DECREASE_FACTOR (0.99) /* decreased every entry_dealloc */
#define STICKY_DECREASE_FACTOR (0.50) /* factor for sticky entries */
#define USAGE_DEALLOC_PERCENT 5 /* free this % of entries at once */
+ #define EXEC_TIME_INIT_MIN DBL_MAX /* initial execution min time */
+ #define EXEC_TIME_INIT_MAX -DBL_MAX /* initial execution max time */
#define JUMBLE_SIZE 1024 /* query serialization buffer size */
***************
*** 137,142 **** typedef struct Counters
--- 139,147 ----
{
int64 calls; /* # of times executed */
double total_time; /* total execution time, in msec */
+ double total_sqtime; /* cumulated square execution time, in msec */
+ double min_time; /* maximum execution time, in msec */
+ double max_time; /* minimum execution time, in msec */
int64 rows; /* total # of retrieved or affected rows */
int64 shared_blks_hit; /* # of shared buffer hits */
int64 shared_blks_read; /* # of shared disk blocks read */
***************
*** 274,283 **** void _PG_init(void);
--- 279,290 ----
void _PG_fini(void);
Datum pg_stat_statements_reset(PG_FUNCTION_ARGS);
+ Datum pg_stat_statements_reset_time(PG_FUNCTION_ARGS);
Datum pg_stat_statements_1_2(PG_FUNCTION_ARGS);
Datum pg_stat_statements(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
+ PG_FUNCTION_INFO_V1(pg_stat_statements_reset_time);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
PG_FUNCTION_INFO_V1(pg_stat_statements);
***************
*** 315,320 **** static char *qtext_fetch(Size query_offset, int query_len,
--- 322,328 ----
static bool need_gc_qtexts(void);
static void gc_qtexts(void);
static void entry_reset(void);
+ static void entry_reset_time(void);
static void AppendJumble(pgssJumbleState *jstate,
const unsigned char *item, Size size);
static void JumbleQuery(pgssJumbleState *jstate, Query *query);
***************
*** 325,331 **** static char *generate_normalized_query(pgssJumbleState *jstate, const char *quer
int *query_len_p, int encoding);
static void fill_in_constant_lengths(pgssJumbleState *jstate, const char *query);
static int comp_location(const void *a, const void *b);
!
/*
* Module load callback
--- 333,339 ----
int *query_len_p, int encoding);
static void fill_in_constant_lengths(pgssJumbleState *jstate, const char *query);
static int comp_location(const void *a, const void *b);
! static double sqrtd(const double x);
/*
* Module load callback
***************
*** 1107,1112 **** pgss_store(const char *query, uint32 queryId,
--- 1115,1123 ----
char *norm_query = NULL;
int encoding = GetDatabaseEncoding();
int query_len;
+ double total_sqtime = total_time * total_time;
+ double min_time;
+ double max_time;
Assert(query != NULL);
***************
*** 1200,1211 **** pgss_store(const char *query, uint32 queryId,
--- 1211,1235 ----
SpinLockAcquire(&e->mutex);
+ /* caluculate min and max time */
+ if (e->counters.min_time > total_time)
+ min_time = total_time;
+ else
+ min_time = total_time;
+ if (e->counters.max_time < total_time)
+ max_time = total_time;
+ else
+ max_time = e->counters.max_time;
+
/* "Unstick" entry if it was previously sticky */
if (e->counters.calls == 0)
e->counters.usage = USAGE_INIT;
e->counters.calls += 1;
e->counters.total_time += total_time;
+ e->counters.total_sqtime += total_sqtime;
+ e->counters.min_time = min_time;
+ e->counters.max_time = max_time;
e->counters.rows += rows;
e->counters.shared_blks_hit += bufusage->shared_blks_hit;
e->counters.shared_blks_read += bufusage->shared_blks_read;
***************
*** 1246,1256 **** pg_stat_statements_reset(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
/* Number of output arguments (columns) for various API versions */
#define PG_STAT_STATEMENTS_COLS_V1_0 14
#define PG_STAT_STATEMENTS_COLS_V1_1 18
! #define PG_STAT_STATEMENTS_COLS_V1_2 19
! #define PG_STAT_STATEMENTS_COLS 19 /* maximum of above */
/*
* Retrieve statement statistics.
--- 1270,1295 ----
PG_RETURN_VOID();
}
+ /*
+ * Reset min/max time statement statistics.
+ */
+ Datum
+ pg_stat_statements_reset_time(PG_FUNCTION_ARGS)
+ {
+ if (!pgss || !pgss_hash)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("pg_stat_statements must be loaded via shared_preload_libraries")));
+
+ entry_reset_time();
+ PG_RETURN_VOID();
+ }
+
/* Number of output arguments (columns) for various API versions */
#define PG_STAT_STATEMENTS_COLS_V1_0 14
#define PG_STAT_STATEMENTS_COLS_V1_1 18
! #define PG_STAT_STATEMENTS_COLS_V1_2 22
! #define PG_STAT_STATEMENTS_COLS 22 /* maximum of above */
/*
* Retrieve statement statistics.
***************
*** 1435,1440 **** pg_stat_statements_internal(FunctionCallInfo fcinfo,
--- 1474,1482 ----
int i = 0;
Counters tmp;
int64 queryid = entry->key.queryid;
+ double avtime;
+ double sqtime;
+ double stdev;
memset(values, 0, sizeof(values));
memset(nulls, 0, sizeof(nulls));
***************
*** 1512,1517 **** pg_stat_statements_internal(FunctionCallInfo fcinfo,
--- 1554,1574 ----
values[i++] = Int64GetDatumFast(tmp.calls);
values[i++] = Float8GetDatumFast(tmp.total_time);
+ if (api_version >= PGSS_V1_2)
+ {
+ if (tmp.min_time == EXEC_TIME_INIT_MIN)
+ nulls[i++] = true;
+ else
+ values[i++] = Float8GetDatumFast(tmp.min_time);
+ if (tmp.max_time == EXEC_TIME_INIT_MAX)
+ nulls[i++] = true;
+ else
+ values[i++] = Float8GetDatumFast(tmp.max_time);
+ avtime = tmp.total_time / tmp.calls;
+ sqtime = tmp.total_sqtime / tmp.calls;
+ stdev = (sqrtd(sqtime - avtime * avtime));
+ values[i++] = Float8GetDatumFast(stdev);
+ }
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
***************
*** 1597,1602 **** entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding,
--- 1654,1663 ----
{
/* New entry, initialize it */
+ /* set the appropriate initial max/min execution time */
+ entry->counters.min_time = EXEC_TIME_INIT_MIN;
+ entry->counters.max_time = EXEC_TIME_INIT_MAX;
+
/* reset the statistics */
memset(&entry->counters, 0, sizeof(Counters));
/* set the appropriate initial usage count */
***************
*** 2125,2130 **** done:
--- 2186,2211 ----
LWLockRelease(pgss->lock);
}
+ /*
+ * Reset min/max time values of all entries.
+ */
+ static void
+ entry_reset_time(void)
+ {
+ HASH_SEQ_STATUS hash_seq;
+ pgssEntry *entry;
+
+ LWLockAcquire(pgss->lock, LW_EXCLUSIVE);
+
+ hash_seq_init(&hash_seq, pgss_hash);
+ while ((entry = hash_seq_search(&hash_seq)) != NULL)
+ {
+ entry->counters.min_time = EXEC_TIME_INIT_MIN;
+ entry->counters.max_time = EXEC_TIME_INIT_MAX;
+ }
+
+ LWLockRelease(pgss->lock);
+ }
/*
* AppendJumble: Append a value that is substantive in a given query to
***************
*** 2888,2890 **** comp_location(const void *a, const void *b)
--- 2969,2988 ----
else
return 0;
}
+
+ /*
+ * fast sqrt algorithm: reference from Fast inverse square root algorithms.
+ */
+ static inline double
+ sqrtd(const double x)
+ {
+ double x_half = 0.5 * x;
+ long long int tmp = 0x5FE6EB50C7B537AAl - ( *(long long int*)&x >> 1);
+ double x_result = * (double*)&tmp;
+
+ x_result *= (1.5 - (x_half * x_result * x_result));
+ /* If retry this calculation, it becomes higher precision at sqrt */
+ x_result *= (1.5 - (x_half * x_result * x_result));
+
+ return x_result * x;
+ }
*** a/doc/src/sgml/pgstatstatements.sgml
--- b/doc/src/sgml/pgstatstatements.sgml
***************
*** 83,92 ****
<entry><structfield>total_time</structfield></entry>
<entry><type>double precision</type></entry>
<entry></entry>
! <entry>Total time spent in the statement, in milliseconds</entry>
</row>
<row>
<entry><structfield>rows</structfield></entry>
<entry><type>bigint</type></entry>
<entry></entry>
--- 83,113 ----
<entry><structfield>total_time</structfield></entry>
<entry><type>double precision</type></entry>
<entry></entry>
! <entry>Total execution time spent in the statement, in milliseconds</entry>
</row>
<row>
+ <entry><structfield>min_time</structfield></entry>
+ <entry><type>double precision</type></entry>
+ <entry></entry>
+ <entry>Minimum execution time spent in the statement, in milliseconds</entry>
+ </row>
+
+ <row>
+ <entry><structfield>max_time</structfield></entry>
+ <entry><type>double precision</type></entry>
+ <entry></entry>
+ <entry>Maximum execution time spent in the statement, in milliseconds</entry>
+ </row>
+
+ <row>
+ <entry><structfield>stddev_time</structfield></entry>
+ <entry><type>double precision</type></entry>
+ <entry></entry>
+ <entry>Standard deviation of the execution time spent in the statement</entry>
+ </row>
+
+ <row>
<entry><structfield>rows</structfield></entry>
<entry><type>bigint</type></entry>
<entry></entry>
***************
*** 298,303 ****
--- 319,342 ----
<varlistentry>
<indexterm>
+ <primary>pg_stat_statements_reset_time</primary>
+ </indexterm>
+
+ <term>
+ <function>pg_stat_statements_reset_time() returns void</function>
+ </term>
+
+ <listitem>
+ <para>
+ <function>pg_stat_statements_reset_time</function> inits statistics of
+ the min_time and max_time gathered so far by <filename>pg_stat_statements</>.
+ By default, this function can only be executed by superusers.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <indexterm>
<primary>pg_stat_statements</primary>
<secondary>function</secondary>
</indexterm>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
