Cómo optimizar las consultas MySQL con el almacenamiento en caché ProxySQL en Ubuntu 16.04

El autor seleccionó a la Free Software Foundation para recibir una donación como parte del programa Write for DOnations.

Introducción

ProxySQL es un servidor proxy compatible con SQL que se puede ubicar entre su aplicación y su base de datos. Ofrece muchas funciones, como el equilibrio de carga entre varios servidores MySQL y la función de capa de almacenamiento en caché para consultas. Este tutorial se centrará en la función de almacenamiento en caché de ProxySQL y en cómo puede optimizar las consultas para su base de datos MySQL.

El almacenamiento en caché de MySQL se produce cuando el resultado de una consulta se almacena de forma que, cuando se repite dicha consulta, se puede obtener el resultado sin necesidad de buscar en la base de datos. Esto puede aumentar significativamente la velocidad de las consultas comunes. Pero en muchos métodos de almacenamiento en caché, los desarrolladores deben modificar el código de su aplicación, lo que podría introducir un error en el código base. Para evitar esta práctica propensa a errores, ProxySQL le permite configurar el almacenamiento en caché transparente.

En el almacenamiento en caché transparente, solo los administradores de bases de datos deben cambiar la configuración de ProxySQL para habilitar el almacenamiento en caché para las consultas más comunes, y estos cambios se pueden realizar a través de la interfaz de administración de ProxySQL. Todo lo que el desarrollador debe hacer es conectarse al proxy que reconoce el protocolo, y el proxy decidirá si la consulta se puede procesar desde el caché sin llegar al servidor back-end.

En este tutorial, utilizará ProxySQL para configurar el almacenamiento en caché transparente para un servidor MySQL en Ubuntu 16.04. Luego, probará su rendimiento utilizando mysqlslap con y sin almacenamiento en caché para demostrar el efecto del almacenamiento en caché y cuánto tiempo puede ahorrar al ejecutar muchas consultas similares.

Prerrequisitos

Antes de comenzar esta guía necesitarás lo siguiente:

• Un servidor Ubuntu 16.04 con al menos 2 GB de RAM, configurado con un usuario no root con privilegios de sudo y un firewall, como se indica en nuestra guía de configuración inicial del servidor Ubuntu 16.04.

Paso 1: Instalación y configuración del servidor MySQL

Primero, instalará el servidor MySQL y lo configurará para que ProxySQL lo use como servidor back-end para atender consultas de clientes.

En Ubuntu 16.04, mysql-serverse puede instalar usando este comando:

sudo apt-get install mysql-server

Presione Ypara confirmar la instalación.

Luego se le solicitará la contraseña del usuario root de MySQL . Ingrese una contraseña segura y guárdela para usarla más adelante.

Ahora que tiene listo su servidor MySQL, lo configurará para que ProxySQL funcione correctamente. Debe agregar un usuario de monitor para que ProxySQL monitoree el servidor MySQL, ya que ProxySQL escucha al servidor back-end a través del protocolo SQL, en lugar de usar una conexión TCP o GETsolicitudes HTTP para asegurarse de que el back-end esté en funcionamiento. El monitor usará una conexión SQL ficticia para determinar si el servidor está activo o no.

Primero, inicie sesión en el shell MySQL:

mysql -uroot -p

-urootinicia sesión con el usuario root de MySQL y -psolicita la contraseña del usuario root . Este usuario root es diferente del usuario root de su servidor y la contraseña es la que ingresó al instalar el mysql-serverpaquete.

Ingrese la contraseña de root y presione ENTER.

Ahora creará dos usuarios, uno llamado monitor for ProxySQL y otro que utilizará para ejecutar consultas de clientes y otorgarles los privilegios adecuados. En este tutorial, le asignaremos a este usuario el nombre sammy .

Crear el usuario monitor :

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';

La CREATE USERconsulta se utiliza para crear un nuevo usuario que se puede conectar desde direcciones IP específicas. El uso %denota que el usuario puede conectarse desde cualquier dirección IP. IDENTIFIED BYEstablece la contraseña para el nuevo usuario; ingrese la contraseña que desee, pero asegúrese de recordarla para usarla más adelante.

