def run_deploy():
args = Args()
if not args.try_configure_connect_to_pg():
sys.exit(1)
if not Pooler.is_superuser():
sys.stderr.write("ERROR: Bootstrap must be run by PostgreSQL superuser\n")
sys.exit(1)
try:
for sql in CreateSchemaSQL.split(QuerySplit):
if args.args.verbose:
sys.stdout.write("\nExecuting query:\n{0}\n".format(sql))
Pooler.query(sql)
except Exception as e:
sys.stderr.write("Query:\n{0}\nerror: {1}\n".format(sql, e))
sys.exit(2)
try:
for sql in GrantsOnSchemaSQL.format(
mamonsu_version.replace('.', '_'), args.args.mamonsu_username).split(QuerySplit):
if args.args.verbose:
sys.stdout.write("\nExecuting query:\n{0}\n".format(sql))
Pooler.query(sql)
except Exception as e:
sys.stderr.write("Query:\n{0}\nerror: {1}\n".format(sql, e))
sys.exit(2)
sys.stdout.write("Bootstrap successfully completed\n")
def _is_connection_work(self):
try:
Pooler.query('select 1')
return True
except Exception as e:
logging.error('Test query error: {0}'.format(e))
return False
def _is_connection_work(self):
try:
Pooler.query('select 1')
return True
except Exception as e:
logging.error('Test query error: {0}'.format(e))
return False
def run_deploy():
args = Args()
if not args.try_configure_connect_to_pg():
sys.exit(1)
try:
for sql in CreateSchemaSQL.split(QuerySplit):
Pooler.query(sql)
except Exception as e:
sys.stderr.write("Query:\n{0}\nerror: {1}\n".format(sql, e))
sys.exit(2)
def run_deploy():
args = Args()
if not args.try_configure_connect_to_pg():
sys.exit(1)
try:
for sql in CreateSchemaSQL.split(QuerySplit):
Pooler.query(sql)
except Exception as e:
sys.stderr.write("Query:\n{0}\nerror: {1}\n".format(sql, e))
sys.exit(2)
def _collect_rate(self):
result = {}
result["_TPS"], result["_ROLLBACKS"] = "", ""
try:
result["row_1"] = Pooler.query(self.QueryRate)[0]
time.sleep(2)
result["row_2"] = Pooler.query(self.QueryRate)[0]
exec_time = float(result["row_2"][0] - result["row_1"][0])
result["_TPS"] = float(result["row_2"][1] - result["row_1"][1]) / exec_time
result["_ROLLBACKS"] = float(result["row_2"][2] - result["row_1"][2]) / exec_time
except Exception as e:
logging.error("Query rate error: {0}".format(e))
return result
def _run_query(self, query='', exit_on_fail=True):
if self.args.dry_run:
logging.info('dry run (query):\t{0}'.format(
query.replace('%%', '%')))
return
try:
Pooler.query(query)
except ProgrammingError as e:
if '{0}'.format(e) == 'no result set':
return
except Exception as e:
logging.error('Query {0} error: {1}'.format(query, e))
if exit_on_fail:
sys.exit(6)
def _collect_rate(self):
result = {'_TPS': '', '_ROLLBACKS': ''}
try:
result['row_1'] = Pooler.query(self.QueryRate)[0]
time.sleep(2)
result['row_2'] = Pooler.query(self.QueryRate)[0]
exec_time = float(result['row_2'][0] - result['row_1'][0])
result['_TPS'] = float(
result['row_2'][1] - result['row_1'][1]) / exec_time
result['_ROLLBACKS'] = float(
result['row_2'][2] - result['row_1'][2]) / exec_time
except Exception as e:
logging.error('Query rate error: {0}'.format(e))
return result
def _collect_biggest(self):
result = {}
for info_dbs in Pooler.query('select datname \
from pg_catalog.pg_database where datistemplate = false'):
try:
for info in Pooler.query(self.BigTableInfo[0], info_dbs[0]):
table_name = '{0}.{1}'.format(info_dbs[0], info[0])
result[table_name] = ''
for val in info[1:]:
result[table_name] = "{0}\t{1}".format(
result[table_name], val)
except Exception as e:
logging.error("Connect to db {0} error: {1}".format(
info_dbs[0], e))
return result
def _collect_rate(self):
result = {}
result['_TPS'], result['_ROLLBACKS'] = '', ''
try:
result['row_1'] = Pooler.query(self.QueryRate)[0]
time.sleep(2)
result['row_2'] = Pooler.query(self.QueryRate)[0]
exec_time = float(result['row_2'][0] - result['row_1'][0])
result['_TPS'] = float(
result['row_2'][1] - result['row_1'][1]) / exec_time
result['_ROLLBACKS'] = float(
result['row_2'][2] - result['row_1'][2]) / exec_time
except Exception as e:
logging.error('Query rate error: {0}'.format(e))
return result
def _collect_connections(self):
result = []
try:
result = Pooler.query(self.QueryConnections)
except Exception as e:
logging.error('Query connections error: {0}'.format(e))
return result
def _collect_connections(self):
result = []
try:
result = Pooler.query(self.QueryConnections)
except Exception as e:
logging.error('Query connections error: {0}'.format(e))
return result
def run(self, zbx):
tables = []
for info_dbs in Pooler.query('select datname \
from pg_catalog.pg_database where datistemplate = false'):
for info_sizes in Pooler.query("select n.nspname, c.relname, \
pg_catalog.pg_total_relation_size(c.oid) as size from \
pg_catalog.pg_class c left join pg_catalog.pg_namespace n \
on n.oid = c.relnamespace \
where c.relkind IN ('r','v','m','S','f','') \
order by size \
desc limit {0};".format(self.Limit), info_dbs[0]):
table_name = '{0}.{1}.{2}'.format(
info_dbs[0], info_sizes[0], info_sizes[1])
tables.append({'{#TABLE}': table_name})
zbx.send('pgsql.table.size[{0}]'.format(
table_name), info_sizes[2])
zbx.send('pgsql.table.discovery[]', zbx.json({'data': tables}))
def _collect_query(self, query_desc):
result = [query_desc[1]]
try:
for row in Pooler.query(query_desc[0]):
result.append(row)
except Exception as e:
logging.error("Query {0} error: {1}".format(query_desc[0], e))
return result
def _collect_query(self, query_desc):
result = [query_desc[1]]
try:
for row in Pooler.query(query_desc[0]):
result.append(row)
except Exception as e:
logging.error("Query {0} error: {1}".format(query_desc[0], e))
return result
def run(self, zbx):
tables = []
for info_dbs in Pooler.query('select datname \
from pg_catalog.pg_database where datistemplate = false'):
for info_sizes in Pooler.query(
"select n.nspname, c.relname, \
pg_catalog.pg_total_relation_size(c.oid) as size from \
pg_catalog.pg_class c left join pg_catalog.pg_namespace n \
on n.oid = c.relnamespace \
where c.relkind IN ('r','v','m','S','f','') \
order by size \
desc limit {0};".format(self.Limit), info_dbs[0]):
table_name = '{0}.{1}.{2}'.format(info_dbs[0], info_sizes[0],
info_sizes[1])
tables.append({'{#TABLE}': table_name})
zbx.send('pgsql.table.size[{0}]'.format(table_name),
info_sizes[2])
zbx.send('pgsql.table.discovery[]', zbx.json({'data': tables}))
def _collect_biggest(self):
result, sizes, sorted_result = {}, {}, OrderedDict({})
for info_dbs in Pooler.query('select datname \
from pg_catalog.pg_database where datistemplate = false'):
try:
for info in Pooler.query(self.BigTableInfo[0], info_dbs[0]):
table_name = '{0}.{1}'.format(info_dbs[0], info[0])
result[table_name] = ''
values = info[1:] # remove first elements (table name with schema)
sizes[table_name] = values.pop() # size in bytes in last element
for val in values:
result[table_name] = "{0}\t\t{1}".format(
result[table_name], val)
except Exception as e:
logging.error("Connect to db {0} error: {1}".format(
info_dbs[0], e))
for table_name in sorted(result, key=sizes.__getitem__, reverse=True):
sorted_result[table_name] = result[table_name]
return sorted_result
def _collect_biggest(self):
result, sizes, sorted_result = {}, {}, OrderedDict({})
for info_dbs in Pooler.query(
"select datname \
from pg_catalog.pg_database where datistemplate = false"
):
try:
for info in Pooler.query(self.BigTableInfo[0], info_dbs[0]):
table_name = "{0}.{1}".format(info_dbs[0], info[0])
result[table_name] = ""
values = info[1:] # remove first elements (table name with schema)
sizes[table_name] = values.pop() # size in bytes in last element
for val in values:
result[table_name] = "{0}\t\t{1}".format(result[table_name], val)
except Exception as e:
logging.error("Connect to db {0} error: {1}".format(info_dbs[0], e))
for table_name in sorted(result, key=sizes.__getitem__, reverse=True):
sorted_result[table_name] = result[table_name]
return sorted_result
def run(self, zbx):
# execute query on default database
result = Pooler.query("""
SELECT '_upd' as type,
sum(calls) as calls,
sum(total_time) as totaltime
FROM pg_stat_statements
where dbid = (select datid from pg_stat_database where datname = 'sparkmes')
and left(query,8) = 'SELECT *' and query ~* '_UPDATE'
union all
SELECT '_sel' as type,
sum(calls) as calls,
sum(total_time) as totaltime
FROM pg_stat_statements
where dbid = (select datid from pg_stat_database where datname = 'sparkmes')
and left(query,8) = 'SELECT *' and not (query ~* '_UPDATE')
union all
select substring(query,15,4) as type, sum(calls) as calls, sum(total_time) as totaltime
FROM pg_stat_statements
where dbid = (select datid from pg_stat_database where datname = 'sparkmes')
and (left(query,27) = 'SELECT * FROM firms_select('
OR
left(query,26) = 'SELECT * FROM ip_select((('
)
group by 1
""", 'sparkmes')
# send a resulting value to zabbix
for idx, item in enumerate(self.Items):
key, zbxkey, val, delta = item[0], item[1], 0, item[4]
#self.log.info('{0}[{1}] '.format(zbxkey, key)+str(idx))
for row in result:
if key.endswith(row[0]):
val = row[item[5]]
if self.Yprev[idx] is None:
self.Yprev[idx] = float(val)
self.Yprev[idx] = round(self.Coef * float(val) + (1 - self.Coef) * self.Yprev[idx], 1)
break
else:
continue
else:
if self.Yprev[idx] is not None:
self.Yprev[idx] = self.Yprev[idx]/3 # исходных данных нет, уменьшаем значение
if self.Yprev[idx] is None or self.Yprev[idx] is not None and self.Yprev[idx] <= 0:
self.Yprev[idx] = None
else:
if val is not None:
if item[6] == Plugin.VALUE_TYPE.numeric_float:
zbx.send('{0}[{1}]'.format(zbxkey, key), float(val), delta)
self.log.info('{0}[{1}] '.format(zbxkey, key)+str(val))
else:
zbx.send('{0}[{1}]'.format(zbxkey, key), int(val), delta)
self.log.info('{0}[{1}] '.format(zbxkey, key)+str(val))
zbx.send('{0}[{1}_Yprev]'.format(zbxkey, key), float(self.Yprev[idx]), delta)
self.log.info('{0}[{1}_Yprev] '.format(zbxkey, key)+str(self.Yprev[idx]))
def _run_query(self, query='', exit_on_fail=True):
if self.args.dry_run:
logging.info('dry run (query):\t{0}'.format(
query.replace('%%', '%')))
return None
try:
return Pooler.query(query)
except Exception as e:
logging.error('Query {0} error: {1}'.format(query, e))
if exit_on_fail:
sys.exit(6)
def run_deploy():
args = Args()
if not args.try_configure_connect_to_pg():
sys.exit(1)
if not Pooler.is_superuser():
sys.stderr.write(
"ERROR: Bootstrap must be run by PostgreSQL superuser\n")
sys.exit(1)
try:
for sql in CreateSchemaSQL.format(
mamonsu_version,
mamonsu_version.replace('.', '_'),
'[0-9A-F]{24}',
'wal' if Pooler.server_version_greater('10.0') else 'xlog',
'wal_lsn' if Pooler.server_version_greater('10.0') else 'xlog_location',
'waiting' if Pooler.server_version_less('9.6.0') else 'case when wait_event_type is null then false '
' else true end as waiting'
).split(QuerySplit):
if args.args.verbose:
sys.stdout.write("\nExecuting query:\n{0}\n".format(sql))
Pooler.query(sql)
except Exception as e:
sys.stderr.write("Query:\n{0}\nerror: {1}\n".format(sql, e))
sys.exit(2)
try:
for sql in GrantsOnSchemaSQL.format(
mamonsu_version.replace('.', '_'),
args.args.mamonsu_username,
'wal' if Pooler.server_version_greater('10.0') else 'xlog'
).split(QuerySplit):
if args.args.verbose:
sys.stdout.write("\nExecuting query:\n{0}\n".format(sql))
Pooler.query(sql)
except Exception as e:
sys.stderr.write("Query:\n{0}\nerror: {1}\n".format(sql, e))
sys.exit(2)
sys.stdout.write("Bootstrap successfully completed\n")
def _checkpointer(self):
self._run_query(
"alter system set checkpoint_completion_target to 0.75")
if platform.WINDOWS:
logging.info('No wal_size tune for windows')
return
sysmemory = self.sys_info.meminfo['_TOTAL']
if sysmemory < 4 * 1024 * 1024 * 1024:
return
wal_size = min(sysmemory / 4, 8.0 * 1024 * 1024 * 1024)
if Pooler.server_version_greater('9.5'):
self._run_query(
"alter system set max_wal_size to '{0}';".format(
self._humansize_and_round_bytes(wal_size)))
def run(self, zbx):
users = self.plugin_config('system_users', as_json=True)
result = Pooler.query('SELECT \
state, \
coalesce(extract(epoch from age(now(), min(query_start))), 0) as max_time \
FROM pg_stat_activity WHERE lower(usename) \
NOT SIMILAR TO \'({})\' \
GROUP BY state'.format('|'.join(users)))
for item in self.Items:
state, key, val = item[0], item[1], 0
for row in result:
if row[0] != state:
continue
else:
val = row[1]
break
zbx.send('pgsql.{0}'.format(key), float(val))
del result
def run(self, zbx):
# execute query on default database
result = Pooler.query(
"""
SELECT '_upd' as type,
sum(calls) as calls,
sum(total_time) as totaltime
FROM pg_stat_statements
where dbid = (select datid from pg_stat_database where datname = 'sparkmes')
and left(query,8) = 'SELECT *' and query ~* '_UPDATE'
union all
SELECT '_sel' as type,
sum(calls) as calls,
sum(total_time) as totaltime
FROM pg_stat_statements
where dbid = (select datid from pg_stat_database where datname = 'sparkmes')
and left(query,8) = 'SELECT *' and not (query ~* '_UPDATE')
union all
select substring(query,15,4) as type, sum(calls) as calls, sum(total_time) as totaltime
FROM pg_stat_statements
where dbid = (select datid from pg_stat_database where datname = 'sparkmes')
and (left(query,27) = 'SELECT * FROM firms_select('
OR
left(query,26) = 'SELECT * FROM ip_select((('
)
group by 1
""", 'sparkmes')
# send a resulting value to zabbix
for idx, item in enumerate(self.Items):
key, zbxkey, val, delta = item[0], item[1], 0, item[4]
for row in result:
if key.endswith(row[0]):
val = row[item[5]]
if self.Yprev[idx] is None:
self.Yprev[idx] = float(val)
self.Yprev[idx] = round(
self.Coef * float(val) +
(1 - self.Coef) * self.Yprev[idx], 1)
break
else:
continue
else:
if self.Yprev[idx] is not None:
# исходных данных нет, уменьшаем значение
self.Yprev[idx] = self.Yprev[idx] / 3
if self.Yprev[idx] is None or self.Yprev[
idx] is not None and self.Yprev[idx] <= 0:
self.Yprev[idx] = None
else:
if val is not None:
if item[6] == Plugin.VALUE_TYPE.numeric_float:
zbx.send('{0}[{1}]'.format(zbxkey, key), float(val),
delta)
self.log.info('{0}[{1}] '.format(zbxkey, key) +
str(val))
else:
zbx.send('{0}[{1}]'.format(zbxkey, key), int(val),
delta)
self.log.info('{0}[{1}] '.format(zbxkey, key) +
str(val))
zbx.send('{0}[{1}_Yprev]'.format(zbxkey, key),
float(self.Yprev[idx]), delta)
self.log.info('{0}[{1}_Yprev] '.format(zbxkey, key) +
str(self.Yprev[idx]))