How to Optimize MySQL and MariaDB Performance

  mysql --version

# Or from inside MySQL:
mysql -u root -p -e "SELECT VERSION();"
  

  [mysqld]
innodb_buffer_pool_size = 1G
  

  [mysqld]
innodb_log_file_size = 256M
  

  [mysqld]
max_connections = 150
  

  SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';
  

  sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf    # Ubuntu/Debian
sudo nano /etc/my.cnf.d/server.cnf               # Rocky/Alma
  

  [mysqld]
# === InnoDB Settings ===
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_buffer_pool_instances = 1

# === Connection Settings ===
max_connections = 150
wait_timeout = 300
interactive_timeout = 300
max_allowed_packet = 64M

# === Query Cache (MariaDB only - removed in MySQL 8.0) ===
# query_cache_type = 1
# query_cache_size = 64M
# query_cache_limit = 2M

# === Temp Tables ===
tmp_table_size = 64M
max_heap_table_size = 64M

# === Table Cache ===
table_open_cache = 2000
table_definition_cache = 1000

# === Thread Settings ===
thread_cache_size = 16

# === Logging ===
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
  

  sudo systemctl restart mysql     # or mariadb
  

  SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 1;
  

  # Summary of slowest queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Sorted by count (most frequent slow queries)
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
  

  EXPLAIN SELECT * FROM wp_posts WHERE post_status = 'publish' ORDER BY post_date DESC LIMIT 10;
  

  -- Example: WordPress posts table slow on post_status queries
CREATE INDEX idx_post_status_date ON wp_posts (post_status, post_date);

-- Check existing indexes
SHOW INDEX FROM wp_posts;
  

  # Download and run
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass yourpassword
  

  [!!] InnoDB buffer pool size is too small (128M) - should be at least 1G
[!!] Slow query log is not enabled
[OK] Thread cache hit rate: 99%
  

  -- Single table
OPTIMIZE TABLE wp_posts;

-- Check fragmentation
SELECT TABLE_NAME, DATA_LENGTH, DATA_FREE,
       ROUND(DATA_FREE / DATA_LENGTH * 100, 1) AS fragmentation_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
  AND DATA_FREE > 0
ORDER BY DATA_FREE DESC;
  

  wp db optimize --path=/var/www/yourdomain.com
  

  -- Current connections and thread usage
SHOW STATUS LIKE 'Threads%';

-- Buffer pool efficiency (hit rate should be > 99%)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- Calculate hit rate
SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_rate;
  

  -- Queries per second
SHOW STATUS LIKE 'Questions';

-- Slow queries
SHOW STATUS LIKE 'Slow_queries';

-- Table locks
SHOW STATUS LIKE 'Table_locks_waited';

-- Temporary tables on disk (should be low)
SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';
  

  -- Delete old post revisions (WordPress keeps every revision forever by default)
DELETE FROM wp_posts WHERE post_type = 'revision';

-- Delete orphaned post metadata
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

-- Delete transients (cached data that may be stale)
DELETE FROM wp_options WHERE option_name LIKE '%_transient_%';

-- Optimize after cleanup
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options;
  

  define('WP_POST_REVISIONS', 5);