- MySQL, al igual que la mayoría de gestores de bases de datos, permite modificar fácilmente sus parámetros que controlan tamaños de memoria dedicados a determinadas tareas, utilización de recursos, límites de concurrencia, etc.
Ajustando adecuadamente estos parámetros se pueden obtener muchas mejoras de rendimiento, sobretodo si el servidor/es de la base de datos no va sobrado de recursos, y si por la parte de optimización SQL no se puede mejorar mucho más.
Yo hace poco he realizado algunos ajustes básicos en una base de datos MySQL, así que voy aprovechar para explicar un poco el proceso que he seguido para quien busque una manera sencilla de hacer una primera optimización de parámetros en la base de datos. Con esto no quiero decir que esta sea la mejor manera de hacerlo, sólo la que a mi me ha resultado bien.
Ajustando adecuadamente estos parámetros se pueden obtener muchas mejoras de rendimiento, sobretodo si el servidor/es de la base de datos no va sobrado de recursos, y si por la parte de optimización SQL no se puede mejorar mucho más.
Yo hace poco he realizado algunos ajustes básicos en una base de datos MySQL, así que voy aprovechar para explicar un poco el proceso que he seguido para quien busque una manera sencilla de hacer una primera optimización de parámetros en la base de datos. Con esto no quiero decir que esta sea la mejor manera de hacerlo, sólo la que a mi me ha resultado bien.
Lo primero comentar que puede ser muy útil echar un vistazo dentro de phpMyAdmin a las secciones 'Mostrar información de tiempo de ejecución de MySQL' y 'Mostrar las variables del sistema MySQL', normalmente accesibles desde la página principal de la aplicación.
La primera muestra información y estadísticas que el sistema recopila y mantiene desde su arranque. Hay que prestar especial atención a los valores de variables que se muestran en color rojo, y a los consejos que se indican a la derecha de estos valores.
Consultar las variables sirve para saber los valores actuales de los parámetros que más adelante podríamos modificar para mejorar el rendimiento.
- Evita SELECT *
Cuanta más información se lee de las tablas, más lenta se ejecutará la petición SQL. Aumenta el tiempo que toma para las operaciones en disco. Además cuando el servidor de bases de datos está separado del servidor web, tendrás mayores retrasos de red debido a que la información tiene que ser transferida entre ambos servidores.
Es un buen hábito especificar siempre las columnas que necesitas cuando estás haciendo un SELECT.
// preferible no hacer:
$r = mysql_query("SELECT * FROM usuarios WHERE id_usuario = 1");
$d = mysql_fetch_assoc($r);
echo "Bienvenido {$d['nombreusuario']}";
// mejor:
$r = mysql_query("SELECT nombreusuario FROM usuarios WHERE id_usuario = 1");
$d = mysql_fetch_assoc($r);
echo "Bienvenido {$d['nombreusuario']}";
- Escoge el motor de almacenamiento adecuado. Los dos principales motores en MySQL son MyISAM y InnoDB, Cada uno tiene sus pros y sus contras.
- Las columnas pequeñas son más rápidas
- Usa NOT NULL si puedes
- Cambiar VARCHAR por CHAR
- al usar un mysql_query() y al terminar de usarlo, Cerrar conexión de inmediato mysql_close().
- Indexa los campos de Búsqueda. Los índices no son sólo para las claves primarias o las claves únicas. Si en tu tabla hay columnas sobre las que vas a realizar búsquedas, deberías indexarlas casi siempre (Indexa solo la columna que usas mucho, ya que si indexas todas en vez de ayudarte te perjudicará).
- Las columnas pequeñas son más rápidas
- Usa NOT NULL si puedes
- Cambiar VARCHAR por CHAR
- al usar un mysql_query() y al terminar de usarlo, Cerrar conexión de inmediato mysql_close().
- Indexa los campos de Búsqueda. Los índices no son sólo para las claves primarias o las claves únicas. Si en tu tabla hay columnas sobre las que vas a realizar búsquedas, deberías indexarlas casi siempre (Indexa solo la columna que usas mucho, ya que si indexas todas en vez de ayudarte te perjudicará).
- El tipo datetime ocupa 8 bytes y no es timezone-aware. Timestamp ocupa la mitad (4 bytes) y es timezone-aware. Usa timestamp.
- El tipo varchar es más lento que char, pero generalmente ocupa menos espacio. Antes de definir la columna piensa que datos se van a guardar. Si son de tamaño fijo (o muy muy similares) usa char (por ejemplo para un md5).
- Si tu charset es UTF-8, es posible que el string ocupe hasta el tripe de espacio. Usa UTF-8 solamente donde sea necesario.
- Si en un campo varchar o char vas a guardar siempre los mismos strings, usa enum.
- Si no necesitas decimales exactos, usa tipos de coma flotante (float o double). El tipo decimal no lo calcula la CPU (no puede), lo hace MySQL, lo cual es muchisimo más lento.
- Todas las columnas deben ser NOT NULL. Procesar un NULL consume CPU y memoria. Si quieres que los campos se puedan quedar vacios, mete un 0 o un string vacio ””.
- Poner índices a todas las columnas no es la solución para mejorar el rendimiento. Ponlo solo en las columnas por las que busques. Contrá más índices, mas uso de memoria y los INSERT y UPDATES serán cada vez más lentos.
- En un sistema de 32 bits da igual cuanta memoria RAM tengas, MySQL solamente usará 2 GB como máximo. No configures los buffers y caches a un valor mayor o te empezarán a fallar las querys.
- Si tienes una tabla de insercción constante (por ejemplo un log) no lo dejes en InnoDB, utiliza archive. Comprimirá los datos y las busquedas posiblemente sean igual o más rápidas.
- No existe un engine perfecto para todas las situaciones. Debes estudiar como se tratará la tabla y que uso harás de ella. ¿Vas a usar transacciones? ¿necesitas merge? ¿haces busquedas full text? ¿se van a updatear o eliminar datos o todo serán insert? ¿vas a comprimir en un futuro la tabla? ¿necesitas cachear datos? Poner todo en InnoDB no es una solución.
- BLOB y TEXT no pueden ser índices.
- El engine MEMORY no soporta BLOB y TEXT, si alguna tabla con esos tipos de datos necesita una tabla temporal irá directamente a disco duro. No uses BLOG y TEXT a poder ser.
- Habilitar sync-binlog tiene cosas buenas y malas. La buena que no dependes del sistema de ficheros para recuperarte de un fallo del servidor (ningún FS es perfecto) y la mala que haces un uso más intenso en I/O del disco duro. Piensa bien que quieres antes de habilitarlo, rendimiento o seguridad.
- El tipo varchar es más lento que char, pero generalmente ocupa menos espacio. Antes de definir la columna piensa que datos se van a guardar. Si son de tamaño fijo (o muy muy similares) usa char (por ejemplo para un md5).
- Si tu charset es UTF-8, es posible que el string ocupe hasta el tripe de espacio. Usa UTF-8 solamente donde sea necesario.
- Si en un campo varchar o char vas a guardar siempre los mismos strings, usa enum.
- Si no necesitas decimales exactos, usa tipos de coma flotante (float o double). El tipo decimal no lo calcula la CPU (no puede), lo hace MySQL, lo cual es muchisimo más lento.
- Todas las columnas deben ser NOT NULL. Procesar un NULL consume CPU y memoria. Si quieres que los campos se puedan quedar vacios, mete un 0 o un string vacio ””.
- Poner índices a todas las columnas no es la solución para mejorar el rendimiento. Ponlo solo en las columnas por las que busques. Contrá más índices, mas uso de memoria y los INSERT y UPDATES serán cada vez más lentos.
- En un sistema de 32 bits da igual cuanta memoria RAM tengas, MySQL solamente usará 2 GB como máximo. No configures los buffers y caches a un valor mayor o te empezarán a fallar las querys.
- Si tienes una tabla de insercción constante (por ejemplo un log) no lo dejes en InnoDB, utiliza archive. Comprimirá los datos y las busquedas posiblemente sean igual o más rápidas.
- No existe un engine perfecto para todas las situaciones. Debes estudiar como se tratará la tabla y que uso harás de ella. ¿Vas a usar transacciones? ¿necesitas merge? ¿haces busquedas full text? ¿se van a updatear o eliminar datos o todo serán insert? ¿vas a comprimir en un futuro la tabla? ¿necesitas cachear datos? Poner todo en InnoDB no es una solución.
- BLOB y TEXT no pueden ser índices.
- El engine MEMORY no soporta BLOB y TEXT, si alguna tabla con esos tipos de datos necesita una tabla temporal irá directamente a disco duro. No uses BLOG y TEXT a poder ser.
- Habilitar sync-binlog tiene cosas buenas y malas. La buena que no dependes del sistema de ficheros para recuperarte de un fallo del servidor (ningún FS es perfecto) y la mala que haces un uso más intenso en I/O del disco duro. Piensa bien que quieres antes de habilitarlo, rendimiento o seguridad.
- Habilita el log_slow_queries y échale un ojo de vez en cuando MySQL 5.0 solo permite especificar segundos para definir una query lenta, debes aplicar parche o pasarte a MySQL 5.1 para poder hacerlo en milisegundos.
- Habilitar log-queries-not-using-indexes no es recomendable. Que no tengan índices no quiere decir que tengan que ser lentas. Un “select count(*) from t” no usa índices y en MyISAM el resultado es instantaneo. Habilitarlo solo aumentará el I/O.
- Si en una misma conexión tienes que escribir en dos BBDD distintas, cambiate primero de una a otra con USE. No seas cerdete y hagas “INSERT INTO db.t”. Eso no se lleva muy bien con los logs binarios y la replicación.
- En replicaciones con poca carga (0 segundos en master behind) si esta se rompe es muy posiblemente culpa tuya, no de la replicación en si.
- A no ser que lo necesites de verdad no actives el log de todas las querys (con la opción “log”). Se escriben todas las sentencias ejecutadas, select incluidas, incluso aunque estas no se terminen commiteando. Además, te terminarás quedando sin disco duro sin contar el I/O que consume.
- Si tienes que borrar gran cantidad de datos cada X tiempo, no hagas un “DELETE from” ya que tardará infinito. Planteate hacer uso de particiones o tablas MERGE de forma que puedas borrarlas de golpe en lugar de fila a fila.
- No uses subquerys. Y si las has estado utilizando, reescribelas como JOIN. El rendimiento es mucho mejor.
- Si tienes que hacer un cambio de schema en una tabla muy grande hazlo por la noche. Un alter table te bloqueará toda la tabla para lecturas/escrituras.
- MySQL tiene un Query Profiler. Úsalo y no vayas siempre llorando al Sysadmin.
- Si en una misma conexión tienes que escribir en dos BBDD distintas, cambiate primero de una a otra con USE. No seas cerdete y hagas “INSERT INTO db.t”. Eso no se lleva muy bien con los logs binarios y la replicación.
- En replicaciones con poca carga (0 segundos en master behind) si esta se rompe es muy posiblemente culpa tuya, no de la replicación en si.
- A no ser que lo necesites de verdad no actives el log de todas las querys (con la opción “log”). Se escriben todas las sentencias ejecutadas, select incluidas, incluso aunque estas no se terminen commiteando. Además, te terminarás quedando sin disco duro sin contar el I/O que consume.
- Si tienes que borrar gran cantidad de datos cada X tiempo, no hagas un “DELETE from” ya que tardará infinito. Planteate hacer uso de particiones o tablas MERGE de forma que puedas borrarlas de golpe en lugar de fila a fila.
- No uses subquerys. Y si las has estado utilizando, reescribelas como JOIN. El rendimiento es mucho mejor.
- Si tienes que hacer un cambio de schema en una tabla muy grande hazlo por la noche. Un alter table te bloqueará toda la tabla para lecturas/escrituras.
- MySQL tiene un Query Profiler. Úsalo y no vayas siempre llorando al Sysadmin.
