How to Optimize MySQL Performance

Prerequisites

Before optimizing MySQL, make sure you have:

• SSH access to your VPS
• MySQL/MariaDB installed
• Root or sudo privileges

Step 1: Analyze Current Performance

Connect to your VPS:

ssh hxroot@YOUR_SERVER_IP -p 22

Check current MySQL status:

mysqladmin status

Run MySQL Tuner for recommendations:

apt install git -y
git clone https://github.com/major/MySQLTuner-perl.git
cd MySQLTuner-perl
perl mysqltuner.pl

Step 2: Edit MySQL Configuration

For MySQL 8.0 / MariaDB 10.x:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

For older versions:

sudo nano /etc/my.cnf

Step 3: Key Optimizations (Adjust based on available RAM)

For a VPS with 4GB RAM:

[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

# Query Cache (MySQL 5.7 and below only)
query_cache_type = 1
query_cache_size = 128M

# Connection Settings
max_connections = 150
thread_cache_size = 8

# Temporary Tables
tmp_table_size = 64M
max_heap_table_size = 64M

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

# Table Cache
table_open_cache = 400
table_definition_cache = 400

Step 4: Restart MySQL

sudo systemctl restart mysql

Or for MariaDB:

sudo systemctl restart mariadb

Step 5: Verify Settings Applied

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -e "SHOW VARIABLES LIKE 'max_connections';"

Memory Calculation Formula

innodb_buffer_pool_size = 70-80% of total RAM (for dedicated database servers)
key_buffer_size = 25% of RAM (MyISAM tables)

Example for 8GB VPS running web + database: innodb_buffer_pool_size = 2G (25% of RAM, leaving memory for web server and OS).

Enable Slow Query Log for Troubleshooting

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

Analyze slow queries:

pt-query-digest /var/log/mysql/slow.log

Monitor MySQL Status

mysqladmin extended-status -i 2