Herramientas:

MySQLTuner

mysqlreport

---

Perl Script
http://mysqltuner.com/mysqltuner.pl



Features:

    * Memory Usage: Calculates MySQL memory usage at max load and makes recommendations for increasing or decreasing the MySQL memory footprint. Per-thread and server-wide buffer data is calculated separately for an accurate snapshot of the server’s configuration.
    * Slow Queries: Reviews the amount of slow queries relative to the total queries. Slow query time limits are also analyzed and recommendations are made.
    * Connections: Current and historical connection counts are reviewed.
    * Key Buffer: Takes configuration data and compares it to the actual indexes found in MyISAM tables. Key cache hit rates are calculated and variable adjustments are suggested.
    * Query Cache: Query cache hit rates and usage percentages are used to make recommendations for the query cache configuration variables.
    * Sorting & Joins: Per-thread buffers that affect sorts and joins are reviewed along with the statistics from the queries run against the server.
    * Temporary Tables: Variable recommendations are made to reduce temporary tables that are written to the disk.
    * Table Cache: Compares total tables opened to the currently open tables. Calculates the table cache hit rate in order to make suggestions.
    * Open Files: Determines if the server will approach or run into the open file limit set by the operating system or the MySQL server itself.
    * Table Locks: Finds table locking that forces queries to wait and makes suggestions for reducing locks that require a wait.
    * Thread Cache: Calculates how many times MySQL must create a new thread to respond to a query.
    * Aborted Connections: Finds applications that are not closing connections to MySQL properly.
    * Read/Write Ratios: Calculates the percentage of read and write operations on your MySQL installation.

Usage:

# ./mysqltuner.pl --help

   MySQLTuner 0.9.0 - MySQL High Performance Tuning Script
   Bug reports, feature requests, and downloads at http://mysqltuner.com/
   Maintained by Major Hayden (major@mhtx.net)

   Important Usage Guidelines:
      To run the script with the default options, run the script without arguments
      Allow MySQL server to run for at least 24-48 hours before trusting suggestions
      Some routines may require root level privileges (script will provide warnings)

   Performance and Reporting Options
      --skipsize       Don't enumerate tables and their types/sizes
                       (Recommended for servers with many tables)
      --skipversion    Don't check for updates to MySQLTuner

   Output Options:
      --nogood         Remove OK responses
      --nobad          Remove negative/suggestion responses
      --noinfo         Remove informational responses
      --nocolor        Don't print output in color

Here is a sample of the script’s output on a well optimized server:

 >>  MySQLTuner 0.9.0 - Major Hayden
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with ‘–help’ for additional options and output filtering

——– General Statistics ————————————————–
[OK] You have the latest version of MySQLTuner
[OK] Currently running supported MySQL version 5.0.51a-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 491M (Tables: 1424)
[--] Data in InnoDB tables: 9M (Tables: 151)

——– Performance Metrics ————————————————-
[--] Up for: 19d 16h 14m 3s (57M q [33.636 qps], 5M conn, TX: 185B, RX: 5B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 2.7M per thread and 606.0M global
[OK] Maximum possible memory usage: 743.5M (37% of installed RAM)
[OK] Slow queries: 0% (81/57M)
[!!] Highest connection usage: 100%  (51/50)
[OK] Key buffer size / total MyISAM indexes: 256.0M/210.9M
[OK] Key buffer hit rate: 99.3%
[OK] Query cache efficiency: 71.4%
[!!] Query cache prunes per day: 2752
[OK] Sorts requiring temporary tables: 0%
[OK] Temporary tables created on disk: 2%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 2%
[OK] Open file limit used: 4%
[OK] Table locks acquired immediately: 99%
[OK] InnoDB data size / buffer pool: 9.8M/50.0M

——– Recommendations —————————————————–
General recommendations:
    Reduce or eliminate persistent connections to reduce connection usage
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 50)
    wait_timeout (< 15)
    interactive_timeout (< 15)
    query_cache_size (> 256M)
    table_cache (> 4096)

---

Descargar Perl Script
http://hackmysql.com/scripts/mysqlreport

http://tag1consulting.com/MySQL_Monitoring_and_Tuning