Con el monitor de usuario creado, a continuación creamos el usuario sammy :

CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';

A continuación, otorgue privilegios a los nuevos usuarios. Ejecute el siguiente comando para configurar el monitor :

GRANT SELECT ON sys.* TO 'monitor'@'%';

La GRANTconsulta se utiliza para otorgar privilegios a los usuarios. Aquí se le otorgan privilegios solo SELECTen todas las tablas de la sysbase de datos al usuario de monitor ; solo necesita este privilegio para escuchar al servidor back-end.

Ahora conceda todos los privilegios a todas las bases de datos al usuario sammy :

GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';

Esto permitirá a sammy realizar las consultas necesarias para probar su base de datos más tarde.

Aplique los cambios de privilegios ejecutando lo siguiente:

FLUSH PRIVILEGES;

Finalmente, salga del mysqlshell:

exit;

Ya ha instalado mysql-servery creado un usuario que ProxySQL utilizará para supervisar su servidor MySQL y otro para ejecutar consultas de clientes. A continuación, instalará y configurará ProxySQL.

Paso 2: Instalación y configuración de ProxySQL Server

Ahora puede instalar el servidor ProxySQL, que se utilizará como capa de almacenamiento en caché para sus consultas. Una capa de almacenamiento en caché existe como una parada entre sus servidores de aplicaciones y los servidores back-end de la base de datos; se utiliza para conectarse a la base de datos y guardar los resultados de algunas consultas en su memoria para un acceso rápido más adelante.

La página de Github de versiones de ProxySQL ofrece archivos de instalación para distribuciones Linux comunes. Para este tutorial, utilizará wgetpara descargar el archivo de instalación de Debian de la versión 2.0.4 de ProxySQL:

wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb

A continuación, instale el paquete usando dpkg:

sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

Una vez instalado, inicie ProxySQL con este comando:

sudo systemctl start proxysql

Puede comprobar si ProxySQL se inició correctamente con este comando:

sudo systemctl status proxysql

Obtendrás un resultado similar a este:

Outputroot@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql● proxysql.service - LSB: High Performance Advanced Proxy for MySQL   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)   Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago     Docs: man:systemd-sysv-generator(8)    Tasks: 0   Memory: 0B      CPU: 0

Ahora es el momento de conectar el servidor ProxySQL al servidor MySQL. Para ello, utilice la interfaz SQL de administración de ProxySQL, que por defecto escucha en el puerto 6032y localhosttiene admincomo nombre de usuario y contraseña.

Conéctese a la interfaz ejecutando lo siguiente:

mysql -uadmin -p -h 127.0.0.1 -P6032

Ingrese admincuando se le solicite la contraseña.

-uadminEstablece el nombre de usuario como adminy el -hindicador especifica el host como localhost. El puerto es 6032, especificado mediante el -Pindicador.

Aquí había que especificar el host y el puerto explícitamente porque, de manera predeterminada, el cliente MySQL se conecta usando un archivo de sockets local y un puerto 3306.

Ahora que ha iniciado sesión en el mysqlshell como admin, configure el usuario de monitor para que ProxySQL pueda usarlo. Primero, utilice consultas SQL estándar para establecer los valores de dos variables globales:

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

La variable mysql-monitor_usernameespecifica el nombre de usuario de MySQL que se utilizará para comprobar si el servidor back-end está activo o no. La variable mysql-monitor_passwordapunta a la contraseña que se utilizará al conectarse al servidor back-end. Utilice la contraseña que creó para el nombre de usuario del monitor .

Cada vez que crea un cambio en la interfaz de administración de ProxySQL, debe usar el LOADcomando correcto para aplicar los cambios a la instancia de ProxySQL en ejecución. Cambió las variables globales de MySQL, así que cárguelas para RUNTIMEaplicar los cambios:

LOAD MYSQL VARIABLES TO RUNTIME;

A continuación, SAVEse realizan los cambios en la base de datos en disco para que los cambios persistan entre reinicios. ProxySQL utiliza su propia base de datos local SQLite para almacenar sus propias tablas y variables:

