HOME / BLOG

MariaDB/MySQL Performance Optimization Guide

15. August 2025

Development / Database / Deployment / Enviroment

.SHARE

Quick Wins (Immediate Impact)

1. The 30x Performance Trick

Run this on all your tables – can improve query performance by 30x:


ANALYZE TABLE your_table_name PERSISTENT FOR ALL;

ANALYZE TABLE your_table_name PERSISTENT FOR ALL;

This generates and stores histograms for data distribution, allowing the optimizer to make much better decisions about join order and filtering.

2. Enable Engine-Independent Statistics


SET GLOBAL use_stat_tables = PREFERABLY;
SET GLOBAL optimizer_use_condition_selectivity = 4;

SET GLOBAL use_stat_tables = PREFERABLY;

SET GLOBAL optimizer_use_condition_selectivity = 4;

3. Update Table Statistics Regularly


# Add to cron job for weekly execution
mysqlcheck --analyze --all-databases -u root -p

# Add to cron job for weekly execution

mysqlcheck --analyze --all-databases -u root -p

Advanced Configuration File Optimizations

Complete my.cnf/my.ini Configuration


[mysqld]
# Memory Settings
# Set to 70-80% of available RAM for dedicated database servers
innodb_buffer_pool_size = 8G
# Optimal: 1 instance per 128GB of buffer pool (MariaDB 10.3+)
innodb_buffer_pool_instances = 1
# Chunk size for dynamic resizing (default 128M)
innodb_buffer_pool_chunk_size = 128M

# Connection Management
max_connections = 500
max_connect_errors = 10000
thread_cache_size = 100
# Thread pool for better concurrency (MariaDB)
thread_handling = pool-of-threads
thread_pool_size = 8
thread_pool_max_threads = 2000

# Query Cache (MySQL 5.7 and earlier)
# Note: Disabled by default in MySQL 8.0+
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 8M
query_cache_min_res_unit = 2K

# InnoDB Advanced Settings
# Log file size - 25% of buffer pool size
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M

# Flush settings for performance vs durability
innodb_flush_log_at_trx_commit = 2  # 0=fastest, 1=safest, 2=good compromise
innodb_flush_method = O_DIRECT

# File per table
innodb_file_per_table = 1

# Read-ahead optimization
innodb_read_ahead_threshold = 56  # Default 56, try 0 for random workloads

# I/O Settings
innodb_io_capacity = 2000  # Set to IOPS capability of your storage
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# Optimizer Switches - this is one long line in practice
optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on'

# Temp Tables
tmp_table_size = 512M
max_heap_table_size = 512M

# Sort and Group
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 8M

# Timeouts
wait_timeout = 600
interactive_timeout = 600
lock_wait_timeout = 120

# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# Binary Logging (if using replication)
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G

# MariaDB Specific Optimizations
# Aria storage engine (MariaDB)
aria_pagecache_buffer_size = 1G
aria_sort_buffer_size = 256M

# Security
skip_name_resolve = 1

[mysqld]

# Memory Settings

# Set to 70-80% of available RAM for dedicated database servers

innodb_buffer_pool_size = 8G

# Optimal: 1 instance per 128GB of buffer pool (MariaDB 10.3+)

innodb_buffer_pool_instances = 1

# Chunk size for dynamic resizing (default 128M)

innodb_buffer_pool_chunk_size = 128M

# Connection Management

max_connections = 500

max_connect_errors = 10000

thread_cache_size = 100

# Thread pool for better concurrency (MariaDB)

thread_handling = pool-of-threads

thread_pool_size = 8

thread_pool_max_threads = 2000

# Query Cache (MySQL 5.7 and earlier)

# Note: Disabled by default in MySQL 8.0+

query_cache_type = 1

query_cache_size = 256M

query_cache_limit = 8M

query_cache_min_res_unit = 2K

# InnoDB Advanced Settings

# Log file size - 25% of buffer pool size

innodb_log_file_size = 2G

innodb_log_files_in_group = 2

innodb_log_buffer_size = 64M

# Flush settings for performance vs durability

innodb_flush_log_at_trx_commit = 2 # 0=fastest, 1=safest, 2=good compromise

innodb_flush_method = O_DIRECT

# File per table

innodb_file_per_table = 1

# Read-ahead optimization

innodb_read_ahead_threshold = 56 # Default 56, try 0 for random workloads

