Tunear MySQL o cómo aumentar el rendimiento de tu servidor

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:
mysqltuner

Links
MySQL Tuner: https://mysqltuner.com

2 comentarios

  1. Hola, buscando también encontré ese script, pero no he podido ponerlo en ejecución, el servidor lo tengo en Windows, no se como debo configurarlo, podrían ayudarme, soy nuevo con respecto a MySQL en el manual vi algo relacionado pero no he logrado entender. les agradezco la ayuda y de antemano muchísimas gracias

  2. Zerial

    junio 30, 2009 a las 7:31 pm

    @Johann: Que problema tienes al intentar ejecutarlo en Windows?

    saludos

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Esto sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.