8000 feature: added pgpro_stats support to the Statements plugin · postgrespro/mamonsu@4d5fbb3 · GitHub
[go: up one dir, main page]
Skip to content

Commit 4d5fbb3

Browse files
cuprumtancuprumtan
committed
feature: added pgpro_stats support to the Statements plugin
It is now automatically selected for PGPro instead of pg_stat_statements for PG 12+ and uses bootstrap '-x' option to configure metrics gathering functions
1 parent ee6a058 commit 4d5fbb3

File tree

3 files changed

+125
-29
lines changed

3 files changed

+125
-29
lines changed

‎mamonsu/plugins/pgsql/statements.py

Lines changed: 62 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -5,12 +5,27 @@
55
from .pool import Pooler
66

77

8-
class PgStatStatement(Plugin):
8+
class Statements(Plugin):
99
AgentPluginType = "pg"
10-
query = """
11-
SELECT {metrics}
12-
FROM {extension_schema}.{extension};
13-
"""
10+
query = {
11+
"pg_stat_statements":
12+
"""
13+
SELECT {metrics}
14+
FROM {extension_schema}.pg_stat_statements;
15+
""",
16+
"pgpro_stats":
17+
"""
18+
SELECT {metrics}
19+
FROM {extension_schema}.pgpro_stats_totals
20+
WHERE object_type = 'cluster';
21+
""",
22+
"pgpro_stats_bootstrap":
23+
"""
24+
SELECT {columns} FROM mamonsu.statements_pro();
25+
"""
26+
}
27+
query["pg_stat_statements_bootstrap"] = query["pg_stat_statements"]
28+
1429
query_info = """
1530
SELECT {metrics}
1631
FROM public.pg_stat_statements_info;
@@ -83,32 +98,40 @@ class PgStatStatement(Plugin):
8398
def run(self, zbx):
8499
if not self.extension_installed("pg_stat_statements") or not self.extension_installed("pgpro_stats"):
85100
self.disable_and_exit_if_extension_is_not_installed(ext="pg_stat_statements/pgpro_stats")
86-
if Pooler.is_pgpro() or Pooler.is_pgpro_ee():
87-
extension = "pgpro_stats_statements"
88-
extension_schema = self.extension_schema(extension="pgpro_stats")
101+
if (Pooler.is_pgpro() or Pooler.is_pgpro_ee()) and Pooler.server_version_greater("12"):
102+
if not Pooler.is_bootstraped():
103+
self.disable_and_exit_if_not_superuser()
104+
extension = "pgpro_stats"
89105
else:
90106
extension = "pg_stat_statements"
91- extension_schema = self.extension_schema(extension="pg_stat_statements")
107+
108+
extension_schema = self.extension_schema(extension=extension)
109+
110+
# TODO: add 13 and 14 items when pgpro_stats added new WAL metrics
111+
all_items = self.Items
92112
if Pooler.server_version_greater("14"):
93113
self.Items[5][1] = self.Items[5][1].format("total_exec_time+total_plan_time")
94-
all_items = self.Items + self.Items_pg_13
114+
if not Pooler.is_pgpro() or not Pooler.is_pgpro_ee():
115+
all_items += self.Items_pg_13
116+
info_items = self.Items_pg_14
117+
info_params = [x[1] for x in info_items]
118+
info_result = Pooler.query(self.query_info.format(metrics=(", ".join(info_params))))
119+
for key, value in enumerate(info_result[0]):
120+
zbx_key, value = "pgsql.{0}".format(
121+
info_items[key][0]), int(value)
122+
zbx.send(zbx_key, value, info_items[key][4])
95123
columns = [x[1] for x in all_items]
96-
info_items = self.Items_pg_14
97-
info_params = [x[1] for x in info_items]
98-
info_result = Pooler.query(self.query_info.format(metrics=(", ".join(info_params))))
99-
for key, value in enumerate(info_result[0]):
100-
zbx_key, value = "pgsql.{0}".format(
101-
info_items[key][0]), int(value)
102-
zbx.send(zbx_key, value, info_items[key][4])
103124
elif Pooler.server_version_greater("13"):
104125
self.Items[5][1] = self.Items[5][1].format("total_exec_time+total_plan_time")
105-
all_items = self.Items + self.Items_pg_13
126+
if not Pooler.is_pgpro() or not Pooler.is_pgpro_ee():
127+
all_items += self.Items_pg_13
106128
columns = [x[1] for x in all_items]
107129
else:
108130
self.Items[5][1] = self.Items[5][1].format("total_time")
109-
all_items = self.Items
110131
columns = [x[1] for x in all_items]
111-
result = Pooler.query(self.query.format(metrics=(", ".join(columns)), extension_schema=extension_schema, extension=extension))
132+
result = Pooler.query(self.query[extension + "_bootstrap"].format(columns=", ".join(
133+
[x[0][x[0].find("[") + 1:x[0].find("]")] for x in all_items])) if Pooler.is_bootstraped() el 1E80 se self.query[
134+
extension].format(metrics=(", ".join(columns)), extension_schema=extension_schema))
112135
for key, value in enumerate(result[0]):
113136
zbx_key, value = "pgsql.{0}".format(all_items[key][0]), int(value)
114137
zbx.send(zbx_key, value, all_items[key][4])
@@ -159,25 +182,38 @@ def graphs(self, template, dashboard=False):
159182

160183
def keys_and_queries(self, template_zabbix):
161184
if self.extension_installed("pg_stat_statements") or not self.extension_installed("pgpro_stats"):
162-
if Pooler.is_pgpro() or Pooler.is_pgpro_ee():
163-
extension = "pgpro_stats_statements"
185+
if (Pooler.is_pgpro() or Pooler.is_pgpro_ee()) and Pooler.server_version_greater("12"):
186+
if not Pooler.is_bootstraped():
187+
self.disable_and_exit_if_not_superuser()
188+
extension = "pgpro_stats"
164189
else:
165190
extension = "pg_stat_statements"
191+
192+
extension_schema = self.extension_schema(extension=extension)
193+
166194
result = []
195+
all_items = self.Items
167196
if LooseVersion(self.VersionPG) < LooseVersion("13"):
168197
self.Items[5][1] = self.Items[5][1].format("total_time")
169-
all_items = self.Items
170198
else:
171199
self.Items[5][1] = self.Items[5][1].format("total_exec_time+total_plan_time")
172-
all_items = self.Items + self.Items_pg_13
200+
if Pooler.is_pgpro() or Pooler.is_pgpro_ee():
201+
all_items += self.Items_pg_13
173202

174203
for i, item in enumerate(all_items):
175204
keys = item[0].split("[")
176205
result.append("{0}[*],$2 $1 -c \"{1}\"".format("{0}{1}.{2}".format(self.key, keys[0], keys[1][:-1]),
177-
self.query.format(metrics=item[1], extension_schema=extension_schema, extension=extension)))
206+
self.query[extension + "_bootstrap"].format(
207+
columns=", ".join(
208+
[x[0][x[0].find("[") + 1:x[0].find("]")] for x in
209+
all_items])) if Pooler.is_bootstraped() else
210+
self.query[extension].format(
211+
metrics=(", ".join(columns)),
212+
extension_schema=extension_schema)))
178213

179214
if LooseVersion(self.VersionPG) >= LooseVersion("14"):
180-
all_items = self.Items_pg_14
215+
if Pooler.is_pgpro() or Pooler.is_pgpro_ee():
216+
all_items += self.Items_pg_14
181217
for i, item in enumerate(all_items):
182218
keys = item[0].split("[")
183219
result.append("{0}[*],$2 $1 -c \"{1}\"".format("{0}{1}.{2}".format(self.key, keys[0], keys[1][:-1]),

‎mamonsu/tools/bootstrap/sql.py

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -314,6 +314,46 @@
314314
$do$;
315315
"""
316316

