Sunday, October 4, 2009

Sundry MySQL Scripts and Docs

MySQL Performance Tuning Primer Script

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).

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 Recent Changes

  • Correct awk display error which formats integers > 4294967296 in scientific notation.
    - 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.
    - 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.
    - 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


  • MySQL Replication Slave Delay tracker

    This script takes information from "SHOW SLAVE STATUS" from MySQL +4.1 to show what
    the delay between the retrieved master's position and the slave's execution position.

    This script has a 1 second granularity.

    Check MySQL Replication Slave Status

    This script produces no output when replication slave is running normally.
    If either IO or SQL threads are not running it will print a message indicating so
    and print the contentsof the "Last_error:"
    I typically set it up on a 2minute cron. It has some logic in place to avoid repeat
    e-mailing from CRON when down status is found. It will wait 15 minutes before producing
    the next message. You can also disable the scripts checking by changing the "active"
    variable to "no". This allows users without direct access to cron to controll the script.
    This script assumes that a ~/.my.cnf will be in place for authentication.

    MYSQL_BACKUP.sh

    This script pulls hot backups of MyISAM tables and flushes the binary log files when called with the 'DAILY' option
    When called with 'HOURLY' option it will rsync the binlogs for the server to the specified backup directory.
    Recently added support for InnoDB with the 3rd party 'ibbackup' utility Added support for MySQL 4.1 and 5.0.

    No comments:

    Post a Comment