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
saludos