MySQL Performance Tuning Primer Script
MySQL Performance Tuning Primer Script
http://www.day32.com/MySQL/
http://www.day32.com/MySQL/tuning-primer.sh
This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…”
to produce sane recomendations for tuning server variables.
It is compatable with all versions of MySQL 3.23 and higher (including 5.1).
Matthew Montgomery has created a script called MySQL performance tuning primer script that generates recomendations for tuning MySQL server variables like:
Slow Query Log
Max Connections
Worker Threads
Key Buffer
Query Cache
Sort Buffer
Joins
Temp Tables
Table (Open & Definition) Cache
Table Locking
Table Scans (read_buffer)
Innodb Status
It’s designed to work on Linux, Solaris, FreeBSD, MacOS and possible on other UNIX based systems using MySQL server version 3.23+ (including 5.1).
First, you need to grab a copy of this script:
wget http://www.day32.com/MySQL/tuning-primer.sh
Before you continue, make sure your MySQL server has at least 48 hours uptime. It’s very important for the script in order to adjust variables corectly.
Running this script is like any other Unix shell script :
sh ./tuning-primer.sh [mode]
Available Modes:
all : perform all checks (default)
prompt : prompt for login credintials and socket and execution mode
mem, memory : run checks for tunable options which effect memory usage
disk, file : run checks for options which effect i/o performance or file handle limits
innodb : run InnoDB checks
misc : run checks for that don’t categorise well Slow Queries, Binary logs, Used Connections and Worker Threads
Read carefully then adjust MySQL server configuration file (usually /etc/my.cnf) and restart MySQL server. It is a good practice to backup your server configuration file before. After few days run again the script and take note of what it says. Tweak again the server if necessary.
Remember, this optimization script only takes care of some server variables and it does not teach you how to use proper indexes and table definitions.
