Optimize MariaDB 11.6 performance with query analysis and indexing for high-traffic applications

Enable Performance Schema and query logging

Configure MariaDB to collect detailed query performance metrics and enable slow query logging for analysis.

[mysqld]
Performance Schema configuration
performance_schema = ON
performance_schema_consumer_events_statements_current = ON
performance_schema_consumer_events_statements_history = ON
performance_schema_consumer_events_statements_history_long = ON

Slow query log configuration
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

Query cache optimization
query_cache_type = 1
query_cache_size = 268435456
query_cache_limit = 2097152

Connection and buffer optimizations
max_connections = 500
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1

Restart MariaDB to apply configuration

Restart the service to enable Performance Schema and slow query logging.

sudo systemctl restart mariadb
sudo systemctl status mariadb

Create sample high-traffic database schema

Set up a realistic e-commerce database schema to demonstrate optimization techniques.

sudo mysql -u root -p

CREATE DATABASE ecommerce_db;
USE ecommerce_db;

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    INDEX idx_email (email),
    INDEX idx_status_created (status, created_at)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category_price (category_id, price),
    INDEX idx_stock (stock_quantity),
    INDEX idx_name (name)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user_status (user_id, order_status),
    INDEX idx_status_date (order_status, created_at),
    INDEX idx_created_at (created_at)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_order_product (order_id, product_id),
    INDEX idx_product_id (product_id)
);

Generate sample data for testing

Insert realistic test data to simulate high-traffic conditions and analyze query performance.

# Insert sample users
INSERT INTO users (username, email, status) VALUES
('john_doe', 'john@example.com', 'active'),
('jane_smith', 'jane@example.com', 'active'),
('bob_wilson', 'bob@example.com', 'inactive');

Insert sample products
INSERT INTO products (name, category_id, price, stock_quantity) VALUES
('Gaming Laptop', 1, 1299.99, 50),
('Wireless Mouse', 1, 29.99, 200),
('Mechanical Keyboard', 1, 149.99, 75),
('4K Monitor', 1, 399.99, 30),
('USB-C Hub', 1, 79.99, 100);

Insert sample orders
INSERT INTO orders (user_id, total_amount, order_status) VALUES
(1, 1329.98, 'delivered'),
(2, 229.98, 'processing'),
(1, 79.99, 'shipped'),
(3, 449.98, 'pending');

Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 1299.99),
(1, 2, 1, 29.99),
(2, 3, 1, 149.99),
(2, 2, 1, 29.99),
(2, 1, 1, 1299.99),
(3, 5, 1, 79.99),
(4, 4, 1, 399.99),
(4, 1, 1, 1299.99);

Analyze query performance with EXPLAIN

Use EXPLAIN to understand query execution plans and identify optimization opportunities.

# Analyze a complex join query
EXPLAIN EXTENDED
SELECT u.username, u.email, o.order_id, o.total_amount, p.name, oi.quantity
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_status = 'delivered'
AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 10;

Show detailed execution information
SHOW WARNINGS;

Create covering indexes for complex queries

Implement covering indexes that include all columns needed for queries to avoid table lookups.

# Create covering index for order analysis queries
CREATE INDEX idx_orders_covering ON orders (order_status, created_at, user_id, order_id, total_amount);

Create covering index for product searches
CREATE INDEX idx_products_covering ON products (category_id, price, name, product_id, stock_quantity);

Create covering index for user order analysis
CREATE INDEX idx_users_covering ON users (status, created_at, user_id, username, email);

Analyze index usage
ANALYZE TABLE users, products, orders, order_items;

Configure Performance Schema for query profiling

Set up Performance Schema consumers and instruments for detailed query analysis.

# Enable statement profiling instruments
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement%';

Enable stage profiling for query execution phases
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%stage%';

Enable wait event profiling
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%wait/io%';

Configure statement history retention
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME IN (
    'events_statements_current',
    'events_statements_history',
    'events_statements_history_long'
);

Create query performance monitoring views

Build custom views to easily identify slow queries and performance bottlenecks.

# Create view for top slow queries
CREATE VIEW slow_queries_summary AS
SELECT 
    DIGEST_TEXT as query_pattern,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT/1000000000000 as avg_time_sec,
    MAX_TIMER_WAIT/1000000000000 as max_time_sec,
    SUM_TIMER_WAIT/1000000000000 as total_time_sec,
    SUM_ROWS_EXAMINED as total_rows_examined,
    SUM_ROWS_SENT as total_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY AVG_TIMER_WAIT DESC;

Create view for index usage analysis
CREATE VIEW index_usage_stats AS
SELECT 
    OBJECT_SCHEMA as db_name,
    OBJECT_NAME as table_name,
    INDEX_NAME as index_name,
    COUNT_FETCH as index_fetches,
    COUNT_INSERT as index_inserts,
    COUNT_UPDATE as index_updates,
    COUNT_DELETE as index_deletes
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'ecommerce_db'
ORDER BY COUNT_FETCH DESC;

Set up automated slow query analysis script

Create a monitoring script that parses slow query logs and generates performance reports.

#!/bin/bash

MariaDB Performance Monitoring Script
Analyzes slow queries and generates performance reports

LOG_FILE="/var/log/mysql/mysql-slow.log"
REPORT_FILE="/var/log/mysql/performance_report_$(date +%Y%m%d_%H%M%S).txt"
ALERT_THRESHOLD=5.0  # Alert for queries taking more than 5 seconds

