(2014/01/23 10:28), Peter Geoghegan wrote:
On Wed, Jan 22, 2014 at 5:28 PM, KONDO Mitsumasa
<kondo.mitsum...@lab.ntt.co.jp> wrote:
Oh, thanks to inform me. I think essential problem of my patch has bottle
neck in sqrt() function and other division caluculation.
Well, that's a pretty easy theory to test. Just stop calling them (and
do something similar to what we do for current counter fields instead)
and see how much difference it makes.
What means "calling them"? I think that part of heavy you think is
pg_stat_statement view that is called by select query, not a part of LWLock
getting statistic by hook. Right?
I tested my patch in pgbench, but I cannot find bottleneck of my latest patch.
(Sorry, I haven't been test select query in pg_stat_statement view...) Here is a
test result.
* Result (Test result is represented by tps.)
method | try1 | try2 | try3
--------------------------------------------
without pgss | 130938 | 131558 | 131796
with pgss | 125164 | 125146 | 125358
with patched pgss| 126091 | 126681 | 126433
* Test Setting
shared_buffers=1024MB
checkpoint_segments = 300
checkpoint_timeout = 15min
checkpoint_completion_target = 0.7
* pgbench script
pgbench -h xxx.xxx.xxx.xxx mitsu-ko -i -s 100
psql -h xxx.xxx.xxx.xxx mitsu-ko -c 'CHECKPOINT'
pgbench -h xxx.xxx.xxx.xxx mitsu-ko -c64 -j32 -S -T 180
pgbench -h xxx.xxx.xxx.xxx mitsu-ko -c64 -j32 -S -T 180
pgbench -h xxx.xxx.xxx.xxx mitsu-ko -c64 -j32 -S -T 180
* Server SPEC:
CPU: Xeon E5-2670 1P/8C 2.6GHz #We don't have 32 core cpu...
Memory: 24GB
RAID: i420 2GB cache
Disk: 15K * 6 (RAID 1+0)
Attached is latest developping patch. It hasn't been test much yet, but sqrt
caluclation may be faster.
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(
--- 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,52 ----
SELECT * FROM pg_stat_statements();
GRANT SELECT ON pg_stat_statements TO PUBLIC;
+
+ /* New Function */
+ 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(
OUT userid oid,
OUT dbid oid,
***************
*** 16,21 **** CREATE FUNCTION pg_stat_statements(
--- 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
***************
*** 78,84 **** static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
#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 JUMBLE_SIZE 1024 /* query serialization buffer size */
/*
--- 78,85 ----
#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 */
/*
***************
*** 114,119 **** typedef struct Counters
--- 115,123 ----
{
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 */
***************
*** 237,245 **** void _PG_init(void);
--- 241,251 ----
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(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);
static void pgss_shmem_startup(void);
***************
*** 266,271 **** static pgssEntry *entry_alloc(pgssHashKey *key, const char *query,
--- 272,278 ----
int query_len, bool sticky);
static void entry_dealloc(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);
***************
*** 276,282 **** 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
--- 283,289 ----
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
***************
*** 964,969 **** pgss_store(const char *query, uint32 queryId,
--- 971,979 ----
const BufferUsage *bufusage,
pgssJumbleState *jstate)
{
+ double total_sqtime = total_time * total_time;
+ double min_time;
+ double max_time;
pgssHashKey key;
pgssEntry *entry;
char *norm_query = NULL;
***************
*** 1040,1051 **** pgss_store(const char *query, uint32 queryId,
--- 1050,1074 ----
SpinLockAcquire(&e->mutex);
+ /* calculate min and max time */
+ if (e->counters.max_time < total_time)
+ max_time = total_time;
+ else
+ max_time = e->counters.max_time;
+ if (e->counters.min_time > total_time)
+ min_time = total_time;
+ else
+ min_time = e->counters.min_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;
***************
*** 1085,1093 **** pg_stat_statements_reset(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
#define PG_STAT_STATEMENTS_COLS_V1_0 14
#define PG_STAT_STATEMENTS_COLS_V1_1 18
! #define PG_STAT_STATEMENTS_COLS 19
/*
* Retrieve statement statistics.
--- 1108,1130 ----
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();
+ }
+
#define PG_STAT_STATEMENTS_COLS_V1_0 14
#define PG_STAT_STATEMENTS_COLS_V1_1 18
! #define PG_STAT_STATEMENTS_COLS 22
/*
* Retrieve statement statistics.
***************
*** 1207,1212 **** pg_stat_statements(PG_FUNCTION_ARGS)
--- 1244,1265 ----
values[i++] = Int64GetDatumFast(tmp.calls);
values[i++] = Float8GetDatumFast(tmp.total_time);
+ if (detected_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);
+ {
+ double avtime = tmp.total_time /tmp.calls;
+ double sqtime = tmp.total_sqtime /tmp.calls;
+ values[i++] = Float8GetDatumFast(sqrtd(sqtime - avtime * avtime));
+ }
+ }
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
***************
*** 1292,1298 **** entry_alloc(pgssHashKey *key, const char *query, int query_len, bool sticky)
if (!found)
{
/* New entry, initialize it */
!
/* reset the statistics */
memset(&entry->counters, 0, sizeof(Counters));
/* set the appropriate initial usage count */
--- 1345,1354 ----
if (!found)
{
/* 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 */
***************
*** 1397,1402 **** entry_reset(void)
--- 1453,1479 ----
}
/*
+ * 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
* the current jumble.
*/
***************
*** 2176,2178 **** comp_location(const void *a, const void *b)
--- 2253,2271 ----
else
return 0;
}
+ /*
+ * fast sqrt algorithm: from Fast inverse square root algotithms.
+ */
+ 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,89 ****
<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>
--- 83,110 ----
<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>
***************
*** 297,302 ****
--- 318,341 ----
</listitem>
</varlistentry>
+ <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>
+
</variablelist>
</sect2>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
