Calculates optimized MySQL 8 settings based on system resources.

README.md

How to use:

jbolus@sandbox:/tmp$ chmod +x mysql-conf-optimizer.sh
jbolus@sandbox:/tmp$ ./mysql-conf-optimizer.sh
MySQL Optimization Suggestions:
---------------------------------
innodb_buffer_pool_size: 24GB
innodb_buffer_pool_instances: 20
innodb_io_capacity: 4000
# Additional Suggestions:
max_connections: 500 (Based on CPU cores)
innodb_flush_method = O_DIRECT (To avoid double buffering with the OS)
innodb_log_file_size = 7GB (25% of total memory for optimal recovery speed)

This script is a MySQL optimization tool that analyzes system hardware and generates recommended configuration settings. Here's a breakdown of what it does:

1. System Information Collection:

   CPU_CORES=$(nproc)
   MEM_TOTAL=$(awk '/MemTotal/ {print $2}' /proc/meminfo)
   NUMA_NODES=$(lscpu | awk '/NUMA node\(s\)/ {print $3}')

   • Gets the number of CPU cores
   • Retrieves total memory from /proc/meminfo
   • Checks for NUMA node configuration

2. Memory Calculations:

   MEM_TOTAL_GB=$((MEM_TOTAL / 1048576))
   INNODB_BUFFER_POOL_SIZE=$((MEM_TOTAL * 80 / 100 / 1048576))

   • Converts memory from KB to GB
   • Calculates InnoDB buffer pool size (set to 80% of total memory)

3. Buffer Pool Instance Optimization:

   if [ "$INNODB_BUFFER_POOL_SIZE" -le "$CPU_CORES" ]; then
       INNODB_BUFFER_POOL_INSTANCES=$INNODB_BUFFER_POOL_SIZE
   else
       INNODB_BUFFER_POOL_INSTANCES=$CPU_CORES
   fi

   • Sets buffer pool instances based on either buffer pool size or CPU cores, whichever is smaller

4. I/O Capacity Calculation:

   INNODB_IO_CAPACITY=$((CPU_CORES * 200))

   • Sets I/O capacity based on CPU cores (200 per core)

5. Output Generation:

   • Creates a file named mysql_optimization_suggestions.txt
   • Outputs all recommendations to both the file and screen using tee
   • Includes settings for:
     • InnoDB buffer pool size
     • Buffer pool instances
     • I/O capacity
     • NUMA configuration (if applicable)
     • Max connections (25 per CPU core)
     • InnoDB flush method
     • Log file size (25% of total memory)

This script is particularly useful for:

• Initial MySQL server setup
• Performance optimization
• Hardware-specific configuration
• Generating documented configuration recommendations

The suggestions are based on common best practices for MySQL performance optimization, taking into account the specific hardware configuration of the system where the script is run.

---

mysql-conf-optimizer.sh

#!/bin/bash

# Get system information
CPU_CORES=$(nproc)
MEM_TOTAL=$(awk '/MemTotal/ {print $2}' /proc/meminfo)
NUMA_NODES=$(lscpu | awk '/NUMA node\(s\)/ {print $3}')

# Convert memory from KB to GB
MEM_TOTAL_GB=$((MEM_TOTAL / 1048576))

# Suggest MySQL innodb_buffer_pool_size (80% of total memory)
INNODB_BUFFER_POOL_SIZE=$((MEM_TOTAL * 80 / 100 / 1048576))

# Suggest innodb_buffer_pool_instances (1 instance per 1GB of buffer pool, capped at CPU cores)
if [ "$INNODB_BUFFER_POOL_SIZE" -le "$CPU_CORES" ]; then
  INNODB_BUFFER_POOL_INSTANCES=$INNODB_BUFFER_POOL_SIZE
else
  INNODB_BUFFER_POOL_INSTANCES=$CPU_CORES
fi

# Suggest innodb_io_capacity based on CPU cores (a conservative default per core)
INNODB_IO_CAPACITY=$((CPU_CORES * 200))

# Output suggestions to a file and display
OUTPUT_FILE="mysql_optimization_suggestions.txt"
echo "MySQL Optimization Suggestions:" | tee "$OUTPUT_FILE"
echo "---------------------------------" | tee -a "$OUTPUT_FILE"
echo "innodb_buffer_pool_size: ${INNODB_BUFFER_POOL_SIZE}GB" | tee -a "$OUTPUT_FILE"
echo "innodb_buffer_pool_instances: ${INNODB_BUFFER_POOL_INSTANCES}" | tee -a "$OUTPUT_FILE"
echo "innodb_io_capacity: ${INNODB_IO_CAPACITY}" | tee -a "$OUTPUT_FILE"

# Additional hardware-related optimizations
echo "# Additional Suggestions:" | tee -a "$OUTPUT_FILE"

# NUMA Configuration suggestion
if [ -n "$NUMA_NODES" ] && [ "$NUMA_NODES" -gt 1 ]; then
  echo "innodb_numa_interleave = 1 (Recommended for NUMA systems)" | tee -a "$OUTPUT_FILE"
fi

# Suggested settings for thread concurrency
MAX_CONNECTIONS=$((CPU_CORES * 25))
echo "max_connections: ${MAX_CONNECTIONS} (Based on CPU cores)" | tee -a "$OUTPUT_FILE"

echo "innodb_flush_method = O_DIRECT (To avoid double buffering with the OS)" | tee -a "$OUTPUT_FILE"
echo "innodb_log_file_size = $((MEM_TOTAL_GB / 4))GB (25% of total memory for optimal recovery speed)" | tee -a "$OUTPUT_FILE"

echo "---------------------------------" | tee -a "$OUTPUT_FILE"