Cristina Alcantarilla
INFORMÁTICA & DISEÑADORA
Optimizar MySQL para servidores web

Optimizar MySQL es vital para mejorar la respuesta de nuestros servidores. El primer consejo para optimizar una base de datos es montarla “stand alone”, es decir montar en este caso MySQL en un servidor a parte del servidor de aplicaciones.

Vamos a empezar con la optimización. Importante antes de tocar nada que las aplicaciones ya estén trabajando o hayamos simulado un comportamiento y carga similar al que tendrá cuando pasemos a producción, sino los supuestos sobre los que estaremos trabajando no serán ciertos.

Antes de nada os aconsejo instalar mytop. La aplicación mytop nos permite ver las queries que se ejecutan en la base de datos de manera interactiva (similar al comando “top”).

apt-get  install mytop
Ahora vamos a usar un script en perl llamado mysqltuner.pl que nos dará mucha información sobre nuestro MySQL y nos hará sugerencias para mejorar el rendimiento.

1. Descargamos el script.

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

2. Le damos permiso de ejecución

chmod +x mysqltuner.pl

3. Ejecutamos el script introduciendo las credenciales de acceso a MySQL

./mysqltuner.pl

Este es un ejemplo del resultado:
——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 6G (Tables: 952)
[!!] InnoDB is enabled but isn’t being used
[!!] Total fragmented tables: 12

——– Security Recommendations ——————————————-
[OK] All database users have passwords assigned

——– Performance Metrics ————————————————-
[–] Up for: 1d 2h 3m 35s (68M q [732.193 qps], 610K conn, TX: 49B, RX: 11B)
[–] Reads / Writes: 76% / 24%
[–] Total buffers: 512.0M global + 2.8M per thread (2000 max threads)
[OK] Maximum possible memory usage: 6.0G (25% of installed RAM)
[OK] Slow queries: 0% (3K/68M)
[OK] Highest usage of available connections: 7% (159/2000)
[OK] Key buffer size / total MyISAM indexes: 230.0M/1.7G
[OK] Key buffer hit rate: 97.8% (11B cached / 257M reads)
[OK] Query cache efficiency: 76.6% (46M cached / 61M selects)
[!!] Query cache prunes per day: 1822075
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 63635
[OK] Temporary tables created on disk: 1% (26K on disk / 2M total)
[OK] Thread cache hit rate: 99% (159 created / 610K connections)
[!!] Table cache hit rate: 4% (1K open / 43K opened)
[OK] Open file limit used: 17% (2K/16K)
[OK] Table locks acquired immediately: 99% (36M immediate / 36M locks)

——– Recommendations —————————————————–
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 256M) [see warning above] join_buffer_size (> 256.0K, or always use indexes with joins) table_cache (> 7200)
 

El script nos devuelve mucha información. Vemos detalles como el uso total y máximo de memoria de MySQL, el porcentaje de aciertos en cache, etc. Vamos a irnos directamente a la parte de recomendaciones. Repito, es importante que es script se ejecute con carga real en el servidor.

Todos los parámetros se configuran en el fichero de configuración /etc/my.cnf. Algunas de las recomendaciones importantes son los tamaños de las caches.

query_cache_size,
join_buffer_size and
table_cache

Editamos las variables:

table_cache = 14400
query_cache_size = 512M
join_buffer_size = 524288

Acordaos de hacer un backup siempre del fichero my.cfg y/o mantener los viejos valores comentados.

#table_cache = 7200
#query_cache_size = 256M
#join_buffer_size = 262144

Para la parte de desfragmentar las tablas podemos usar el comando mysqlcheck desde la misma línea de comandos.

mysqlcheck -u root -p –auto-repair –check –optimize –all-databases
Reiniciamos mysql y comprobamos que no hay ningún error-

/etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]

Para afinar más nuestra configuración de MySQL podemos usar Tuning-premier.sh. Funciona de manera similar a mysqltuner.pl, accediendo a la base de datos y analizando la información. Aconseja variables y como reducir cuellos de botella y problemas de locks.

wget http://www.pc-freak.net/files/Tuning-primer.sh

chmod +x Tunning-premier.sh

./Tunning-premier.sh

Este es un ejemplo de resultado:

MySQL Version 5.0.51a-24+lenny5 x86_64

Uptime = 8 days 10 hrs 19 min 8 sec
Avg. qps = 179
Total Questions = 130851322
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

SLOW QUERIES
Current long_query_time = 1 sec.
You have 16498 out of 130851322 that take longer than 1 sec. to complete
The slow query log is NOT enabled.
Your long_query_time seems to be fine

MAX CONNECTIONS
Current max_connections = 2000
Current threads_connected = 1
Historic max_used_connections = 85
The number of used connections is 4% of the configured maximum.
Your max_connections variable seems to be fine.

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 84
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MEMORY USAGE
Tuning-primer.sh: line 994: let: expression expected
Max Memory Ever Allocated : 741 M
Configured Max Memory Limit : 5049 M
Total System Memory : 23640 M

KEY BUFFER
Current MyISAM index space = 1646 M
Current key_buffer_size = 476 M
Key cache miss rate is 1 / 56
Key buffer fill ratio = 90.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 38 M
Current Query cache fill ratio = 59.90 %

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256.00 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 128.00 K
You have had 111560 queries where a join could not use an index properly
You have had 91 joins without keys that check for key usage after each row
You should enable “log-queries-not-using-indexes”
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

TABLE CACHE
Current table_cache value = 3600 tables
You have a total of 798 tables
You have 1904 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current tmp_table_size = 128 M
1% of tmp tables created were disk based
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128.00 K
Current table scan ratio = 797 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1782
You may benefit from selective use of InnoDB.

Os aconsejo ejecutar estos scripts varias veces y a diferentes horas del día. Tened en cuenta que las recomendaciones son en base a la información actual de sistema y vosotros como DBA debéis analizar posibles picos o variaciones en la carga, ya sea por Jobs programados, procesos batch o comportamientos esporádicos que pueda tener la aplicación.

Share Button

Deja un comentario