/**
* MySQL Performance Tools - Analysis
*
* Query analysis and performance monitoring tools.
* 8 tools: explain, explain_analyze, slow_queries, query_stats, index_usage, table_stats, buffer_pool_stats, thread_stats.
*/
import type { MySQLAdapter } from '../../MySQLAdapter.js';
import type { ToolDefinition, RequestContext } from '../../../../types/index.js';
import {
ExplainSchema,
SlowQuerySchema,
IndexUsageSchema,
TableStatsSchema
} from '../../types.js';
import { z } from 'zod';
export function createExplainTool(adapter: MySQLAdapter): ToolDefinition {
return {
name: 'mysql_explain',
title: 'MySQL EXPLAIN',
description: 'Get query execution plan using EXPLAIN.',
group: 'performance',
inputSchema: ExplainSchema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (params: unknown, _context: RequestContext) => {
const { query, format } = ExplainSchema.parse(params);
const sql = format === 'JSON'
? `EXPLAIN FORMAT=JSON ${query}`
: format === 'TREE'
? `EXPLAIN FORMAT=TREE ${query}`
: `EXPLAIN ${query}`;
const result = await adapter.executeReadQuery(sql);
if (format === 'JSON' && result.rows?.[0] !== undefined) {
const explainRow = result.rows[0];
const jsonStr = explainRow['EXPLAIN'];
if (typeof jsonStr === 'string') {
return { plan: JSON.parse(jsonStr) as unknown };
}
}
return { plan: result.rows };
}
};
}
export function createExplainAnalyzeTool(adapter: MySQLAdapter): ToolDefinition {
const schema = z.object({
query: z.string().describe('SQL query to analyze'),
format: z.enum(['JSON', 'TREE']).optional().default('TREE')
});
return {
name: 'mysql_explain_analyze',
title: 'MySQL EXPLAIN ANALYZE',
description: 'Get query execution plan with actual timing using EXPLAIN ANALYZE (MySQL 8.0+).',
group: 'performance',
inputSchema: schema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true
},
handler: async (params: unknown, _context: RequestContext) => {
const { query, format } = schema.parse(params);
const sql = `EXPLAIN ANALYZE FORMAT=${format} ${query}`;
const result = await adapter.executeReadQuery(sql);
if (format === 'JSON' && result.rows?.[0] !== undefined) {
const explainRow = result.rows[0];
const jsonStr = explainRow['EXPLAIN'];
if (typeof jsonStr === 'string') {
return { analysis: JSON.parse(jsonStr) as unknown };
}
}
return { analysis: result.rows };
}
};
}
export function createSlowQueriesTool(adapter: MySQLAdapter): ToolDefinition {
return {
name: 'mysql_slow_queries',
title: 'MySQL Slow Queries',
description: 'Get slow queries from performance_schema (if available).',
group: 'performance',
inputSchema: SlowQuerySchema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (params: unknown, _context: RequestContext) => {
const { limit, minTime } = SlowQuerySchema.parse(params);
let sql = `
SELECT
DIGEST_TEXT as query,
COUNT_STAR as executions,
AVG_TIMER_WAIT/1000000000 as avg_time_ms,
SUM_TIMER_WAIT/1000000000 as total_time_ms,
SUM_ROWS_EXAMINED as rows_examined,
SUM_ROWS_SENT as rows_sent
FROM performance_schema.events_statements_summary_by_digest
`;
if (minTime !== undefined) {
sql += ` WHERE AVG_TIMER_WAIT > ${minTime * 1000000000}`;
}
sql += ` ORDER BY AVG_TIMER_WAIT DESC LIMIT ${limit}`;
const result = await adapter.executeReadQuery(sql);
return { slowQueries: result.rows };
}
};
}
export function createQueryStatsTool(adapter: MySQLAdapter): ToolDefinition {
const schema = z.object({
orderBy: z.enum(['total_time', 'avg_time', 'executions']).optional().default('total_time'),
limit: z.number().optional().default(20)
});
return {
name: 'mysql_query_stats',
title: 'MySQL Query Stats',
description: 'Get query statistics from performance_schema.',
group: 'performance',
inputSchema: schema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (params: unknown, _context: RequestContext) => {
const { orderBy, limit } = schema.parse(params);
const orderColumn = {
total_time: 'SUM_TIMER_WAIT',
avg_time: 'AVG_TIMER_WAIT',
executions: 'COUNT_STAR'
}[orderBy];
const sql = `
SELECT
SCHEMA_NAME as database_name,
DIGEST_TEXT as query_text,
COUNT_STAR as execution_count,
AVG_TIMER_WAIT/1000000000 as avg_time_ms,
MAX_TIMER_WAIT/1000000000 as max_time_ms,
SUM_TIMER_WAIT/1000000000 as total_time_ms,
SUM_ROWS_EXAMINED as total_rows_examined,
SUM_ROWS_SENT as total_rows_sent,
FIRST_SEEN as first_seen,
LAST_SEEN as last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY ${orderColumn} DESC
LIMIT ${limit}
`;
const result = await adapter.executeReadQuery(sql);
return { queries: result.rows };
}
};
}
export function createIndexUsageTool(adapter: MySQLAdapter): ToolDefinition {
return {
name: 'mysql_index_usage',
title: 'MySQL Index Usage',
description: 'Get index usage statistics from performance_schema.',
group: 'performance',
inputSchema: IndexUsageSchema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (params: unknown, _context: RequestContext) => {
const { table } = IndexUsageSchema.parse(params);
let sql = `
SELECT
object_schema as database_name,
object_name as table_name,
index_name,
count_read,
count_write,
count_fetch,
count_insert,
count_update,
count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
`;
if (table) {
sql += ` AND object_name = ?`;
}
sql += ` ORDER BY count_read + count_write DESC`;
const result = await adapter.executeReadQuery(sql, table ? [table] : []);
return { indexUsage: result.rows };
}
};
}
export function createTableStatsTool(adapter: MySQLAdapter): ToolDefinition {
return {
name: 'mysql_table_stats',
title: 'MySQL Table Stats',
description: 'Get detailed table statistics including size, rows, and engine info.',
group: 'performance',
inputSchema: TableStatsSchema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (params: unknown, _context: RequestContext) => {
const { table } = TableStatsSchema.parse(params);
const sql = `
SELECT
TABLE_NAME as table_name,
ENGINE as engine,
ROW_FORMAT as row_format,
TABLE_ROWS as estimated_rows,
AVG_ROW_LENGTH as avg_row_length,
DATA_LENGTH as data_size_bytes,
INDEX_LENGTH as index_size_bytes,
DATA_FREE as free_space_bytes,
AUTO_INCREMENT as auto_increment,
CREATE_TIME as create_time,
UPDATE_TIME as update_time,
TABLE_COLLATION as collation
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = ?
`;
const result = await adapter.executeReadQuery(sql, [table]);
return { stats: result.rows?.[0] };
}
};
}
export function createBufferPoolStatsTool(adapter: MySQLAdapter): ToolDefinition {
const schema = z.object({});
return {
name: 'mysql_buffer_pool_stats',
title: 'MySQL Buffer Pool Stats',
description: 'Get InnoDB buffer pool statistics.',
group: 'performance',
inputSchema: schema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (_params: unknown, _context: RequestContext) => {
// Use SELECT * for compatibility across MySQL versions
// Different MySQL versions have different column sets
const result = await adapter.executeReadQuery(
`SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS`
);
return { bufferPoolStats: result.rows };
}
};
}
export function createThreadStatsTool(adapter: MySQLAdapter): ToolDefinition {
const schema = z.object({});
return {
name: 'mysql_thread_stats',
title: 'MySQL Thread Stats',
description: 'Get thread activity statistics.',
group: 'performance',
inputSchema: schema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (_params: unknown, _context: RequestContext) => {
const result = await adapter.executeReadQuery(`
SELECT
THREAD_ID,
NAME,
TYPE,
PROCESSLIST_ID,
PROCESSLIST_USER,
PROCESSLIST_HOST,
PROCESSLIST_DB,
PROCESSLIST_COMMAND,
PROCESSLIST_TIME,
PROCESSLIST_STATE,
CONNECTION_TYPE
FROM performance_schema.threads
WHERE PROCESSLIST_ID IS NOT NULL
ORDER BY PROCESSLIST_TIME DESC
`);
return { threads: result.rows };
}
};
}
