pgpool2.git / summary / log / commit / refs
commit 689a8fccdee4e91fea0f5a79b5757032e9d7ce1e
Author: Tatsuo Ishii <ishii@sraoss.co.jp>
Date: Fri Aug 14 04:53:57 2020 +0000
Add new show command "show pool_backend_stats".
The new command shows the number SQL commands executed since Pgpool-II
started.
Discussion: https://www.pgpool.net/pipermail/pgpool-hackers/2020-July/003754.html
doc/src/sgml/ref/allfiles.sgml | 1 +
doc/src/sgml/ref/show_pool_backend_stats.sgml | 63 +++++++++++++++++++
doc/src/sgml/reference.sgml | 1 +
src/include/pcp/libpcp_ext.h | 16 +++++
src/include/utils/pool_process_reporting.h | 5 +-
src/protocol/pool_proto_modules.c | 10 +++
src/utils/pool_process_reporting.c | 88 +++++++++++++++++++++++++++
7 files changed, 183 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index a4c9d72cc..369955f4a 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -34,6 +34,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY showPoolVersion SYSTEM "show_pool_version.sgml">
<!ENTITY showPoolCache SYSTEM "show_pool_cache.sgml">
<!ENTITY showPoolHealthCheckStats SYSTEM "show_pool_health_check_stats.sgml">
+<!ENTITY showPoolBackendStats SYSTEM "show_pool_backend_stats.sgml">
<!ENTITY pgpoolAdmPcpNodeInfo SYSTEM "pgpool_adm_pcp_node_info.sgml">
<!ENTITY pgpoolAdmPcpHealthCheckStats SYSTEM "pgpool_adm_pcp_health_check_stats.sgml">
<!ENTITY pgpoolAdmPcpPoolStatus SYSTEM "pgpool_adm_pcp_pool_status.sgml">
diff --git a/doc/src/sgml/ref/show_pool_backend_stats.sgml b/doc/src/sgml/ref/show_pool_backend_stats.sgml
new file mode 100644
index 000000000..dd33f9f04
--- /dev/null
+++ b/doc/src/sgml/ref/show_pool_backend_stats.sgml
@@ -0,0 +1,63 @@
+<!--
+ doc/src/sgml/ref/show_pool_backend_stats.sgml
+ Pgpool-II documentation
+ -->
+
+<refentry id="SQL-SHOW-POOL-BACKEND-STATS">
+ <indexterm zone="sql-show-pool-backend-stats">
+ <primary>SHOW POOL_BACKEND_STATS</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>SHOW POOL_BACKEND_STATS</refentrytitle>
+ <manvolnum>1</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SHOW POOL_BACKEND_STATS</refname>
+ <refpurpose>
+ sends back a list of all configured nodes
+ </refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+ <synopsis>
+ SHOW POOL_BACKEND_STATS
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>SHOW POOL_BACKEND_STATS</command> displays the node id,
+ the hostname, the port, the status, the role, the
+ SELECT/INSERT/UPDATE/DELETE/DDL/other query counts issued to each
+ backend. The node id, the hostname, the port, the status, the role
+ are same as <xref linkend="sql-show-pool-nodes">.
+ </para>
+ <para>
+ select_cnt, insert_cnt, update_cnt, delete_cnt, ddl_cnt, other_cnt
+ are the numbers of SQL command: SELECT, INSERT, UPDATE, DELETE, DDL
+ and others issued since <productname>Pgpool-II</productname>
+ started. Commands rolled back are also counted. Currently, other
+ than
+ SELECT/WITH/INSERT/UPDATE/DELETE/CHECKPOINT/DEALLOCATE/DISCARD/EXECUTE/
+ EXPLAIN/LISTEN/LOAD/LOCK/NOTIFY/PREPARE/SET/SHOW/Transaction
+ commands/UNLISTEN are considered as DDL.
+ </para>
+ <para>
+ Here is an example session:
+ <programlisting>
+test=# show pool_backend_stats;
+ node_id | hostname | port | status | role | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt
+---------+----------+-------+--------+---------+------------+------------+------------+------------+---------+-----------
+ 0 | /tmp | 11002 | up | primary | 27773 | 27784 | 83273 | 0 | 11 | 83298
+ 1 | /tmp | 11003 | up | standby | 1 | 0 | 0 | 0 | 0 | 41410
+(2 rows)
+ </programlisting>
+ </para>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index f525253f5..040af9940 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -156,6 +156,7 @@
&showPoolVersion
&showPoolCache
&showPoolHealthCheckStats
+ &showPoolBackendStats
</reference>
<reference id="pgpool-adm">
diff --git a/src/include/pcp/libpcp_ext.h b/src/include/pcp/libpcp_ext.h
index addab9140..271b23e71 100644
--- a/src/include/pcp/libpcp_ext.h
+++ b/src/include/pcp/libpcp_ext.h
@@ -257,6 +257,22 @@ typedef struct
char last_failed_health_check[POOLCONFIG_MAXDATELEN];
} POOL_HEALTH_CHECK_STATS;
+/* show backend statistics report struct */
+typedef struct
+{
+ char node_id[POOLCONFIG_MAXIDLEN + 1];
+ char hostname[MAX_DB_HOST_NAMELEN + 1];
+ char port[POOLCONFIG_MAXPORTLEN + 1];
+ char status[POOLCONFIG_MAXSTATLEN + 1];
+ char role[POOLCONFIG_MAXWEIGHTLEN + 1];
+ char select_cnt[POOLCONFIG_MAXWEIGHTLEN + 1];
+ char insert_cnt[POOLCONFIG_MAXWEIGHTLEN + 1];
+ char update_cnt[POOLCONFIG_MAXWEIGHTLEN + 1];
+ char delete_cnt[POOLCONFIG_MAXWEIGHTLEN + 1];
+ char ddl_cnt[POOLCONFIG_MAXWEIGHTLEN + 1];
+ char other_cnt[POOLCONFIG_MAXWEIGHTLEN + 1];
+} POOL_BACKEND_STATS;
+
typedef enum
{
PCP_CONNECTION_OK,
diff --git a/src/include/utils/pool_process_reporting.h b/src/include/utils/pool_process_reporting.h
index 45f717d88..ed8606e3f 100644
--- a/src/include/utils/pool_process_reporting.h
+++ b/src/include/utils/pool_process_reporting.h
@@ -34,7 +34,8 @@ extern POOL_REPORT_POOLS * get_pools(int *nrows);
extern POOL_REPORT_PROCESSES * get_processes(int *nrows);
extern POOL_REPORT_NODES * get_nodes(int *nrows);
extern POOL_REPORT_VERSION * get_version(void);
-POOL_HEALTH_CHECK_STATS *get_health_check_stats(int *nrows);
+extern POOL_HEALTH_CHECK_STATS *get_health_check_stats(int *nrows);
+extern POOL_BACKEND_STATS *get_backend_stats(int *nrows);
extern void config_reporting(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend);
extern void pools_reporting(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend);
@@ -43,6 +44,8 @@ extern void nodes_reporting(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * b
extern void version_reporting(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend);
extern void cache_reporting(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend);
extern void show_health_check_stats(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend);
+extern void show_backend_stats(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend);
+
extern void send_config_var_detail_row(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend, const char *name, const char *value, const char *description);
extern void send_config_var_value_only_row(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend, const char *value);
diff --git a/src/protocol/pool_proto_modules.c b/src/protocol/pool_proto_modules.c
index 68a60edc7..32a74bd1c 100644
--- a/src/protocol/pool_proto_modules.c
+++ b/src/protocol/pool_proto_modules.c
@@ -181,6 +181,7 @@ SimpleQuery(POOL_CONNECTION * frontend,
static char *sq_version = "pool_version";
static char *sq_cache = "pool_cache";
static char *sq_health_check_stats = "pool_health_check_stats";
+ static char *sq_backend_stats = "pool_backend_stats";
int commit;
List *parse_tree_list;
Node *node = NULL;
@@ -445,6 +446,15 @@ SimpleQuery(POOL_CONNECTION * frontend,
show_health_check_stats(frontend, backend);
}
+ else if (!strcmp(sq_backend_stats, vnode->name))
+ {
+ is_valid_show_command = true;
+ ereport(DEBUG1,
+ (errmsg("SimpleQuery"),
+ errdetail("backend stats")));
+ show_backend_stats(frontend, backend);
+ }
+
if (is_valid_show_command)
{
pool_ps_idle_display(backend);
diff --git a/src/utils/pool_process_reporting.c b/src/utils/pool_process_reporting.c
index 736ca534b..2bbb371c4 100644
--- a/src/utils/pool_process_reporting.c
+++ b/src/utils/pool_process_reporting.c
@@ -2107,6 +2107,94 @@ show_health_check_stats(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backe
pfree(stats);
}
+/*
+ * for SHOW backend_stats
+ */
+POOL_BACKEND_STATS *
+get_backend_stats(int *nrows)
+{
+ int i;
+ POOL_BACKEND_STATS *backend_stats = palloc(NUM_BACKENDS * sizeof(POOL_BACKEND_STATS));
+ BackendInfo *bi = NULL;
+
+ pool_get_session_context(false);
+
+ for (i = 0; i < NUM_BACKENDS; i++)
+ {
+ bi = pool_get_node_info(i);
+
+ snprintf(backend_stats[i].node_id, POOLCONFIG_MAXIDLEN, "%d", i);
+ StrNCpy(backend_stats[i].hostname, bi->backend_hostname, strlen(bi->backend_hostname) + 1);
+ snprintf(backend_stats[i].port, POOLCONFIG_MAXPORTLEN, "%d", bi->backend_port);
+ snprintf(backend_stats[i].status, POOLCONFIG_MAXSTATLEN, "%s", backend_status_to_str(bi));
+ snprintf(backend_stats[i].select_cnt, POOLCONFIG_MAXWEIGHTLEN, UINT64_FORMAT, stat_get_select_count(i));
+ snprintf(backend_stats[i].insert_cnt, POOLCONFIG_MAXWEIGHTLEN, UINT64_FORMAT, stat_get_insert_count(i));
+ snprintf(backend_stats[i].update_cnt, POOLCONFIG_MAXWEIGHTLEN, UINT64_FORMAT, stat_get_update_count(i));
+ snprintf(backend_stats[i].delete_cnt, POOLCONFIG_MAXWEIGHTLEN, UINT64_FORMAT, stat_get_delete_count(i));
+ snprintf(backend_stats[i].ddl_cnt, POOLCONFIG_MAXWEIGHTLEN, UINT64_FORMAT, stat_get_ddl_count(i));
+ snprintf(backend_stats[i].other_cnt, POOLCONFIG_MAXWEIGHTLEN, UINT64_FORMAT, stat_get_other_count(i));
+
+ if (STREAM)
+ {
+ if (i == REAL_PRIMARY_NODE_ID)
+ {
+ snprintf(backend_stats[i].role, POOLCONFIG_MAXWEIGHTLEN, "%s", "primary");
+ }
+ else
+ {
+ snprintf(backend_stats[i].role, POOLCONFIG_MAXWEIGHTLEN, "%s", "standby");
+ }
+ }
+ else
+ {
+ if (i == REAL_MASTER_NODE_ID)
+ snprintf(backend_stats[i].role, POOLCONFIG_MAXWEIGHTLEN, "%s", "master");
+ else
+ snprintf(backend_stats[i].role, POOLCONFIG_MAXWEIGHTLEN, "%s", "slave");
+ }
+ }
+
+ *nrows = i;
+
+ return backend_stats;
+}
+
+/*
+ * SHOW backend_stats;
+ */
+void
+show_backend_stats(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend)
+{
+ static char *field_names[] = {"node_id", "hostname", "port", "status", "role",
+ "select_cnt", "insert_cnt", "update_cnt", "delete_cnt", "ddl_cnt", "other_cnt"};
+
+ static int offsettbl[] = {
+ offsetof(POOL_BACKEND_STATS, node_id),
+ offsetof(POOL_BACKEND_STATS, hostname),
+ offsetof(POOL_BACKEND_STATS, port),
+ offsetof(POOL_BACKEND_STATS, status),
+ offsetof(POOL_BACKEND_STATS, role),
+ offsetof(POOL_BACKEND_STATS, select_cnt),
+ offsetof(POOL_BACKEND_STATS, insert_cnt),
+ offsetof(POOL_BACKEND_STATS, update_cnt),
+ offsetof(POOL_BACKEND_STATS, delete_cnt),
+ offsetof(POOL_BACKEND_STATS, ddl_cnt),
+ offsetof(POOL_BACKEND_STATS, other_cnt)
+ };
+
+ int nrows;
+ short num_fields;
+ POOL_BACKEND_STATS *backend_stats;
+
+ num_fields = sizeof(field_names) / sizeof(char *);
+ backend_stats = get_backend_stats(&nrows);
+
+ send_row_description_and_data_rows(frontend, backend, num_fields, field_names, offsettbl,
+ (char *)backend_stats, sizeof(POOL_BACKEND_STATS), nrows);
+
+ pfree(backend_stats);
+}
+
/*
* Send row description and data rows.
*
[parent: e01ae5009832]