Пример #1
0
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")
Пример #2
0
 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
Пример #3
0
 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
Пример #4
0
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)
Пример #5
0
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)
Пример #6
0
 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
Пример #7
0
 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)
Пример #8
0
 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
Пример #9
0
 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
Пример #10
0
 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
Пример #11
0
 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
Пример #12
0
 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
Пример #13
0
 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}))
Пример #14
0
 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
Пример #15
0
 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
Пример #16
0
 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}))
Пример #17
0
 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
Пример #18
0
 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
Пример #19
0
    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]))
Пример #20
0
 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)
Пример #21
0
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")
Пример #22
0
    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)))
Пример #23
0
 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
Пример #24
0
 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]))