Database Workload Profiling

To determine if a data base is primarily utilized for read or write operations, inspect the global status counters:

SHOW STATUS LIKE 'Com_%';

Key metrics to evaluate:

• Com_select: Total count of SELECT operations.
• Com_insert/Com_update/Com_delete: Frequency of data modification operations.
• Innodb_rows_read/inserted/updated/deleted: Specific row-level statistics for the InnoDB storage engine.

Monitoring Com_commit and Com_rollback can also identify inefficient transaction management in appilcation logic.

Identifying Inefficient Queries

Real-time analysis is performed using the process list:

SHOW PROCESSLIST;

This command reveals the current status, execution time, and potential locking issues for all active threads.

Diagnosing Lock Contention

MySQL 5.7 and Earlier

Query information_schema tables to link waiting transactions with blocking ones:

SELECT 
    r.trx_wait_started AS wait_start,
    r.trx_mysql_thread_id AS waiting_pid,
    b.trx_mysql_thread_id AS blocking_pid,
    r.trx_query AS waiting_sql,
    b.trx_query AS blocking_sql
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

MySQL 8.0

Leverage the performance_schema for more detailed locking insights:

SELECT 
    t.PROCESSLIST_ID, 
    dl.OBJECT_NAME, 
    dl.LOCK_TYPE, 
    dl.LOCK_STATUS
FROM performance_schema.data_locks dl
JOIN performance_schema.data_lock_waits dw ON dl.ENGINE_LOCK_ID = dw.BLOCKING_ENGINE_LOCK_ID
JOIN performance_schema.threads t ON t.THREAD_ID = dl.THREAD_ID;

High Resource Usage Analysis

To identify queries consuming high CPU or memory, map operating system threads to MySQL data base threads:

1. Locate the PID via ps -ef | grep mysql.
2. Use top -p <PID> -H to find the OS thread ID (LWP).
3. Correlate with MySQL:

SELECT thread_id, PROCESSLIST_ID 
FROM performance_schema.threads 
WHERE THREAD_OS_ID = <LWP>;

4. Extract the query:

SELECT sql_text 
FROM performance_schema.events_statements_current 
WHERE thread_id = <thread_id>;

Analyzing Top-Performing Queries

If query logs are unavailable, query the sys schema to aggregate execution history:

-- Retrieve queries with the longest total execution time
SELECT query, exec_count, total_latency 
FROM sys.statement_analysis 
ORDER BY total_latency DESC 
LIMIT 10;

-- Retrieve queries with the highest average latency
SELECT digest_text, avg_timer_wait 
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY avg_timer_wait DESC 
LIMIT 10;

Managing Connections

Monitor active concurrency and connection limits:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW VARIABLES LIKE 'max_connections';

For bulk termination of stagnant sessions (excluding idle connections):

SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.processlist 
WHERE command != 'Sleep' AND time > 60;