def saveHost(hostDict): sql_cols = "" sql_vals = [] if hostDict['host_id'] == '': hostDict.pop('host_id') if 'host_id' not in hostDict: # insert new host sql_last_user, sql_last_pw = getLastInsertedHostUserAndPassword() sql = "INSERT INTO hosts (" for k, v in hostDict.iteritems(): sql_cols += k + "," sql_vals.append(v) sql_cols += "host_user, host_password" sql += sql_cols + ") VALUES (" + ("%s," * len(sql_vals)) sql += "COALESCE((" + sql_last_user + "),'dummyuser'), COALESCE((" + sql_last_pw + "), 'dummypass')) RETURNING host_id" ret = datadb.execute(sql, tuple(sql_vals)) return ret[0]['host_id'] else: # update host_id = hostDict['host_id'] hostDict.pop('host_id') hostDict.update({'host_last_modified':'now'}) sql = "UPDATE hosts SET\n" for k, v in hostDict.iteritems(): sql_cols += k + "=%s,\n" sql_vals.append(v) sql_cols = sql_cols.strip(",\n") sql += sql_cols sql += "\nWHERE host_id = " + host_id + " RETURNING host_id" ret = datadb.execute(sql, tuple(sql_vals)) return ret[0]['host_id']
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)
def getStatStatements(host_name, date1=None, date2=None, order_by='1', limit='50'): order_by = int(order_by) + 1 sql = ''' select * from ( select max(ssd_query) as query, max(ssd_calls) - min(ssd_calls) as calls, max(ssd_total_time) - min(ssd_total_time) as total_time, max(ssd_blks_read) - min(ssd_blks_read) as blks_read, max(ssd_blks_written) - min(ssd_blks_written) as blks_written, max(ssd_temp_blks_read) - min(ssd_temp_blks_read) as temp_blks_read, max(ssd_temp_blks_written) - min(ssd_temp_blks_written) as temp_blks_written, ssd_query_id as query_id from monitor_data.stat_statements_data join monitor_data.hosts on ssd_host_id = host_id where host_name = %s and ssd_timestamp >= coalesce(%s, current_date-1) and ssd_timestamp < coalesce(%s, now()) group by ssd_query_id ) a order by ''' + str(order_by) + ''' desc limit ''' + limit return datadb.execute(sql, (host_name, date1, date2))
def pgo_get_data_and_columns_from_view(host_id, view_name, max_days_to_fetch, idb_latest_timestamp=None): dt_now = datetime.now() from_timestamp = idb_latest_timestamp to_timestamp = dt_now if from_timestamp is None: from_timestamp = dt_now - timedelta(days=max_days_to_fetch) if from_timestamp < dt_now - timedelta(days=MAX_DAYS_TO_SELECT_AT_A_TIME): to_timestamp = from_timestamp + timedelta(days=MAX_DAYS_TO_SELECT_AT_A_TIME) else: to_timestamp = to_timestamp - timedelta(seconds=SAFETY_SECONDS_FOR_LATEST_DATA) if from_timestamp >= to_timestamp: return [], None sql = open(os.path.join(TEMPLATES_FOLDER, view_name)).read() sql_params = {'host_id': host_id, 'from_timestamp': from_timestamp, 'to_timestamp': to_timestamp} logging.debug("Executing:") logging.debug("%s", datadb.mogrify(sql, sql_params)) view_data, columns = datadb.execute(sql, sql_params) # removing timestamp, we only want to store the utc epoch "time" column timestamp_index = columns.index('timestamp') if timestamp_index != 0: raise Exception('"timestamp" needs to be the 1st column returned!') columns.remove('timestamp') ret_data = [] for d in view_data: ret_data.append(list(d[1:])) return ret_data, columns
def getDatabaseSizes(host_id = None, days='8'): size_data = {} current_host = 0 last_timestamp = None for record in datadb.execute(getSizeTrendSQL(host_id, days)): if record['tsd_host_id'] != current_host: current_host = record['tsd_host_id'] set_ins = False set_del = False set_upd = False l_ins = None l_upd = None l_del = None last_timestamp = None if last_timestamp == None: last_timestamp = int(time.mktime(record['tsd_timestamp'].timetuple()) * 1000) if not record['tsd_host_id'] in size_data: size_data[record['tsd_host_id']] = { 'size' : [] , 'ins': [], 'upd': [], 'del':[] } """ exclude 0 values, otherwise there is a big peak at start, with wraparound this should be ok""" if not set_ins and record['s_ins']!=0: l_ins = record['s_ins'] set_ins = True if not set_upd and record['s_upd']!=0: l_upd = record['s_upd'] set_upd = True if not set_del and record['s_del']!=0: l_del = record['s_del'] set_del = True if l_ins == None: l_ins = record['s_ins'] if l_upd == None: l_upd = record['s_upd'] if l_del == None: l_del = record['s_del'] size_data[record['tsd_host_id']]['size'].append( ( record['tsd_timestamp'] , record['size'] ) ) size_data[record['tsd_host_id']]['ins'].append( ( record['tsd_timestamp'] , max( record['s_ins'] - l_ins , 0) ) ) size_data[record['tsd_host_id']]['del'].append( ( record['tsd_timestamp'] , max( record['s_del'] - l_del , 0) ) ) size_data[record['tsd_host_id']]['upd'].append( ( record['tsd_timestamp'] , max( record['s_upd'] - l_upd , 0) ) ) l_ins = record['s_ins'] l_upd = record['s_upd'] l_del = record['s_del'] last_timestamp = int(time.mktime(record['tsd_timestamp'].timetuple()) * 1000) return size_data
def getTopStatementsData(hostId, interval1='3hours', interval2='1hour', limit='10'): data = datadb.execute(TOP_STATEMENTS_SQL, {'host_id': hostId, 'interval1': interval1, 'interval2': interval2, 'limit': limit}) for d in data: d['avg_time_pretty'] = topsprocs.makeTimeIntervalReadable(d['avg_ms']) d['total_time_pretty'] = topsprocs.makeTimeIntervalReadable(d['total_ms']) return {x: list(y) for x, y in itertools.groupby(data, lambda x: x['mode'])}
def getLocksReport(host_name, date1, date2): # IN p_from_date timestamp, IN p_from_hour integer, IN p_to_date timestamp, IN p_is_ignore_advisory boolean DEFAULT true, # OUT host_name text, OUT total_time_ss bigint, OUT threads_count bigint, OUT incidents_count bigint, OUT blocked_query text, OUT one_blocking_query text q_locks = ''' select * from monitor_data.blocking_last_day(%s, %s) where (host_name = %s or %s = 'all') and total_time_ss > 5 order by host_name, incidents_count desc ''' return datadb.execute(q_locks, (date1, date2, host_name, host_name))
def getBlockedProcessesCounts(hostId, days='8'): ret = [] days += 'days' sql = """ with q_wait_startpoints as ( select date_trunc('hour'::text, query_start) + floor(date_part('minute'::text, query_start) / 15::double precision) * '00:15:00'::interval as wait_start_timestamp, count(1) as wait_starts from ( select query_start, query, min(bp_timestamp) as wait_start, max(bp_timestamp) as wait_end, count(1) from monitor_data.blocking_processes where bp_host_id = """ + str(adapt(hostId)) + """ and bp_timestamp > now() - """ + str(adapt(days)) + """::interval and waiting group by 1, 2 order by 1, 2 ) a where wait_end - wait_start >= '5 seconds'::interval group by date_trunc('hour'::text, query_start) + floor(date_part('minute'::text, query_start) / 15::double precision) * '00:15:00'::interval ), q_timeline as ( select * from (select generate_series(current_date - """ + str(adapt(days)) + """::interval, now(), '00:15:00'::interval) AS ts) a where ts > now() - """ + str(adapt(days)) + """::interval ) SELECT date_trunc('hour'::text, q_timeline.ts) + floor(date_part('minute'::text, q_timeline.ts) / 15::double precision) * '00:15:00'::interval AS ts, coalesce(wait_starts, 0) as count FROM q_timeline left join q_wait_startpoints on q_wait_startpoints.wait_start_timestamp = q_timeline.ts ORDER BY 1 ASC """ for record in datadb.execute(sql): ret.append( (record['ts'] , record['count'] ) ) return ret
def get_all_dbnames(): sql = """ WITH RECURSIVE t(dbname) AS ( SELECT MIN(dbname) AS dbname FROM metrics UNION SELECT (SELECT MIN(dbname) FROM metrics WHERE dbname > t.dbname) FROM t ) SELECT dbname FROM t WHERE dbname NOTNULL ORDER BY 1; """ ret, err = datadb.execute(sql, on_metric_store=True) if err: raise Exception('Failed to delete from "metric": ' + err) return [x['dbname'] for x in ret]
def set_bulk_password(params, cmd_args): err = '' sql = """update pgwatch2.monitored_db set md_password_type = %(bulk_password_type)s, md_password = %(bulk_password)s, md_last_modified_on = now() where (md_password, md_password_type) is distinct from (%(bulk_password)s, %(bulk_password_type)s)""" if params.get('bulk_password_type') == 'aes-gcm-256': # NB! when changing this part also review insert/update_monitored_db() if not cmd_args.aes_gcm_keyphrase: return "Password encryption not possible as keyphrase/keyfile not specified on UI startup - use the PW2_AES_GCM_KEYPHRASE env. variable or --aes-gcm-keyphrase/ aes-gcm-keyphrase-file params", 0 params['bulk_password'] = crypto.encrypt(cmd_args.aes_gcm_keyphrase, params.get('bulk_password')) ret, _ = datadb.execute(sql, params) if ret and len(ret) == 1: return err, ret[0]['rows_affected'] return err, '0'
def retrieve_bgwriter_stats(hostId, from_date, to_date=datetime.now()): """Loads the prepared performance indicators from monitordb and returns a dataset with data points which can be displayed as graph""" sql="""select date_trunc('hour', sbd_timestamp) as sbd_timestamp, sum(elapsed) as elapsed, sum(checkpoints_timed) as checkpoints_timed, sum(checkpoints_req) as checkpoints_req, sum(buffers_checkpoint) as buffers_checkpoint, sum(buffers_clean) as buffers_clean, sum(buffers_backend) as buffers_backend, max(block_size) as block_size from ( SELECT sbd_timestamp, sbd_timestamp - lead(sbd_timestamp, 1, sbd_timestamp) over hostpart AS elapsed, sbd_checkpoints_timed - lead(sbd_checkpoints_timed, 1, sbd_checkpoints_timed) OVER hostpart as checkpoints_timed, sbd_checkpoints_req - lead(sbd_checkpoints_req, 1, sbd_checkpoints_req) OVER hostpart as checkpoints_req, sbd_buffers_checkpoint - lead(sbd_buffers_checkpoint, 1, sbd_buffers_checkpoint) OVER hostpart as buffers_checkpoint, sbd_buffers_clean - lead(sbd_buffers_clean, 1, sbd_buffers_clean) OVER hostpart as buffers_clean, sbd_buffers_backend - lead(sbd_buffers_backend, 1, sbd_buffers_backend) OVER hostpart as buffers_backend, sbd_buffers_alloc - lead(sbd_buffers_alloc, 1, sbd_buffers_alloc) OVER hostpart as buffers_alloc, (select cast(current_setting('block_size') as integer)) as block_size FROM monitor_data.stat_bgwriter_data WHERE sbd_host_id = %(hostId)s AND sbd_timestamp > %(from)s WINDOW hostpart as (PARTITION BY sbd_host_id ORDER BY sbd_timestamp DESC) ) as a where a.elapsed > '0 sec'::interval group by date_trunc('hour', sbd_timestamp) order by 1""" rows = datadb.execute(sql, {'hostId':hostId, 'from': from_date}) result = {'avgWritesPerCheckpoint': [], 'checkpointRequestPercentage': [], 'checkpoint_write_percentage': [], 'backend_write_percentage': [], 'written_per_second': [],} for row in rows: checkpoints = (row['checkpoints_timed'] + row['checkpoints_req']) timepoint = time.mktime(row['sbd_timestamp'].timetuple()) * 1000 total_buffer_writes = row['buffers_checkpoint'] + row['buffers_clean'] + row['buffers_backend'] block_size = row['block_size'] # there might be too many gathered data which result in 0 difference or somebody reseted statistics if checkpoints > 0: avg_chp_write = row['buffers_checkpoint'] * block_size / checkpoints result['avgWritesPerCheckpoint'].append({'x': timepoint, 'y': avg_chp_write}) result['checkpointRequestPercentage'].append({'x': timepoint, 'y': 100*row['checkpoints_req']/checkpoints}) if total_buffer_writes > 0: result['checkpoint_write_percentage'].append({'x': timepoint, 'y': 100*row['buffers_checkpoint']/total_buffer_writes}) result['backend_write_percentage'].append({'x': timepoint, 'y': 100*row['buffers_backend']/total_buffer_writes}) result['written_per_second'].append({'x': timepoint, 'y': block_size*total_buffer_writes/row['elapsed'].seconds}) return result
def getStatStatements(uishortname, date1=None, date2=None, order_by='1', limit='50', no_copy_ddl=True, min_calls='3'): order_by = int(order_by) + 1 sql = ''' select ltrim(regexp_replace(query, E'[ \\t\\r]+' , ' ', 'g')) as query, calls, total_time, blks_read, blks_written, temp_blks_read, temp_blks_written, case when calls > 0 then round(total_time / calls::numeric) else null end as avg_runtime_ms, query_id, host_ui_shortname from ( select max(ssd_query) as query, max(ssd_calls) - min(ssd_calls) as calls, max(ssd_total_time) - min(ssd_total_time) as total_time, max(ssd_blks_read) - min(ssd_blks_read) as blks_read, max(ssd_blks_written) - min(ssd_blks_written) as blks_written, max(ssd_temp_blks_read) - min(ssd_temp_blks_read) as temp_blks_read, max(ssd_temp_blks_written) - min(ssd_temp_blks_written) as temp_blks_written, ssd_query_id as query_id, host_ui_shortname from monitor_data.stat_statements_data join monitor_data.hosts on ssd_host_id = host_id where host_ui_shortname = %s and ssd_timestamp >= coalesce(%s, current_date-1) and ssd_timestamp < coalesce(%s, now()) and case when %s then not upper(ssd_query) like any(array['COPY%%', 'CREATE%%']) else true end group by host_ui_shortname, ssd_query_id ) a where calls >= %s::int order by ''' + str(order_by) + ''' desc nulls last limit ''' + limit return datadb.execute( sql, (uishortname, date1, date2, True if no_copy_ddl else False, min_calls))
def update_preset_config(params): sql = """ update pgwatch2.preset_config set pc_description = %(pc_description)s, pc_config = %(pc_config)s, pc_last_modified_on = now() where pc_name = %(pc_name)s """ ret, err = datadb.execute(sql, params) if err: raise Exception('Failed to update "preset_config": ' + err)
def getGetActiveFrontendAnnouncementIfAny(): announcement = None sql = """ SELECT fa_announcement_text FROM frontpage_announcement WHERE fa_validity_range @> now()::timestamp; """ try: announcement = datadb.execute(sql) if announcement: announcement = announcement[0]['fa_announcement_text'] except: print('Exception reading frontpage_announcement table. is it there?') return announcement
def get_all_metrics(): sql = """ select m_id, m_name, m_pg_version_from, m_sql, m_sql_su, coalesce(m_comment, '') as m_comment, m_is_active, m_is_helper, date_trunc('second', m_last_modified_on::timestamp) as m_last_modified_on, m_master_only, m_standby_only, coalesce(m_column_attrs::text, '') as m_column_attrs, coalesce(ma_metric_attrs::text, '') as ma_metric_attrs from pgwatch2.metric left join pgwatch2.metric_attribute on (ma_metric_name = m_name) order by m_is_active desc, m_name, m_pg_version_from """ return datadb.execute(sql)[0]
def getSingleSprocData(hostId, name, interval=None): data = { 'calls' : [], 'self_time': [], 'total_time' : [] , 'avg_time' : [] , 'avg_self_time': [] , 'name' : None } if name: dataset = datadb.execute( getSingleSprocSQL(hostId, name, interval ) ) for r in dataset: if not data['name']: data['name'] = r['name'] # in case sproc is present in multiple apis the name from 1st api will be used data['calls'].append( ( r['xaxis'] , r['d_calls'] ) ) data['total_time'].append ( ( r['xaxis'] , r['d_total_time'] ) ) data['self_time'].append ( ( r['xaxis'] , r['d_self_time'] ) ) data['avg_time'].append ( ( r['xaxis'] , r['d_avg_time'] ) ) data['avg_self_time'].append ( ( r['xaxis'] , r['d_avg_self_time'] ) ) return data
def getHostsDataForConnecting(hostname='all'): q_active_hosts=""" select host_id, host_name, host_port, host_user, host_password, host_db from monitor_data.hosts where host_enabled and (%s = 'all' or host_name=%s) """ return datadb.execute(q_active_hosts, (hostname, hostname))
def getCpuLoad(hostId, days='8'): load = { "load_15min_avg" : [] , "load_15min_max" : [] } days += 'days' sql = """ SELECT date_trunc('hour'::text, load_timestamp) + floor(date_part('minute'::text, load_timestamp) / 15::double precision) * '00:15:00'::interval AS load_timestamp, AVG(load_1min_value) AS load_15min_avg, MAX(load_1min_value) AS load_15min_max FROM monitor_data.host_load WHERE load_host_id = """ + str(adapt(hostId)) + """ AND load_timestamp > now() - """ + str(adapt(days)) + """::interval GROUP BY date_trunc('hour'::text, load_timestamp) + floor(date_part('minute'::text, load_timestamp) / 15::double precision) * '00:15:00'::interval ORDER BY 1 ASC """ for record in datadb.execute(sql): load['load_15min_avg'].append( (record['load_timestamp'] , round( float(record['load_15min_avg'])/100,2) ) ) load['load_15min_max'].append( (record['load_timestamp'] , round( float(record['load_15min_max'])/100,2) ) ) return load
def getAllActiveSprocNames(hostId): sql = """ select distinct regexp_replace(sproc_name,'(\(.*\))','') as sproc_name from sprocs join sproc_performance_data on sp_sproc_id = sproc_id where sproc_host_id = %s and sp_host_id = %s and sp_timestamp > now() - '1 day'::interval """ ret = datadb.execute(sql, (hostId, hostId)) ret = [ x['sproc_name'] for x in ret ] ret.sort() return ret
def get_all_monitored_dbs(): sql = """ select *, date_trunc('second', md_last_modified_on) as md_last_modified_on, md_config::text, md_custom_tags::text, coalesce(md_include_pattern, '') as md_include_pattern, coalesce(md_exclude_pattern, '') as md_exclude_pattern from pgwatch2.monitored_db order by md_is_enabled desc, md_id """ return datadb.execute(sql)[0]
def exec_for_time_pairs(sql, dbname, pairs, decimal_digits=2): ret = [] for time_window, agg_interval in pairs: data, err = datadb.execute(sql, { 'dbname': dbname, 'time_window': time_window, 'agg_interval': agg_interval, 'decimal_digits': decimal_digits }, on_metric_store=True, quiet=True) # print(data, err) if not err and data: ret.append(data[0]['metric']) return ret
def get_active_db_uniques(): sql = """ select md_unique_name from pgwatch2.monitored_db where md_is_enabled order by 1 """ ret, err = datadb.execute(sql) if ret: return [x['md_unique_name'] for x in ret] return []
def insert_metric(params): sql = """ insert into pgwatch2.metric (m_name, m_pg_version_from, m_sql, m_comment, m_is_active, m_is_helper, m_master_only, m_standby_only, m_column_attrs) values (%(m_name)s, %(m_pg_version_from)s, %(m_sql)s, %(m_comment)s, %(m_is_active)s, %(m_is_helper)s, %(m_master_only)s, %(m_standby_only)s, , %(m_column_attrs)s) returning m_id """ cherrypy_checkboxes_to_bool( params, ['m_is_active', 'm_is_helper', 'm_master_only', 'm_standby_only']) ret, err = datadb.execute(sql, params) if err: raise Exception('Failed to insert into "metric": ' + err) return ret[0]['m_id']
def find_top_growth_statements(dbname, sort_column, start_time=(datetime.utcnow() - timedelta(days=1)).isoformat() + 'Z', end_time=datetime.utcnow().isoformat() + 'Z', limit=20): """start_time/end_time expect UTC date inputs currently!""" if sort_column not in STATEMENT_SORT_COLUMNS: raise Exception('unknown sort column: ' + sort_column) ret = [] # list of dicts with all columns from "stat_statements" sql = r""" select queryid, query, round(sum(total_time - total_time_lag)::numeric, 2) as total_time, round(avg((total_time - total_time_lag)/(calls - calls_lag))::numeric, 2) as mean_time, sum(calls - calls_lag) as calls, sum(shared_blks_hit - shared_blks_hit_lag) as shared_blks_hit, sum(shared_blks_read - shared_blks_read_lag) as shared_blks_read, sum(shared_blks_written - shared_blks_written_lag) as shared_blks_written, sum(temp_blks_written - temp_blks_written_lag) as temp_blks_written, round(sum(blk_read_time - blk_read_time_lag)::numeric, 2) as blk_read_time, round(sum(blk_write_time - blk_write_time_lag)::numeric, 2) as blk_write_time from ( select tag_data->>'queryid' as queryid, tag_data->>'query' as query, (data->>'total_time')::float8 as total_time, lag((data->>'total_time')::float8) over w as total_time_lag, (data->>'calls')::float8 as calls, lag((data->>'calls')::float8) over w as calls_lag, (data->>'shared_blks_hit')::float8 as shared_blks_hit, lag((data->>'shared_blks_hit')::float8) over w as shared_blks_hit_lag, (data->>'shared_blks_read')::float8 as shared_blks_read, lag((data->>'shared_blks_read')::float8) over w as shared_blks_read_lag, (data->>'shared_blks_written')::float8 as shared_blks_written, lag((data->>'shared_blks_written')::float8) over w as shared_blks_written_lag, (data->>'temp_blks_read')::float8 as temp_blks_read, lag((data->>'temp_blks_read')::float8) over w as temp_blks_read_lag, (data->>'temp_blks_written')::float8 as temp_blks_written, lag((data->>'temp_blks_written')::float8) over w as temp_blks_written_lag, (data->>'blk_read_time')::float8 as blk_read_time, lag((data->>'blk_read_time')::float8) over w as blk_read_time_lag, (data->>'blk_write_time')::float8 as blk_write_time, lag((data->>'blk_write_time')::float8) over w as blk_write_time_lag from stat_statements where dbname = %(dbname)s and time between %(start_time)s and %(end_time)s and not tag_data->>'query' ~* E'\\(extract\\(\\$\\d+\\W*from\\W*now\\(\\)\\)\\W?\\*\\W*\\$\\d+\\).*::\\w+\\W+as\\W+epoch_ns\\W*,' and not tag_data->>'query' ~* E'/\\*\\W*pgwatch2_generated\\W*\\*/' window w as (partition by tag_data->>'queryid' order by time) ) x where calls > calls_lag group by 1, 2 order by {sort_column} desc limit %(limit)s """ data, _ = datadb.execute(sql.format(sort_column=sort_column), {'dbname': dbname, 'start_time': start_time, 'end_time': end_time, 'limit': limit}, on_metric_store=True) return data
def getAllActiveSprocNames(hostId, active_days=1): active_days = str(adapt(active_days)) + ' days' sql = """ select distinct regexp_replace(sproc_name, E'(\\\\(.*\\\\))', '') as sproc_name from sprocs join sproc_performance_data on sp_sproc_id = sproc_id where sproc_host_id = %(host_id)s and sp_host_id = %(host_id)s and sp_timestamp > now() - %(active_days)s::interval order by 1 """ ret = datadb.execute(sql, {'host_id': hostId, 'active_days': active_days}) ret = [x['sproc_name'] for x in ret] return ret
def update_metric(params): sql = """ update pgwatch2.metric set m_name = %(m_name)s, m_pg_version_from = %(m_pg_version_from)s, m_sql = %(m_sql)s, m_is_active = %(m_is_active)s, m_last_modified_on = now() where m_id = %(m_id)s """ cherrypy_checkboxes_to_bool(params, ['m_is_active']) ret, err = datadb.execute(sql, params) if err: raise Exception('Failed to update "metric": ' + err)
def getWalVolumes(hostId, days='8'): load = { "wal_15min_growth" : []} days += 'days' sql = """ SELECT date_trunc('hour'::text, load_timestamp) + floor(date_part('minute'::text, load_timestamp) / 15::double precision) * '00:15:00'::interval AS load_timestamp, coalesce(max(xlog_location_mb)-min(xlog_location_mb),0) AS wal_15min_growth FROM monitor_data.host_load WHERE load_host_id = """ + str(adapt(hostId)) + """ AND load_timestamp > ('now'::timestamp - """ + str(adapt(days)) + """::interval) GROUP BY date_trunc('hour'::text, load_timestamp) + floor(date_part('minute'::text, load_timestamp) / 15::double precision) * '00:15:00'::interval ORDER BY 1 ASC """ for record in datadb.execute(sql): load['wal_15min_growth'].append( (record['load_timestamp'] , record['wal_15min_growth'] ) ) return load
def get_monitored_db_by_id(id): sql = """ select *, date_trunc('second', md_last_modified_on) as md_last_modified_on, md_config::text, md_custom_tags::text, coalesce(md_include_pattern, '') as md_include_pattern, coalesce(md_exclude_pattern, '') as md_exclude_pattern from pgwatch2.monitored_db where md_id = %s """ data, err = datadb.execute(sql, (id, )) if not data: return None return data[0]
def insert_monitored_db(params): sql = """ insert into pgwatch2.monitored_db (md_unique_name, md_hostname, md_port, md_dbname, md_user, md_password, md_sslmode, md_is_enabled, md_preset_config_name, md_config, md_statement_timeout_seconds) values (%(md_unique_name)s, %(md_hostname)s, %(md_port)s, %(md_dbname)s, %(md_user)s, %(md_password)s, %(md_sslmode)s, %(md_is_enabled)s, %(md_preset_config_name)s, %(md_config)s, %(md_statement_timeout_seconds)s) returning md_id """ cherrypy_checkboxes_to_bool(params, ['md_is_enabled', 'md_sslmode']) cherrypy_empty_text_to_nulls(params, ['md_preset_config_name', 'md_config']) ret, err = datadb.execute(sql, params) if err: raise Exception('Failed to insert into "monitored_db": ' + err) return ret[0]['md_id']
def getTopStatementsData(hostId, interval1='3hours', interval2='1hour', limit='10'): data = datadb.execute( TOP_STATEMENTS_SQL, { 'host_id': hostId, 'interval1': interval1, 'interval2': interval2, 'limit': limit }) for d in data: d['avg_time_pretty'] = topsprocs.makeTimeIntervalReadable(d['avg_ms']) d['total_time_pretty'] = topsprocs.makeTimeIntervalReadable( d['total_ms']) return { x: list(y) for x, y in itertools.groupby(data, lambda x: x['mode']) }
def getWalVolumes(hostId, days='8'): load = {"wal_15min_growth": []} days += 'days' sql = """ SELECT date_trunc('hour'::text, load_timestamp) + floor(date_part('minute'::text, load_timestamp) / 15::double precision) * '00:15:00'::interval AS load_timestamp, coalesce(max(xlog_location_mb)-min(xlog_location_mb),0) AS wal_15min_growth FROM monitor_data.host_load WHERE load_host_id = """ + str( adapt( hostId)) + """ AND load_timestamp > ('now'::timestamp - """ + str( adapt(days)) + """::interval) GROUP BY date_trunc('hour'::text, load_timestamp) + floor(date_part('minute'::text, load_timestamp) / 15::double precision) * '00:15:00'::interval ORDER BY 1 ASC """ for record in datadb.execute(sql): load['wal_15min_growth'].append( (record['load_timestamp'], record['wal_15min_growth'])) return load
def getCpuLoad(hostId, days='8'): load = {"load_15min_avg": [], "load_15min_max": []} days += 'days' sql = """ SELECT date_trunc('hour'::text, load_timestamp) + floor(date_part('minute'::text, load_timestamp) / 15::double precision) * '00:15:00'::interval AS load_timestamp, AVG(load_1min_value) AS load_15min_avg, MAX(load_1min_value) AS load_15min_max FROM monitor_data.host_load WHERE load_host_id = """ + str( adapt(hostId)) + """ AND load_timestamp > now() - """ + str( adapt(days)) + """::interval GROUP BY date_trunc('hour'::text, load_timestamp) + floor(date_part('minute'::text, load_timestamp) / 15::double precision) * '00:15:00'::interval ORDER BY 1 ASC """ for record in datadb.execute(sql): load['load_15min_avg'].append( (record['load_timestamp'], round(float(record['load_15min_avg']) / 100, 2))) load['load_15min_max'].append( (record['load_timestamp'], round(float(record['load_15min_max']) / 100, 2))) return load
def getStatStatements(host_name, date1=None, date2=None, order_by='1', limit='50', no_copy_ddl=True, min_calls='3'): order_by = int(order_by) + 1 sql = ''' select query, calls, total_time, blks_read, blks_written, temp_blks_read, temp_blks_written, case when calls > 0 then round(total_time / calls::numeric) else null end as avg_runtime_ms, query_id from ( select max(ssd_query) as query, max(ssd_calls) - min(ssd_calls) as calls, max(ssd_total_time) - min(ssd_total_time) as total_time, max(ssd_blks_read) - min(ssd_blks_read) as blks_read, max(ssd_blks_written) - min(ssd_blks_written) as blks_written, max(ssd_temp_blks_read) - min(ssd_temp_blks_read) as temp_blks_read, max(ssd_temp_blks_written) - min(ssd_temp_blks_written) as temp_blks_written, ssd_query_id as query_id from monitor_data.stat_statements_data join monitor_data.hosts on ssd_host_id = host_id where host_name = %s and ssd_timestamp >= coalesce(%s, current_date-1) and ssd_timestamp < coalesce(%s, now()) and case when %s then not upper(ssd_query) like any(array['COPY%%', 'CREATE%%']) else true end group by ssd_query_id ) a where calls >= %s::int order by ''' + str(order_by) + ''' desc nulls last limit ''' + limit return datadb.execute(sql, (host_name, date1, date2, True if no_copy_ddl else False, min_calls))
def getStatStatementsGraph(hostid, query_id, date1, date2): sql = """ select ssd_query_id as query_id, ssd_timestamp as timestamp, ssd_query as query, ssd_calls as calls, ssd_total_time as total_time, ssd_blks_read as blks_read, ssd_blks_written as blks_written, ssd_temp_blks_read as temp_blks_read, ssd_temp_blks_written as temp_blks_written from monitor_data.stat_statements_data where ssd_host_id = %s and ssd_query_id = %s and ssd_timestamp between %s and %s order by ssd_timestamp """ return datadb.execute(sql, (hostid, query_id, date1, date2))
def update_metric(params): sql = """ update pgwatch2.metric set m_name = %(m_name)s, m_pg_version_from = %(m_pg_version_from)s, m_sql = %(m_sql)s, m_comment = %(m_comment)s, m_is_active = %(m_is_active)s, m_is_helper = %(m_is_helper)s, m_master_only = %(m_master_only)s, m_standby_only = %(m_standby_only)s, m_column_attrs = %(m_column_attrs)s, m_last_modified_on = now() where m_id = %(m_id)s """ cherrypy_checkboxes_to_bool(params, ['m_is_active', 'm_is_helper', 'm_master_only', 'm_standby_only']) ret, err = datadb.execute(sql, params) if err: raise Exception('Failed to update "metric": ' + err)
def getLoadReportDataDailyAvg(hostId, weeks=10): query = """ select * from ( select load_timestamp::date::timestamp as date, round(avg(load_15min_value)/100,2) AS cpu_15, (max(xlog_location_mb) - min(xlog_location_mb)) * 10^6 as wal_b from monitor_data.host_load where load_host_id = %(host_id)s and load_timestamp > now() - '""" + str(weeks) + """ weeks'::interval group by load_timestamp::date having max(xlog_location_mb) >= min(xlog_location_mb) ) a order by 1 """ return datadb.execute(query, {'host_id': hostId})
def getLoadReportDataDailyAvg(hostId, weeks=10): query = """ select * from ( select load_timestamp::date::timestamp as date, round(avg(load_15min_value)/100,2) AS cpu_15, (max(xlog_location_mb) - min(xlog_location_mb)) * 10^6 as wal_b from monitor_data.host_load where load_host_id = %(host_id)s and load_timestamp > now() - '""" + str( weeks) + """ weeks'::interval group by load_timestamp::date having max(xlog_location_mb) >= min(xlog_location_mb) ) a order by 1 """ return datadb.execute(query, {'host_id': hostId})
def get_schema_usage_for_host(uishortname, date1, date2, filter=''): sql = """ select sud_schema_name as schema_name, sud_timestamp::date AS date, array[max(sud_sproc_calls) - min(sud_sproc_calls), (max(sud_seq_scans) + max(sud_idx_scans)) - (min(sud_seq_scans) + min(sud_idx_scans)), max(sud_tup_ins) - min(sud_tup_ins), max(sud_tup_upd) - min(sud_tup_upd), max(sud_tup_del) - min(sud_tup_del)] as daily_counts from monitor_data.schema_usage_data where sud_host_id = (select host_id from monitor_data.hosts where host_ui_shortname = %s) and sud_timestamp between %s and %s and sud_schema_name like '%%'||%s||'%%' group by sud_schema_name, sud_timestamp::date order by 1, 2 """ usage = datadb.execute(sql, (uishortname, date1, date2, filter)) ret = OrderedDict() for u in usage: if u['schema_name'] not in ret: ret[u['schema_name']] = [] ret[u['schema_name']].append((u['date'], u['daily_counts'])) return ret
def get_schema_usage_for_host(host_name, date1, date2, filter=''): sql = """ select sud_schema_name as schema_name, sud_timestamp::date AS date, array[max(sud_sproc_calls) - min(sud_sproc_calls), (max(sud_seq_scans) + max(sud_idx_scans)) - (min(sud_seq_scans) + min(sud_idx_scans)), max(sud_tup_ins) - min(sud_tup_ins), max(sud_tup_upd) - min(sud_tup_upd), max(sud_tup_del) - min(sud_tup_del)] as daily_counts from monitor_data.schema_usage_data where sud_host_id = (select host_id from monitor_data.hosts where host_name = %s) and sud_timestamp between %s and %s and sud_schema_name like '%%'||%s||'%%' group by sud_schema_name, sud_timestamp::date order by 1, 2 """ usage = datadb.execute(sql, (host_name, date1, date2, filter)) ret = OrderedDict() for u in usage: if u['schema_name'] not in ret: ret[u['schema_name']] = [] ret[u['schema_name']].append((u['date'], u['daily_counts'])) return ret
def getTopTables(hostId=1, limit=10, order=2): limit_sql = "" if limit is None else """ LIMIT """ + str(adapt(limit)) order_by_sql = { 1: "ORDER BY schema ASC,name ASC ", 2: "ORDER BY table_size DESC" , 3: "ORDER BY table_size - min_table_size DESC", 4: "ORDER BY CASE WHEN min_table_size > 0 THEN table_size::float / min_table_size ELSE 0 END DESC", 5: "ORDER BY index_size DESC", 6: "ORDER BY index_size - min_index_size DESC", 7: "ORDER BY CASE WHEN min_index_size > 0 THEN index_size::float / min_index_size ELSE 0 END DESC", 8: "ORDER BY iud_delta DESC"}[int(order)] sql = """ with q_min_max_timestamps AS ( SELECT MIN(tsd_timestamp) AS min_date, MAX(tsd_timestamp) AS max_date FROM monitor_data.table_size_data WHERE tsd_host_id = %s AND tsd_timestamp > now() - '7 days'::interval ), q_min_sizes AS ( SELECT tsd_table_id, tsd_table_size as min_table_size, tsd_index_size as min_index_size, tsd_tup_ins + tsd_tup_upd + tsd_tup_del as min_iud FROM monitor_data.table_size_data st JOIN q_min_max_timestamps on true WHERE st.tsd_host_id = %s AND st.tsd_timestamp = q_min_max_timestamps.min_date ), q_max_sizes AS ( SELECT tsd_table_id, tsd_table_size as max_table_size, tsd_index_size as max_index_size, tsd_tup_ins + tsd_tup_upd + tsd_tup_del as max_iud FROM monitor_data.table_size_data st JOIN q_min_max_timestamps on true WHERE st.tsd_host_id = %s AND st.tsd_timestamp = q_min_max_timestamps.max_date ) SELECT * FROM ( SELECT t_schema AS schema, t_name AS name, q_max_sizes.max_table_size AS table_size, COALESCE(q_min_sizes.min_table_size, 0) AS min_table_size, q_max_sizes.max_table_size - COALESCE(q_min_sizes.min_table_size, 0) AS table_size_delta, q_max_sizes.max_index_size AS index_size, COALESCE(q_min_sizes.min_index_size, 0) AS min_index_size, q_max_sizes.max_index_size - COALESCE(q_min_sizes.min_index_size, 0) AS index_size_delta, q_max_sizes.max_iud - COALESCE(q_min_sizes.min_iud, 0) AS iud_delta FROM q_max_sizes LEFT JOIN q_min_sizes ON q_min_sizes.tsd_table_id = q_max_sizes.tsd_table_id JOIN monitor_data.tables ON t_id = q_max_sizes.tsd_table_id WHERE t_host_id = %s ) t """ + order_by_sql + limit_sql list = datadb.execute(sql, (hostId, hostId, hostId, hostId)) for d in list: d['table_size_pretty'] = makePrettySize( d['table_size'] ) d['index_size_pretty'] = makePrettySize( d['index_size'] ) d['table_size_delta'] = makePrettySize( d['table_size_delta'] ) d['index_size_delta'] = makePrettySize( d['index_size_delta'] ) if d['min_table_size'] > 0: d['growth'] = round( ( ( float(d['table_size']) / d['min_table_size'] ) - 1) * 100 , 1 ) else: d['growth'] = 0 if d['min_index_size'] > 0: d['growth_index'] = round( ( ( float(d['index_size']) / d['min_index_size']) - 1) * 100 , 1 ) else: d['growth_index'] = 0 d['iud_delta'] = makePrettyCounter(d['iud_delta']) return list
def get_last_loads_and_sizes(self): yyyymm = datetime.datetime.now().strftime('%Y%m') sql = """ with q_last_5m_loads as ( select distinct on (load_host_id) load_host_id as host_id, round(load_5min_value/100.0,1) as last_5min_load from monitor_data_partitions.host_load_""" + yyyymm + """ where load_timestamp > now() - '1 day'::interval order by load_host_id, load_timestamp desc ), q_size as ( select distinct on (tsda_host_id) tsda_host_id as host_id, pg_size_pretty(tsda_db_size) last_agg_size from monitor_data_partitions.table_size_data_agg_""" + yyyymm + """ where tsda_timestamp > now() - '1 day'::interval order by tsda_host_id, tsda_timestamp desc ) select h.host_id, h.host_ui_shortname, h.host_ui_longname, hg.group_name, coalesce(q_last_5m_loads.last_5min_load::text, '-') as last_5min_load, coalesce(q_size.last_agg_size::text, '-') as last_agg_size from hosts h left join q_last_5m_loads on q_last_5m_loads.host_id = h.host_id left join q_size on q_size.host_id = h.host_id left join host_groups hg on hg.group_id = h.host_group_id where host_enabled order by case when hg.group_id = 0 then 0 else 1 end, hg.group_name nulls first, h.host_ui_longname """ sql_no_aggr = """ select h.host_id, h.host_ui_shortname, h.host_ui_longname, hg.group_name, coalesce((select round(load_5min_value/100.0,1)::text from host_load where load_host_id = h.host_id and load_timestamp > (now() - '1day'::interval) order by load_timestamp desc limit 1), '-') as last_5min_load, '-' as last_agg_size from hosts h left join host_groups hg on hg.group_id = h.host_group_id where host_enabled order by case when hg.group_id = 0 then 0 else 1 end, hg.group_name nulls first, h.host_ui_longname """ if tplE._settings.get('run_aggregations'): return datadb.execute(sql) else: return datadb.execute(sql_no_aggr) # no "last db size"
def getTopTables(hostId, date_from, date_to, order=None, limit=10, pattern=''): limit_sql = "" if limit is None else """ LIMIT """ + str(adapt(limit)) if not order: order = 2 # size order_by_sql = { 1: "ORDER BY schema ASC,name ASC ", 2: "ORDER BY table_size DESC" , 3: "ORDER BY table_size - min_table_size DESC", 4: "ORDER BY CASE WHEN min_table_size > 0 THEN table_size::float / min_table_size ELSE 0 END DESC", 5: "ORDER BY index_size DESC", 6: "ORDER BY index_size - min_index_size DESC", 7: "ORDER BY CASE WHEN min_index_size > 0 THEN index_size::float / min_index_size ELSE 0 END DESC", 8: "ORDER BY iud_delta DESC", 9: "ORDER BY s_delta DESC", 10: "ORDER BY i_delta DESC", 11: "ORDER BY u_delta DESC", 12: "ORDER BY d_delta DESC" }[int(order)] sql = """ with q_min_max_timestamps AS ( SELECT tsd_host_id as host_id, MIN(tsd_timestamp) AS min_date, MAX(tsd_timestamp) AS max_date FROM monitor_data.table_size_data JOIN monitor_data.hosts h ON h.host_id = tsd_host_id WHERE (%s is null or tsd_host_id = %s) AND tsd_timestamp >= %s::timestamp AND tsd_timestamp <= %s::timestamp GROUP BY 1 ), q_min_sizes AS ( SELECT tsd_host_id, tsd_table_id, tsd_table_size as min_table_size, tsd_index_size as min_index_size, tsd_tup_ins + tsd_tup_upd + tsd_tup_del as min_iud, tsd_seq_scans as min_s, tsd_tup_ins as min_i, tsd_tup_upd as min_u, tsd_tup_del as min_d FROM monitor_data.table_size_data st JOIN q_min_max_timestamps on q_min_max_timestamps.host_id = st.tsd_host_id WHERE st.tsd_timestamp = q_min_max_timestamps.min_date ), q_max_sizes AS ( SELECT tsd_host_id, tsd_table_id, tsd_table_size as max_table_size, tsd_index_size as max_index_size, tsd_tup_ins + tsd_tup_upd + tsd_tup_del as max_iud, tsd_seq_scans as max_s, tsd_tup_ins as max_i, tsd_tup_upd as max_u, tsd_tup_del as max_d FROM monitor_data.table_size_data st JOIN q_min_max_timestamps on q_min_max_timestamps.host_id = st.tsd_host_id WHERE st.tsd_timestamp = q_min_max_timestamps.max_date ) SELECT * FROM ( SELECT q_max_sizes.tsd_host_id as host_id, t_schema AS schema, t_name AS name, q_max_sizes.max_table_size AS table_size, COALESCE(q_min_sizes.min_table_size, 0) AS min_table_size, q_max_sizes.max_table_size - COALESCE(q_min_sizes.min_table_size, 0) AS table_size_delta, q_max_sizes.max_index_size AS index_size, COALESCE(q_min_sizes.min_index_size, 0) AS min_index_size, q_max_sizes.max_index_size - COALESCE(q_min_sizes.min_index_size, 0) AS index_size_delta, q_max_sizes.max_iud - COALESCE(q_min_sizes.min_iud, 0) AS iud_delta, q_max_sizes.max_s - COALESCE(q_min_sizes.min_s, 0) AS s_delta, q_max_sizes.max_i - COALESCE(q_min_sizes.min_i, 0) AS i_delta, q_max_sizes.max_u - COALESCE(q_min_sizes.min_u, 0) AS u_delta, q_max_sizes.max_d - COALESCE(q_min_sizes.min_d, 0) AS d_delta FROM q_max_sizes LEFT JOIN q_min_sizes ON q_min_sizes.tsd_table_id = q_max_sizes.tsd_table_id JOIN monitor_data.tables ON t_id = q_max_sizes.tsd_table_id ) t WHERE name ilike %s """ + order_by_sql + limit_sql pattern = '%' + pattern + '%' list = datadb.execute(sql, (hostId, hostId, date_from, date_to, pattern)) for d in list: d['table_size_pretty'] = makePrettySize( d['table_size'] ) d['index_size_pretty'] = makePrettySize( d['index_size'] ) d['table_size_delta'] = makePrettySize( d['table_size_delta'] ) d['index_size_delta'] = makePrettySize( d['index_size_delta'] ) if d['min_table_size'] > 0: d['growth'] = round( ( ( float(d['table_size']) / d['min_table_size'] ) - 1) * 100 , 1 ) else: d['growth'] = 0 if d['min_index_size'] > 0: d['growth_index'] = round( ( ( float(d['index_size']) / d['min_index_size']) - 1) * 100 , 1 ) else: d['growth_index'] = 0 d['iud_delta'] = makePrettyCounter(d['iud_delta']) d['s_delta'] = makePrettyCounter(d['s_delta']) d['i_delta'] = makePrettyCounter(d['i_delta']) d['u_delta'] = makePrettyCounter(d['u_delta']) d['d_delta'] = makePrettyCounter(d['d_delta']) # d['hostuiname'] = hosts.getHostData()[]['host_ui_longname'] return list
def getDatabaseStatistics(hostid, days='8'): days += 'days' sql = """ select sdd_timestamp, sdd_numbackends, sdd_xact_commit, sdd_xact_rollback, sdd_blks_read, sdd_blks_hit, sdd_temp_files, sdd_temp_bytes, sdd_deadlocks, sdd_blk_read_time, sdd_blk_write_time from monitor_data.stat_database_data where sdd_host_id = %s and sdd_timestamp >= current_date - %s::interval order by sdd_timestamp """ data = datadb.execute(sql, (hostid, days)) ret = [] prev_row = None for row in data: rr = {} if prev_row: rr['timestamp'] = row['sdd_timestamp'] rr['numbackends'] = row['sdd_numbackends'] # commit_delta = max(row['sdd_xact_commit'] - prev_row['sdd_xact_commit'], 0) # max() is for cases where stats are reset rollback_delta = max(row['sdd_xact_rollback'] - prev_row['sdd_xact_rollback'], 0) rr['rollbacks'] = rollback_delta # blks_read_delta = max(row['sdd_blks_read'] - prev_row['sdd_blks_read'], 0) # blks_hit_delta = max(row['sdd_blks_hit'] - prev_row['sdd_blks_hit'], 0) # blk_read_time_delta = max(row['sdd_blk_read_time'] - prev_row['sdd_blk_read_time'], 0) # blk_write_time_delta = max(row['sdd_blk_write_time'] - prev_row['sdd_blk_write_time'], 0) #rr['temp_files'] = max(row['sdd_temp_files'] - prev_row['sdd_temp_files'], 0) rr['temp_files_bytes'] = max(row['sdd_temp_bytes'] - prev_row['sdd_temp_bytes'], 0) rr['deadlocks'] = max(row['sdd_deadlocks'] - prev_row['sdd_deadlocks'], 0) # if commit_delta + rollback_delta > 0: # rr['rollback_ratio'] = round (rollback_delta / float(commit_delta + rollback_delta), 1) # else: # rr['rollback_ratio'] = 0 # if blks_read_delta + blks_hit_delta > 0: # rr['buffers_miss_ratio'] = round (blks_read_delta / float(blks_read_delta + blks_hit_delta), 1) # else: # rr['buffers_miss_ratio'] = 0 # if blk_read_time_delta + blk_write_time_delta > 0: # rr['write_time_ratio'] = round (blk_write_time_delta / float(blk_read_time_delta + blk_write_time_delta), 1) # else: # rr['write_time_ratio'] = 0 # was done in the data fetching query previously but this seems to be faster time_delta = row['sdd_timestamp'] - prev_row['sdd_timestamp'] rr = apply_average(rr, 15, time_delta, keys_to_skip=['timestamp','numbackends', 'rollback_ratio','buffers_miss_ratio','write_time_ratio']) ret.append(rr) prev_row = row return ret
def set_bulk_config(params): sql = """update pgwatch2.monitored_db set md_preset_config_name = %(bulk_preset_config_name)s, md_config = null, md_last_modified_on = now() where md_preset_config_name != %(bulk_preset_config_name)s""" ret, _ = datadb.execute(sql, params) if ret and len(ret) == 1: return ret[0]['rows_affected'] return '0'
def set_bulk_timeout(params): sql = """update pgwatch2.monitored_db set md_statement_timeout_seconds = %(bulk_timeout_seconds)s, md_last_modified_on = now() where md_statement_timeout_seconds != %(bulk_timeout_seconds)s""" ret, _ = datadb.execute(sql, params) if ret and len(ret) == 1: return ret[0]['rows_affected'] return '0'
def get_all_top_level_metric_tables(): sql = """select table_name from admin.get_top_level_metric_tables()""" ret, err = datadb.execute(sql, on_metric_store=True) if err: raise Exception('Failed to determine storage schema type:' + err) return [x["table_name"] for x in ret]
def enable_all_dbs(): sql = """update pgwatch2.monitored_db set md_is_enabled = true, md_last_modified_on = now() where not md_is_enabled""" ret, _ = datadb.execute(sql) if ret and len(ret) == 1: return ret[0]['rows_affected'] return '0'
def insert_monitored_db(params, cmd_args=None): ret = [] # to enable adding DBs via POST requests where nonmandatory fields are not specified expected_monitored_db_params = [('md_port', '5432'), ('md_password', ''), ('md_root_ca_path', ''), ('md_client_cert_path', ''), ('md_client_key_path', ''), ('md_config', ''), ('md_statement_timeout_seconds', '5'), ('md_dbtype', 'postgres'), ('md_only_if_master', False), ('md_custom_tags', ''), ('md_host_config', ''), ('md_include_pattern', ''), ('md_exclude_pattern', ''), ('md_group', 'default'), ('md_password_type', 'plain-text'), ('md_sslmode', 'disable')] for p, default in expected_monitored_db_params: if not p in params: params[p] = default sql_insert_new_db = """ insert into pgwatch2.monitored_db (md_unique_name, md_hostname, md_port, md_dbname, md_user, md_password, md_password_type, md_is_superuser, md_sslmode, md_root_ca_path,md_client_cert_path, md_client_key_path, md_is_enabled, md_preset_config_name, md_config, md_statement_timeout_seconds, md_dbtype, md_include_pattern, md_exclude_pattern, md_custom_tags, md_group, md_host_config, md_only_if_master) values (%(md_unique_name)s, %(md_hostname)s, %(md_port)s, %(md_dbname)s, %(md_user)s, %(md_password)s, %(md_password_type)s, %(md_is_superuser)s, %(md_sslmode)s, %(md_root_ca_path)s, %(md_client_cert_path)s, %(md_client_key_path)s, %(md_is_enabled)s, %(md_preset_config_name)s, %(md_config)s, %(md_statement_timeout_seconds)s, %(md_dbtype)s, %(md_include_pattern)s, %(md_exclude_pattern)s, %(md_custom_tags)s, %(md_group)s, %(md_host_config)s, %(md_only_if_master)s) returning md_id """ sql_active_dbs = "select datname from pg_database where not datistemplate and datallowconn" cherrypy_checkboxes_to_bool(params, [ 'md_is_enabled', 'md_sslmode', 'md_is_superuser', 'md_only_if_master' ]) cherrypy_empty_text_to_nulls(params, [ 'md_preset_config_name', 'md_config', 'md_custom_tags', 'md_host_config' ]) password_plain = params['md_password'] if password_plain == '***': raise Exception( "'***' cannot be used as password, denotes unchanged password") if params.get('md_password_type') == 'aes-gcm-256': if not cmd_args.aes_gcm_keyphrase: ret.append( "FYI - skipping password encryption as keyphrase/keyfile not specified on UI startup (hint: use the PW2_AES_GCM_KEYPHRASE env. variable or --aes-gcm-keyphrase param)" ) params['md_password_type'] = 'plain-text' else: params['md_password'] = crypto.encrypt(cmd_args.aes_gcm_keyphrase, password_plain) if not params['md_dbname'] and params['md_dbtype'] not in [ 'postgres-continuous-discovery', 'patroni', 'patroni-continuous-discovery' ]: # add all DBs found if params['md_dbtype'] == 'postgres': # get all active non-template DBs from the entered host active_dbs_on_host, err = datadb.executeOnRemoteHost( sql_active_dbs, host=params['md_hostname'], port=params['md_port'], dbname='template1', user=params['md_user'], password=password_plain, sslmode=params['md_sslmode']) if err: raise Exception( "Could not read active DBs from specified host!") active_dbs_on_host = [x['datname'] for x in active_dbs_on_host] # "subtract" DBs that are already monitored currently_monitored_dbs, err = datadb.execute( "select md_dbname from pgwatch2.monitored_db where " " (md_hostname, md_port) = (%(md_hostname)s, %(md_port)s)", params) if err: raise Exception( "Could not read currently active DBs from config DB!") currently_monitored_dbs = [ x['md_dbname'] for x in currently_monitored_dbs ] params_copy = params.copy() dbs_to_add = set(active_dbs_on_host) - set(currently_monitored_dbs) for db_to_add in dbs_to_add: params_copy['md_unique_name'] = '{}_{}'.format( params['md_unique_name'], db_to_add) params_copy['md_dbname'] = db_to_add retdata, err = datadb.execute(sql_insert_new_db, params_copy) if err: raise Exception('Failed to insert into "monitored_db": ' + err) if currently_monitored_dbs: ret.append( 'Warning! Some DBs not added as already under monitoring: ' + ', '.join(currently_monitored_dbs)) else: ret.append('{} DBs added: {}'.format(len(dbs_to_add), ', '.join(dbs_to_add))) elif params['md_dbtype'] == 'pgbouncer': # get all configured pgbouncer DBs params['md_dbname'] = 'pgbouncer' active_dbs_on_host, err = datadb.executeOnRemoteHost( "show databases", host=params['md_hostname'], port=params['md_port'], dbname='pgbouncer', user=params['md_user'], password=password_plain, sslmode=params['md_sslmode']) if err: raise Exception( "Could not read active DBs from specified host!") active_dbs_on_host = [x['name'] for x in active_dbs_on_host] # "subtract" DBs that are already monitored currently_monitored_dbs, err = datadb.execute( "select md_dbname from pgwatch2.monitored_db where " " (md_hostname, md_port) = (%(md_hostname)s, %(md_port)s)", params) if err: raise Exception( "Could not read currently active DBs from config DB!") currently_monitored_dbs = [ x['md_dbname'] for x in currently_monitored_dbs ] params_copy = params.copy() dbs_to_add = set(active_dbs_on_host) - set(currently_monitored_dbs) for db_to_add in dbs_to_add: params_copy['md_unique_name'] = '{}_{}'.format( params['md_unique_name'], db_to_add) params_copy['md_dbname'] = db_to_add retdata, err = datadb.execute(sql_insert_new_db, params_copy) if err: raise Exception('Failed to insert into "monitored_db": ' + err) if currently_monitored_dbs: ret.append( 'Warning! Some DBs not added as already under monitoring: ' + ', '.join(currently_monitored_dbs)) else: ret.append('{} DBs added: {}'.format(len(dbs_to_add), ', '.join(dbs_to_add))) else: # only 1 DB if params['md_dbtype'] in [ 'postgres-continuous-discovery', 'patroni-continuous-discovery' ]: params['md_dbname'] = '' data, err = datadb.execute(sql_insert_new_db, params) if err: raise Exception('Failed to insert into "monitored_db": ' + err) ret.append('Host with ID {} added!'.format(data[0]['md_id'])) if params['md_dbtype'] in ['patroni', 'patroni-continuous-discovery']: ret.append( 'Actual DB hosts will be discovered by the metrics daemon via DCS' ) # check if DCS is accessible? would cause more deps... return ret if params['md_dbtype'] == 'postgres-continuous-discovery': params['md_dbname'] = 'template1' data, err = datadb.executeOnRemoteHost('select 1', params['md_hostname'], params['md_port'], params['md_dbname'], params['md_user'], password_plain, sslmode=params['md_sslmode'], quiet=True) if err: ret.append('Could not connect to specified host: ' + str(err)) return ret
def update_monitored_db(params, cmd_args=None): ret = [] password_plain = params['md_password'] old_row_data = get_monitored_db_by_id(params['md_id']) if params.get('md_password_type') == 'aes-gcm-256' and old_row_data.get( 'md_password_type' ) == 'plain-text': # NB! when changing this part also review set_bulk_password() if not cmd_args.aes_gcm_keyphrase: ret.append( "FYI - not enabling password encryption as keyphrase/keyfile not specified on UI startup (hint: use the PW2_AES_GCM_KEYPHRASE env. variable or --aes-gcm-keyphrase param)" ) params['md_password_type'] = old_row_data['md_password_type'] params['md_password'] = '******' else: if params['md_password'] != '***': params['md_password'] = crypto.encrypt( cmd_args.aes_gcm_keyphrase, password_plain) else: params['md_password'] = crypto.encrypt( cmd_args.aes_gcm_keyphrase, old_row_data.get('md_password')) elif params.get('md_password_type') == 'plain-text' and old_row_data.get( 'md_password_type') == 'aes-gcm-256': if not cmd_args.aes_gcm_keyphrase: ret.append( "FYI - skipping password decryption as keyphrase/keyfile not specified on UI startup (hint: use the PW2_AES_GCM_KEYPHRASE env. variable or --aes-gcm-keyphrase param)" ) params['md_password_type'] = old_row_data['md_password_type'] params['md_password'] = '******' else: if params['md_password'] == '***': params['md_password'] = crypto.decrypt( cmd_args.aes_gcm_keyphrase, old_row_data.get('md_password')) sql = """ with q_old as ( /* using CTE to be enable detect if connect info is being changed */ select * from pgwatch2.monitored_db where md_id = %(md_id)s ) update pgwatch2.monitored_db new set md_group = %(md_group)s, md_hostname = %(md_hostname)s, md_port = %(md_port)s, md_dbname = %(md_dbname)s, md_include_pattern = %(md_include_pattern)s, md_exclude_pattern = %(md_exclude_pattern)s, md_user = %(md_user)s, md_password = case when %(md_password)s = '***' and %(md_password_type)s = new.md_password_type then new.md_password else %(md_password)s end, md_password_type = %(md_password_type)s, md_is_superuser = %(md_is_superuser)s, md_sslmode = %(md_sslmode)s, md_root_ca_path = %(md_root_ca_path)s, md_client_cert_path = %(md_client_cert_path)s, md_client_key_path = %(md_client_key_path)s, md_dbtype = %(md_dbtype)s, md_is_enabled = %(md_is_enabled)s, md_preset_config_name = %(md_preset_config_name)s, md_config = %(md_config)s, md_host_config = %(md_host_config)s, md_only_if_master = %(md_only_if_master)s, md_custom_tags = %(md_custom_tags)s, md_statement_timeout_seconds = %(md_statement_timeout_seconds)s, md_last_modified_on = now() from q_old where new.md_id = %(md_id)s returning (q_old.md_hostname, q_old.md_port, q_old.md_dbname, q_old.md_user, q_old.md_password, q_old.md_sslmode, q_old.md_root_ca_path, q_old.md_client_cert_path, q_old.md_client_key_path) is distinct from (%(md_hostname)s, %(md_port)s, %(md_dbname)s, %(md_user)s, case when %(md_password)s = '***' then q_old.md_password else %(md_password)s end, %(md_sslmode)s, %(md_root_ca_path)s, %(md_client_cert_path)s, %(md_client_key_path)s ) as connection_data_changed, case when %(md_password)s = '***' and %(md_password_type)s = q_old.md_password_type then q_old.md_password else %(md_password)s end as md_password """ cherrypy_checkboxes_to_bool(params, [ 'md_is_enabled', 'md_sslmode', 'md_is_superuser', 'md_only_if_master' ]) cherrypy_empty_text_to_nulls(params, [ 'md_preset_config_name', 'md_config', 'md_custom_tags', 'md_host_config' ]) if params['md_dbtype'] == 'postgres-continuous-discovery': params['md_dbname'] = '' data, err = datadb.execute(sql, params) if err: raise Exception('Failed to update "monitored_db": ' + err) ret.append('Updated!') if params['md_dbtype'] in ['patroni', 'patroni-continuous-discovery']: return ret # check if DCS is accessible? # check connection if connect string changed or inactive host activated if data[0]['connection_data_changed'] or ( old_row_data and (not old_row_data['md_is_enabled'] and params['md_is_enabled']) ): # show warning when changing connect data but cannot connect if params.get( 'md_password_type' ) == 'aes-gcm-256' and cmd_args.aes_gcm_keyphrase and data[0][ 'md_password'] and data[0]['md_password'].find('-') > 0: password_plain = crypto.decrypt(cmd_args.aes_gcm_keyphrase, data[0]['md_password']) else: password_plain = data[0]['md_password'] data, err = datadb.executeOnRemoteHost( 'select 1', params['md_hostname'], params['md_port'], 'template1' if params['md_dbtype'] == 'postgres-continuous-discovery' else params['md_dbname'], params['md_user'], password_plain, sslmode=params['md_sslmode'], sslrootcert=params['md_root_ca_path'], sslcert=params['md_client_cert_path'], sslkey=params['md_client_key_path'], quiet=True) if err: ret.append( 'Could not connect to specified host (ignore if gatherer daemon runs on another host): ' + str(err)) return ret
def get_unused_schemas(host_name, from_date, to_date, filter): sql = """ with q_max_daily_timestamps as ( select sud_host_id as host_id, sud_schema_name as schema_name, --sud_timestamp::date as day, max(sud_timestamp) as timestamp from monitor_data.schema_usage_data s where sud_timestamp between %s and %s and sud_host_id in (select host_id from monitor_data.hosts where host_name = %s or %s = 'all') and (not sud_schema_name like any (array['pg\_%%', '%%\_data']) and sud_schema_name not in ('public', '_v')) and sud_schema_name like '%%'||%s||'%%' group by sud_host_id, sud_schema_name, sud_timestamp::date order by sud_host_id, sud_schema_name, sud_timestamp::date ), q_min_max as ( select sud_host_id as host_id, sud_schema_name as schema_name, min(sud_timestamp), max(sud_timestamp) from monitor_data.schema_usage_data where sud_timestamp between %s and %s and sud_schema_name like '%%'||%s||'%%' group by sud_host_id, sud_schema_name ), q_endofday_total_counts as ( select host_id, schema_name, timestamp, sud_sproc_calls /* + max(sud_seq_scans) + max(sud_idx_scans) */ + sud_tup_ins + sud_tup_upd + sud_tup_del as daily_total from monitor_data.schema_usage_data join q_max_daily_timestamps on sud_host_id = host_id and sud_schema_name = schema_name and sud_timestamp = timestamp order by 1, 2, 3 ) select h.host_name, h.host_db, h.host_id, b.schema_name, mm.min, mm.max from ( select host_id, schema_name, bool_and(is_same_as_prev) is_unchanged from ( select *, case when lag(daily_total) over w is null then true --1st day when lag(daily_total) over w > daily_total then true --stats reset/overflow else lag(daily_total) over w = daily_total end as is_same_as_prev from q_endofday_total_counts window w as (partition by host_id, schema_name order by host_id, schema_name, timestamp) ) a group by host_id, schema_name order by host_id, schema_name ) b join monitor_data.hosts h on h.host_id = b.host_id join q_min_max mm on mm.host_id = h.host_id and mm.schema_name = b.schema_name where is_unchanged order by host_name, schema_name """ unused = datadb.execute(sql, (from_date, to_date, host_name, host_name, filter, from_date, to_date, filter)) return unused
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 ) b ORDER BY index_size_bytes DESC, index_full_name """ 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 ) 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: 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:
def is_sproc_installed(sproc_name): sql = """select * from pg_proc where proname = %s""" return datadb.execute(sql, (sproc_name,))