# I/O Settings

innodb_io_capacity = 2000 # Set to IOPS capability of your storage

innodb_io_capacity_max = 4000

innodb_read_io_threads = 8

innodb_write_io_threads = 8

# Optimizer Switches - this is one long line in practice

optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on'

# Temp Tables

tmp_table_size = 512M

max_heap_table_size = 512M

# Sort and Group

sort_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

join_buffer_size = 8M

# Timeouts

wait_timeout = 600

interactive_timeout = 600

lock_wait_timeout = 120

# Slow Query Log

slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 1

log_queries_not_using_indexes = 1

# Binary Logging (if using replication)

binlog_format = ROW

expire_logs_days = 7

max_binlog_size = 1G

# MariaDB Specific Optimizations

# Aria storage engine (MariaDB)

aria_pagecache_buffer_size = 1G

aria_sort_buffer_size = 256M

# Security

skip_name_resolve = 1

Advanced InnoDB Tweaks

1. Buffer Pool Preflushing


-- Aggressive flushing for high-write workloads
SET GLOBAL innodb_max_dirty_pages_pct = 75;
SET GLOBAL innodb_max_dirty_pages_pct_lwm = 50;

-- Aggressive flushing for high-write workloads

SET GLOBAL innodb_max_dirty_pages_pct = 75;

SET GLOBAL innodb_max_dirty_pages_pct_lwm = 50;

2. Adaptive Hash Index Control


-- Disable if you have many range scans
SET GLOBAL innodb_adaptive_hash_index = OFF;

-- Disable if you have many range scans

SET GLOBAL innodb_adaptive_hash_index = OFF;

3. Page Cleaners (MySQL 5.7+)


# Multiple page cleaners for better I/O parallelism
innodb_page_cleaners = 8

# Multiple page cleaners for better I/O parallelism

innodb_page_cleaners = 8

4. Doublewrite Buffer Optimization


# For SSD storage with battery-backed cache
innodb_doublewrite = 0  # Use with caution - test thoroughly

# For SSD storage with battery-backed cache

innodb_doublewrite = 0 # Use with caution - test thoroughly

OS-Level Optimizations

1. Virtual Memory Settings


# Reduce swappiness
echo 'vm.swappiness = 1' >> /etc/sysctl.conf

# Increase dirty page thresholds
echo 'vm.dirty_ratio = 15' >> /etc/sysctl.conf
echo 'vm.dirty_background_ratio = 5' >> /etc/sysctl.conf

# Apply changes
sysctl -p

# Reduce swappiness

echo 'vm.swappiness = 1' >> /etc/sysctl.conf

# Increase dirty page thresholds

echo 'vm.dirty_ratio = 15' >> /etc/sysctl.conf

echo 'vm.dirty_background_ratio = 5' >> /etc/sysctl.conf

# Apply changes

sysctl -p

2. File System Optimizations


# Add noatime to /etc/fstab for database partition
/dev/sdb1 /var/lib/mysql ext4 defaults,noatime,data=writeback 0 0

# Remount with new options
mount -o remount /var/lib/mysql

# Add noatime to /etc/fstab for database partition

/dev/sdb1 /var/lib/mysql ext4 defaults,noatime,data=writeback 0 0

# Remount with new options

mount -o remount /var/lib/mysql

3. I/O Scheduler


# For SSD storage
echo noop > /sys/block/sda/queue/scheduler

# For traditional HDDs
echo deadline > /sys/block/sda/queue/scheduler

# For SSD storage

echo noop > /sys/block/sda/queue/scheduler

# For traditional HDDs

echo deadline > /sys/block/sda/queue/scheduler

4. CPU Governor


# Set CPU to performance mode
echo performance | tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

# Set CPU to performance mode

echo performance | tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

5. Network Optimizations


# Increase network buffers
echo 'net.core.rmem_max = 268435456' >> /etc/sysctl.conf
echo 'net.core.wmem_max = 268435456' >> /etc/sysctl.conf
echo 'net.ipv4.tcp_rmem = 4096 87380 268435456' >> /etc/sysctl.conf
echo 'net.ipv4.tcp_wmem = 4096 65536 268435456' >> /etc/sysctl.conf

# Increase network buffers

echo 'net.core.rmem_max = 268435456' >> /etc/sysctl.conf

echo 'net.core.wmem_max = 268435456' >> /etc/sysctl.conf

