Buscando como mejorar el rendimiento de un servidor mysql encontre un script en perl que realiza una serie de pruebas en nuestras bases de datos y finalmente nos muestra un resultado de esos test de memoria, consulta, “velocidad”, rendimiento, etc. Y nos da algunos tips para poder mejorar la configuracion del mysql y llegar a un rendimiento mas optimo.

Caracteristicas del mysqltuner:

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

Para ejecutar el script lo unico que debes hacer es:
perl mysqltuner.pl
Y te devolverá algo como esto:

>> MySQLTuner 1.0.0 – Major Hayden
>> Bug reports, feature requests, and downloads at https://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.75
[OK] Operating on 32-bit architecture with less than 2GB RAM

——– Storage Engine Statistics ——————————————-
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 2K (Tables: 14)
[–] Data in InnoDB tables: 15M (Tables: 84)
[OK] Total fragmented tables: 0

——– Performance Metrics ————————————————-
[–] Up for: 2h 55m 9s (586 q [0.056 qps], 159 conn, TX: 129K, RX: 32K)
[–] Reads / Writes: 49% / 51%
[–] Total buffers: 58.0M global + 1.2M per thread (100 max threads)
[OK] Maximum possible memory usage: 176.8M (17% of installed RAM)
[OK] Slow queries: 0% (0/586)
[OK] Highest usage of available connections: 3% (3/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.2M
[!!] Key buffer hit rate: 90.7% (162 cached / 15 reads)
[OK] Query cache efficiency: 71.7% (129 cached / 180 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 19 total)
[OK] Thread cache hit rate: 98% (3 created / 159 connections)
[OK] Table cache hit rate: 37% (12 open / 32 opened)
[OK] Open file limit used: 2% (24/1K)
[OK] Table locks acquired immediately: 100% (115 immediate / 115 locks)
[OK] InnoDB data size / buffer pool: 15.9M/16.0M

——– Recommendations —————————————————–
General recommendations:
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries

screenshot:

Links
MySQL Tuner: https://mysqltuner.com