SAVE MYSQL VARIABLES TO DISK;

Ahora, le informará a ProxySQL sobre el servidor back-end. La tabla mysql_serverscontiene información sobre cada servidor back-end donde ProxySQL puede conectarse y ejecutar consultas, por lo que debe agregar un nuevo registro utilizando una INSERTdeclaración SQL estándar con los siguientes valores para hostgroup_id, hostnamey port:

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);

Para aplicar los cambios, ejecute LOADnuevamente SAVE:

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Finalmente, le indicará a ProxySQL qué usuario se conectará al servidor back-end; configure a sammy como usuario y reemplácelo sammy_passwordcon la contraseña que creó anteriormente:

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);

La tabla mysql_userscontiene información sobre los usuarios utilizados para conectarse a los servidores back-end; usted especificó username, password, y default_hostgroup.

LOADy SAVElos cambios:

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Luego salga del mysqlshell:

exit;

Para probar que puede conectarse a su servidor back-end mediante ProxySQL, ejecute la siguiente consulta de prueba:

mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"

En este comando, se utilizó el -eindicador para ejecutar una consulta y cerrar la conexión. La consulta imprime el nombre de host del servidor back-end.

Nota: ProxySQL utiliza el puerto 6033de forma predeterminada para escuchar conexiones entrantes.

El resultado se verá así, your_hostnamereemplazado por su nombre de host:

Output+----------------------------+| hostname                   |+----------------------------+| your_hostname        |+----------------------------+

Para obtener más información sobre la configuración de ProxySQL, consulte el Paso 3 de Cómo usar ProxySQL como equilibrador de carga para MySQL en Ubuntu 16.04.

Hasta ahora, ha configurado ProxySQL para utilizar su servidor MySQL como backend y se ha conectado al backend mediante ProxySQL. Ahora, está listo para utilizarlo mysqlslappara evaluar el rendimiento de las consultas sin almacenamiento en caché.

Paso 3: Pruebas mysqlslapsin almacenamiento en caché

En este paso, descargará una base de datos de prueba para poder ejecutar consultas en ella y mysqlslapprobar la latencia sin almacenamiento en caché, lo que establecerá un punto de referencia para la velocidad de sus consultas. También explorará cómo ProxySQL mantiene registros de las consultas en la stats_mysql_query_digesttabla.

mysqlslapes un cliente de emulación de carga que se utiliza como herramienta de prueba de carga para MySQL. Puede probar un servidor MySQL con consultas generadas automáticamente o con algunas consultas personalizadas ejecutadas en una base de datos. Viene instalado con el paquete de cliente MySQL, por lo que no es necesario instalarlo; en su lugar, descargará una base de datos solo para fines de prueba, en la que puede usar mysqlslap.

En este tutorial, utilizará una base de datos de empleados de muestra. La utilizará porque cuenta con un gran conjunto de datos que puede ilustrar las diferencias en la optimización de consultas. La base de datos tiene seis tablas, pero los datos que contiene tienen más de 300 000 registros de empleados. Esto le ayudará a emular una carga de trabajo de producción a gran escala.

Para descargar la base de datos, primero clona el repositorio de Github usando este comando:

git clone https://github.com/datacharmer/test_db.git

Luego ingrese al test_dbdirectorio y cargue la base de datos en el servidor MySQL usando estos comandos:

cd test_db
mysql -uroot -p  employees.sql

Este comando utiliza la redirección de shell para leer las consultas SQL en employees.sqlel archivo y ejecutarlas en el servidor MySQL para crear la estructura de la base de datos.

Verá un resultado como este:

OutputINFOCREATING DATABASE STRUCTUREINFOstorage engine: InnoDBINFOLOADING departmentsINFOLOADING employeesINFOLOADING dept_empINFOLOADING dept_managerINFOLOADING titlesINFOLOADING salariesdata_load_time_diff00:00:32

Una vez cargada la base de datos en su servidor MySQL, pruebe que mysqlslapfunciona con la siguiente consulta:

mysqlslap -usammy -p -P6033 -h127.0.0.1  --auto-generate-sql --verbose

