Python DataDB.execute方法代码示例

# 需要导入模块: import DataDB [as 别名]
# 或者: from DataDB import execute [as 别名]
 def topsprocsbyruntime(self):
     q = """
         select
             host_db_export_name as db,
             sproc_name,
             total_runtime
         from (
             select
                 *,
                 row_number() over(partition by host_db_export_name order by total_runtime desc)
             from (
                 select
                     host_db_export_name,
                     substring(sproc_name, 1, position ('(' in sproc_name)-1) as sproc_name,
                     max(sp_total_time)-min(sp_total_time) as total_runtime
                 from sprocs
                 join sproc_performance_data on sp_sproc_id = sproc_id
                 join hosts on host_id = sproc_host_id 
                 where sp_timestamp > now() - '7days'::interval
                 and host_db_export_name is not null
                 group by 1, 2
             ) a
         ) b
         where row_number <= 10
         order by host_db_export_name, total_runtime desc
     """
     topbyruntime = DataDB.execute(q)
     retdict=defaultdict(list)
     for r in topbyruntime:
         retdict[r['db']].append(r['sproc_name'])
     return json.dumps(retdict)

# 需要导入模块: import DataDB [as 别名]
# 或者: from DataDB import execute [as 别名]

#.........这里部分代码省略.........
        SELECT
        *,
        pg_size_pretty(total_marked_index_size_bytes::bigint) AS total_marked_index_size
        FROM (
          SELECT
          *,
          pg_size_pretty(index_size_bytes) AS index_size,
          pg_size_pretty(indexes_size_bytes) AS indexes_size,
          pg_size_pretty(table_size_bytes) AS table_size,
          CASE WHEN indexes_size_bytes = 0 THEN 0 ELSE round((index_size_bytes::numeric / indexes_size_bytes::numeric)*100,1) END AS pct_of_tables_index_space,
          sum(index_size_bytes) over () AS total_marked_index_size_bytes
          FROM (
          SELECT   %s as host_name,
                   %s as host_id,
                   quote_ident(schemaname)||'.'||quote_ident(relname) AS table_full_name,
                   quote_ident(schemaname)||'.'||quote_ident(indexrelname) AS index_full_name,
                   pg_relation_size(i.indexrelid) as index_size_bytes,
                   pg_indexes_size(i.relid) AS indexes_size_bytes,
                   pg_relation_size(i.relid) AS table_size_bytes,
                   idx_scan AS scans
              FROM pg_stat_user_indexes i 
              JOIN pg_index USING(indexrelid) 
              WHERE NOT indisunique
              AND NOT schemaname LIKE ANY (ARRAY['tmp%%','temp%%'])
          ) a
          WHERE index_size_bytes > %s
          AND scans <= %s          
        ) b
        ORDER BY scans, index_size_bytes DESC
    """
    q_duplicate = """
        SELECT %s AS host_name,
               %s as host_id,
               n.nspname||'.'||ci.relname AS index_full_name,
               n.nspname||'.'||ct.relname AS table_full_name,
               pg_size_pretty(pg_total_relation_size(ct.oid)) AS table_size,
               pg_total_relation_size(ct.oid) AS table_size_bytes,
               n.nspname AS schema_name,
               index_names,
               def,
               count
        FROM (
          select regexp_replace(replace(pg_get_indexdef(i.indexrelid),c.relname,'X'), '^CREATE UNIQUE','CREATE') as def,
                 max(indexrelid) as indexrelid,
                 max(indrelid) as indrelid,
                 count(1),
                 array_agg(relname::text) as index_names
            from pg_index i
            join pg_class c
              on c.oid = i.indexrelid
           where indisvalid
           group 
              by regexp_replace(replace(pg_get_indexdef(i.indexrelid),c.relname,'X'), '^CREATE UNIQUE','CREATE')
          having count(1) > 1
        ) a
          JOIN pg_class ci
            ON ci.oid=a.indexrelid        
          JOIN pg_class ct
            ON ct.oid=a.indrelid
          JOIN pg_namespace n
            ON n.oid=ct.relnamespace
         ORDER
            BY count DESC, table_size_bytes DESC, schema_name, table_full_name
    """
    q_active_hosts="""
        select
            host_id,
            host_name,
            host_user,
            host_password,
            host_db
        from monitor_data.hosts
        where host_enabled
        and (%s = 'all' or host_name=%s)
        """
    q_indexing_thresholds="""select * from monitor_data.perf_indexes_thresholds"""
    data_invalid = []
    data_unused = []
    data_duplicate = []
    data_noconnect = []
    conn=None

    hosts = DataDB.execute(q_active_hosts, (hostname, hostname))      
    indexing_thresholds = DataDB.execute(q_indexing_thresholds)[0]

    for h in hosts:
        try:
            #print ('processing: {}', h)
            conn = psycopg2.connect(host=h['host_name'], dbname=h['host_db'], user=h['host_user'], password=h['host_password'],connect_timeout='3')
            cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            cur.execute(q_invalid, (h['host_name'], h['host_id']))
            data_invalid += cur.fetchall()
            cur.execute(q_unused, (h['host_name'], h['host_id'], indexing_thresholds['pit_min_size_to_report'], indexing_thresholds['pit_max_scans_to_report']))
            data_unused += cur.fetchall()
            cur.execute(q_duplicate, (h['host_name'], h['host_id']))
            data_duplicate += cur.fetchall()
        except Exception, e:
            print ('ERROR could not connect to {}:{}'.format(h['host_name'], e))
            data_noconnect.append({'host_id':h['host_id'],'host_name': h['host_name']})
        finally:

# 需要导入模块: import DataDB [as 别名]
# 或者: from DataDB import execute [as 别名]
def getIndexIssues(hostname):
    q_invalid = """
        SELECT
        *,
        CASE WHEN indexes_size_bytes = 0 THEN 0 ELSE round((index_size_bytes::numeric / indexes_size_bytes::numeric)*100,1) END AS pct_of_tables_index_space,
        pg_size_pretty(total_marked_index_size_bytes::bigint) AS total_marked_index_size
        FROM (
                SELECT
                %s as host_name,
                %s as host_id,
                schemaname||'.'||relname AS table_full_name,
                schemaname||'.'||indexrelname AS index_full_name,
                index_size_bytes,
                indexes_size_bytes,
                pg_size_pretty(index_size_bytes) AS index_size,
                pg_size_pretty(indexes_size_bytes) AS indexes_size,
                pg_size_pretty(table_size_bytes) AS table_size,
                sum(index_size_bytes) over () AS total_marked_index_size_bytes
                FROM
                (
                  SELECT quote_ident(schemaname) as schemaname,
                         quote_ident(relname) as relname,
                         quote_ident(indexrelname) as indexrelname,
                         pg_relation_size(i.indexrelid) AS index_size_bytes,
                         pg_indexes_size(i.relid) AS indexes_size_bytes,                 
                         pg_relation_size(i.relid) AS table_size_bytes
                  FROM pg_stat_user_indexes i
                  JOIN pg_index USING(indexrelid) 
                  WHERE NOT indisvalid
                ) a
                ORDER BY index_size_bytes DESC, relname
        ) b 
    """
    q_unused = """
        SELECT
        *,
        pg_size_pretty(total_marked_index_size_bytes::bigint) AS total_marked_index_size
        FROM (
          SELECT
          *,
          pg_size_pretty(index_size_bytes) AS index_size,
          pg_size_pretty(indexes_size_bytes) AS indexes_size,
          pg_size_pretty(table_size_bytes) AS table_size,
          CASE WHEN indexes_size_bytes = 0 THEN 0 ELSE round((index_size_bytes::numeric / indexes_size_bytes::numeric)*100,1) END AS pct_of_tables_index_space,
          sum(index_size_bytes) over () AS total_marked_index_size_bytes
          FROM (
          SELECT   %s as host_name,
                   %s as host_id,
                   quote_ident(schemaname)||'.'||quote_ident(relname) AS table_full_name,
                   quote_ident(schemaname)||'.'||quote_ident(indexrelname) AS index_full_name,
                   pg_relation_size(i.indexrelid) as index_size_bytes,
                   pg_indexes_size(i.relid) AS indexes_size_bytes,
                   pg_relation_size(i.relid) AS table_size_bytes,
                   idx_scan AS scans
              FROM pg_stat_user_indexes i 
              JOIN pg_index USING(indexrelid) 
              WHERE NOT indisunique
              AND NOT schemaname LIKE ANY (ARRAY['tmp%%','temp%%'])
          ) a
          WHERE index_size_bytes > %s
          AND scans <= %s
          ORDER BY scans, index_size_bytes DESC
        ) b
    """
    q_active_hosts="""
        select
            host_id,
            host_name,
            host_user,
            host_password,
            host_db
        from monitor_data.hosts
        where host_enabled
        and (%s = 'all' or host_name=%s)
        """
    q_indexing_thresholds="""select * from monitor_data.perf_indexes_thresholds"""
    data_invalid = []
    data_unused = []
    data_noconnect = []
    conn=None

    hosts = DataDB.execute(q_active_hosts, (hostname, hostname))      
    indexing_thresholds = DataDB.execute(q_indexing_thresholds)[0]

    for h in hosts:
        try:
            #print ('processing: {}', h)
            conn = psycopg2.connect(host=h['host_name'], dbname=h['host_db'], user=h['host_user'], password=h['host_password'],connect_timeout='3')
            cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            cur.execute(q_invalid, (h['host_name'], h['host_id']))
            data_invalid += cur.fetchall()
            cur.execute(q_unused, (h['host_name'], h['host_id'], indexing_thresholds['pit_min_size_to_report'], indexing_thresholds['pit_max_scans_to_report']))
            data_unused += cur.fetchall()
        except Exception, e:
            print ('ERROR could not connect to {}:{}'.format(h['host_name'], e))
            data_noconnect.append({'host_id':h['host_id'],'host_name': h['host_name']})
        finally: