Diario informal de K-nábora

Optimización del Servidor MySQL

Lo mejor que podríamos hacer para conseguir un pleno rendimiento del servidor MySQL, sería asegurarnos que las consultas estén optimizadas, esto sería, comprobar que los índices de las tablas sean los más adecuados, y que las consultas realmente se valen de todo el potencial que MySQL puede ofrecer.

Identificar Consultas Lentas

En un servidor de base de datos la información de la misma se almacena en disco. Los índices proveen al servidor de medios para encontrar una fila concreta de información en una tabla, sin tener que buscar en la tabla entera. Cuando tenemos que analizar la tabla entera estamos realizando un escaneo de tabla . En muchos casos, solo necesitamos recuperar un pequeño subconjunto de la información de la tabla, de tal forma que un escaneo completo de la misma, acaba saturando el acceso al disco donde está guardada y haciéndonos perder tiempo. El problema crece exponencialmente cuando la información a recuperar es producto de algún cruce entre tablas, ya que habría que recorrer mucha más información.

Los escaneos de tablas no son siempre signo de que exista un problema, en muchos casos, es más eficiente recorrer la tabla entera que escoger los registros concretos que nos hacen falta.

El uso de índices ineficientes, o no usarlos, ralentiza las consultas, esto se vuelve cada vez más notorio, según aumenta la carga de consultas en el servidor y el tamaño de las tablas aumenta. Las consultas que requieren más de un cierto tiempo para ejecutarse se denominan consultas lentas.

Se puede configurar mysqld para llevar un log de las consultas lentas, las cuales se recogen en el llamado â€oe log de consultas lentas â€.

Los administradores del sistema se centrarán en este archivo para identificar qué partes de la aplicación necesitan mayor revisión. A continuación se muestra la configuración que se debe realizar en el archivo my.cnf para permitir el registro de consultas lentas.

[mysqld]

;enable the slow query log, default 10 seconds log-slow-queries

;log queries taking longer than 5 seconds

long_query_time = 5

;log queries that don’t use indexes even if they take less than long_query_time

;MySQL 4.1 and newer only log-queries-not-using-indexes

Estas propiedades de la configuración, registran cualquier consulta que emplee en ejecutarse más de 5 segundos, así como aquellas que no utilicen índices.

El archivo de log donde reside esta información, se almacena en el directorio

data de MySQL, bajo el nombre nombre_del_host-slow.log. Si

deseamos utilizar un nombre o un path distintos, especificar lo siguiente, en el archivo my.cnf :

log-slow-queries = /new/path/to/file

Caché de consultas

Muchas aplicaciones se apoyan exageradamente en la base de datos, lo que por ejemplo observamos, cuando las mismas consultas se ejecutan una y otra vez. Cada vez que esta consulta repetitiva es ejecutada la base de datos debe hacer el mismo trabajo, identificar la consulta, determinar cómo ejecutarla, cargar la información desde disco y devolvérsela al cliente.

MySQL tiene una característica denominada query cache (caché de consultas) , que almacena el resultado de una consulta en memoria para el caso en que ésta vuelva a ser necesitada. En muchos casos este simple hecho, aumenta el rendimiento drásticamente, sin embargo, esta opción está desactivada por defecto.

Añadiendo:

query_cache_size = 32M

Al archivo de configuración situado en /etc/my.conf , asigna una caché de 32MB.

Una vez activada está caché, es importante identificar si está siendo correctamente usada o no. MySQL dispone de distintas variables que se pueden monitorizar para ver cómo van las cosas en la caché.

Un ejemplo del estado de la caché lo vemos a continuación:

mysql> SHOW STATUS LIKE ‘qcache%’;

+————————-+————+

| Variable_name | Value |

+————————-+————+

| Qcache_free_blocks | 5216 |

| Qcache_free_memory | 14640664 |

| Qcache_hits | 2581646882 |

| Qcache_inserts | 360210964 |

| Qcache_lowmem_prunes | 281680433 |

| Qcache_not_cached | 79740667 |

| Qcache_queries_in_cache | 16927 |

| Qcache_total_blocks | 47042 |

+————————-+————+

8 rows in set (0.00 sec)

El significado de cada una de estas variables lo veremos a continuación:

Qcache_free_blocks: el número de bloques de memoria continuos en la cach é. La existencia de números altos es signo de fragmentación

.

Qcache_free_memory: La memoria libre en la caché.

Qcache_hits: Este valor se incrementa cada vez que una consulta se resuelve con la informaci ón de la caché.