mysqlslaptiene indicadores similares al mysqlcliente; aquí están los utilizados en este comando:

• -uEspecifica el usuario utilizado para conectarse al servidor.
• -psolicita la contraseña del usuario.
• -Pse conecta utilizando el puerto especificado.
• -hse conecta al host especificado.
• --auto-generate-sqlpermite que MySQL realice pruebas de carga utilizando sus propias consultas generadas.
• --verbosehace que la salida muestre más información.

Obtendrá un resultado similar al siguiente:

OutputBenchmark    Average number of seconds to run all queries: 0.015 seconds    Minimum number of seconds to run all queries: 0.015 seconds    Maximum number of seconds to run all queries: 0.015 seconds    Number of clients running queries: 1    Average number of queries per client: 0

En este resultado, puede ver la cantidad promedio, mínima y máxima de segundos que se emplearon para ejecutar todas las consultas. Esto le brinda una indicación sobre la cantidad de tiempo que se necesita para ejecutar las consultas por parte de varios clientes. En este resultado, solo se utilizó un cliente para ejecutar las consultas.

A continuación, busque qué consultas mysqlslapse ejecutaron en el último comando consultando el archivo .exe de ProxySQL stats_mysql_query_digest. Esto nos dará información como el resumen de las consultas, que es una forma normalizada de la declaración SQL a la que se puede hacer referencia más adelante para habilitar el almacenamiento en caché.

Ingrese a la interfaz de administración de ProxySQL con este comando:

mysql -uadmin -p -h 127.0.0.1 -P6032

Luego ejecute esta consulta para buscar información en la stats_mysql_query_digesttabla:

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

Verá un resultado similar al siguiente:

+------------+----------+-----------+--------------------+----------------------------------+| count_star | sum_time | hostgroup | digest             | digest_text                      |+------------+----------+-----------+--------------------+----------------------------------+| 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    || 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |+------------+----------+-----------+--------------------+----------------------------------+2 rows in set (0.01 sec)

La consulta anterior selecciona datos de la stats_mysql_query_digesttabla que contiene información sobre todas las consultas ejecutadas en ProxySQL. Aquí tiene cinco columnas seleccionadas:

• count_star:El número de veces que se ejecutó esta consulta.
• sum_time:Tiempo total en milisegundos que tardó esta consulta en ejecutarse.
• hostgroup:El grupo de host utilizado para ejecutar la consulta.
• digest:Un resumen de la consulta ejecutada.
• digest_text: La consulta real. En el ejemplo de este tutorial, la segunda consulta se parametriza utilizando ?marcas en lugar de parámetros variables. select @@version_comment limit 1y select @@version_comment limit 2, por lo tanto, se agrupan como la misma consulta con el mismo resumen.

Ahora que sabe cómo comprobar los datos de la consulta en la stats_mysql_query_digesttabla, salga del mysqlshell:

exit;

La base de datos que ha descargado contiene algunas tablas con datos de demostración. Ahora probará las consultas en la dept_emptabla seleccionando los registros cuyo valor from_datesea mayor que 2000-04-20y registrando el tiempo de ejecución promedio.

Utilice este comando para ejecutar la prueba:

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date'2000-04-20'" --verbose

Aquí estás usando algunas nuevas banderas:

• --concurrency=100:Esto establece el número de usuarios a simular, en este caso 100.
• --iterations=20:Esto hace que la prueba se ejecute 20varias veces y calcule los resultados de todas ellas.
• --create-schema=employees:Aquí usted seleccionó la employeesbase de datos.
• --query="SELECT * from dept_emp WHERE from_date'2000-04-20'":Aquí se especifica la consulta que se ejecutará en la prueba.

La prueba tardará unos minutos. Una vez realizada, obtendrá resultados similares a los siguientes:

OutputBenchmark        Average number of seconds to run all queries: 18.117 seconds        Minimum number of seconds to run all queries: 8.726 seconds        Maximum number of seconds to run all queries: 22.697 seconds        Number of clients running queries: 100        Average number of queries per client: 1

Los números pueden ser un poco diferentes. Guarde estos números en algún lugar para poder compararlos con los resultados obtenidos después de habilitar el almacenamiento en caché.

