Usando Linux para muchas cosas y disfrutando de videojuegos, en dispositivos moviles, consolas y pc. Using Linux for many things and enjoying video games, mobile devices, consoles and pc

Optimizar MySQL

Muchas de las aplicaciones de hoy en dia utilizan MySQL como motor de bases de datos. En base a esto se necesita que los query sean lo mas rapido posible para que no tengamos retrasos en la entrega de datos. Ademas de tener buen codigo, es buena idea tener MySQL bien optimizado para que todo sea rapido. Buscando en internet vi este script que nos permite optimizar MySQL de una forma bastante sencilla. Su nombre es mysqltuner y esta escrito en perl pudiendose ejecutar en cualquier distribuccion Linux actual que tenga perl instalado. MySQL puede estar localmente o en un server remoto, para lo cual podemos pasarle por parametros la direccion ip del server.
Para mostrarles su manejo me basare en un Ubuntu Server como el que instalamos en un articulo pasado que pueden ver en este link.
-Desde una consola descargamos el script:

wget http://mysqltuner.com/mysqltuner.pl

-Para ejecutarlo no necesitamos ser root, solo colocarle atributos de ejecucion:

chmod +x mysqltuner.pl

-Para ver sus opciones ejecutamos:

./mysqltuner.pl --help

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

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)
You must provide the remote server's total memory when connecting to other servers

Connection and Authentication
--host Connect to a remote host to perform tests (default: localhost)
--port Port to use for connection (default: 3306)
--user Username to use for authentication
--pass Password to use for authentication

Performance and Reporting Options
--skipsize Don't enumerate tables and their types/sizes (default: on)
(Recommended for servers with many tables)
--checkversion Check for updates to MySQLTuner (default: don't check)
--forcemem Amount of RAM installed in megabytes
--forceswap Amount of swap memory configured in megabytes

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

-Suponiendo que el MySQL esta local, lo ejecutamos sin parametros y le decimos que se conecte con el usuario root y el password que ya nuestro MySQL debe tener:

./mysqltuner.pl

>> MySQLTuner 0.9.8 - Major Hayden
>> Bug reports, feature requests, and downloads at http://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.45
[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: 344M (Tables: 21)
[!!] InnoDB is enabled but isn't being used
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 5

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 20h 42m 22s (510K q [3.171 qps], 9K conn, TX: 108M, RX: 75M)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 2.7M per thread and 34.0M global
[OK] Maximum possible memory usage: 302.7M (60% of installed RAM)
[OK] Slow queries: 0% (26/510K)
[OK] Highest usage of available connections: 46% (46/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/24.9M
[OK] Key buffer hit rate: 98.3%
[!!] Query cache is disabled
[!!] Sorts requiring temporary tables: 11%
[!!] Temporary tables created on disk: 47%
[!!] Thread cache is disabled
[OK] Table cache hit rate: 73%
[OK] Open file limit used: 5%
[OK] Table locks acquired immediately: 99%

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
sort_buffer_size (> 1M)
read_rnd_buffer_size (> 256K)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)

-Prestamos mucha atencion a todas las recomendaciones que nos da, especialmente las que aparecen al final. Para hacer los cambios editamos como root el file my.cnf:

sudo vi /etc/mysql/my.cnf

-Buscamos la seccion [mysqld] y alli colocamos los cambios sugeridos:

[mysqld]
query_cache_size=8M
sort_buffer_size=1M
read_rnd_buffer_size=256K
tmp_table_size=32M
max_heap_table_size=16M
thread_cache_size=4

-Reiniciamos el mysqld y volvemos a ejecutar el script para ver que nuevos cambios nos sugiere:

sudo /etc/init.d/mysqld restart
./mysqltuner.pl

Y listo, nuestro motor DB MySQL debe mejorar su velocidad. Espero les sirva.


Share/Bookmark