Python/Bash Script to Print the Optimized Parameters for MySQL Servers

def get_total_ram():
    with open('/proc/meminfo', 'r') as f:
        for line in f:
            if line.startswith("MemTotal:"):
                total_ram_kb = int(line.split()[1])
                return total_ram_kb * 1024  # Convert to bytes
    return 0  # Return 0 if MemTotal is not found

def calculate_mysql_settings():
    # Get total RAM in bytes
    total_ram = get_total_ram()

    # Calculate MySQL settings based on total RAM in bytes
    innodb_buffer_pool_size = int(total_ram * 0.3)  # 30% of RAM
    key_buffer_size = min(total_ram * 20 // 100, 512 * 1024 * 1024)  # 20% of RAM, capped at 512MB
    sort_buffer_size = min(total_ram * 25 // 1000, 4 * 1024 * 1024)  # 0.25% of RAM, capped at 4MB
    read_rnd_buffer_size = min(total_ram * 625 // 100000, 512 * 1024)  # 0.0625% of RAM, capped at 512KB
    tmp_table_size = max_heap_table_size = min(total_ram * 5 // 100, 64 * 1024 * 1024)  # 5% of RAM, capped at 64MB
    join_buffer_size = min(total_ram * 2 // 1000, 4 * 1024 * 1024)  # 0.2% of RAM, capped at 4MB
    table_open_cache = min(400 + (total_ram // 64), 2000)  # Dynamic based on RAM
    thread_cache_size = min(total_ram * 15 // 1000, 100)  # 1.5% of RAM, capped at 100
    innodb_log_buffer_size = min(total_ram * 5 // 100, 16 * 1024 * 1024)  # 5% of RAM, capped at 16MB

    # Print the settings in bytes
    print(f"MySQL Configuration Settings based on Total RAM ({total_ram/(1024*1024):.2f} MB):")
    print("Add them to the end of /etc/mysql/mysql.conf.d/mysqld.cnf\n")
    
    print(f"innodb_buffer_pool_size = {innodb_buffer_pool_size}")
    print(f"key_buffer_size = {key_buffer_size}")
    print(f"sort_buffer_size = {sort_buffer_size}")
    print(f"read_rnd_buffer_size = {read_rnd_buffer_size}")
    print(f"tmp_table_size = {tmp_table_size}")
    print(f"max_heap_table_size = {max_heap_table_size}")
    print(f"join_buffer_size = {join_buffer_size}")
    print(f"table_open_cache = {table_open_cache}")
    print(f"thread_cache_size = {thread_cache_size}")
    print(f"innodb_log_buffer_size = {innodb_log_buffer_size}")

    # Print custom settings
    print("expire_logs_days = 3")
    print("max_binlog_size = 100M")

if __name__ == "__main__":
    calculate_mysql_settings()

#!/bin/bash

get_total_ram() {
    # Extract total RAM in kB from /proc/meminfo
    total_ram_kb=$(awk '/^MemTotal:/ {print $2}' /proc/meminfo)
    if [[ -z "$total_ram_kb" ]]; then
        echo 0  # Return 0 if MemTotal is not found
    else
        echo $((total_ram_kb * 1024))  # Convert kB to bytes
    fi
}

calculate_mysql_settings() {
    # Get total RAM in bytes
    total_ram=$(get_total_ram)

    # Calculate MySQL settings based on total RAM in bytes
    innodb_buffer_pool_size=$((total_ram * 30 / 100))
    key_buffer_size=$(($((total_ram * 20 / 100)) < $((512 * 1024 * 1024)) ? $((total_ram * 20 / 100)) : $((512 * 1024 * 1024))))
    sort_buffer_size=$(($((total_ram * 25 / 1000)) < $((4 * 1024 * 1024)) ? $((total_ram * 25 / 1000)) : $((4 * 1024 * 1024))))
    read_rnd_buffer_size=$(($((total_ram * 625 / 100000)) < $((512 * 1024)) ? $((total_ram * 625 / 100000)) : $((512 * 1024))))
    tmp_table_size=$((total_ram * 5 / 100 < 64 * 1024 * 1024 ? total_ram * 5 / 100 : 64 * 1024 * 1024))
    max_heap_table_size=$tmp_table_size
    join_buffer_size=$(($((total_ram * 2 / 1000)) < $((4 * 1024 * 1024)) ? $((total_ram * 2 / 1000)) : $((4 * 1024 * 1024))))
    table_open_cache=$(($((400 + total_ram / 64)) < 2000 ? $((400 + total_ram / 64)) : 2000))
    thread_cache_size=$(($((total_ram * 15 / 1000)) < 100 ? $((total_ram * 15 / 1000)) : 100))
    innodb_log_buffer_size=$(($((total_ram * 5 / 100)) < $((16 * 1024 * 1024)) ? $((total_ram * 5 / 100)) : $((16 * 1024 * 1024))))

    # Print the settings in bytes
    echo "MySQL Configuration Settings based on Total RAM ($((total_ram / (1024 * 1024))) MB):"
    echo "Add them to the end of /etc/mysql/mysql.conf.d/mysqld.cnf"
    echo
    echo "innodb_buffer_pool_size = $innodb_buffer_pool_size"
    echo "key_buffer_size = $key_buffer_size"
    echo "sort_buffer_size = $sort_buffer_size"
    echo "read_rnd_buffer_size = $read_rnd_buffer_size"
    echo "tmp_table_size = $tmp_table_size"
    echo "max_heap_table_size = $max_heap_table_size"
    echo "join_buffer_size = $join_buffer_size"
    echo "table_open_cache = $table_open_cache"
    echo "thread_cache_size = $thread_cache_size"
    echo "innodb_log_buffer_size = $innodb_log_buffer_size"
    echo
    echo "expire_logs_days = 3"
    echo "max_binlog_size = 100M"
}

# Main function
calculate_mysql_settings