Después de probar ProxySQL sin almacenamiento en caché, es hora de ejecutar la misma prueba nuevamente, pero esta vez con el almacenamiento en caché habilitado.

Paso 4: Pruebas de uso mysqlslapcon almacenamiento en caché

En este paso, el almacenamiento en caché nos ayudará a reducir la latencia al ejecutar consultas similares. Aquí, identificará las consultas ejecutadas, tomará sus resúmenes de stats_mysql_query_digestla tabla de ProxySQL y los usará para habilitar el almacenamiento en caché. Luego, realizará una nueva prueba para verificar la diferencia.

Para habilitar el almacenamiento en caché, debe conocer los resúmenes de las consultas que se almacenarán en caché. Inicie sesión en la interfaz de administración de ProxySQL con este comando:

mysql -uadmin -p -h127.0.0.1 -P6032

Luego, ejecute esta consulta nuevamente para obtener una lista de consultas ejecutadas y sus resúmenes:

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

Obtendrás un resultado similar a este:

Output+------------+-------------+-----------+--------------------+------------------------------------------+| count_star | sum_time    | hostgroup | digest             | digest_text                              |+------------+-------------+-----------+--------------------+------------------------------------------+| 2000       | 33727110501 | 1         | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date? || 1          | 601         | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname            || 1          | 0           | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ?         |+------------+-------------+-----------+--------------------+------------------------------------------+3 rows in set (0.00 sec)

Observa la primera fila. Se trata de una consulta que se ejecutó 2000 veces. Esta es la consulta de referencia ejecutada anteriormente. Toma su resumen y guárdalo para usarlo al agregar una regla de consulta para el almacenamiento en caché.

Las próximas consultas agregarán una nueva regla de consulta a ProxySQL que coincidirá con el resumen de la consulta anterior y cache_ttlle asignará un valor. cache_ttles la cantidad de milisegundos que el resultado se almacenará en la memoria caché:

INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);

En este comando, está agregando un nuevo registro a la mysql_query_rulestabla; esta tabla contiene todas las reglas aplicadas antes de ejecutar una consulta. En este ejemplo, está agregando un valor para la cache_ttlcolumna que hará que la consulta coincidente con el resumen dado se almacene en caché durante una cantidad de milisegundos especificada en esta columna. Ingrese 1la columna de aplicación para asegurarse de que la regla se aplique a las consultas.

LOADy SAVEestos cambios, luego salir del mysqlshell:

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
exit;

Ahora que el almacenamiento en caché está habilitado, vuelva a ejecutar la prueba para verificar el resultado:

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date'2000-04-20'" --verbose

Esto dará un resultado similar al siguiente:

OutputBenchmark        Average number of seconds to run all queries: 7.020 seconds        Minimum number of seconds to run all queries: 0.274 seconds        Maximum number of seconds to run all queries: 23.014 seconds        Number of clients running queries: 100        Average number of queries per client: 1

Aquí puedes ver la gran diferencia en el tiempo promedio de ejecución: bajó de 18.117segundos a 7.020.

Conclusión

En este artículo, configuraste el almacenamiento en caché transparente con ProxySQL para almacenar en caché los resultados de las consultas de la base de datos. También probaste la velocidad de las consultas con y sin almacenamiento en caché para ver la diferencia que puede generar el almacenamiento en caché.

En este tutorial, ha utilizado un nivel de almacenamiento en caché. También puede probar el almacenamiento en caché web, que se ubica frente a un servidor web y almacena en caché las respuestas a solicitudes similares, enviando la respuesta de vuelta al cliente sin llegar a los servidores back-end. Esto es muy similar al almacenamiento en caché de ProxySQL, pero en un nivel diferente. Para obtener más información sobre el almacenamiento en caché web, consulte nuestro manual básico de almacenamiento en caché web: terminología, encabezados HTTP y estrategias de almacenamiento en caché.

El servidor MySQL también tiene su propio caché de consultas; puede obtener más información sobre ello en nuestro tutorial Cómo optimizar MySQL con caché de consultas en Ubuntu 18.04.