Como otimizar consultas do MySQL com o cache do ProxySQL no Ubuntu 16.04

O autor selecionou a Free Software Foundation para receber uma doação como parte do programa Write for DOnations.

Introdução

O ProxySQL é um servidor proxy com reconhecimento de SQL que pode ser posicionado entre seu aplicativo e seu banco de dados. Ele oferece muitos recursos, como o de balancear carga entre vários servidores de MySQL e servir como uma camada de cache para consultas. Este tutorial irá se concentrar no recurso de cache do ProxySQL e como ele pode otimizar as consultas para o seu banco de dados do MySQL.

O cache do MySQL ocorre quando o resultado de uma consulta é armazenado para que, quando essa consulta for repetida, o resultado possa ser retornado sem a necessidade de classificar o banco de dados. Isso pode aumentar significativamente a velocidade das consultas comuns. Em muitos métodos de cache, porém, os desenvolvedores precisam modificar o código do seu aplicativo, o que poderia introduzir um bug na base de códigos. Para evitar essa prática propensa a erros, o ProxySQL permite que você configure o cache transparente.

No cache transparente, apenas os administradores do banco de dados precisam alterar a configuração do ProxySQL para habilitar o cache para as consultas mais comuns. Tais alterações podem ser feitas através da interface de administrador do ProxySQL. Tudo o que o desenvolvedor precisa fazer é conectar-se ao proxy que reconhece o protocolo. O proxy decidirá se a consulta pode ser atendida a partir do cache sem chegar ao servidor de back-end.

Neste tutorial, você usará o ProxySQL para configurar o cache transparente de um servidor MySQL no Ubuntu 16.04. Em seguida, você testará seu desempenho usando o mysqlslap - com e sem o cache, no intuito de demonstrar o efeito do cache e quanto tempo ele pode poupar na execução de várias consultas semelhantes.

Pré-requisitos

Antes de iniciar este guia, você precisará do seguinte:

• Um servidor Ubuntu 16.04 com pelo menos 2 GB de memória RAM, configurado com um usuário não raiz com privilégios sudo e um firewall, conforme instruído em nosso guia de Configuração inicial de servidor Ubuntu 16.04.

Passo 1 — Instalando e configurando o servidor MySQL

Primeiro, você instalará o servidor MySQL e o configurará para ser usado pelo ProxySQL como um servidor de back-end para atender consultas de clientes.

No Ubuntu 16.04, o mysql-server pode ser instalado usando este comando:

sudo apt-get install mysql-server

Pressione Y para confirmar a instalação.

Em seguida, você será solicitado a digitar sua senha de usuário root do MySQL. Digite uma senha forte e salve-a para usar mais tarde.

Agora que você tem seu servidor MySQL pronto, irá configurá-lo para que o ProxySQL funcione corretamente. Você precisa adicionar um usuário monitor para o ProxySQL monitorar o servidor MySQL, uma vez que o ProxySQL escuta o servidor de back-end através do protocolo da SQL, em vez de usar uma conexão TCP ou pedidos pelo método GET do HTTP - para garantir que o back-end esteja funcionando. O monitor usará uma conexão SQL fictícia para determinar se o servidor está ativo ou não.

Primeiro, faça login no shell do MySQL:

mysql -uroot -p

O parâmetro -uroot, conecta você através do usuário root do MySQL e o -p solicita a senha do usuário root. Esse usuário root é diferente do usuário root do seu servidor e a senha é a que você digitou quando instalou o pacote mysql-server.

Digite a senha do root e pressione ENTER.

Agora, você criará dois usuários, um chamado monitor para o ProxySQL e outro que você usará para executar consultas de clientes e conceder-lhes os privilégios corretos. Este tutorial nomeará esse usuário como sammy.

Crie o usuário monitor:

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

A consulta CREATE USER é usada para criar um novo usuário que pode se conectar a partir de IPs específicos. Usar % denota que o usuário pode se conectar a partir de qualquer endereço IP. IDENTIFIED BY define a senha para o novo usuário; digite qualquer senha que quiser, mas certifique-se de lembrá-la para uso posterior.

Com o usuário monitor criado, crie o usuário sammy:

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

Em seguida, conceda privilégios aos seus novos usuários. Execute o seguinte comando para configurar o monitor:

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

A consulta GRANT é usada para dar privilégios aos usuários. Aqui, você concedeu privilégios somente de SELECT em todas as tabelas no banco de dados sys para o usuário monitor; ele precisa apenas desse privilégio para escutar o servidor de back-end.

