Optimize MySQL Settings on VPS Based on Hardware with Bash Script

To ensure your MySQL server is performing at its best, it’s essential to configure it based on the hardware specifications of your VPS. This Bash script automates the process by dynamically adjusting MySQL settings such as innodb_buffer_pool_size, max_connections, and more, according to your system’s RAM, CPU cores, and disk type (SSD/HDD).

By using this script, you can achieve optimal MySQL performance, taking full advantage of your VPS resources. Here’s how the script works and how you can implement it.

Key Features of the Bash Script:

1. Automatically Detects Hardware Specifications:

• The script checks the total RAM, number of CPU cores, and whether your VPS uses an SSD or HDD.

1. Dynamically Configures MySQL Settings:

• It adjusts important MySQL parameters such as:
  • innodb_buffer_pool_size: Allocates ~70% of your system’s RAM for caching.
  • innodb_log_file_size: Set to 25% of the buffer pool size.
  • max_connections: Adjusted based on the number of CPU cores.
  • query_cache_size: Optimized based on disk type.
  • tmp_table_size: Allocated based on 10% of the total RAM.
  • table_open_cache: Set based on CPU cores.

1. Smart Configuration Update:

• The script checks if [mysqld] already exists in /etc/mysql/my.cnf. If it does, it only adds the necessary configurations, ensuring no overwriting of existing settings.

1. Automatic MySQL Restart:

• Once the configurations are updated, the script restarts MySQL to apply the new settings instantly.

How to Use the Script:

1. Create the Bash script file:

   nano optimize_mysql.sh

2. Copy the code provided below into your file.
3. Make the script executable:

   chmod +x optimize_mysql.sh

4. Run the script with sudo to ensure proper permissions:

   sudo ./optimize_mysql.sh

Bash Script Code:

#!/bin/bash

# Get system hardware specs
total_ram=$(grep MemTotal /proc/meminfo | awk '{print $2}') 
total_ram_mb=$((total_ram / 1024))
cpu_cores=$(nproc)
disk_type=$(cat /sys/block/sda/queue/rotational)

# Calculate MySQL settings based on VPS specs
innodb_buffer_pool_size=$(echo "$total_ram_mb * 0.7" | bc | awk '{print int($1)}')M
max_connections=$((cpu_cores * 100))
innodb_log_file_size=$(echo "$innodb_buffer_pool_size * 0.25" | bc | awk '{print int($1)}')M

if [ "$disk_type" -eq 0 ]; then
    query_cache_size="128M"
else
    query_cache_size="64M"
fi

tmp_table_size=$(echo "$total_ram_mb * 0.1" | bc | awk '{print int($1)}')M
table_open_cache=$((cpu_cores * 200))

# Check if [mysqld] exists, if so, append settings
if grep -q "\[mysqld\]" /etc/mysql/my.cnf; then
    echo "[mysqld] found, appending settings."
    sed -i '/\[mysqld\]/a innodb_buffer_pool_size = '"$innodb_buffer_pool_size" /etc/mysql/my.cnf
    sed -i '/\[mysqld\]/a innodb_log_file_size = '"$innodb_log_file_size" /etc/mysql/my.cnf
    sed -i '/\[mysqld\]/a max_connections = '"$max_connections" /etc/mysql/my.cnf
    sed -i '/\[mysqld\]/a query_cache_size = '"$query_cache_size" /etc/mysql/my.cnf
    sed -i '/\[mysqld\]/a tmp_table_size = '"$tmp_table_size" /etc/mysql/my.cnf
    sed -i '/\[mysqld\]/a table_open_cache = '"$table_open_cache" /etc/mysql/my.cnf
else
    echo "[mysqld] not found, adding it along with settings."
    cat <<EOF >> /etc/mysql/my.cnf

[mysqld]

innodb_buffer_pool_size = $innodb_buffer_pool_size innodb_log_file_size = $innodb_log_file_size max_connections = $max_connections query_cache_size = $query_cache_size tmp_table_size = $tmp_table_size table_open_cache = $table_open_cache EOF fi # Restart MySQL to apply changes systemctl restart mysql echo “MySQL settings optimized based on VPS specs:” echo “InnoDB Buffer Pool Size: $innodb_buffer_pool_size” echo “InnoDB Log File Size: $innodb_log_file_size” echo “Max Connections: $max_connections” echo “Query Cache Size: $query_cache_size” echo “Tmp Table Size: $tmp_table_size” echo “Table Open Cache: $table_open_cache”

Why This Script is Important for MySQL Performance:

MySQL performance heavily relies on server hardware. Manually tuning MySQL parameters to match your server’s RAM, CPU, and disk can be time-consuming and error-prone. This script simplifies the process by automatically setting optimal configurations, ensuring efficient memory usage, better caching, and improved query performance.

By implementing this script, you can optimize MySQL to handle more concurrent connections and perform better in environments with varied hardware configurations.