Archivo de log de sentencias SQL en MySQL o MariaDB

Muchas aplicaciones utilizan para su capa de persistencia un framework ORM que abstraen al programador de la base de datos, de las peculiaridades de cada una ellas y de las sentencias SQL que se envían a la base de datos para realizar las operaciones de recuperación, actualización, borrado y búsqueda. En Java el ORM suele ser Hibernate, en Symphony es Doctrine, en Ruby on Rails es ActiveRecord, en Django es Models pero en definitiva todas sirven para el mismo propósito que es darnos un marco de trabajo más similar a un lenguaje orientado a objetos y hacernos olvidar el lenguaje SQL que hay que utilizar para comunicarse con cualquier base de datos relacional.

Sin embargo, esa facilidad que dan los ORM puede hacer que las SQL que realmente se envían a la base de datos no sean eficientes o no sepamos el número exacto de las que se están enviando (problema N+1) lo que puede hacer que ciertas funcionalidades de una aplicación sean lentas y poco eficientes con un volumen de datos grande.

En MySQL y MariaDB se puede obtener un archivo de log con todas las sentencias que se están enviando. Además también se puede obtener un log con aquellas sentencias que tardan en ejecutarse más tiempo de que estimemos como lento para una sentencia. Para obtener estos archivos de log debemos modificar la configuración de MySQL o de MariaDB que en Linux está en el archivo /etc/mysq/my.cnf. Al final de la sección [mysqld] debemos añadir las siguiente linea para obtener un archivo con todas las sentencias:

log = /var/log/mysqld.log

view raw my-1.cnf hosted with ❤ by GitHub

Y lo siguiente para obtener un archivo con solo las sentencias lentas, en el caso del ejemplo las que tardan en ejecutarse más de un segundo:

	long_query_time = 1
	log-slow-queries = /var/log/mysqld-slow-queries.log

view raw my-2.cnf hosted with ❤ by GitHub

Para que MySQL pueda escribir sobre estos archivos debemos crear los archivos y asignarle permisos al usuario con el que se ejecuta MySQL con:

	$ sudo touch /var/log/mysqld.log
	$ sudo touch /var/log/mysqld-slow-queries.log sudo
	$ sudo chown mysql:mysql /var/log/mysqld.log
	$ sudo chown mysql:mysql /var/log/mysqld-slow-queries.log

view raw bash.sh hosted with ❤ by GitHub

Una vez modificada la configuración y creados los archivos de log con los permisos adecuados debemos reiniciar el servicio de MySQL.
Estos los nos ayudarán a identificar sitios con el problema de N+1 al acceder a una relación 1 a N entre dos objetos o si es necesario que creemos algún índice para hacer más rápidas ciertas consultas si vemos que algunas están saliendo en el log de sentencias lentas.

Referencia:
http://www.redips.net/mysql/query-logging/
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
http://stackoverflow.com/questions/6614429/logging-mysql-query-on-ubuntu