Agora, conceda ao usuário sammy todos os privilégios em relação a todos os bancos de dados:

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

Isso permitirá que o sammy faça as consultas necessárias para testar seu banco de dados mais tarde.

Aplique as alterações de privilégios, executando o seguinte:

FLUSH PRIVILEGES;

Por fim, saia do shell do mysql:

exit;

Agora, você instalou o mysql-server e criou um usuário para ser usado pelo ProxySQL para monitorar seu servidor MySQL e outro para executar consultas de clientes. Em seguida, você instalará e configurará o ProxySQL.

Passo 2 — Instalando e configurando o servidor ProxySQL

Agora, você pode instalar o servidor ProxySQL, que será usado como uma camada de cache para as suas consultas. Uma camada de cache existe como uma parada entre os servidores do seu aplicativo e os servidores de back-end do banco de dados; ela é usada para se conectar ao banco de dados e salvar os resultados de algumas consultas em sua memória para acesso rápido mais tarde.

A página de lançamentos do ProxySQL no Github oferece arquivos de instalação para distribuições comuns do Linux. Para este tutorial, você usará o wget para baixar o arquivo de instalação do ProxySQL versão 2.0.4 do, Debian:

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

Em seguida, instale o pacote usando o dpkg:

sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

Assim que estiver instalado, inicie o ProxySQL com este comando:

sudo systemctl start proxysql

Verifique se o ProxySQL iniciou corretamente com este comando:

sudo systemctl status proxysql

Você receberá um resultado semelhante a este:

Output
root@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

Agora, é hora de conectar o seu servidor ProxySQL ao servidor MySQL. Para tanto, utilize a interface administrativa SQL do ProxySQL, a qual, por padrão, escuta a porta 6032 no localhost e tem admin como seu nome de usuário e senha.

Conecte-se à interface executando o seguinte:

mysql -uadmin -p -h 127.0.0.1 -P6032

Digite admin quando for solicitado a inserir uma senha.

-uadmin define o nome de usuário como admin e o sinalizador -h especifica o host como localhost. A porta é 6032, especificada com o sinalizador -P.

Aqui, você teve que especificar claramente o host e a porta porque, por padrão, o cliente MySQL se conecta usando um arquivo de socket local e a porta 3306.

Agora que você se conectou ao shell mysql como admin, configure o usuário monitor para que o ProxySQL possa usá-lo. Primeiro, use consultas SQL padrão para definir os valores de duas variáveis globais:

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';

A variável mysql-monitor_username especifica o nome de usuário do MySQL que será usado para verificar se o servidor de back-end está ativo ou não. A variável mysql-monitor_password aponta para a senha que será usada ao se conectar ao servidor de back-end. Use a senha que criou para o nome de usuário monitor.

Toda vez que fizer uma alteração na interface administrativa do ProxySQL, precisará usar o comando LOAD (carregar) correto para aplicar as alterações na instância do ProxySQL em execução. Você alterou variáveis globais do MySQL,assim, carregue-as no RUNTIME para aplicar as alterações:

LOAD MYSQL VARIABLES TO RUNTIME;

Em seguida, SAVE (salve) as alterações no banco de dados em disco para manter as alterações entre as reinicializações. O ProxySQL usa seu próprio banco de dados do SQLite local para armazenar suas próprias tabelas e variáveis:

SAVE MYSQL VARIABLES TO DISK;

Agora, você dirá ao ProxySQL sobre o servidor de back-end. A tabela mysql_servers detém as informações sobre cada servidor de back-end ao qual o ProxySQL pode conectar-se e onde pode executar consultas. Assim, adicione um novo registro usando uma instrução SQL padrão INSERT, com os seguintes valores para hostgroup_id, hostname e port:

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

Para aplicar as alterações, execute LOAD e SAVE novamente:

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

Por fim, você dirá ao ProxySQL qual usuário se conectará ao servidor de back-end; defina o sammy como o usuário e substitua sammy_password pela senha que criou anteriormente:

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

A tabela mysql_users contém informações sobre os usuários usados para se conectar aos servidores de back-end; você especificou o username (nome de usuário), password (senha) e default_hostgroup (grupo de host padrão).

LOAD e SAVE as alterações:

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

Então, saia do shell do mysql:

exit;

Para testar se você consegue se conectar ao seu servidor de back-end usando o ProxySQL, execute a seguinte consulta teste:

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

Nesse comando, você usou o sinalizador -e para executar uma consulta e fechar a conexão. A consulta imprime o nome do host do servidor de back-end.