317+
CreateStatementsFunctionsSQL = """
318+
DO
319+
$do$
320+
DECLARE
321+
pg_type text;
322+
extension_schema text;
323+
BEGIN
324+
CREATE EXTENSION IF NOT EXISTS pgpro_stats WITH SCHEMA mamonsu;
325+
326+
WITH tb_type AS (SELECT exists(SELECT * FROM pg_proc WHERE proname = 'pgpro_version'))
327+
SELECT
328+
CASE
329+
WHEN exists = false THEN 'vanilla' ELSE 'pro'
330+
END INTO pg_type
331+
FROM tb_type;
332+
333+
<<functions_creation>>
334+
BEGIN
335+
IF pg_type = 'pro' THEN
336+
IF (SELECT EXISTS(SELECT * FROM pg_extension WHERE extname = 'pgpro_stats')) THEN
337+
SELECT n.nspname INTO extension_schema
338+
FROM pg_extension e
339+
JOIN pg_namespace n
340+
ON e.extnamespace = n.oid
341+
WHERE e.extname = 'pgpro_stats';
342+
EXECUTE 'CREATE OR REPLACE FUNCTION mamonsu.statements_pro()
343+
RETURNS TABLE({columns}) AS $$
344+
SELECT {metrics}
345+
FROM ' || extension_schema || '.pgpro_stats_totals
346+
WHERE object_type = ''cluster'';
347+
$$ LANGUAGE SQL SECURITY DEFINER;';
348+
ELSE
349+
EXIT functions_creation;
350+
END IF;
351+
END IF;
352+
END functions_creation;
353+
END
354+
$do$;
355+
"""
356+
317357
GrantsOnDefaultSchemaSQL = """
318358
ALTER TABLE mamonsu.config OWNER TO {1};
319359
@@ -364,3 +404,14 @@
364404
END
365405
$do$;
366406
"""
407+
408+
GrantsOnStatementsFunctionsSQL = """
409+
DO
410+
$do$
411+
BEGIN
412+
IF (SELECT EXISTS(SELECT proname FROM pg_proc WHERE proname = 'statements_pro')) THEN
413+
EXECUTE 'GRANT EXECUTE ON FUNCTION mamonsu.statements_pro() TO {1};';
414+
END IF;
415+
END
416+
$do$;
417+
"""