echo 'net.ipv4.tcp_rmem = 4096 87380 268435456' >> /etc/sysctl.conf

echo 'net.ipv4.tcp_wmem = 4096 65536 268435456' >> /etc/sysctl.conf

Query and Schema Optimization

1. Index Optimization Commands


-- Identify unused indexes
SELECT DISTINCT
    CONCAT('DROP INDEX `', i.index_name, '` ON `', i.table_schema, '`.`', i.table_name, '`;') AS drop_command
FROM information_schema.statistics i
LEFT JOIN information_schema.index_statistics s 
    ON i.table_schema = s.table_schema 
    AND i.table_name = s.table_name 
    AND i.index_name = s.index_name
WHERE s.table_name IS NULL 
    AND i.index_name != 'PRIMARY'
    AND i.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

-- Find duplicate indexes
SELECT 
    table_schema,
    table_name,
    GROUP_CONCAT(index_name) as duplicate_indexes,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) as columns
FROM information_schema.statistics 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
GROUP BY table_schema, table_name, column_name
HAVING COUNT(*) > 1;

-- Identify unused indexes

SELECT DISTINCT

CONCAT('DROP INDEX `', i.index_name, '` ON `', i.table_schema, '`.`', i.table_name, '`;') AS drop_command

FROM information_schema.statistics i

LEFT JOIN information_schema.index_statistics s

ON i.table_schema = s.table_schema

AND i.table_name = s.table_name

AND i.index_name = s.index_name

WHERE s.table_name IS NULL

AND i.index_name != 'PRIMARY'

AND i.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

-- Find duplicate indexes

SELECT

table_schema,

table_name,

GROUP_CONCAT(index_name) as duplicate_indexes,

GROUP_CONCAT(column_name ORDER BY seq_in_index) as columns

FROM information_schema.statistics

WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')

GROUP BY table_schema, table_name, column_name

HAVING COUNT(*) > 1;

2. Analyze Table Structure


-- Find tables without primary keys
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints tc 
    ON t.table_schema = tc.table_schema 
    AND t.table_name = tc.table_name 
    AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL 
    AND t.table_type = 'BASE TABLE'
    AND t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

-- Find tables without primary keys

SELECT t.table_schema, t.table_name

FROM information_schema.tables t

LEFT JOIN information_schema.table_constraints tc

ON t.table_schema = tc.table_schema

AND t.table_name = tc.table_name

AND tc.constraint_type = 'PRIMARY KEY'

WHERE tc.constraint_name IS NULL

AND t.table_type = 'BASE TABLE'

AND t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

3. Partitioning for Large Tables


-- Example: Partition by date
ALTER TABLE large_table 
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

-- Example: Partition by date

ALTER TABLE large_table

PARTITION BY RANGE (YEAR(created_at)) (

PARTITION p2020 VALUES LESS THAN (2021),

PARTITION p2021 VALUES LESS THAN (2022),

PARTITION p2022 VALUES LESS THAN (2023),

PARTITION p2023 VALUES LESS THAN (2024),

PARTITION p2024 VALUES LESS THAN (2025),

PARTITION pFuture VALUES LESS THAN MAXVALUE

);

Performance Monitoring Commands

1. Real-time Performance Monitoring


-- Show current running queries
SHOW PROCESSLIST;

-- Engine status
SHOW ENGINE INNODB STATUS\G

-- Buffer pool hit ratio (should be > 99%)
SELECT 
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 
    AS buffer_pool_hit_ratio
FROM information_schema.global_status 
WHERE variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests');

