-- Check current memory settings
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%cache%';

-- Key buffer size for MyISAM tables
SET GLOBAL key_buffer_size = 256M;

-- InnoDB buffer pool size (most important for InnoDB)
-- Set in my.cnf: innodb_buffer_pool_size = 1G

-- Check InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Key InnoDB variables
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

[mysqld]
# InnoDB Settings
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1

# Query Cache (deprecated in MySQL 8.0)
query_cache_type = 1
query_cache_size = 256M

# Connection Settings
max_connections = 200
max_connect_errors = 1000000

# Buffer Settings
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

-- Analyze query execution plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Extended explain information
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = '[email protected]';

-- Visual explain (MySQL Workbench)
EXPLAIN FORMAT=TREE SELECT * FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE c.country = 'USA';

-- Check table indexes
SHOW INDEX FROM table_name;

-- Analyze index usage
SELECT * FROM sys.schema_unused_indexes;

-- Find duplicate indexes
SELECT * FROM sys.schema_redundant_indexes;

-- Create composite indexes for common queries
CREATE INDEX idx_user_email_status ON users(email, status);

-- Drop unused indexes
DROP INDEX unused_index_name ON table_name;

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Check slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- Performance schema queries
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY avg_timer_wait DESC LIMIT 10;

-- Single column index
CREATE INDEX idx_lastname ON users(last_name);

-- Composite index (order matters!)
CREATE INDEX idx_user_search ON users(last_name, first_name, email);

-- Covering index (includes all needed columns)
CREATE INDEX idx_order_details ON orders(customer_id, order_date, status, total);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Functional index (MySQL 8.0+)
CREATE INDEX idx_email_domain ON users((SUBSTRING_INDEX(email, '@', -1)));

-- Check index cardinality
SELECT table_name, index_name, cardinality 
FROM information_schema.statistics 
WHERE table_schema = 'your_database';

-- Analyze table to update statistics
ANALYZE TABLE table_name;

-- Optimize table (rebuilds indexes)
OPTIMIZE TABLE table_name;

-- Check fragmentation
SELECT table_name, data_length, index_length, data_free
FROM information_schema.tables 
WHERE table_schema = 'your_database';

-- Connection statistics
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads%';

-- Query performance
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Queries';

-- Buffer pool hit ratio (should be > 95%)
SELECT (1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)) * 100 
AS hit_ratio
FROM (
  SELECT variable_value AS innodb_buffer_pool_reads 
  FROM performance_schema.global_status 
  WHERE variable_name = 'Innodb_buffer_pool_reads'
) reads,
(
  SELECT variable_value AS innodb_buffer_pool_read_requests 
  FROM performance_schema.global_status 
  WHERE variable_name = 'Innodb_buffer_pool_read_requests'
) requests;

-- Enable performance schema (requires restart)
-- In my.cnf: performance_schema = ON

-- Top SQL statements by execution time
SELECT schema_name, digest_text, count_star, avg_timer_wait/1000000000 as avg_ms
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY avg_timer_wait DESC LIMIT 10;

-- Table I/O statistics
SELECT object_schema, object_name, count_read, count_write, count_fetch
FROM performance_schema.table_io_waits_summary_by_table 
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY count_read + count_write DESC;

-- Index usage statistics
SELECT object_schema, object_name, index_name, count_fetch, count_insert, count_update, count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY count_fetch DESC;

-- Check total memory usage
SELECT @@global.innodb_buffer_pool_size / 1024 / 1024 / 1024 AS 'InnoDB Buffer Pool (GB)',
       @@global.key_buffer_size / 1024 / 1024 AS 'Key Buffer (MB)',
       @@global.query_cache_size / 1024 / 1024 AS 'Query Cache (MB)';

-- Memory usage by component
SELECT event_name, current_alloc / 1024 / 1024 AS 'Current MB'
FROM performance_schema.memory_summary_global_by_event_name
WHERE current_alloc > 0
ORDER BY current_alloc DESC;

-- Check disk I/O statistics
SELECT file_name, count_read, count_write, sum_number_of_bytes_read, sum_number_of_bytes_write
FROM performance_schema.file_summary_by_instance
WHERE file_name LIKE '%.ibd'
ORDER BY count_read + count_write DESC LIMIT 10;

-- InnoDB file I/O
SHOW STATUS LIKE 'Innodb_data_reads';
SHOW STATUS LIKE 'Innodb_data_writes';
SHOW STATUS LIKE 'Innodb_os_log_written';

-- Monitor connections
SHOW PROCESSLIST;

-- Connection statistics
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Aborted_connects';
SHOW STATUS LIKE 'Aborted_clients';

-- Optimize connection settings
SET GLOBAL max_connections = 200;
SET GLOBAL connect_timeout = 10;
SET GLOBAL interactive_timeout = 3600;
SET GLOBAL wait_timeout = 3600;

# my.cnf configuration
[mysqld]
thread_handling = pool-of-threads
thread_pool_size = 16
thread_pool_stall_limit = 10
thread_pool_max_threads = 1000

-- Enable query cache
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 256M;

-- Monitor query cache performance
SHOW STATUS LIKE 'Qcache%';

-- Query cache hit ratio
SELECT (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100 AS hit_ratio
FROM (
  SELECT variable_value AS Qcache_hits FROM performance_schema.global_status WHERE variable_name = 'Qcache_hits'
) hits,
(
  SELECT variable_value AS Qcache_inserts FROM performance_schema.global_status WHERE variable_name = 'Qcache_inserts'  
) inserts;

# Basic load test
mysqlslap --user=root --password --host=localhost \
  --concurrency=50 --iterations=10 --create-schema=test \
  --query="SELECT * FROM users WHERE id = FLOOR(RAND() * 1000)"

# Custom query file test
mysqlslap --user=root --password --host=localhost \
  --concurrency=100 --iterations=5 --create-schema=test \
  --query=/path/to/queries.sql

-- Stress test with random data
SELECT BENCHMARK(1000000, SQRT(RAND()));

-- Test index performance
SELECT SQL_NO_CACHE * FROM large_table WHERE indexed_column = 'value';
SELECT SQL_NO_CACHE * FROM large_table WHERE non_indexed_column = 'value';