/**
* MySQL sys Schema Tools - Resource Monitoring
*
* Tools for monitoring database resources and objects.
* 3 tools: schema_stats, innodb_lock_waits, memory_summary.
*/
import { z } from 'zod';
import type { MySQLAdapter } from '../../MySQLAdapter.js';
import type { ToolDefinition, RequestContext } from '../../../../types/index.js';
// =============================================================================
// Zod Schemas
// =============================================================================
const LimitSchema = z.object({
limit: z.number().default(20).describe('Maximum number of results to return')
});
/**
* Get schema object statistics
*/
export function createSysSchemaStatsTool(adapter: MySQLAdapter): ToolDefinition {
return {
name: 'mysql_sys_schema_stats',
title: 'MySQL Schema Statistics',
description: 'Get aggregated statistics for a schema including tables, indexes, and auto-increment status.',
group: 'sysschema',
inputSchema: z.object({
schema: z.string().optional().describe('Schema name (defaults to current database)'),
limit: z.number().default(50).describe('Maximum number of results')
}),
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (params: unknown, _context: RequestContext) => {
const { schema, limit } = z.object({
schema: z.string().optional(),
limit: z.number().default(50)
}).parse(params);
// Get table statistics
const tableStatsQuery = `
SELECT
table_schema,
table_name,
rows_fetched,
fetch_latency,
rows_inserted,
insert_latency,
rows_updated,
update_latency,
rows_deleted,
delete_latency,
io_read_requests,
io_read,
io_read_latency,
io_write_requests,
io_write,
io_write_latency
FROM sys.schema_table_statistics
WHERE table_schema = COALESCE(?, DATABASE())
ORDER BY (fetch_latency + insert_latency + update_latency + delete_latency) DESC
LIMIT ${String(limit)}
`;
// Get index statistics
const indexStatsQuery = `
SELECT
table_schema,
table_name,
index_name,
rows_selected,
select_latency,
rows_inserted,
insert_latency,
rows_updated,
update_latency,
rows_deleted,
delete_latency
FROM sys.schema_index_statistics
WHERE table_schema = COALESCE(?, DATABASE())
ORDER BY (select_latency + insert_latency + update_latency + delete_latency) DESC
LIMIT ${String(limit)}
`;
// Get auto-increment status
const autoIncQuery = `
SELECT
table_schema,
table_name,
column_name,
data_type,
column_type,
auto_increment_ratio
FROM sys.schema_auto_increment_columns
WHERE table_schema = COALESCE(?, DATABASE())
ORDER BY auto_increment_ratio DESC
LIMIT ${String(limit)}
`;
const [tableStats, indexStats, autoIncStats] = await Promise.all([
adapter.executeQuery(tableStatsQuery, [schema ?? null]),
adapter.executeQuery(indexStatsQuery, [schema ?? null]),
adapter.executeQuery(autoIncQuery, [schema ?? null])
]);
return {
tableStatistics: tableStats.rows ?? [],
indexStatistics: indexStats.rows ?? [],
autoIncrementStatus: autoIncStats.rows ?? [],
schemaName: schema ?? 'current'
};
}
};
}
/**
* Get InnoDB lock waits
*/
export function createSysInnoDBLockWaitsTool(adapter: MySQLAdapter): ToolDefinition {
return {
name: 'mysql_sys_innodb_lock_waits',
title: 'MySQL InnoDB Lock Waits',
description: 'Get current InnoDB lock contention information from sys schema.',
group: 'sysschema',
inputSchema: LimitSchema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (params: unknown, _context: RequestContext) => {
const { limit } = LimitSchema.parse(params);
const query = `
SELECT
wait_started,
wait_age,
locked_table,
locked_index,
locked_type,
waiting_trx_id,
waiting_trx_started,
waiting_trx_age,
waiting_trx_rows_locked,
waiting_trx_rows_modified,
waiting_query,
waiting_lock_mode,
blocking_trx_id,
blocking_trx_started,
blocking_trx_age,
blocking_trx_rows_locked,
blocking_trx_rows_modified,
blocking_query,
blocking_lock_mode
FROM sys.innodb_lock_waits
ORDER BY wait_started
LIMIT ${String(limit)}
`;
const result = await adapter.executeQuery(query);
return {
lockWaits: result.rows,
count: result.rows?.length ?? 0,
hasContention: (result.rows?.length ?? 0) > 0
};
}
};
}
/**
* Get memory usage summary
*/
export function createSysMemorySummaryTool(adapter: MySQLAdapter): ToolDefinition {
return {
name: 'mysql_sys_memory_summary',
title: 'MySQL Memory Summary',
description: 'Get memory usage summary by allocation type from sys schema.',
group: 'sysschema',
inputSchema: LimitSchema,
requiredScopes: ['read'],
annotations: {
readOnlyHint: true,
idempotentHint: true
},
handler: async (params: unknown, _context: RequestContext) => {
const { limit } = LimitSchema.parse(params);
// Global memory summary
const globalQuery = `
SELECT
event_name,
current_count,
current_alloc,
current_avg_alloc,
high_count,
high_alloc,
high_avg_alloc
FROM sys.memory_global_by_current_bytes
ORDER BY current_alloc DESC
LIMIT ${String(limit)}
`;
// Memory by user
const userQuery = `
SELECT
user,
current_count_used,
current_allocated,
current_avg_alloc,
current_max_alloc,
total_allocated
FROM sys.memory_by_user_by_current_bytes
ORDER BY current_allocated DESC
LIMIT ${String(limit)}
`;
const [globalStats, userStats] = await Promise.all([
adapter.executeQuery(globalQuery),
adapter.executeQuery(userQuery)
]);
return {
globalMemory: globalStats.rows ?? [],
memoryByUser: userStats.rows ?? []
};
}
};
}