O resultado ficará parecido com este, sendo o your_hostname substituído pelo seu nome de host:

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

Para aprender mais sobre a configuração do ProxySQL, consulte o Passo 3 sobre Como usar o ProxySQL como um balanceador de carga para o MySQL no Ubuntu 16.04.

Até aqui, você configurou o ProxySQL para usar seu servidor MySQL como um back-end e se conectou ao back-end usando o ProxySQL. Agora,você está pronto para usar o mysqlslap para comparar o desempenho das consultas sem cache.

Passo 3 — Testando o uso do mysqlslap sem o cache

Neste passo, você fará download de um banco de dados de teste para que possa executar consultas nele com o mysqlslap, no intuito de testar a latência sem o armazenamento em cache, definindo um parâmetro de comparação para a velocidade das suas consultas. Você também irá explorar como o ProxySQL mantém os registros das consultas na tabela stats_mysql_query_digest.

O mysqlslap é um cliente de emulação de carga que é usado como uma ferramenta de teste de carga para o MySQL. Ele pode testar um servidor MySQL com consultas geradas automaticamente ou com algumas consultas personalizadas, executadas em um banco de dados. Ele vem instalado no pacote do cliente MySQL, de modo que não é necessário instalá-lo; em vez disso, você irá baixar um banco de dados apenas para fins de teste, no qual você poderá usar o mysqlslap.

Neste tutorial, você usará uma amostra de banco de dados de funcionários. Você vai usar essa amostra de banco de dados de funcionários porque ela apresenta um conjunto grande de dados que pode ilustrar as diferenças na otimização das consultas. O banco de dados tem seis tabelas, mas os dados que ele contém têm mais de 300.000 registros de funcionários. Isso ajudará você a emular uma carga de trabalho de produção em grande escala.

Para baixar o banco de dados, clone primeiro o repositório do Github usando este comando:

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

Em seguida, acesse o diretório test_db e carregue o banco de dados no servidor MySQL usando estes comandos:

cd test_db
mysql -uroot -p < employees.sql

Esse comando usa o redirecionamento da shell para ler as consultas em SQL no arquivo employees.sql e as executa no servidor MySQL para criar a estrutura do banco de dados.

Você verá um resultado como este:

Output
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:32

Assim que o banco de dados for carregado no seu servidor MySQL, teste se o mysqlslap está funcionando com a seguinte consulta:

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

O mysqlslap tem sinalizadores semelhantes aos do cliente mysql; aqui estão os usados neste comando:

• -u - especifica o usuário usado para se conectar ao servidor.
• -p - solicita a senha do usuário.
• -P - conecta-se usando a porta especificada.
• -h - conecta-se ao host especificado.
• --auto-generate-sql - permite que o MySQL faça testes de carga, usando suas próprias consultas geradas.
• --verbose - faz o resultado mostrar mais informações.

Você irá obter um resultado similar ao seguinte:

Output
Benchmark
    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

Nesse resultado, você pode ver o número médio, mínimo e máximo de segundos gastos para executar todas as consultas. Isso lhe dará uma ideia sobre o tempo necessário para executar as consultas feitas por um certo número de clientes. Nesse resultado, apenas um cliente foi usado para executar consultas.

Em seguida, descubra quais consultas o mysqlslap executou no último comando, examinando o stats_mysql_query_digest do ProxySQL. Isso nos dará informações como o resumo das consultas, que é uma forma normalizada da instrução em SQL que poderá ser referenciada mais tarde para habilitar o armazenamento em cache.

Acesse a interface de administração do ProxySQL com este comando:

mysql -uadmin -p -h 127.0.0.1 -P6032

Depois, execute esta consulta para encontrar informações na tabela stats_mysql_query_digest:

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

Você verá um resultado similar ao seguinte:

+------------+----------+-----------+--------------------+----------------------------------+
| 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)

A consulta anterior seleciona os dados da tabela stats_mysql_query_digest, a qual contém informações sobre todas as consultas executadas no ProxySQL. Aqui, você tem cinco colunas selecionadas:

• count_star: o número de vezes que essa consulta foi executada.
• sum_time: tempo total em milissegundos que essa consulta levou para executar.
• hostgroup: o grupo de hosts usado para executar a consulta.
• digest: um resumo da consulta executada.
• digest_text: a consulta em si. No exemplo deste tutorial, a segunda consulta é parametrizada usando sinais de ? no lugar de parâmetros de variável. select @@version_comment limit 1 e select @@version_comment limit 2 são, portanto, agrupados juntos como a mesma consulta e com o mesmo resumo.

