ServerKit is a lightweight, modern server control panel for managing web applications, databases, and services on your VPS or dedicated server. Built with Python/Flask backend and React frontend.
Python astracloudflarecontrol-panelcyberpaneldnsfablehosting-control-panelhosting-panelkimi-k3server-managementserverkitubuntu-web-panelwebserver
"""Read-only database visibility: top queries and health gauges.
For the Database Explorer's Insights tab, per server or container, with the
same target shape as :mod:`db_process_service`:
* **gauges**: connections in use vs ``max_connections``, and the cache hit
ratio (PostgreSQL ``pg_stat_database`` ``blks_hit/blks_read``, InnoDB buffer
pool ``read_requests/reads``);
* **top queries** by total time: ``pg_stat_statements`` and
``performance_schema.events_statements_summary_by_digest``. Each needs the
engine to be collecting; when it is not, the response says why and what
turns it on (``DbConfigTunerService.enable_pg_stat_statements``, or
``performance_schema=ON`` for MariaDB, where it is off by default).
Nothing here writes. Status counters come from ``SHOW GLOBAL STATUS``, which
works on MySQL and MariaDB alike (MySQL 8 has no
``information_schema.GLOBAL_STATUS``, MariaDB often runs with
``performance_schema`` off).
"""
from typing import Dict, List, Optional
from app.services import db_exec
SUPPORTED_ENGINES = ('mysql', 'postgresql')
TOP_LIMIT = 20
_PG_GAUGES = (
"SELECT (SELECT setting FROM pg_settings WHERE name = 'max_connections'), "
"(SELECT count(*) FROM pg_stat_activity), "
"COALESCE(sum(blks_hit), 0), COALESCE(sum(blks_read), 0), "
"(SELECT count(*) FROM pg_extension WHERE extname = 'pg_stat_statements'), "
"(SELECT count(*) FROM pg_settings WHERE name = 'shared_preload_libraries' "
"AND setting LIKE '%pg_stat_statements%') "
"FROM pg_stat_database;"
)
# calls | total ms | mean ms | rows | query (LAST, flattened server-side, so a
# tab or newline in user SQL can never shift the columns).
_PG_TOP = (
"SELECT calls, round(total_exec_time::numeric, 1), round(mean_exec_time::numeric, 2), "
"rows, regexp_replace(query, E'[\\n\\r\\t]+', ' ', 'g') "
f"FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT {TOP_LIMIT};"
)
_MYSQL_STATUS = (
"SHOW GLOBAL STATUS WHERE Variable_name IN "
"('Threads_connected', 'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads');"
)
# Timer columns are picoseconds; /1e9 = milliseconds.
_MYSQL_TOP = (
"SELECT COUNT_STAR, ROUND(SUM_TIMER_WAIT / 1e9, 1), ROUND(AVG_TIMER_WAIT / 1e9, 2), "
"SUM_ROWS_SENT, REPLACE(REPLACE(REPLACE(DIGEST_TEXT, '\\n', ' '), '\\r', ' '), '\\t', ' ') "
"FROM performance_schema.events_statements_summary_by_digest "
f"WHERE DIGEST_TEXT IS NOT NULL ORDER BY SUM_TIMER_WAIT DESC LIMIT {TOP_LIMIT};"
)
def _rows(output: str) -> List[List[str]]:
return [line.split('\t') for line in (output or '').splitlines() if line.strip()]
def _ratio(hits: float, misses: float) -> Optional[float]:
total = hits + misses
return hits / total if total > 0 else None
def _num(value, cast=float):
try:
return cast(value)
except (TypeError, ValueError):
return None
def _queries(rows) -> List[Dict]:
out = []
for row in rows:
if len(row) < 5:
continue
out.append({'calls': _num(row[0], int), 'total_ms': _num(row[1]),
'mean_ms': _num(row[2]), 'rows': _num(row[3], int),
'query': '\t'.join(row[4:]).strip()})
return out
class DbInsightsService:
@staticmethod
def _exec_sql(target, sql):
"""One exec seam (tests stub it), parse-friendly output."""
return db_exec.exec_sql(target, sql, machine_readable=True)
@classmethod
def insights(cls, target) -> Dict:
engine = (target.get('engine') or '').lower()
engine = 'postgresql' if engine in db_exec.POSTGRES_ALIASES else engine
engine = 'mysql' if engine in db_exec.MYSQL_ALIASES else engine
if engine not in SUPPORTED_ENGINES:
return {'error': 'unsupported engine', 'code': 'unsupported_engine'}
if not target.get('container'):
# Dockerised engines only (the host executors don't return
# parse-friendly output).
return {'error': 'insights need a Docker database container'}
target = dict(target, engine=engine)
return cls._postgres(target) if engine == 'postgresql' else cls._mysql(target)
@classmethod
def _postgres(cls, target) -> Dict:
res = cls._exec_sql(target, _PG_GAUGES)
if not res['success']:
return {'error': res.get('error') or 'query failed'}
row = (_rows(res['output']) or [[]])[0]
if len(row) < 6:
return {'error': 'unexpected output from pg_stat_database'}
max_conn, in_use, hit, read, has_ext, preloaded = row[:6]
result = {
'engine': 'postgresql',
'connections': {'in_use': _num(in_use, int), 'max': _num(max_conn, int)},
'cache_hit_ratio': _ratio(_num(hit) or 0, _num(read) or 0),
'top_queries': [],
'top_queries_source': 'pg_stat_statements',
}
if _num(has_ext, int):
top = cls._exec_sql(target, _PG_TOP)
if top['success']:
result['top_queries'] = _queries(_rows(top['output']))
result['top_queries_available'] = True
return result
result['top_queries_reason'] = top.get('error') or 'pg_stat_statements query failed'
elif _num(preloaded, int):
result['top_queries_reason'] = ('pg_stat_statements is preloaded but the extension '
'is not created in this database')
else:
result['top_queries_reason'] = 'pg_stat_statements is not enabled'
result['top_queries_available'] = False
result['can_enable'] = bool(target.get('container'))
return result
@classmethod
def _mysql(cls, target) -> Dict:
res = cls._exec_sql(target, _MYSQL_STATUS)
if not res['success']:
return {'error': res.get('error') or 'query failed'}
status = {row[0]: row[1] for row in _rows(res['output']) if len(row) >= 2}
max_conn = cls._exec_sql(target, 'SELECT @@max_connections;')
max_value = (_rows(max_conn['output']) or [[None]])[0][0] if max_conn['success'] else None
requests = _num(status.get('Innodb_buffer_pool_read_requests')) or 0
disk_reads = _num(status.get('Innodb_buffer_pool_reads')) or 0
result = {
'engine': 'mysql',
'connections': {'in_use': _num(status.get('Threads_connected'), int),
'max': _num(max_value, int)},
# read_requests counts every logical read; reads are the misses
# that went to disk.
'cache_hit_ratio': _ratio(max(requests - disk_reads, 0), disk_reads),
'top_queries': [],
'top_queries_source': 'performance_schema',
}
top = cls._exec_sql(target, _MYSQL_TOP)
if top['success']:
result['top_queries'] = _queries(_rows(top['output']))
result['top_queries_available'] = True
else:
result['top_queries_available'] = False
result['top_queries_reason'] = (
'performance_schema is not collecting statement digests '
'(set performance_schema=ON; it is off by default on MariaDB)')
return result