/**
* MySQL Resource - Performance
*
* Query performance metrics and slow query information.
*/
import type { MySQLAdapter } from "../MySQLAdapter.js";
import type {
ResourceDefinition,
RequestContext,
} from "../../../types/index.js";
export function createPerformanceResource(
adapter: MySQLAdapter,
): ResourceDefinition {
return {
uri: "mysql://performance",
name: "Performance Metrics",
title: "MySQL Performance Metrics",
description: "Query performance statistics and slow query analysis",
mimeType: "application/json",
annotations: {
audience: ["user", "assistant"],
priority: 0.8,
},
handler: async (_uri: string, _context: RequestContext) => {
// Get performance-related status variables
const statusResult = await adapter.executeQuery(`
SHOW GLOBAL STATUS WHERE Variable_name IN (
'Queries', 'Questions', 'Slow_queries',
'Select_full_join', 'Select_range_check', 'Select_scan',
'Sort_merge_passes', 'Sort_range', 'Sort_rows', 'Sort_scan',
'Created_tmp_disk_tables', 'Created_tmp_tables',
'Handler_read_first', 'Handler_read_key', 'Handler_read_rnd',
'Handler_read_rnd_next', 'Handler_write'
)
`);
const status: Record<string, number> = {};
for (const row of statusResult.rows ?? []) {
status[row["Variable_name"] as string] = parseInt(
row["Value"] as string,
10,
);
}
// Get performance schema if available (MySQL 5.6+)
let topQueries: unknown[] = [];
try {
const perfResult = await adapter.executeQuery(`
SELECT
DIGEST_TEXT as query_pattern,
COUNT_STAR as execution_count,
ROUND(SUM_TIMER_WAIT / 1000000000, 2) as total_time_ms,
ROUND(AVG_TIMER_WAIT / 1000000000, 2) as avg_time_ms,
ROUND(MAX_TIMER_WAIT / 1000000000, 2) as max_time_ms,
SUM_ROWS_EXAMINED as rows_examined,
SUM_ROWS_SENT as rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10
`);
topQueries = perfResult.rows ?? [];
} catch {
// Performance schema may not be available
}
// Calculate derived metrics
const tmpTablesCreated = status["Created_tmp_tables"] ?? 0;
const tmpDiskRatio =
tmpTablesCreated > 0
? Math.round(
((status["Created_tmp_disk_tables"] ?? 0) / tmpTablesCreated) *
100,
)
: 0;
const fullTableScans = status["Handler_read_rnd_next"] ?? 0;
const indexReads = status["Handler_read_key"] ?? 0;
const scanVsIndexRatio =
fullTableScans + indexReads > 0
? Math.round((fullTableScans / (fullTableScans + indexReads)) * 100)
: 0;
return {
summary: {
total_queries: status["Queries"] ?? 0,
slow_queries: status["Slow_queries"] ?? 0,
tmp_tables_to_disk_percent: tmpDiskRatio,
full_scan_percent: scanVsIndexRatio,
},
sorts: {
merge_passes: status["Sort_merge_passes"] ?? 0,
range: status["Sort_range"] ?? 0,
rows: status["Sort_rows"] ?? 0,
scan: status["Sort_scan"] ?? 0,
},
joins: {
full_join: status["Select_full_join"] ?? 0,
range_check: status["Select_range_check"] ?? 0,
scan: status["Select_scan"] ?? 0,
},
handler: {
read_key: status["Handler_read_key"] ?? 0,
read_rnd: status["Handler_read_rnd"] ?? 0,
read_rnd_next: status["Handler_read_rnd_next"] ?? 0,
write: status["Handler_write"] ?? 0,
},
top_queries: topQueries,
};
},
};
}
