When optimizing MySQL performance, tailoring the configuration to the server’s available memory is critical. Two scripts—one in Python and the other in Bash—offer automated solutions to calculate recommended MySQL settings based on the total RAM of the server. Both scripts extract memory information from the system and compute configuration values like innodb_buffer_pool_size, key_buffer_size, and other key parameters to ensure efficient database performance.
I’ve applied the optimised parameters to my blog which are based on WordPress (PHP and MYSQL). I can notice an obvious performance gain e.g. the blog system runs a lot smoother.
Optimize MySQL Parameters via Python Script
The Python script is structured with a function to read /proc/meminfo, retrieve the total memory, and calculate MySQL settings using predefined formulas. These formulas allocate percentages of the total memory to various MySQL buffers and caches while respecting reasonable upper limits to prevent over-allocation. The script also provides a user-friendly output, detailing each calculated parameter and explaining where to place these settings in the MySQL configuration file. With Python’s readability and flexibility, this script is well-suited for developers who prefer a dynamic and scalable solution.
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()
Optimize MySQL Parameters via BASH Script
On the other hand, the Bash script offers a lightweight, shell-based alternative for environments where Python might not be available. Leveraging Unix utilities like awk, the Bash script extracts memory details directly and performs similar calculations using shell arithmetic. It outputs the recommended MySQL configuration in a format ready to be appended to the MySQL configuration file. The script’s simplicity and reliance on standard Linux tools make it an excellent choice for system administrators who need quick, scriptable solutions without external dependencies.
Both scripts demonstrate how system resources can be dynamically analyzed and leveraged to create optimized configurations. Whether you’re a developer or a system administrator, these tools can help automate tedious manual calculations and ensure your MySQL server operates efficiently, regardless of the underlying hardware. By adapting these scripts to your workflow, you can save time while improving database performance and stability.
The following BASH requires the awk.
#!/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
Please note that the default MySQL parameters are quite conservative. The above script sets the innodb_buffer_pool_size as the 30% of the RAM, however, you can set it to 60-80% if your server isn’t overloaded with other applications at the same time.
You can also add some custom settings at the end – modify to suit your needs.
Both scripts are written by the ChatGPT LVM AI.
DevOps / Site Reliability Engineering
- How to Clean Up NVM Node Versions Except One?
- Monitoring 28 VPS Machines including a Raspberry Pi with Nezha Dashboard
- Python/Bash Script to Print the Optimized Parameters for MySQL Servers
- Learn to Manage Your MySQL Database with a Python Script
- A Simple PHP Command Line Tool to Convert MySQL Tables from MyISAM to InnoDB in Specified Database
- How to Print MySQL Table Summary using PHP?
- Secure the Linux Server by Disallow the Remote Root Login (SSH and FTP and MySQL database)
- Bash Script to Check, Repair, Optimise and Backup MySQL database
- Duplicate a MySQL table - Copy Table / Duplicate Database / PHP Script
- MySQL server stopped due of out of memory exception on Ubuntu VPS
- Running Apache Server (PHP + MySQL) on Raspberry PI
- How to Optimise SQL Queries? Quick Tips
- Recovery Models in SQL Server
- Database Optimisation Script in PHP
–EOF (The Ultimate Computing & Technology Blog) —
1152 wordsLast Post: The Sunset of OneKey Crypto Card
Next Post: The mode vs multimode in Python