-- Query cache hit ratio
SELECT 
    (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100 AS query_cache_hit_ratio
FROM information_schema.global_status 
WHERE variable_name IN ('Qcache_hits', 'Qcache_inserts');

-- Show current running queries

SHOW PROCESSLIST;

-- Engine status

SHOW ENGINE INNODB STATUS\G

-- Buffer pool hit ratio (should be > 99%)

SELECT

(1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100

AS buffer_pool_hit_ratio

FROM information_schema.global_status

WHERE variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests');

-- Query cache hit ratio

SELECT

(Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100 AS query_cache_hit_ratio

FROM information_schema.global_status

WHERE variable_name IN ('Qcache_hits', 'Qcache_inserts');

2. Performance Schema Queries (MySQL 5.6+)


-- Top 10 slowest queries by total execution time
SELECT query_digest_text, 
       count_star, 
       avg_timer_wait/1000000000 as avg_time_seconds,
       sum_timer_wait/1000000000 as total_time_seconds
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY sum_timer_wait DESC 
LIMIT 10;

-- Table I/O by reads
SELECT object_schema, object_name, count_read, count_write
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY count_read DESC
LIMIT 10;

-- Top 10 slowest queries by total execution time

SELECT query_digest_text,

count_star,

avg_timer_wait/1000000000 as avg_time_seconds,

sum_timer_wait/1000000000 as total_time_seconds

FROM performance_schema.events_statements_summary_by_digest

ORDER BY sum_timer_wait DESC

LIMIT 10;

-- Table I/O by reads

SELECT object_schema, object_name, count_read, count_write

FROM performance_schema.table_io_waits_summary_by_table

ORDER BY count_read DESC

LIMIT 10;

Version-Specific Optimizations

MariaDB 10.4+ Specific


# Instant DDL for better ALTER TABLE performance
innodb_alter_table_default_algorithm = INSTANT

# Improved thread pool
thread_pool_dedicated_listener = ON
thread_pool_exact_stats = ON

# Instant DDL for better ALTER TABLE performance

innodb_alter_table_default_algorithm = INSTANT

# Improved thread pool

thread_pool_dedicated_listener = ON

thread_pool_exact_stats = ON

MySQL 8.0+ Specific


# Invisible columns for better schema evolution
# (Use in CREATE TABLE or ALTER TABLE statements)

# Improved hash join
hash_join = ON

# Better random sampling
histogram_generation_max_mem_size = 20971520

# Invisible columns for better schema evolution

# (Use in CREATE TABLE or ALTER TABLE statements)

# Improved hash join

hash_join = ON

# Better random sampling

histogram_generation_max_mem_size = 20971520

Maintenance Scripts

1. Weekly Optimization Script


#!/bin/bash
# weekly_db_optimize.sh

# Analyze all tables
mysqlcheck --analyze --all-databases -u root -p

# Optimize fragmented tables
mysql -u root -p -e "
SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') as optimize_command
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
AND data_free > 0;" | grep OPTIMIZE | mysql -u root -p

# Update engine-independent statistics
mysql -u root -p -e "
SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ' PERSISTENT FOR ALL;') as analyze_command
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');" | grep ANALYZE | mysql -u root -p

#!/bin/bash

# weekly_db_optimize.sh

# Analyze all tables

mysqlcheck --analyze --all-databases -u root -p

# Optimize fragmented tables

mysql -u root -p -e "

SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') as optimize_command

FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')

AND data_free > 0;" | grep OPTIMIZE | mysql -u root -p

# Update engine-independent statistics

mysql -u root -p -e "

SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ' PERSISTENT FOR ALL;') as analyze_command

FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');" | grep ANALYZE | mysql -u root -p

2. Performance Monitoring Script


#!/bin/bash
# db_performance_check.sh

mysql -u root -p -e "
SELECT 
    'Buffer Pool Hit Ratio' as metric,
    CONCAT(ROUND((1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2), '%') as value
FROM information_schema.global_status 
WHERE variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')
UNION ALL
SELECT 
    'Slow Queries' as metric,
    variable_value as value
FROM information_schema.global_status 
WHERE variable_name = 'Slow_queries'
UNION ALL
SELECT 
    'Connections' as metric,
    CONCAT(c.variable_value, '/', m.variable_value) as value
FROM information_schema.global_status c, information_schema.global_variables m
WHERE c.variable_name = 'Threads_connected' AND m.variable_name = 'max_connections';
"

#!/bin/bash

# db_performance_check.sh

mysql -u root -p -e "

SELECT

'Buffer Pool Hit Ratio' as metric,

CONCAT(ROUND((1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2), '%') as value

FROM information_schema.global_status

WHERE variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')

UNION ALL

SELECT

'Slow Queries' as metric,

variable_value as value

FROM information_schema.global_status

WHERE variable_name = 'Slow_queries'

UNION ALL

SELECT

'Connections' as metric,

CONCAT(c.variable_value, '/', m.variable_value) as value

FROM information_schema.global_status c, information_schema.global_variables m

WHERE c.variable_name = 'Threads_connected' AND m.variable_name = 'max_connections';

Important Notes

Always backup before making changes
Test configuration changes in development first
Monitor performance after each change
Some settings require a restart to take effect
Adjust values based on your hardware and workload
Query cache is removed in MySQL 8.0+ – use alternative caching strategies

Performance Testing

Load Testing Command


# Install sysbench first: apt-get install sysbench

# Prepare test data
sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourpassword prepare

# Run performance test
sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourpassword --time=300 --threads=8 run

# Cleanup
sysbench oltp_read_write --mysql-db=test --mysql-user=root --mysql-password=yourpassword cleanup

# Install sysbench first: apt-get install sysbench

# Prepare test data

sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourpassword prepare

# Run performance test

sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=yourpassword --time=300 --threads=8 run

# Cleanup

sysbench oltp_read_write --mysql-db=test --mysql-user=root --mysql-password=yourpassword cleanup

Remember: Performance optimization is an iterative process. Start with the quick wins, then gradually implement the advanced configurations while monitoring the impact on your specific workload.

Let’s Talk!

Looking for a reliable partner to bring your project to the next level? Whether it’s development, design, security, or ongoing support—I’d love to chat and see how I can help.

Get in touch,
and let’s create something amazing together!

_hyperscript: The Time-Traveling Web Language That Wants You to Just… Talk to Your Browser

Design / Development / Javascript

Or: How I Learned to Stop Worrying and Love the Underscore Remember when you could just tell your computer what to do, in plain English, and it would actually do it? No? Well, grab your DeLorean, because we’re going back to the future with _hyperscript (yes, that underscore is part of the name, and yes, […]

Best SSH / SFTP Extensions for VSCode in 2025

Development / Automation / Build-Tools / Debugging / Deployment

As Visual Studio Code continues to dominate the code editor landscape in 2025, developers working with remote servers face an important decision: which SFTP extension should they use? The marketplace offers numerous options, but not all extensions are created equal. Some have been abandoned by their maintainers, while others have evolved into robust, actively maintained […]

Building a Chrome Extension – A Guide!

Development / Chrome

Hey there! So you wanna build a Chrome extension? Awesome! It’s way easier than you think. Seriously, you can have a basic one running in like 5 minutes. Let me walk you through everything you need to know. Just build a leads data extractor for myself and a client! Not my first Chrome Extension, but […]

Alexander

I am a full-stack developer. My expertise include:

Server, Network and Hosting Environments
Data Modeling / Import / Export
Business Logic
API Layer / Action layer / MVC
User Interfaces
User Experience
Understand what the customer and the business needs

I have a deep passion for programming, design, and server architecture—each of these fuels my creativity, and I wouldn’t feel complete without them.

With a broad range of interests, I’m always exploring new technologies and expanding my knowledge wherever needed. The tech world evolves rapidly, and I love staying ahead by embracing the latest innovations.

Beyond technology, I value peace and surround myself with like-minded individuals.

I firmly believe in the principle: Help others, and help will find its way back to you when you need it.

MariaDB/MySQL Performance Optimization Guide

Table of Contents

Quick Wins (Immediate Impact)

1. The 30x Performance Trick

2. Enable Engine-Independent Statistics

3. Update Table Statistics Regularly

Advanced Configuration File Optimizations

Complete my.cnf/my.ini Configuration

Advanced InnoDB Tweaks

1. Buffer Pool Preflushing

2. Adaptive Hash Index Control

3. Page Cleaners (MySQL 5.7+)

4. Doublewrite Buffer Optimization

OS-Level Optimizations

1. Virtual Memory Settings

2. File System Optimizations

3. I/O Scheduler

4. CPU Governor

5. Network Optimizations

Query and Schema Optimization

1. Index Optimization Commands

2. Analyze Table Structure

3. Partitioning for Large Tables

Performance Monitoring Commands

1. Real-time Performance Monitoring

2. Performance Schema Queries (MySQL 5.6+)

Version-Specific Optimizations

MariaDB 10.4+ Specific

MySQL 8.0+ Specific

Maintenance Scripts

1. Weekly Optimization Script

2. Performance Monitoring Script

Important Notes

Performance Testing

Load Testing Command

RELATED POSTS

_hyperscript: The Time-Traveling Web Language That Wants You to Just… Talk to Your Browser

Best SSH / SFTP Extensions for VSCode in 2025

Building a Chrome Extension – A Guide!

Alexander

.highlights

.highlights