What are the key parameters and optimization strategies for MariaDB performance tuning?

MariaDB performance tuning requires optimization across multiple dimensions. Here are the main tuning strategies:

1. Configuration Parameter Optimization

ini
# my.cnf configuration file

# Connection configuration
max_connections = 500
max_connect_errors = 100000
wait_timeout = 28800
interactive_timeout = 28800

# InnoDB configuration
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# MyISAM configuration
key_buffer_size = 256M
myisam_sort_buffer_size = 64M

# Query cache (removed in MariaDB 10.3+)
# query_cache_size = 64M
# query_cache_type = 1

# Temporary table configuration
tmp_table_size = 256M
max_heap_table_size = 256M

# Sort and join configuration
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

# Thread configuration
thread_cache_size = 16
thread_stack = 256K

# Log configuration
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1

2. Memory Optimization

sql
-- Check InnoDB buffer pool usage
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Check connection count
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- Check memory usage
SHOW STATUS LIKE 'Memory%';

3. Hardware Optimization

1. CPU: Multi-core processor, recommended 8+ cores
2. Memory: Recommended 16GB+, InnoDB buffer pool 70-80%
3. Disk: Use SSD, configure RAID 10
4. Network: Gigabit+ network bandwidth

4. Table Structure Optimization

sql
-- Use appropriate data types
-- Not recommended: VARCHAR(255) for status fields
-- Recommended: TINYINT or ENUM

-- Balance normalization and denormalization
-- Read-heavy: Appropriate denormalization
-- Write-heavy: Maintain normalization

-- Partition tables
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

5. Query Optimization

sql
-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- Create appropriate indexes
CREATE INDEX idx_user_id_created ON orders(user_id, created_at);

-- Avoid full table scans
SELECT * FROM large_table WHERE indexed_column = 'value';

-- Use batch operations
INSERT INTO users (name, email) VALUES
    ('John', 'john@example.com'),
    ('Jane', 'jane@example.com');

6. Monitoring and Diagnostics

sql
-- View slow queries
SELECT * FROM mysql.slow_log 
ORDER BY query_time DESC LIMIT 10;

-- View table status
SHOW TABLE STATUS FROM database_name;

-- View index usage
SELECT * FROM information_schema.statistics 
WHERE table_schema = 'database_name';

-- View process list
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

7. Performance Testing Tools

bash
# Use sysbench for performance testing
sysbench oltp_read_write \
  --mysql-host=localhost \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  --threads=16 \
  --time=300 \
  --report-interval=10 \
  run

8. Regular Maintenance

sql
-- Analyze table
ANALYZE TABLE table_name;

-- Optimize table
OPTIMIZE TABLE table_name;

-- Check table
CHECK TABLE table_name;

-- Repair table
REPAIR TABLE table_name;

Through systematic performance tuning, you can significantly improve MariaDB's overall performance and stability.