Optimizar servidor mysql

1. Optimizando MySQL

Hoy en día vemos como la comunidad del Open Source crece y crece con
aplicaciones bien robustas en todos los ámbitos. En especial, aquellas
diseñadas bajo LAMP (Linux+Apache+MySQL+PHP) están acaparando todos los
ámbitos, desde simples aplicaciones para manejo de fotos vía web hasta
complejos sistemas de manejo de contenido (CMS) que compiten y funcionan
mejor que las soluciones actuales en el mercado.

Es en este ámbito en que si bien implementar este tipo de soluciones a nivel empresarial es una alternativa de calidad y bajo costo, siempre se presentan
problemas a medida que se comienza a escalar en la cantidad de usuarios
concurrentes.

Bajo tennología LAMP, siempre el escollo es la base de datos.
MySQL, con su configuración por defecto, viene configurada para un nivel
muy bajo de concurrencia. Es menester de este documento el presentarles
varios tips direccionando los tres puntos críticos que involucran el tener
un motor de bases de datos como MySQL, como lo son el servidor, la conexión
del cliente, y el debugging de las queries.

1.1. Optimizando Parámentros en el Servidor.

Desde el punto de vista del servidor, hay varias cosas que se pueden hacer.
Por un lado tenemos los parámentros de manejo de memoria, y por el otro tenemos el manejo del query caché.

1.1.1. Parámetros de Manejo de Memoria.

MySQL tiene varios parámetros para el manejo de memoria en el servidor.
Esos parámetros se dividen en parámetros globales, que son los seteos generales para el servidor, y los parámetros que controlan cada conexión
de los clientes al servidor.

Los parámetros de configuración del servidor MySQL están en el archivo
my.cnf. Dependiendo del sistema operativo y la versión el path donde hallar este archivo varía, pero generalmente en los Linux es /etc/my.cnf.

Para el caso de los seteos del servidor, dentro del archivo my.cnf estos tienen que ir bajo la directiva [mysqld].

Dentro de los parámetros de manejo de memoria para el MySQL tenemos los siguientes:

key_buffer_size : cantidad de memoria destinada a cachear bloques de los
índices de tipo MyISAM.

table_cache : numero máximo de tablas abiertas.

open_files_limit : número máximo de archivos abiertos.

thread_cache_size : número de thread siempre activas en el sistema.

connect_timeout : timeout para conexiones entrantes.

1.1.2. Query Caché.

Query caché, para MySQL, es un pool de memoria donde el motor de bases de
datos guarda las consultas y sus resultados. Cuando esta funcionalidad está
activa dentro del servidor, la primera vez que se genera una consulta esta
es pasada por el parser del SQL, luego revisión de permisos y bloqueos,
analizada por el plan de ejecución, y finalmente ejecutada. La segunda vez que gatillen la misma consulta, el motor de bases de datos llegara a la
revisión de los permisos y si pasa sacará los datos del query caché.
Esto hace que MySQL responda las consultas muy rápido, hasta que la query
caché sea invalidada.

Query Caché viene desabilitado por defecto en MySQL. Esta funcionalidad
fue agregada desde la versión 4. Si query caché está disponible en tu versión
de MySQL, desde el cliente de MySQL debiera salir algo como esto:

mysql> SHOW VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| have_query_cache | YES   |
+——————+——-+

Para ver si está activo, pues que el servidor implemente query caché no
implica que esté activo tienes que tener algo como esto:

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
+——————+———–+
| Variable_name    | Value     |
+——————+———–+
| query_cache_size | 266338304 |
+——————+———–+

Si el parámetro ‘query_cache_size’ está en 0 entonces query caché está
desabilitado!. Basta con setear este parámetro dentro del archivo my.cnf
dentro del aldirectiva [mysqld] o dinámicamente con:

mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Para configurar el Query Caché al inicio del servidor hay que poner las
siguientes directivas dentro de [mysqld] en el my.cnf:

query_cache_size: le indica al servidor la cantidad de memoria que será
destinada para cachear las queries. Este parámetro siempre será
alineado a 1024.

query_cache_limit: establece un límite para que un resultado individual
de una query sea cacheado. Con este parámetro se evita meter a los
full scan table de sacar datos de la caché.

query_cache_type: define como funciona el caché.

query_cache_type=0 implica no tomar datos de la caché ni guardarlos.

query_cache_type=1 implica ocupar la caché para todas las consultas,
salvo aquellas que con ‘SELECT SQL_NO_CACHE’.

quety_cache_type=2 implica ocupar la caché solo con las queries que
explícitamente lo piden con `SELECT SQL_CACHE’.

para un buen uso de la caché lo mejor es poner este parámetro en 1.

1.2. Optimizando Parámetros del Cliente.

Como se explicó anteriormente, MySQL en su configuración principal
separa los seteos entre globales y de clientes. Dentro de estos últimos parámetros hacemos mención de los siguientes:

sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 8M

Algunos de estos parámetros dependen, además, del motor para el manejo
de tablas y bases de datos, como lo son Falcon, MyISAM, InnoDB y Berkeley DB.

1.3. Optimizando las Cosultas SQL.

Para debuggear consultas, MySQL cuenta con un sistema para guardar
estadísticas de ejecución de los queries. Estas estadísticas quedan en
un archivo para luego revisar que es lo que está pasando.

Para activar esta funcionalidad tenemos los siguientes parámetros globales de MySQL:

log-slow-queries : le indica a MySQL a registrar las estadísticas de
todas las queries que demoren más de 10 segundos o lo que indica
el parámetro long_query_time. Esta variable global recibe como
parámetro el path del archivo donde se guardarán las estadísticas.
Por defecto este el archivo con las estadísticas se llama
{HOST_NAME}-slow.log con {HOST_NAME} el nombre del servidor.

long_query_time : cantidad de segundos que MySQL espera por una query
antes de registrar sus estadísticas. Por defecto este parámetro es
10 segundos.