Qcache_inserts: Este valor se incrementa cada vez que se inserta una consulta . Si dividimos el número de inserciones entre el número de aciertos (Qcache_hits), obtenemos la tasa de fallos; restando este valor de 1, obtenemos la tasa de aciertos.

Qcache_lowmem_prunes: Representa el número de veces que la memoria de la caché se ha agotado y ha tenido que ser vaciada para hacer sitio a nuevas consultas. Si este número crece rápidamente es signo de fragmentación o de falta de memoria .

Qcache_not_cached: Representa el número de consultas que no fueron candidatas para pasar a la caché , normalmente porque no se trataba de sentencias SELECT.

Qcache_queries_in_cache: Indica el número de consultas (y resultados) almacenados en ese momento en la cach é.

Qcache_total_blocks: Indica el número de bloques en la caché.

Reforzar los Límites

Se deberían reforzar algunos límites en mysqld para asegurarnos que la carga del sistema no produzca esperas infinitas por los recursos. A continuación se muestran las variables a configurar para evitar esto:

set-variable=max_connections=500

set-variable=wait_timeout=10

max_connect_errors = 100

Veamos qué significa cada una de estas variables:

set-variable=max_connections=500: identifica en número máximo de conexiones.

set-variable=wait_timeout=10: indica al servidor que termine cualquier conexión que haya estado inactiva por más de 10 segundos.

max_connect_errors = 100: Si un cliente tiene problemas de conexión al servidor y acaba por cancelar la petición muchas veces dicho

cliente será bloqueado hasta que se ejecute FLUSH HOSTS. Por defecto, con 10 errores ya se produciría dicho bloqueo, cambiando el valor a 100, le da al servidor tiempo suficiente para recuperarse de cualquier problema que tenga. Usar un valor más alto, no ayudaría mucho ya que si no se puede establecer una sola conexión en 100 intentos, seguramente no se vaya a conectar en 100 más.

Buffers y Cachés

MySQL permite la modificación de aproximadamente 100 características, pero afortunadamente, con conocer una pequeña parte de ellas podemos lograr grandes mejoras en el rendimiento. Identificar el valor más adecuado para cada variable a configurar, implica analizar el estado general del servidor mediante el comando SHOW STATUS y, a partir de ahí, determinar si mysqld se está comportando como deseamos.

Muchas de las configuraciones a realizar pueden involucrar a todo el servidor o ser aplicadas solo a una conexión cliente en concreto.

A nivel de Servidor:

Cada tabla está representada en disco como un archivo, que debe ser abierto antes de poder ser leído. Para acelerar este proceso de lectura,

mysqld , almacena en caché estos ficheros abiertos, hasta el límite especificado por table_cache en /etc/mysqld.conf .

mysql> SHOW STATUS LIKE ‘open%tables’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables | 5000 |

| Opened_tables | 195 |

+—————+——-+

2 rows in set (0.00 sec)

En el ejemplo anterior, podemos observar que hay en este momento 5.000 tablas abiertas y que 195 tablas tuvieron que ser abiertas, dado que no se encontraba un acceso en caché a dicho fichero.

Si el valor Opened_tables crece rápidamente (lo podemos comprobar ejecutando varias veces, SHOW STATUS ), significará que la caché no almacena la gran mayoría de los ficheros que se solicitan.

Si el valor Open_tables es muy inferior que el valor que tengamos especificado para table_cache , es que hemos asignado demasiado.

Así como hay una caché para las tablas, también la hay para los hilos. En un servidor con gran cantidad de clientes, donde las peticiones llegan y se van rápidamente, hacer una caché de hilos, para su uso posterior, acelera la conexión inicial.

mysql> SHOW STATUS LIKE ‘threads%’;

+——————-+——–+

| Variable_name | Value |

+——————-+——–+

| Threads_cached | 27 |

| Threads_connected | 15 |

| Threads_created | 838610 |

| Threads_running | 3 |

+——————-+——–+

4 rows in set (0.00 sec)

El buffer clave almacena los bloques índices para las tablas MyISAM. Estos bloques deberían ser sacados de memoria en lugar de de disco.

mysql> show status like ‘%key_read%’;

+——————-+———–+

| Variable_name | Value |

+——————-+———–+

| Key_read_requests | 163554268 |

| Key_reads | 98247 |

+——————-+———–+

2 rows in set (0.00 sec)

