pgpool2.git / summary / log / commit / refs
commit 2cae6c15efb9b5bd39b03b43608bdd7e96970e14
Author: Tatsuo Ishii <ishii@postgresql.org>
Date: Tue May 08 07:44:16 2012 +0000
Add show pool_cache command to show on memory query cache statistics.
doc/pgpool-en.html | 46 ++++++++++++++++--
doc/pgpool-ja.html | 46 +++++++++++++++++-
pool_memqcache.c | 31 ++++++------
pool_memqcache.h | 3 ++
pool_process_reporting.c | 122 +++++++++++++++++++++++++++++++++++++++++++++++
pool_process_reporting.h | 3 +-
pool_proto_modules.c | 7 +++
7 files changed, 238 insertions(+), 20 deletions(-)
diff --git a/doc/pgpool-en.html b/doc/pgpool-en.html
index 233b83e42..a64e20ca7 100644
--- a/doc/pgpool-en.html
+++ b/doc/pgpool-en.html
@@ -7,7 +7,7 @@
</head>
<!-- hhmts start -->
-Last modified: Sun Mar 4 11:18:24 JST 2012
+Last modified: Tue May 8 16:42:49 JST 2012
<!-- hhmts end -->
<body bgcolor="#ffffff">
@@ -2410,8 +2410,8 @@ memory_cache_enabled = true
</pre>
<p id="MEMQCACHE_METHOD">
-Memory cache behavior can be specified by cache_param directive:<br>
-Cache store method. Either "shmem"(shared memory) or "memcached". Default is shmem.
+Memory cache behavior can be specified by memqcache_method directive.
+Either "shmem"(shared memory) or "memcached". Default is shmem.
</p>
<pre>
memqcache_method = 'shmem'
@@ -2788,6 +2788,46 @@ benchs2=# show pool_version;
</pre>
</p>
+<h2 id="pool_cache">pool_cache</h2>
+<p>"SHOW pool_cache" displays cache storage statistics if <a href="#memqcache">on memory query cache</a> is enabled.
+Here is an example of it:
+</p>
+<p>
+<pre>
+test=# \x
+\x
+Expanded display is on.
+test=# show pool_cache;
+show pool_cache;
+-[ RECORD 1 ]---------------+---------
+num_cache_hits | 891703
+num_selects | 99995
+cache_hit_ratio | 0.90
+num_hash_entries | 131072
+used_hash_entries | 99992
+num_cache_entries | 99992
+used_cache_enrties_size | 12482600
+free_cache_entries_size | 54626264
+fragment_cache_entries_size | 0
+</pre>
+</p>
+<p>
+num_cache_hits means the number of SELECTs which hit cache.
+num_selects means the number of SELECTs which do not hit cache.
+cache_hit_ratio means cache hit ratio, calculated from num_cache_hits/(num_cache_hits+num_selects)
+Anything below num_hash_entries are valid only when cache storage is on shared memory.
+num_hash_entries means number of entries in hash table, which is used for index to cache storage
+and should be equal to memqcache_max_num_cache in pgpool.conf.
+This is the upper limit for number of cache entries.
+used_hash_entries means number of already used entries in num_hash_entries.
+num_cache_entries means number of valid cache entries in the cache storage and
+should be equal to used_hash_entries.
+used_cache_entries_size means total size of cache storage in bytes which is already used.
+free_cache_entries_size means total size of cache storage in bytes which is not used yet or can be usable.
+fragment_cache_entries_size means total size of cache storage in bytes which cannot be used because of fragmentation.
+The fragmented area can be reused later if free_cache_entries_size becomes 0 (or there's no enough space for the SELECT result).
+</p>
+
<!-- ================================================================================ -->
<h1><a name="online-recovery"></a>Online Recovery</h1>
diff --git a/doc/pgpool-ja.html b/doc/pgpool-ja.html
index a8ecabd1b..ac5f0ef71 100644
--- a/doc/pgpool-ja.html
+++ b/doc/pgpool-ja.html
@@ -8,7 +8,7 @@
<body>
<!-- hhmts start -->
-Last modified: Wed May 2 16:22:29 JST 2012
+Last modified: Tue May 8 16:25:59 JST 2012
<!-- hhmts end -->
<body bgcolor="#ffffff">
@@ -2706,6 +2706,9 @@ memqcache_cache_hits | 36717 | Number of SELE
<p>
$B$,%-%c%C%7%e%R%C%HN($H$$$&$3$H$K$J$j$^$9!#(B
</p>
+<p>
+<a href="#pool_cache">show pool_cache</a>$B%3%^%s%I$G$bF1MM$NFbMF$,3NG'$G$-$^$9!#(B
+</p>
<h2 id="shmem_params">$B6&M-%a%b%j@_Dj9`L\(B</h2>
<p>
@@ -3058,6 +3061,47 @@ benchs2=# show pool_version;
(1 line)
</pre>
+<h2 id="pool_cache">pool_cache</h2>
+<p>"SHOW pool_cache" $B$O(B<a href="#memqcache">$B%*%s%a%b%j%/%(%j%-%c%C%7%e(B</a>$B$,M-8z$G$"$k>l9g$K!"%/%(%j%-%c%C%7%e$N%R%C%HN($d!"%-%c%C%7%e%9%H%l!<%8$N>u67$rI=<($7$^$9!#(B
+$BNc$r<($7$^$9!#(B
+</p>
+<p>
+<pre>
+test=# \x
+\x
+Expanded display is on.
+test=# show pool_cache;
+show pool_cache;
+-[ RECORD 1 ]---------------+---------
+num_cache_hits | 891703
+num_selects | 99995
+cache_hit_ratio | 0.90
+num_hash_entries | 131072
+used_hash_entries | 99992
+num_cache_entries | 99992
+used_cache_enrties_size | 12482600
+free_cache_entries_size | 54626264
+fragment_cache_entries_size | 0
+</pre>
+</p>
+<p>
+num_cache_hits $B$O!"%-%c%C%7%e$K%R%C%H$7$?8!:w7o?t$rI=<($7$^$9!#(B
+num_selects $B$O!"%-%c%C%7%e$K%R%C%H$7$J$+$C$?8!:w7o?t$rI=<($7$^$9!#(B
+cache_hit_ratio $B$O!"%-%c%C%7%e%R%C%HN($G!"(Bnum_cache_hits/(num_cache_hits+num_selects)
+$B$+$i7W;;$5$l$F$$$^$9!#(B
+num_hash_entries $B0J2<$O!"%-%c%C%7%e%9%H%l!<%8$,6&M-%a%b%j$N;~$@$1M-8z$G$9!#(B
+num_hash_entries $B$O!"%-%c%C%7%e$N8!:w%$%s%G%C%/%9$G$"$k%O%C%7%e%F!<%V%k$N%(%s%H%j?t$rI=$7!"(Bpgpool.conf$B$N(B memqcache_max_num_cache $B$H0lCW$7$^$9!#(B
+$B$3$NCM$,EPO?$G$-$k%-%c%C%7%e7o?t$N:GBgCM$K$J$j$^$9!#(B
+used_hash_entries $B$O!"(Bnum_hash_entries $B$N$&$A!";HMQ:Q$_$N%(%s%H%j?t$G$9!#(B
+num_cache_entries $B$O!"%-%c%C%7%eK\BN$NEPO?7o?t$G!"(Bused_hash_entries $B$H0lCW$7$^$9!#(B
+used_cache_enrties_size $B$O!";HMQ:Q$_$N%-%c%C%7%eNN0h$N9g7W%5%$%:$G$9!#(B
+$BC10L$O%P%$%H$G$9!#(B
+free_cache_entries_size $B$O!"%-%c%C%7%eNN0h$N$&$A!"L$;HMQ$^$?$O:GMxMQ2DG=$JNN0h$N9g7W%5%$%:$G$9!#(B
+$BC10L$O%P%$%H$G$9!#(B
+fragment_cache_entries_size $B$O!"6u$-NN0h$G$O$"$k$,!"CGJR2=$7$F$$$FMxMQ$G$-$J$$%-%c%C%7%eNN0h$N9g7W%5%$%:$G$9!#(B
+$BC10L$O%P%$%H$G$9!#(B
+$BCGJR2=$7$?NN0h$O!"MxMQ$G$-$k%-%c%C%7%eNN0h$r;H$$2L$?$7$?;~$K<+F0E*$K:FMxMQ$G$-$k$h$&$K$J$j$^$9!#(B
+</p>
<!-- ================================================================================ -->
diff --git a/pool_memqcache.c b/pool_memqcache.c
index 000778174..61a527c5d 100644
--- a/pool_memqcache.c
+++ b/pool_memqcache.c
@@ -78,8 +78,6 @@ static void pool_reset_memqcache_buffer(void);
static POOL_CACHEID *pool_add_item_shmem_cache(POOL_QUERY_HASH *query_hash, char *data, int size);
static POOL_CACHEID *pool_find_item_on_shmem_cache(POOL_QUERY_HASH *query_hash);
static char *pool_get_item_shmem_cache(POOL_QUERY_HASH *query_hash, int *size, int *sts);
-static void pool_shmem_lock(void);
-static void pool_shmem_unlock(void);
static void pool_add_query_cache_array(POOL_QUERY_CACHE_ARRAY *cache_array, POOL_TEMP_QUERY_CACHE *cache);
static void pool_add_temp_query_cache(POOL_TEMP_QUERY_CACHE *temp_cache, char kind, char *data, int data_len);
static void pool_add_oids_temp_query_cache(POOL_TEMP_QUERY_CACHE *temp_cache, int num_oids, int *oids);
@@ -2255,7 +2253,7 @@ static void pool_wipe_out_cache_block(POOL_CACHE_BLOCKID blockid)
/*
* Aquire lock: XXX giant lock
*/
-static void pool_shmem_lock(void)
+void pool_shmem_lock(void)
{
if (pool_is_shmem_cache())
{
@@ -2266,7 +2264,7 @@ static void pool_shmem_lock(void)
/*
* Release lock
*/
-static void pool_shmem_unlock(void)
+void pool_shmem_unlock(void)
{
if (pool_is_shmem_cache())
{
@@ -3397,8 +3395,9 @@ static void put_back_hash_element(volatile POOL_HASH_ELEMENT *element)
* Subsequent call to this function will break return value
* because its in static memory.
* Caller must hold shmem_lock before calling this function.
+ * If on mememory query cache is not enabled, all stats are 0.
*/
-POOL_SHMEM_STATS * pool_get_shmem_storage_stats(void)
+POOL_SHMEM_STATS *pool_get_shmem_storage_stats(void)
{
static POOL_SHMEM_STATS mystats;
POOL_HASH_ELEMENT *element;
@@ -3407,6 +3406,18 @@ POOL_SHMEM_STATS * pool_get_shmem_storage_stats(void)
memset(&mystats, 0, sizeof(POOL_SHMEM_STATS));
+ if (!pool_config-> memory_cache_enabled)
+ return &mystats;
+
+ /*
+ * Cop cache hit data
+ */
+ mystats.cache_stats.num_selects = stats->num_selects;
+ mystats.cache_stats.num_cache_hits = stats->num_cache_hits;
+
+ if (strcmp(pool_config-> memqcache_method, "shmem"))
+ return &mystats;
+
/* number of total hash entries */
mystats.num_hash_entries = hash_header->nhash;
@@ -3463,15 +3474,5 @@ POOL_SHMEM_STATS * pool_get_shmem_storage_stats(void)
*/
memcpy(&mystats.cache_stats, stats, sizeof(mystats.cache_stats));
- pool_debug("num_hash_entries: %d used_hash_entries:%d",
- mystats.num_hash_entries,
- mystats.used_hash_entries);
-
- pool_debug("num_cache_entries: %d used_cache_entries_size:%ld fragment_cache_entries_size: %ld free_cache_entries_size:%ld",
- mystats.num_cache_entries,
- mystats.used_cache_entries_size,
- mystats.fragment_cache_entries_size,
- mystats.free_cache_entries_size);
-
return &mystats;
}
diff --git a/pool_memqcache.h b/pool_memqcache.h
index 8fcd35bf3..2a2d19b16 100644
--- a/pool_memqcache.h
+++ b/pool_memqcache.h
@@ -257,4 +257,7 @@ extern POOL_TEMP_QUERY_CACHE *pool_get_current_cache(void);
extern POOL_TEMP_QUERY_CACHE *pool_get_current_cache(void);
extern void pool_discard_temp_query_cache(POOL_TEMP_QUERY_CACHE *temp_cache);
+extern void pool_shmem_lock(void);
+extern void pool_shmem_unlock(void);
+
#endif /* POOL_MEMQCACHE_H */
diff --git a/pool_process_reporting.c b/pool_process_reporting.c
index cf748ef05..55837e23c 100644
--- a/pool_process_reporting.c
+++ b/pool_process_reporting.c
@@ -24,6 +24,7 @@
#include "pool_proto_modules.h"
#include "pool_stream.h"
#include "pool_config.h"
+#include "pool_memqcache.h"
#include "version.h"
#include <stdlib.h>
@@ -895,6 +896,7 @@ void nodes_reporting(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend)
free(nodes);
}
+
POOL_REPORT_POOLS* get_pools(int *nrows)
{
int child, pool, poolBE, backend_id;
@@ -1296,3 +1298,123 @@ void version_reporting(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend)
free(version);
}
+
+/*
+ * Show on memory cache reporting
+ */
+void cache_reporting(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend)
+{
+ static char *field_names[] = {"num_cache_hits", "num_selects", "cache_hit_ratio", "num_hash_entries", "used_hash_entries", "num_cache_entries", "used_cache_enrties_size", "free_cache_entries_size", "fragment_cache_entries_size"};
+ short num_fields = sizeof(field_names)/sizeof(char *);
+ int i;
+ short s;
+ int len;
+ int size;
+ int hsize;
+ static unsigned char nullmap[2] = {0xff, 0xff};
+ int nbytes = (num_fields + 7)/8;
+ volatile POOL_SHMEM_STATS *mystats;
+#ifdef HAVE_SIGPROCMASK
+ sigset_t oldmask;
+#else
+ int oldmask;
+#endif
+ double ratio;
+
+#define POOL_CACHE_STATS_MAX_STRING_LEN 32
+ typedef struct {
+ int len; /* length of string excluding null terminate */
+ char string[POOL_CACHE_STATS_MAX_STRING_LEN+1];
+ } MY_STRING_CACHE_STATS;
+
+ MY_STRING_CACHE_STATS *strp;
+
+ strp = malloc(num_fields*sizeof(MY_STRING_CACHE_STATS));
+ if (!strp)
+ {
+ pool_error("cache_reporting: malloc failed");
+ return;
+ }
+
+ /*
+ * Get raw cache stat data
+ */
+ POOL_SETMASK2(&BlockSig, &oldmask);
+ pool_shmem_lock();
+ mystats = pool_get_shmem_storage_stats();
+ pool_shmem_unlock();
+ POOL_SETMASK(&oldmask);
+
+ /*
+ * Convert to string
+ */
+ i = 0;
+ snprintf(strp[i++].string, POOL_CACHE_STATS_MAX_STRING_LEN+1, "%lld", mystats->cache_stats.num_cache_hits);
+ snprintf(strp[i++].string, POOL_CACHE_STATS_MAX_STRING_LEN+1, "%lld", mystats->cache_stats.num_selects);
+ if ((mystats->cache_stats.num_cache_hits + mystats->cache_stats.num_selects) == 0)
+ {
+ ratio = 0.0;
+ }
+ else
+ {
+ ratio = (double)mystats->cache_stats.num_cache_hits/(mystats->cache_stats.num_selects + mystats->cache_stats.num_cache_hits);
+ }
+ snprintf(strp[i++].string, POOL_CACHE_STATS_MAX_STRING_LEN+1, "%.2f", ratio);
+ snprintf(strp[i++].string, POOL_CACHE_STATS_MAX_STRING_LEN+1, "%d", mystats->num_hash_entries);
+ snprintf(strp[i++].string, POOL_CACHE_STATS_MAX_STRING_LEN+1, "%d", mystats->used_hash_entries);
+ snprintf(strp[i++].string, POOL_CACHE_STATS_MAX_STRING_LEN+1, "%d", mystats->num_cache_entries);
+ snprintf(strp[i++].string, POOL_CACHE_STATS_MAX_STRING_LEN+1, "%ld", mystats->used_cache_entries_size);
+ snprintf(strp[i++].string, POOL_CACHE_STATS_MAX_STRING_LEN+1, "%ld", mystats->free_cache_entries_size);
+ snprintf(strp[i++].string, POOL_CACHE_STATS_MAX_STRING_LEN+1, "%ld", mystats->fragment_cache_entries_size);
+
+ /*
+ * Calculate total data length
+ */
+ len = 2; /* number of fields (int16) */
+ for (i=0;i<num_fields;i++)
+ {
+ strp[i].len = strlen(strp[i].string);
+ len += 4 /* length of string (int32) */
+ + strp[i].len;
+ }
+
+ /* Send row description */
+ send_row_description(frontend, backend, num_fields, field_names);
+
+ /* Send each field */
+ if (MAJOR(backend) == PROTO_MAJOR_V2)
+ {
+ pool_write(frontend, "D", 1);
+ pool_write(frontend, nullmap, nbytes);
+
+ for (i=0;i<num_fields;i++)
+ {
+ size = strp[i].len + 1;
+ hsize = htonl(size+4);
+ pool_write(frontend, &hsize, sizeof(hsize));
+ pool_write(frontend, strp[i].string, size);
+ }
+ }
+ else
+ {
+ /* Kind */
+ pool_write(frontend, "D", 1);
+ /* Packet length */
+ len = htonl(len+sizeof(int32));
+ pool_write(frontend, &len, sizeof(len));
+ /* Number of fields */
+ s = htons(num_fields);
+ pool_write(frontend, &s, sizeof(s));
+
+ for (i=0;i<num_fields;i++)
+ {
+ hsize = htonl(strp[i].len);
+ pool_write(frontend, &hsize, sizeof(hsize));
+ pool_write(frontend, strp[i].string, strp[i].len);
+ }
+ }
+
+ send_complete_and_ready(frontend, backend, 1);
+
+ free(strp);
+}
diff --git a/pool_process_reporting.h b/pool_process_reporting.h
index bc451c2a6..8d8d16eb7 100644
--- a/pool_process_reporting.h
+++ b/pool_process_reporting.h
@@ -6,7 +6,7 @@
* pgpool: a language independent connection pool server for PostgreSQL
* written by Tatsuo Ishii
*
- * Copyright (c) 2003-2011 PgPool Global Development Group
+ * Copyright (c) 2003-2012 PgPool Global Development Group
*
* Permission to use, copy, modify, and distribute this software and
* its documentation for any purpose and without fee is hereby
@@ -39,5 +39,6 @@ extern void pools_reporting(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *bac
extern void processes_reporting(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend);
extern void nodes_reporting(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend);
extern void version_reporting(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend);
+extern void cache_reporting(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend);
#endif
diff --git a/pool_proto_modules.c b/pool_proto_modules.c
index 25c59b7a2..a076e3467 100644
--- a/pool_proto_modules.c
+++ b/pool_proto_modules.c
@@ -108,6 +108,7 @@ POOL_STATUS SimpleQuery(POOL_CONNECTION *frontend,
static char *sq_processes = "pool_processes";
static char *sq_nodes = "pool_nodes";
static char *sq_version = "pool_version";
+ static char *sq_cache = "pool_cache";
int commit;
List *parse_tree_list;
Node *node = NULL;
@@ -347,6 +348,12 @@ POOL_STATUS SimpleQuery(POOL_CONNECTION *frontend,
pool_debug("version reporting");
version_reporting(frontend, backend);
}
+ else if (!strcmp(sq_cache, vnode->name))
+ {
+ is_valid_show_command = true;
+ pool_debug("cache reporting");
+ cache_reporting(frontend, backend);
+ }
if (is_valid_show_command)
{
[parent: 6792c63e7323]