‎mamonsu/tools/bootstrap/start.py

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,9 +11,10 @@
1111
from mamonsu import __version__ as mamonsu_version
1212
from mamonsu.lib.default_config import DefaultConfig
1313
from mamonsu.plugins.pgsql.pool import Pooler
14-
from mamonsu.tools.bootstrap.sql import CreateMamonsuUserSQL, CreatePgBuffercacheFunctionsSQL, \
15-
CreateSchemaDefaultSQL, GrantsOnDefaultSchemaSQL, GrantsOnPgBuffercacheFunctionsSQL, QuerySplit, \
16-
CreateWaitSamplingFunctionsSQL, GrantsOnWaitSamplingFunctionsSQL
14+
from mamonsu.tools.bootstrap.sql import CreateMamonsuUserSQL, CreatePgBuffercacheFunctionsSQL, CreateSchemaDefaultSQL, \
15+
GrantsOnDefaultSchemaSQL, GrantsOnPgBuffercacheFunctionsSQL, QuerySplit, CreateWaitSamplingFunctionsSQL, \
16+
GrantsOnWaitSamplingFunctionsSQL, CreateStatementsFunctionsSQL, GrantsOnStatementsFunctionsSQL
17+
from mamonsu.plugins.pgsql.statements import Statements
1718

1819

1920
class Args(DefaultConfig):
@@ -232,6 +233,12 @@ def run_deploy():
232233
if Pooler.is_pgpro() or Pooler.is_pgpro_ee():
233234
bootstrap_extension_queries = fill_query_params(CreateWaitSamplingFunctionsSQL)
234235
Pooler.query(bootstrap_extension_queries)
236+
statements_items = [x[1] for x in Statements.Items]
237+
statements_items[5] = statements_items[5].format("total_exec_time+total_plan_time")
238+
statements_columns = [x[0][x[0].find("[")+1:x[0].find("]")] for x in Statements.Items]
239+
bootstrap_extension_queries = CreateStatementsFunctionsSQL.format(
240+
columns=" bigint, ".join(statements_columns) + " bigint", metrics=(", ".join(statements_items)))
241+
Pooler.query(bootstrap_extension_queries)
235242
except Exception as e:
236243
sys.stderr.write(
237244
"Bootstrap failed to create auxiliary extensions and functions.\n"
@@ -254,6 +261,8 @@ def run_deploy():
254261
if Pooler.is_pgpro() or Pooler.is_pgpro_ee():
255262
bootstrap_grant_extension_queries = fill_grant_params(GrantsOnWaitSamplingFunctionsSQL, args)
256263
Pooler.query(bootstrap_grant_extension_queries)
264+
bootstrap_grant_extension_queries = fill_grant_params(GrantsOnStatementsFunctionsSQL, args)
265+
Pooler.query(bootstrap_grant_extension_queries)
257266
except Exception as e:
258267
sys.stderr.write("Bootstrap failed to grant execution permission to "
259268
"the function which required auxiliary extension.\n")

0 commit comments

Comments
 (0)
0