MySQL Performance Tuning Primer Script

MySQL Performance Tuning Primer Script

木铎校园 BBS 社区:{D!k&[)Ze

This script takes information from "SHOW STATUS LIKE..." and "SHOW VARIABLES LIKE..."木铎校园 BBS 社区4N |4p;@m
to produce sane recomendations for tuning server variables.木铎校园 BBS 社区]jx
VV$] ~
It is compatable with all versions of MySQL 3.23 and higher (including 5.1).
'\h~hxJ#`@0

5zDH7R{ {F3{z1K0X0木铎校园 BBS 社区)xf:F+o8l+V5D d

Currently it handles recomendations for the following:

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

/n9C e@:H7J G0

`8c,W"}#rG0Recent Changes

Correct awk display error which formats integers > 4294967296 in scientific notation.木铎校园 BBS 社区d;i9C@,}9e f^e
- Was manfest in MEMORY USAGE section where total system RAM > 4GB.

Fixed rounding error where mysql will lose 4K from the join_buffer_size value.木铎校园 BBS 社区%R
y(i?x7eI/zo
- Other values may have the same issue but are not yet reported

Added support for FreeBSD and MacOS (thanks Sam and Geert)

Added support for Solaris

Changed how system memory is derived on Linux.
-g:C-Y7pazN;?8V}0- Use /proc/meminfo vs free -b and avoid inclusion of swap space.

Include note warning of instability when key_buffer_size > 4GB in versions 5.0.51 and lower

#TL;{ A#}4S^9p$NT0

MySQL Replication Slave Delay tracker

木铎校园 BBS 社区:MPB-v~CE+t

This script takes information from "SHOW SLAVE STATUS" from MySQL +4.1 to show what木铎校园 BBS 社区-k Mp(G(?l
the delay between the retrieved master's position and the slave's execution position.
V7\-Y)X+f*m0木铎校园 BBS 社区^-]8Y:pd+pR

9zt s.}pr-z'a0f3r0This script has a 1 second granularity.
&P~g~F{0木铎校园 BBS 社区4]*W:j$_u%B

Check MySQL Replication Slave Status

B R#o-C `uuZM0This script produces no output when replication slave is running normally.木铎校园 BBS 社区.Hu@+IPhU _
If either IO or SQL threads are not running it will print a message indicating so木铎校园 BBS 社区'P1|'Th2b!|:[6Y
and print the contentsof the "Last_error:"

'U6]'pW^2Fz"a0木铎校园 BBS 社区w0|(x @7y&B^

I typically set it up on a 2minute cron. It has some logic in place to avoid repeat木铎校园 BBS 社区qAPa:\P(s:h
e-mailing from CRON when down status is found. It will wait 15 minutes before producing木铎校园 BBS 社区X
p'b$_A_M
the next message. You can also disable the scripts checking by changing the "active"木铎校园 BBS 社区m UzRWL Kq
variable to "no". This allows users without direct access to cron to controll the script.
-IsF-d0c0This script assumes that a ~/.my.cnf will be in place for authentication.木铎校园 BBS 社区a!X(i}br/h T:s
木铎校园 BBS 社区e#Q\^2H"BG

MYSQL_BACKUP.sh

This script pulls hot backups of MyISAM tables and flushes the binary log files when called with the 'DAILY' option木铎校园 BBS 社区"g#VL:x`^d
V
When called with 'HOURLY' option it will rsync the binlogs for the server to the specified backup directory.木铎校园 BBS 社区x$FY4w!K{~ ^0eC
Recently added support for InnoDB with the 3rd party 'ibbackup' utility Added support for MySQL 4.1 and 5.0.