echo "MariaDB Performance Report - $(date)" > "$REPORT_FILE"
echo "=============================================" >> "$REPORT_FILE"
echo "" >> "$REPORT_FILE"

Check if slow query log exists
if [[ ! -f "$LOG_FILE" ]]; then
    echo "Error: Slow query log not found at $LOG_FILE" >> "$REPORT_FILE"
    exit 1
fi

Analyze slow queries using mysqldumpslow
echo "Top 10 Slowest Query Patterns:" >> "$REPORT_FILE"
echo "------------------------------" >> "$REPORT_FILE"
mysqldumpslow -s t -t 10 "$LOG_FILE" >> "$REPORT_FILE" 2>/dev/null
echo "" >> "$REPORT_FILE"

echo "Top 10 Most Frequent Slow Queries:" >> "$REPORT_FILE"
echo "----------------------------------" >> "$REPORT_FILE"
mysqldumpslow -s c -t 10 "$LOG_FILE" >> "$REPORT_FILE" 2>/dev/null
echo "" >> "$REPORT_FILE"

Query Performance Schema for current statistics
echo "Current Performance Metrics:" >> "$REPORT_FILE"
echo "---------------------------" >> "$REPORT_FILE"
mysql -u root -e "
SELECT 
    'Total Queries' as metric,
    FORMAT(SUM(COUNT_STAR), 0) as value
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%'
UNION ALL
SELECT 
    'Avg Query Time (ms)' as metric,
    FORMAT(AVG(AVG_TIMER_WAIT)/1000000000, 2) as value
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%' AND COUNT_STAR > 0
UNION ALL
SELECT 
    'Queries > ${ALERT_THRESHOLD}s' as metric,
    COUNT(*) as value
FROM performance_schema.events_statements_history_long
WHERE TIMER_WAIT > ${ALERT_THRESHOLD}000000000000;
" >> "$REPORT_FILE" 2>/dev/null

echo "" >> "$REPORT_FILE"
echo "Index Usage Analysis:" >> "$REPORT_FILE"
echo "--------------------" >> "$REPORT_FILE"
mysql -u root -e "
SELECT 
    CONCAT(OBJECT_SCHEMA, '.', OBJECT_NAME) as table_name,
    INDEX_NAME,
    FORMAT(COUNT_FETCH, 0) as fetches,
    FORMAT(COUNT_INSERT, 0) as inserts,
    FORMAT(COUNT_UPDATE, 0) as updates,
    FORMAT(COUNT_DELETE, 0) as deletes
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
AND COUNT_FETCH > 0
ORDER BY COUNT_FETCH DESC
LIMIT 20;
" >> "$REPORT_FILE" 2>/dev/null

echo "Performance report generated: $REPORT_FILE"

Check for critical performance issues
SLOW_QUERIES=$(mysql -u root -s -e "SELECT COUNT(*) FROM performance_schema.events_statements_history_long WHERE TIMER_WAIT > ${ALERT_THRESHOLD}000000000000;" 2>/dev/null)

if [[ "$SLOW_QUERIES" -gt 10 ]]; then
    echo "WARNING: $SLOW_QUERIES queries detected taking longer than ${ALERT_THRESHOLD} seconds"
    # Send alert email (configure mail system separately)
    # echo "Critical performance alert: $SLOW_QUERIES slow queries detected" | mail -s "MariaDB Performance Alert" admin@example.com
fi

Make monitoring script executable and schedule it

Set up automated performance monitoring with proper permissions and cron scheduling.

sudo chown root:root /usr/local/bin/mariadb_performance_monitor.sh
sudo chmod 755 /usr/local/bin/mariadb_performance_monitor.sh

# Create log directory for reports
sudo mkdir -p /var/log/mysql/reports
sudo chown mysql:mysql /var/log/mysql/reports
sudo chmod 775 /var/log/mysql/reports

# Schedule monitoring script to run every hour
sudo crontab -e

# MariaDB Performance Monitoring - runs every hour
0     /usr/local/bin/mariadb_performance_monitor.sh >/dev/null 2>&1

Weekly slow query log rotation
0 0   0 /usr/sbin/logrotate -f /etc/logrotate.d/mysql-server

Configure query optimization settings

Fine-tune MariaDB optimizer settings for better query execution plans and performance.

[mysqld]
Query optimizer settings
optimizer_search_depth = 62
optimizer_prune_level = 1
optimizer_use_condition_selectivity = 4

Join buffer optimization
join_buffer_size = 2M
join_buffer_space_limit = 16M

Sort and temporary table optimization
sort_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

Thread and connection optimization
thread_cache_size = 16
table_open_cache = 4096
table_definition_cache = 2048

InnoDB optimization for high concurrency
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_method = O_DIRECT
innodb_adaptive_hash_index = ON
innodb_stats_on_metadata = OFF

Apply optimization settings and restart

Restart MariaDB to apply the new optimization configuration.

sudo systemctl restart mariadb
sudo systemctl status mariadb

Test query performance improvements

Execute test queries and compare performance before and after optimization.

sudo mysql -u root -p ecommerce_db

# Test complex query performance
SET profiling = 1;

SELECT 
    u.username,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order_value
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.status = 'active'
GROUP BY u.user_id, u.username
HAVING total_orders > 0
ORDER BY total_spent DESC;

Show query profiling information
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

Check index usage for the query
EXPLAIN EXTENDED
SELECT 
    u.username,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order_value
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.status = 'active'
GROUP BY u.user_id, u.username
HAVING total_orders > 0
ORDER BY total_spent DESC;