Agora que você sabe como verificar os dados de consulta na tabela stats_mysql_query_digest, saia do shell do mysql:

exit;

O banco de dados que baixou contém algumas tabelas com dados de demonstração. Agora, você testará consultas na tabela dept_emp, selecionando quaisquer registros cujo from_date formaior que 2000-04-20 e registrando o tempo médio de execução.

Use este comando para executar o teste:

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

Aqui, você está usando alguns sinalizadores novos:

• --concurrency=100: define o número de usuários a simular, neste caso 100.
• --iterations=20: faz com que o teste seja executado 20 vezes e calcula os resultados de todos elas.
• --create-schema=employees: aqui você selecionou o banco de dados employees (funcionários).
• --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'": aqui você especificou a consulta executada no teste.

O teste levará alguns minutos. Após terminar, você receberá resultados semelhantes ao seguinte:

Output
Benchmark
        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

Seus números podem ser um pouco diferentes. Mantenha esses números em algum lugar para compará-los com os resultados obtidos após habilitar o armazenamento em cache.

Após testar o ProxySQL sem armazenar em cache, é hora de executar o mesmo teste novamente; mas, desta vez, com o armazenamento em cache habilitado.

Passo 4 — Testando o uso do mysqlslap com armazenamento em cache

Neste passo, o armazenamento em cache nos ajudará a diminuir a latência ao executar consultas semelhantes. Aqui, você identificará as consultas executadas, pegará seus resumos da tabela stats_mysql_query_digest do ProxySQL e os usará para habilitar o armazenamento em cache. Em seguida, você testará novamente para verificar a diferença.

Para habilitar o armazenamento em cache, você precisa conhecer os resumos das consultas que serão armazenadas em cache. Faça login na interface de administração do ProxySQL, usando este comando:

mysql -uadmin -p -h127.0.0.1 -P6032

Depois, execute esta consulta novamente para obter uma lista das consultas executadas e seus resumos:

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

Você receberá um resultado semelhante 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)

Examine a primeira linha. Trata-se de uma consulta que foi executada 2000 vezes. Essa é a consulta executada anteriormente como parâmetro de comparação. Pegue seu resumo e guarde-o para ser usado na adição de uma regra de consulta para o armazenamento em cache.

As próximas consultas vão adicionar uma nova regra de consulta ao ProxySQL que fará a correspondência entre o resumo da consulta anterior e colocará um valor cache_ttl para ela. O cache_ttl é o número de milissegundos em que o resultado ficará armazenado em memória cache:

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

Nesse comando, você está adicionando um novo registro à tabela mysql_query_rules; essa tabela contém todas as regras aplicadas antes de executar uma consulta. Nesse exemplo, você está adicionando um valor à coluna cache_ttl, que fará com que a consulta - que foi combinada por determinado resumo - seja armazenada em cache pelo tempo (em milissegundos) especificado nessa coluna. Coloque 1 na coluna de aplicação para garantir que a regra seja aplicada às consultas.

LOAD e SAVE essas alterações e, em seguida, saia do shell mysql:

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

Agora que o armazenamento em cache está habilitado, execute novamente o teste para verificar o 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

Isso dará um resultado similar ao seguinte:

Output
Benchmark
        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

Aqui, você consegue ver a grande diferença em tempo médio de execução: o tempo baixou de 18.117 segundos para 7.020.

Conclusão

Neste artigo, você configurou o armazenamento em cache transparente com o ProxySQL para armazenar em cache os resultados das consultas no banco de dados. Também testou a velocidade de consulta com e sem o armazenamento em cache para ver a diferença que o armazenamento em cache pode fazer.

Neste tutorial, você usou um nível de armazenamento em cache. Você também poderia tentar fazer o armazenamento em cache baseado na Web, o qual fica na frente de um servidor Web e armazena em cache as respostas a pedidos semelhantes, enviando a resposta de volta para o cliente, sem chegar aos servidores de back-end. É bem parecido com o armazenamento em cache do ProxySQL, porém em um outro nível. Para aprender mais sobre o armazenamento em cache baseado na Web, acesse o artigo Noções básicas de armazenamento em cache baseado na Web: terminologia, cabeçalhos de HTTP e primer de estratégias de armazenamento em cache.

O servidor MySQL também tem seu próprio cache de consulta; você pode aprender mais sobre isso em nosso tutorial sobre Como otimizar o MySQL com o cache de consulta no Ubuntu 18.04.