Herramientas:

MySQL Tuning Primer

MySQLTuner

mysqlreport

Optimizar MySQL
http://wiki.elhacker.net/bases-de-datos/mysql/optimizacion

---

MySQL Tuning Primer

Descargar el shell script:
http://www.day32.com/MySQL/tuning-primer.sh

chmod 755 y ejecutar

---

MySQLTuner



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)

---

mysqlreport



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

http://tag1consulting.com/MySQL_Monitoring_and_Tuning

---

#!/bin/bash
# Written by James Crow crow.jamesm <at> google email domain name <dot> com
# 2010-09-16
# This script connects to the MythTV mythconverg database to pull
# the two most important tuning parameters. This script only works
# for MyISAM tables at the moment. The only part that needs to be
# configured is the variables at the top of the script
# ver 0.1
 
# *** Change these to match you environment ***
mysql='/usr/bin/mysql'
mythconverg='mythconverg'
sql_user='mythtv'
sql_pass='mythtv'
sql_host='192.168.1.2'
# *** You should not need to change anything after this line ***
 
# determine if we are using MyISAM tables
$mysql -h $sql_host -u $sql_user -p$sql_pass $mythconverg -e 'show
create table record' | grep -q MyISAM
myisam=$?
 
if [ $myisam -ne 0 ]; then
	echo "You do not appear to be using MyISAM tables for the myth database"
	exit;
fi
#echo $myisam
 
# The most important tuning parameters are the key_buffer_size and
table_open_cache
# find the key_buffer_size and % in use first
key_blocks_unused=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e
'show status like "key_blocks_unused"' | grep "[Kk]ey_blocks_unused" |
sed 's/[^0-9]//g'`
key_cache_block_size=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e
'show variables like "key_cache_block_size"' | grep
key_cache_block_size | sed 's/[^0-9]//g'`
key_buffer_size=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e 'show
variables like "key_buffer_size"' | grep key_buffer_size | sed
's/[^0-9]//g'`
 
# using a simple formula from the MySQL doc we get the key_buffer_cache in use
key_buffer_cache_in_use=`echo "scale=2; 1-(($key_blocks_unused *
$key_cache_block_size)/$key_buffer_size)" | bc`
# this next line rounds each value to an even integer, but that is the
best way to display the result
key_buffer_use_pct=`echo "scale=0; $key_buffer_cache_in_use*100" | bc`
key_buffer_use_pct=`echo "($key_buffer_use_pct+0.5)/1" | bc`
#echo "scale=0; $key_buffer_cache_in_use*100"
echo ""
echo "**** key_buffer_cache section *****************************"
if [ $key_buffer_use_pct -eq 100 ]; then
	echo "!!!!!! WARNING: You are using 100% of your key_buffer_cache."
	echo "                You should raise the value!"
elif [ $key_buffer_use_pct -gt 80 ]; then
	echo "NOTE: You are using more than 80% of your key_buffer_cache."
	echo "      You may may want to raise the value."
fi
echo "key_buffer_size: $key_buffer_size, $(($key_buffer_size/1024))
kb, $(($key_buffer_size/(1024*1024))) mb"
echo "key_buffer_cache in use: $key_buffer_use_pct%"
echo "***********************************************************"
 
# the second of two most important variables is the table_open_cache
open_tables=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e 'show
status like "open_tables"' | grep Open_tables | sed 's/[^0-9]//g'`
open_table_cache=`$mysql -h $sql_host -u $sql_user -p$sql_pass -e
'show variables like "table_open_cache"' | grep table_open_cache | sed
's/[^0-9]//g'`
free_tables=$(($open_table_cache-$open_tables))
open_tables_free_pct=`echo "scale=2; $free_tables/$open_table_cache*100" | bc`
open_tables_free_pct=`echo "($open_tables_free_pct+0.5)/1" | bc`
#echo "scale=0; $free_tables/$open_table_cache*100"
echo ""
echo "**** table_open_cache section *****************************"
if [ $free_tables -eq 0 ]; then
	echo "!!!!!! WARNING: You have no free tables."
	echo "                You should raise the value."
elif [ $open_tables_free_pct -lt 10 ]; then
	echo "NOTE: You have less than 10% free in the table_open_cache."
	echo "      You may want to raise the value."
fi
echo "table_open_cache: $free_tables free out of $open_table_cache,
$open_tables used"
echo "table_open_cache used pct: $((100-$open_tables_free_pct))%"
echo "***********************************************************"

muy bueno,gracias por el aporte