lunes, 28 de marzo de 2011

Optimización de MySql InnoDB en un servidor Linux

Tras un arduo proceso de investigación y documentación, voy a describir el proceso a seguir para configurar un servidor MySql para producción. Las bases consultadas no son otras que la documentación oficial en dev.mysql.com y mysqlperformanceblog.com.

En la configuración de un servidor debes pensar en una instalación que tenga un rendimiento acorde con lo que se necesita, empezando por la instalación del sistema operativo. Como es de imaginar principalmente MySql realiza tareas pesadas sobre los discos duros, por lo tanto la elección de discos duros con bajos tiempos de búsqueda es importante pero también es importante si tienes varios discos, optar por usar RAID 0 para unirlos, lo cual afecta bastante al rendimiento de las búsquedas.

Después será necesario revisar la implementación y el diseño de tu base de datos. Respecto al diseño no contaré nada puesto que depende de la aplicación/es que hagan uso del Data Source con MySql, pero respecto a la implementación si que hay un par de cuestiones a tener en cuenta:
  • Manten el tamaño de los campos de tu BBDD lo más pequeño posible. Si puedes usar mediumint en lugar de bigint hazlo, y a colación e igual de importante es que:
  • No utilices campos `varchar`. Y si lo necesitas, al final de la tabla. Dependiendo de tus necesidades por supuesto, pero ten en cuenta que en su lugar puedes usar el tipo `char`. Un tamaño variable para las cadenas ahorra espacio en disco pero un tamaño fijo permite un acceso más rápido a los datos puesto que no hay que calcular la longitud del registro variable como ocurre cuando tenemos un `varchar` y saltamos al siguiente registro. InnoDB realiza algunas optimizaciones por ejemplo convirtiendo `varchar` de tamaño menor de 4 en `char`, pero es deseable que calcules la longitud máxima de un campo y crees campos de tamaño `fixed`. Dentro de poco cuando regalen TeraBytes con las cajas de galletas, esta pérdida de espacio con registros `fixed` no será tan importante como el rendimiento de las consultas a tu servidor... ;)
  • Crea solo los índices que necesitas y elimina índices duplicados. Los índices benefician el proceso de lectura pero pueden complicar el de escritura, así que debes tener cuidado con esto. También ocurre que borras y creas una tabla un varias veces cambiando el script, modificando... y al final, revisando te das cuenta de que tienes un índice duplicado. Si el tamaño de tu tabla no es muy grande, el gasto en memoria puede no ser demasiado, pero he llegado a reducir en cerca de 3 Gb el tamaño de una tabla tan solo descubriendo y eliminando un par de índices duplicados.

Una vez que hayas revisado esto lo deseable es afinar el rendimiento de la ejecución de MySql. Los parámetros más importantes son (para InnoDB):

  • innodb_data_home_dir ( =/var/lib/mysql): Es el lugar donde InnoDB almacenará la información (anteponiéndolo a innodb_data_file_path en caso de existir) así como los ficheros de registro. Asegúrate que apunta a tus discos en RAID 0.
  • innodb_data_file_path | innodb_file_per_table: Con estos parámetros configuras como se almacenan tus datos y donde. Si especificas innodb_file_per_table, indicas a MySql que guarde los datos de cada tabla en un fichero distinto. Esto es beneficioso si tienes en mente ampliaciones y/o migraciones.
  • innodb_buffer_pool_size: El tamaño que ocupa en memoria el almacenamiento intermedio de datos. Cuanto mayor es este valor, menor es la cantidad de operaciones de lectura/escritura en disco. El valor adecuado para un servidor dedicado solo a MySql debería permitir la ejecución del sistema operativo sin problemas. Ten en cuenta que a este tamaño se suma el del parámetro key_buffer_size en el motor MyISAM. Desde la documentación oficial de MySql especifican un 80% de la memoria del ordenador, pero con 64Gb de RAM tendríamos 12 Gb para el SO... es mucho ¿no?
  • innodb_log_file_size: Es el tamaño de los ficheros de registro de InnoDB. Pueden existir varios y la suma de sus tamaños no puede exceder los 4Gb. Un tamaño mayor reduce las operaciones de lectura/escritura en disco, así que este parámetro también es importante. Una regla para fijar este valor se basa en darle el 25% del tamaño especificado en innodb_buffer_pool_size. Ten en cuenta que cada vez que quieras cambiar el tamaño de los ficheros de registro debes fijarte en que probablemente ya existan así es que tendrás que:
    1. Parar el servidor,
    2. Hacer copia de seguridad de los ficheros de log. Normalmente ib_logifle0 e ib_logfile1 en /var/lib/mysql renombrados a ib_logfile0.BAK e ib_logfile1.BAK. En caso de pánico y horror puedes restaurarlos.
    3. Borrar los originales (ib_logfile0 e ib_logfile1),
    4. Especificar el nuevo tamaño, e
    5. Iniciar MySql
  • innodb_log_buffer_size: Es el tamaño del almacenamiento intermedio en los ficheros de registros, por lo tanto valores altos también reducen el número de operaciones de lectura/escritura. Aún así el tamaño de esta variable (evidentemente siempre menor que innodb_log_file_size) se establece como razonable entre 8 y 16 Mb ya que este buffer se vuelca forzosamente cada segundo, así que puede no ser necesario un tamaño mayor y si que sería un gasto de memoria.
  • innodb_flush_log_at_trx_commit: Este parámetro controla cuando se vuelcan los datos del buffer en los ficheros de registros. Este volcado se realiza cada segundo forzosamente pero puede hacerse más a menudo. El valor por defecto de 1, indica que este volcado se realiza tras un commit. Un valor de 2 sería cada 2 commit, o cada segúndo, lo que ocurra antes. De esta forma un valor de 2 reduce las operaciones de E/S en disco pero podríamos perder un segundo de transacciones en caso de error del sistema. Yo pongo 2... ;)
  • innodb_flush_method: Indica la forma de volcado de los ficheros de registros y datos. Si está disponible, con la opción O_DIRECT evitamos el uso de memoria swap y así MySql vuelca estos ficheros directamente en memoria física evitando consumir ancho de banda entre la memoria y la CPU por las copias de la cache del kernel y del espacio de memoria del usuario.

Pues con esto espero haber arrojado algo más de información sobre los parámetros más importantes, aunque evidentemente me dejo mucho en el tintero, pero para mí estas son las cuestiones más importantes. Suerte!!