Las tablas temporales se usan en consultas avanzadas, donde los datos deben ser almacenados temporalmente antes de llevar a cabo las siguientes fases del procesamiento. Estas tablas deberían ser creadas en memoria, pero cuando una tabla temporal es demasiado grande, se escribe en disco.

mysql> SHOW STATUS LIKE ‘created_tmp%’;

+————————-+——-+

| Variable_name | Value |

+————————-+——-+

| Created_tmp_disk_tables | 30660 |

| Created_tmp_files | 2 |

| Created_tmp_tables | 32912 |

+————————-+——-+

3 rows in set (0.00 sec)

Cada uso de una tabla temporal incrementa el valor Created_tmp_tables , las tablas temporales que se almacenan en disco afectan al indicador Created_tmp_disk_tables.

A nivel de Conexión:

Las siguientes configuraciones que se mencionan a continuación, se realizan para cada sesión individual. Hay que prestar especial atención a los valores que se asignan, ya que estos valores multiplicados por el número de posibles conexiones, puede traducirse en gran cantidad de memoria. Los valores de configuración por sesión se pueden asignar mediante código en cada sesión o para todas las sesiones en el fichero my.cnf.

Cuando MySQL debe realizar una ordenación, sitúa un buffer para almacenar las filas, tal y como son leídas de disco. Si el tamaño de la información a ordenar es muy grande, los datos deben ir a ficheros temporales en disco y ordenados de nuevo.

mysql> SHOW STATUS LIKE “sort%”;

+——————-+———+

| Variable_name | Value |

+——————-+———+

| Sort_merge_passes | 1 |

| Sort_range | 79192 |

| Sort_rows | 2066532 |

| Sort_scan | 44006 |

+——————-+———+

4 rows in set (0.00 sec)

Seguridad en MySQL

La instalación por defecto de MySQL configura las capacidades de seguridad básicas, es decir, una instalación por defecto de MySQL es una puerta de entrada a todo tipo de intrusos.

Una vez instalada la base de datos MySQL, para establecer la contraseña del usuario root hay que teclear desde la shell o línea de comandos de su directorio:

mysqladmin -u root password ‘contraseña’

De forma predeterminada MySQL tiene dos usuarios definidos y una base de datos ‘test’. Los usuarios no tienen contraseña y las tablas de la base de datos, que comienzan por ‘test’, tienen permisos de escritura para cualquiera. Para deshabilitar estos usuarios y eliminar las tablas mencionadas hay que ejecutar las siguientes sentencias:

DELETE FROM user WHERE User = ”;

DELETE FROM db WHERE Host = ‘%’;

Si la base de datos solo necesita estar accesible desde la máquina local se debería deshabilitar el TCP Networking. Así eliminaremos cualquier intento de ataque desde la red. Para realizar esta configuración se debe editar el script ‘safe_mysqld’ e incorporar las siguientes modificaciones:

Antes:

–skip-locking >> $err_log 2>&1

–skip-locking “$@” >> $err_log 2>&1

Después:

–skip-networking –skip-locking >> $err_log 2>&1

–skip-networking –skip-locking “$@” >> $err_log 2>&1

Se debería iniciar MySQL con el parámetro –skip-symlink . Con esto se prevendría la posibilidad de una ‘escalada de privilegios’ mediante la sobreescritura de archivos en el sistema.

Para prevenir la posibilidad de sufrir un ataque por ‘denegación de servicio’ se debería restringir las conexiones, estableciendo la variable max_user_connections . Pueden comprobarse los valores actuales de las variables con el comando SHOW VARIABLES , y pueden establecerse mediante el comando SET .

También se debería deshabilitar la posibilidad de utilizar el comando LOAD DATA LOCAL INFILE , lo que eliminará la posibilidad de accesos no autorizados en modo lectura a los ficheros locales. Para ello se deberá agregar el siguiente parámetro al fichero de configuración de

MySQLm my.cnf :

set-variable-local-infile=0

Es recomendable renombrar la cuenta del administrador de MySQL (root). De esta forma estaremos disminuyendo la posibilidad de éxito de un ataque de fuerza bruta contra la contraseña del administrador. Para ello deberemos ejecutar:

UPDATE user SET user = “nuevonombre” WHERE user = “root”; flush privileges;

Para los usuarios de Linux es aconsejable que tras una sesión de línea de comandos con MySQL se vacíe el contenido del archivo .

mysql_history , ya que puede contener información sensible (en él se almacena un histórico con los comandos ejecutados). Para proceder con ello ejecutaremos:

:> /home/usuario/.mysql_history

 

Fuente: DBRunas.com.ar