def qps(self):
sql = "select select_count+update_count+insert_count+delete_count from gs_sql_count;"
with DBAgent(port=self.port, database='postgres') as db:
num1 = db.fetch_all_result(sql)
time.sleep(0.1)
num2 = db.fetch_all_result(sql)
result = (num2[0][0] - num1[0][0] -
1) * 10 if num2[0][0] > num1[0][0] else 0
return result
def current_connections(self):
"""
Get current connections
:return:
"""
# Get current_connections:
sql = "select count(1) from pg_stat_activity;"
with DBAgent(port=self.port, database='postgres') as db:
result = db.fetch_all_result(sql)[0][0]
return result - 1
def temp_file(self):
sql = 'select datapath from pg_node_env;'
with DBAgent(port=self.port, database='postgres') as db:
datapath = db.fetch_all_result(sql)[0][0]
pgsql_tmp = os.path.join(datapath, 'base/pgsql_tmp')
if not os.path.exists(pgsql_tmp):
return 'f'
if len(os.listdir(pgsql_tmp)) > 0:
return 't'
else:
return 'f'
def wdr_features(start_time, end_time, port, database='postgres'):
sql = 'select query, execution_time from statement_history '
if start_time and end_time:
sql = "select query, execution_time from dbe_perf.get_global_slow_sql_by_timestamp" \
" (\'{start_time}\',\'{end_time}\')" \
.format(start_time=start_time, end_time=end_time)
with DBAgent(port=port, database=database) as db:
result = db.fetch_all_result(sql)
if result:
result = list(
filter(
lambda x: re.match(r'UPDATE|SELECT|DELETE|INSERT', x[0])
and x[1] != 0, result))
result = list(map(mapper_function, result))
return result
def wdr_features(self, start_time, end_time):
if start_time and end_time:
sql = "select db_name, query, query_plan, start_time, finish_time, n_returned_rows, n_tuples_fetched, " \
"n_tuples_returned, n_tuples_inserted, n_tuples_updated, n_tuples_deleted, n_blocks_fetched, " \
"n_blocks_hit, db_time, cpu_time, execution_time, parse_time, plan_time, rewrite_time, " \
"pl_execution_time, pl_compilation_time, data_io_time, lock_count, lock_time, lock_wait_count, " \
"lock_wait_time, lock_max_count, lwlock_count, lwlock_wait_count, lwlock_time, lwlock_wait_time from " \
"statement_history where finish_time between '{start_time}' and '{end_time}'" \
.format(start_time=start_time, end_time=end_time)
with DBAgent(port=self.port, database='postgres') as db:
result = db.fetch_all_result(sql)
if result:
result = list(
filter(
lambda x: re.match(r'UPDATE|SELECT|INSERT|DELETE', x[1]
.strip().upper()), result))
result = list(map(self.mapper_function, result))
return result
def disk_space(self):
sql = 'select datapath from pg_node_env;'
with DBAgent(port=self.port, database='postgres') as db:
datapath = db.fetch_all_result(sql)[0][0]
pg_data = os.path.realpath(datapath)
child = subprocess.Popen(['du', '-sh', pg_data],
stdout=subprocess.PIPE,
shell=False)
sub_chan = child.communicate()
if sub_chan[1] is not None:
raise ValueError(
'error when get disk usage of openGauss: {error}'.format(
error=sub_chan[1].decode('utf-8')))
if not sub_chan[0]:
result = '0.0'
else:
result = str(convert_to_mb(sub_chan[0].decode('utf-8')))
return result
def guc_parameter(self):
"""
get database guc parameter
:return: {work_mem: value, shared_buffers: value, max_connections: value}
"""
result = []
guc_names = ['work_mem', 'shared_buffers', 'max_connections']
sql = "select setting, unit from pg_settings where name = '{guc_name}';"
with DBAgent(port=self.port, database='postgres') as db:
for guc_name in guc_names:
res = db.fetch_all_result(sql.format(guc_name=guc_name))
if guc_name != 'max_connections':
res = convert_to_mb(str(res[0][0]) + res[0][1])
result.append(res)
else:
result.append(res[0][0])
result = ",".join(map(lambda x: str(x), result))
return result
def extract_index(self, database, table):
sql = "select indexname, indexdef from pg_indexes where tablename='{table}'".format(
table=table)
with DBAgent(port=self.port, database=database) as db:
res = db.fetch_all_result(sql)
return dict(res)