26 novembro 2017

Autenticação externa via PAM no MySQL Enterprise e Oracle Linux 7.4

O MySQL Enterprise Edition suporta um método de autenticação que permite que o MySQL Server use o PAM (Pluggable Authentication Modules) para autenticar usuários do MySQL.
O PAM pode ser visto como um subsistema do Linux e macOS que provê uma interface padronizada para autenticação. Ou seja, os aplicativos podem usar a interface do PAM para confirmar usuários e senhas. Pode-se configurar o PAM para vários métodos de autenticação, incluindo usuários e senhas armazenados localmente no próprio SO ou buscá-los remotamente em um diretório LDAP.
Com o plugin authentication_pam habilitado, o MySQL server vai delegar a autenticação dos usuários para o PAM. Você pode, por exemplo, usar os usuários/senhas do Sistema Operacional para fazer login no MySQL. Outro exemplo, se o SO e o PAM estiverem integrados a um serviço de identidade e autenticação, tais como Open LDAP ou Active Directory, o MySQL pode usar os usuários/senhas a partir destes serviços.

O plugin authentication_pam também tem suporte a usuários 'proxy'. Esta funcionalidade permite que o DBA mapeie as permissões de um usuário MySQL para um grupo definido externamente. Por exemplo, supondo que exista no diretório LDAP um grupo 'developers' contendo vários usuários, o DBA pode criar um único usuário 'developer' no MySQL com as devidas permissões e mapeá-lo com o grupo 'developers' do diretório LDAP (1 para n). Desta forma, o time de segurança que mantém o diretório LDAP pode adicionar, remover, alterar senhas dos desenvolvedores que vão acessar o MySQL sem que o DBA precise tocar no banco de dados.
Nota: se você quiser configurar o MySQL para conectar-se diretamente a um serviço de identidade e autenticação LDAP sem configurar o PAM e o Sistema Operacional, pode usar o plugin authentication_ldap, também disponível no MySQL Enterprise Edition. O MySQL Enterprise é um software comercial e sua licença para uso em produção deve ser adquirida junto à Oracle. Porém, você pode utilizá-lo em ambiente não-produtivo por 30 dias.
Abaixo veremos como configurar o plugin authentication_pam no Oracle Linux 7.4. Os passos são bastante similares para outras distribuições Linux e macOS. Consulte a documentação para mais detalhes.

Passo 1: habilitar o plugin authentication_pam no MySQL

Acidione ao arquivo my.cnf:
[mysqld]
plugin-load-add=authentication_pam.so
Reinicie o MySQL:
systemctl restart mysqld
Confirme se o plugin está ativo:
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';
+--------------------+---------------+
| PLUGIN_NAME        | PLUGIN_STATUS |
+--------------------+---------------+
| authentication_pam | ACTIVE        |
+--------------------+---------------+
1 row in set (0.00 sec)

Passo 2: criar um módulo mysql no PAM

Crie um novo serviço mysql no PAM. Basta criar um arquivo com nome mysql no diretório de configuração do PAM (normalmente /etc/pam.d):
vi /etc/pam.d/mysql
#
# PAM-1.0 configuration file for the 'mysql' service
#
auth       include   password-auth
account    include   password-auth
O que fizemos ao usar o include foi repassar autenticação para outro serviço password-auth já definido no PAM. Se quiser ver em detalhes como está configurado, basta visualizar o conteúdo do arquivo /etc/pam.d/password-auth.
cat /etc/pam.d/password-auth
#%PAM-1.0
# This file is auto-generated.
# User changes will be destroyed the next time authconfig is run.
auth        required      pam_env.so
auth        required      pam_faildelay.so delay=2000000
auth        sufficient    pam_unix.so nullok try_first_pass
auth        requisite     pam_succeed_if.so uid >= 1000 quiet_success
auth        required      pam_deny.so
account     required      pam_unix.so
account     sufficient    pam_localuser.so
account     sufficient    pam_succeed_if.so uid < 1000 quiet
account     required      pam_permit.so
password    requisite     pam_pwquality.so try_first_pass local_users_only retry=3 authtok_type=
password    sufficient    pam_unix.so sha512 shadow nullok try_first_pass use_authtok
password    required      pam_deny.so
session     optional      pam_keyinit.so revoke
session     required      pam_limits.so
-session     optional      pam_systemd.so
session     [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session     required      pam_unix.so
Note que este arquivo foi gerado automaticamente pela ferramenta authconfig. Uma maneira usual de integrar o Sistema Operacional a diferentes serviços de identidade e autenticação (LDAP, Activer Directory, etc.) é usar o System Security Services Daemon (SSSD).

Passo 3: garantir permissões do processo mysqld ao arquivo de usuários do SO

Em alguns Sistemas Operacionais, tal como o Oracle Linux 7.4, é usado o arquivo /etc/shadow para manter informações de acesso os usuários. O processo mysqld vai precisar de permissões de acesso a este arquivo para funcionar corretamente com o PAM.
Uma maneira de dar tais permissões é:
  1. criar um grupo shadow;
  2. dar permissões ao grupo para acessar /etc/shadow;
  3. adicionar o usuário que vai rodar o processo mysqld (por padrão, o usuário mysql);
  4. reiniciar o processo mysqld.
Execute os seguintes comandos:
groupadd shadow
chgrp shadow /etc/shadow
chmod g+r /etc/shadow
gpasswd -a mysql shadow
systemctl restart mysqld
Neste ponto, a autenticação externa via PAM já deve estar funcionando.

Passo 4: mapear usuários do MySQL para autenticação externa

Opcão 1: mapeamento 1 para 1

Você pode mapear um usuário MySQL para um usuário externo. O usuário do MySQL vai definir os privilégios enquanto que a senha para login estará definida fora do MySQL.
Teste 1 para 1 Para testar a autenticação externa, criaremos um usuário myuser no Sistema Operacional e o mapearemos para um usuário no MySQL. O usuário do SO vai definir a senha enquanto que o usuário no MySQL vai definir quais dados terá acesso.
  1. Crie o usuário myuser no Sistema Operacional e dê uma senha:
adduser myuser
passwd myuser
  1. (opcional) Verifique se foi criado com sucesso com:
# su - myuser
$ id
uid=1001(myuser) gid=1001(myuser) groups=1001(myuser) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
$ exit
logout
#
  1. No MySQL, crie um usuário mapeando as permissões para o usuário myuser:
CREATE USER 'myuser'@'localhost' IDENTIFIED WITH authentication_pam AS 'mysql';
GRANT SELECT ON *.* TO 'myuser'@'localhost';
Neste exemplo demos apenas permissões de SELECT ao usuário myuser.
  1. Teste o acesso ao MySQL usando a senha que você definiu para o usuário myuser no SO:
# mysql -umyuser -p --enable-cleartext-plugin
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
  1. Você pode verificar as informações do usuário autenticado com:
SELECT USER(), CURRENT_USER(), @@proxy_user;
+------------------+------------------+--------------+
| USER()           | CURRENT_USER()   | @@proxy_user |
+------------------+------------------+--------------+
| myuser@localhost | myuser@localhost | NULL         |
+------------------+------------------+--------------+
1 row in set (0.00 sec)
  1. Ao final dos testes, remova os usuários do MySQL e do SO:
DROP USER 'myuser'@'localhost';
userdel -r myuser

Opção 2: mapeamento 1 para n (grupos de usuários)

Você pode mapear um usuário MySQL para um grupo de usuários. O usuário do MySQL vai definir os privilégios e estará ligado a um grupo de usuários. Desta forma, os dados de quem serão os usuários pertencentes ao grupo e suas respectivas senhas estarão definidas e mantidos fora do MySQL.
Teste 1 para n Para testar a autenticação externa, criaremos no Sistema Operacional um grupo myusers contendo os usuários myuser1 e myuser2. Depois mapearemos o grupo myusers para um usuário MySQL priv_myusers.
  1. No Sistema Operacional, crie o grupo myusers com os usuários myuser1 e myuser2:
groupadd myusers
adduser myuser1
passwd myuser1
gpasswd -a myuser1 myusers
adduser myuser2
passwd myuser2
gpasswd -a myuser2 myusers
  1. No MySQL, garanta que não existe um usuário anônimo:
SELECT user, host FROM mysql.user WHERE user='';
Empty set (0.00 sec)
  1. No MySQL, crie um usuário proxy padrão ''@'' que irá mapear os usuários externos (PAM) para usuários internos (MySQL):
CREATE USER ''@'' IDENTIFIED WITH authentication_pam AS 'mysql, myusers=priv_myusers';
  1. No MySQL, crie um usuário priv_myusers, mapeando as permissões para o grupo myusers:
CREATE USER 'priv_myusers'@'localhost' IDENTIFIED BY 'SecretPass1!';
GRANT PROXY ON 'priv_myusers'@'localhost' TO ''@'';
GRANT SELECT ON *.* TO 'priv_myusers'@'localhost';
Neste exemplo demos apenas permissões de SELECT no MySQL.
  1. Teste o acesso ao MySQL usando a senha que você definiu para o usuário myuser1 no SO:
# mysql -umyuser1 -p --enable-cleartext-plugin
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
  1. Você pode verificar as informações do usuário autenticado com:
SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+------------------------+--------------+
| USER()            | CURRENT_USER()         | @@proxy_user |
+-------------------+------------------------+--------------+
| myuser1@localhost | priv_myusers@localhost | ''@''        |
+-------------------+------------------------+--------------+
1 row in set (0.00 sec)

  1. Teste também o acesso ao MySQL com o usuário myuser2:
# mysql -umyuser2 -p --enable-cleartext-plugin
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+------------------------+--------------+
| USER()            | CURRENT_USER()         | @@proxy_user |
+-------------------+------------------------+--------------+
| myuser2@localhost | priv_myusers@localhost | ''@''        |
+-------------------+------------------------+--------------+
1 row in set (0.01 sec)
  1. Ao final dos testes, remova os usuários do MySQL e do SO:
DROP USER 'priv_myusers'@'localhost';
DROP USER ''@'';
userdel -r myuser1
userdel -r myuser2

Conclusão

O recurso de Autenticação Externa do MySQL Enterprise Edition permite gerenciar usuários e senhas fora do MySQL. É um recurso que pode facilitar a implantação de políticas de segurança, principalmente as que exigem gerenciamento de identidades por administradores que não sejam os DBAs.

Referências

  1. Manual de referência do MySQL 5.7 - Authentication Plugins

23 novembro 2017

Livro: Pro MySQL NDB Cluster



Adquiri recentemente o excelente livro Pro MySQL NDB Cluster na versão eBook. Faltava um guia definitivo, agora o temos.

O livro Pro MySQL NDB Cluster é escrito por dois engenheiros de suporte do time Oracle MySQL, Jesper W. Krogh e Mikiya Okuno. É um mergulho profundo de quase 700 páginas no produto, publicado pela Apress (em Inglês).

É importante enfatizar que há várias formas de trabalhar com o MySQL em Cluster. Há a Replicação nativa do MySQL, há também o MySQL InnoDB Cluster e o MySQL (NDB) Cluster. O livro é dedicado inteiramente a este último.

De forma sucinta, o MySQL NDB Cluster é uma solução que vai atender muito bem alguns requisitos, como: disponibilidade de 99,999%, workloads transacionais (OLTP), replicação geográfica ativo-ativo, tempos de respostas consistentes da ordem de milésimos de segundo, escalabilidade praticamente linear (inclusive de escritas) e capacidade de atender dezenas de milhares de transações por segundo. Contudo, o MySQL Cluster não vai ser uma boa escolha para executar consultas analíticas (OLAP) e, por se tratar de um sistema distribuído, vai adicionar maior complexidade na implantação e administração. Para entender os motivos, é necessário conhecer um pouco da sua origem e arquitetura, tópicos muito bem cobertos no livro.

O livro é dividido em cinco partes e 20 capítulos.

Parte I - O básico

A primeira parte fornece algumas informações básicas sobre os vários componentes do MySQL NDB Cluster e como funcionam. Os capítulos são:

Capítulo 1: Arquitetura e conceitos básicos
Capítulo 2: Data Nodes

Parte II - Instalação e Configuração

A segunda parte foca os tópicos relacionados à instalação e configuração, incluindo a replicação entre clusters. São os seguintes capítulos:

Capítulo 3: Planejamento
Capítulo 4: Configuração
Capítulo 5: Instalação
Capítulo 6: Replicação

Parte III - Tarefas diárias e manutenção

Na terceira parte, os tópicos incluem tarefas que fazem parte da rotina diária do DBA. Os capítulos são:

Capítulo 7: NDB Management e outros utilitários do NDB
Capítulo 8: Backups e Restores
Capítulo 9: Manutenção de tabelas
Capítulo 10: Reinicializações
Capítulo 11: Upgrades e Downgrades
Capítulo 12: Segurança
Capítulo 13: MySQL Cluster Manager

Capítulo IV - Monitoramento e Troubleshoting

A quarta parte continua com dois tópicos que também fazem parte da rotina diária do DBA: monitoramento e investigação e solução de problemas. Os capítulos são:

Capítulo 14: Soluções de Monitoramento e o Sistema Operacional
Capítulo 15: Dados de monitoramento
Capítulo 16: Monitorando o MySQL NDB Cluster
Capítulo 17: Problemas típicos e soluções

Capítulo V - Performance Tuning

A parte final abrange os tópicos relacionados ao ajuste de desempenho do cluster. Os capítulos são:

Capítulo 18: Desenvolvimento de aplicações para MySQL NDB Cluster com SQL
Capítulo 19: MySQL NDB Cluster como um banco de dados NoSQL
Capítulo 20: Tuning da aplicação para MySQL NDB Cluster

Para quem usa o MySQL NDB Cluster, pretende usar ou mesmo se interessa por Sistemas Distribuídos de alta performance é leitura obrigatória. Recomendo!

08 junho 2017

Erro ‘Got an error reading communication packets’ no MySQL

Se uma aplicação cliente se conecta com êxito no MySQL, mas depois desconecta indevidamente ou a conexão é encerrada de maneira inesperada você receberá uma mensagem de erro ‘Got an error reading communication packets’. Para solucionar o problema, primeiro tentamos identificar a causa. Porém, uma dificuldade aqui é que se trata de um erro de comunicação entre cliente e servidor e são várias as possíveis causas.

Sintomas


O MySQL Server vai registrar uma mensagem de conexão abortada no log de erros, algo como ‘[Warning] Aborted connection 9999 to db: 'xxxxxx' user: 'yyyyyy' host:'zzzzzz' (Got an error reading communication packets)’. No MySQL 5.6 você deve configurar log_warnings para 2 ou mais para que essas mensagens sejam registradas. No MySQL 5.7 não é necessário alterar a configuração padrão (log_error_verbosity) para ter estes detalhes. Do lado da aplicação, também é comum aparecer erros ‘Lost connection to MySQL server’.

Outra maneira de verificar o problema é monitorar o valor da variável de status do MySQL Aborted_clients. Ela incrementa toda vez que um erro deste tipo ocorre. Outra variável de status relacionada a problemas de comunicação é Aborted_connects, mas ela será incrementada somente no caso da aplicação cliente falhar ao estabelecer a conexão inicial com o MySQL Server. Uma forma de acompanhar os valores destas variáveis é com o comando SHOW GLOBAL STATUS LIKE 'Aborted%';.

Se você estiver usando o MySQL Enterprise Monitor também receberá alertas relacionados a este problema e poderá acompanhar as conexões abortadas no respectivo gráfico.

Causas


Algumas das principais causas do erro são:

  • A aplicação cliente tem algum bug e não fecha adequadamente as conexões ou não encerra transações corretamente com COMMIT.
  • A aplicação está configurada com algum limite máximo do tempo de conexão e o excede. Por exemplo, no caso do PHP há a opção max_execution_time. Outras linguagens e ambientes têm opções similares.
  • A aplicação cliente foi encerrada abruptamente (crash) no meio de uma transferência de dados.
  • O MySQL está com o valor para a configuração max_allowed_packet muito pequeno. Neste caso, a aplicação cliente também deve receber um erro do tipo ‘packet too large’.
  • O MySQL está configurado com limites muito baixos para wait_timeout ou interactive_timeout (em segundos). Neste caso, a conexão que permanecer no estado SLEEP por um tempo superior ao configurado será interrompida. Você poderá verificar o tempo e estado das conexões com o comando SHOW PROCESSLIST;.
  • A rede é ruim e o MySQL está configurado com limites muito baixos para net_write_timeout e net_read_timeout. Neste caso, se o cliente interromper o recebimento ou envio de resultados de queries acima destes limites, o MySQL Server pode pensar que o cliente está morto e fechar a conexão.
  • Algumas de suas queries podem precisar de mais memória do que a configurada no MySQL Server e/ou não puderam ser concluídas em tempo adequado. Tente identificar se o problema ocorre para queries específicas.
  • Firewalls, proxies, TCP/IP mal configurado,MTU auto discovery sem funcionar, DNS, hubs, switches, cabos defeituosos e outros problemas de rede/ethernet.
  • No Linux, alguns drivers apresentam problemas quando as interfaces de rede estão configuradas em Half Duplex em uma ponto e Full Duplex em outra.
  • Algum problema com a biblioteca de threads que causa interrupções nas leituras.

Solução


Se você conseguir identificar e isolar a causa, a solução será trivial. Porém, nem sempre será fácil. Você pode recorrer aos seguintes procedimentos:

  • Revise as seguintes configurações do MySQL: skip_name_resolve, max_allowed_packet, wait_timeout, interactive_timeout, net_write_timeout e net_read_timeout. Monitore o log de erros e as variáveis de status Aborted% para certificar-se de que o problema diminuiu ou desapareceu. Modifique uma configuração de cada vez e gradualmente.
  • Esse erro usualmente está relacionado às condições da sua rede. Comece certificando-se que não há erros reportados pelas interfaces de rede (ifconfig -a) e depois com ferramentas como tcpdump e netstat. Gere um pacote maior que 1GB com ping enquanto procura por anomalias com tcpdump e netstat.
  • Relacione as mensagens de erro tanto do lado da aplicação cliente quanto do lado do MySQL Server. Prefira usar os Id’s das conexões com o MySQL para fazer esta correlação ao invés do timestamp dos logs. Se puder, faça com que sua aplicação coloque esta informação (CONNECTION_ID()) no log de debug. No MySQL Server veja os Id’s das conexões problemáticas no log de erros. Se estiver trabalhando com versão mais antiga que 5.7, configure log_warnings para maior ou igual a 2.
  • Se for necessário, colete e analise os detalhes das conexões problemáticas. Se você estiver usando o MySQL Enterprise Edition, habilite o Audit Log. Se estiver usando o MySQL Community, use o General Log, mas esteja ciente de que isto pode gerar impacto indesejado na performance, portanto use com cuidado. Com a lista dos Id’s de conexões problemáticas verifique se queries específicas estão relacionadas ao problema. Se for possível isolar tais queries será mais fácil reproduzir e corrigir o problema.
  • Se você tiver acesso ao Suporte da Oracle para MySQL, poderá abrir um chamado e solicitar ajuda para identificar a causa-raíz.


Referências:
https://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-communication-packet-errors

25 maio 2017

MySQL Performance Tuning: sempre configure o InnoDB Buffer Pool

Se você for um usuário iniciante de MySQL, provavelmente vai esbarrar com a necessidade de fazer algum tuning no servidor para melhorar a performance e vai ficar na dúvida de onde iniciar. Alguns ajustes básicos podem ser antecipados, já no momento da instalação do servidor, sendo o principal innodb_buffer_pool_size.
O InnoDB Buffer Pool é a principal área da memória usada pelo InnoDB, que é o storage engine default do MySQL. O ideal é que haja neste buffer espaço suficiente para os dados "mais quentes". A quantidade de memória que o MySQL vai reservar para o Buffer Pool é controlada pela variável de sistema innodb_buffer_pool_size.

Após a instalação do MySQL, o valor padrão da variável de sistema innodb_buffer_pool_size é de 128 MB (versão 5.7), o que é bem baixo para os hardwares atuais. Você pode verificar qual atual configurado no seu MySQL com o comando:
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
Uma boa prática é, logo após a instalação, editar seu arquivo my.cnf (ou my.ini no Windows) e atribuir um valor adequado para a variável de configuração innodb_buffer_pool_size. Muitas pessoas vão recomendar como valor “70% da memória RAM disponível no seu servidor”. Isto é um bom começo caso o servidor seja dedicado. A melhor prática é monitorar o uso do Buffer Pool e ajustar seu tamanho conforme a carga de trabalho específica que você submete ao MySQL. Uma boa ferramenta para fazer isso é o MySQL Enterprise Monitor.
Ajudei recentemente um cliente que tinha um servidor compartilhado entre banco de dados e aplicação. O cliente chegou a configurar innodb_buffer_pool_size, porém o manteve baixo demais. O resultado é que a performance não estava agradando. Foi fácil identificar olhando para o gráfico “InnoDB Buffer Pool” do MySQL Enterprise Monitor:
Veja que a quantidade de memória usada pelo buffer (linha vermelha) era maior que a quantidade de memória alocada (linha verde). Como analogia, pense numa máquina fazendo Swap. Depois que fiz o ajuste para um valor maior que ‘used’, as linhas se inverteram e o banco de dados começou muito mais queries por segundo:
Como o cliente usava o MySQL 5.7, fiz a alteração online (sem reiniciar o servidor) com os comandos:
mysql> SELECT ROUND(1.8*1024*1024*1024);
+---------------------------+
| ROUND(1.8*1024*1024*1024) |
+---------------------------+
|                1932735283 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL innodb_buffer_pool_size=1932735283;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 2013265920 |
+-------------------------+------------+
1 row in set (0.00 sec)
Depois de verificado que a configuração teve o efeito desejado, editei o arquivo my.cnf com o valor ideal.
Interessante notar que houve um pico maior de Queries Por Segundo logo após o ajuste (mais de 300 QPS). O valor ‘used’ do Buffer Pool também chegou em certo momento a superar novamente o valor ‘free’ (em torno de 1.2GB). Contudo, depois de um tempo o sistema estabilizou. A explicação é que havia um contenção no sistema e depois que eliminamos o gargalo o sistema levou um tempo para estabilizar, porém, uma vez estabilizado (em torno de 200 QPS), manteve uma boa quantidade de Buffer Pool livre.
Portanto, algumas lições:
  1. Sempre que instalar o MySQL, ajuste o valor de innodb_buffer_pool_size;
  2. Monitore o comportamento do Buffer Pool e faça o tuning de acordo com sua carga de trabalho;
  3. Logo depois do tuning, espere o sistema estabilizar;
  4. Mantenha uma folga no buffer pool, permitindo que o sistema estabilize-se de eventuais momentos de pico de demanda;
  5. Não se esqueça de salvar a configuração no my.cnf ou my.ini.

30 setembro 2016

Migrando Legados para MySQL 5.7 com Query Rewrite Plugin - Parte 2

Moodle 2.3 e MySQL 5.7
No post anterior, vimos o que são os Query Rewrite Plugins, tanto preparse, quanto postparse. Agora é o momento de ver como este recurso pode viabilizar a migração de uma aplicação legada real. Para exemplificar, usaremos uma versão antiga do Moodle que é incompatível com MySQL 5.7. Veremos como implementar um preparse QR plugin.

O legado: Moodle 2.3

Moodle é uma plataforma de aprendizado extremamente popular criada com o LAMP Stack. A versão 2.3 já é bastante antiga e não recebe mais atualizações desde janeiro de 2014.
Ao tentar instalar o Moodle 2.3 com MySQL 5.7, ocorre o erro:
PHP Warning:  mysqli::mysqli(): Headers and client library minor version mismatch. Headers:50550 Library:50631 in /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php on line 377

Installation
System

Error reading from database

More information about this error

It is usually not possible to recover from errors triggered during installation, you may need to create a new database or use a different database prefix if you want to retry the installation.

Debug info: Unknown system variable 'storage_engine'
SELECT @@storage_engine
[NULL]
Error code: dmlreadexception
Stack trace:
line 407 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 184 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 121 of /lib/ddl/mysql_sql_generator.php: call to mysqli_native_moodle_database->get_dbengine()
line 248 of /lib/ddl/sql_generator.php: call to mysql_sql_generator->getCreateTableSQL()
line 401 of /lib/ddl/database_manager.php: call to sql_generator->getCreateStructureSQL()
line 356 of /lib/ddl/database_manager.php: call to database_manager->install_from_xmldb_structure()
line 1422 of /lib/upgradelib.php: call to database_manager->install_from_xmldb_file()
line 184 of /admin/index.php: call to install_core()
Na base de bugs do Moodle é possível encontrar um bug com a análise do motivo do problema (MDL-50633). Resumidamente o problema ocorre porque o Moodle tenta ler qual o Storage Engine padrão usando a variável de sistema do MySQL @@storage_engine. Esta variável foi renomeada no MySql 5.7 ou superior para @@default_storage_engine.
Uma saída seria atualizar o Moodle, mas isto nem sempre é possível em alguns cenários. Outra seria corrigir esta consulta específica na aplicação. No maravilhoso mundo open source quase sempre podemos contar com esta opção, porém não é o caso de outros softwares de código fechado. Vamos supor que não seja possível mexer na aplicação.
Resta a opção de interceptar os comandos enviados pela aplicação e modificá-los antes que o banco de dados execute-os. Há algumas formas de fazer isso, como por exemplo o MySQL Proxy, porém na maioria dos casos é indesejável adicionar mais um componente na infra-estrutura, principalmente algo que seja um ponto único de falha entre aplicação e banco de dados. E se fosse possível fazer a modificação de comandos online, diretamente no servidor de banco de dados? É exatamente isto que faz um Query Rewrite Plugin!

Tentativa 1: usar um preparse Query Rewrite Plugin

O caminho mais fácil seria usar o plugin Rewriter, apresentado no post anterior, para interceptar a query SELECT @@storage_engine e reescrevê-la para SELECT @@default_storage_engine.
O passo-a-passo é o seguinte:
Passo 1: Instalar o Rewriter plugin:
cd /usr/share/mysql
mysql -uroot -p < install_rewriter.sql
Passo 2: Criar a regra de reescrita:
mysql> INSERT INTO query_rewrite.rewrite_rules ( pattern, pattern_database, replacement ) VALUES ('SELECT @@storage_engine', 'moodle', 'SELECT @@default_storage_engine');
Query OK, 1 row affected (0.02 sec)
Passo 3: Carregar a regra na memória:
mysql> CALL query_rewrite.flush_rewrite_rules();
ERROR 1644 (45000): Loading of some rule(s) failed.
Houve algo errado! Vamos fazer um debug verificando o que o plugin retornou na coluna message da tabela query_rewrite.rewrite_rules:
mysql> SELECT * FROM query_rewrite.rewrite_rules \G

           pattern: SELECT @@storage_engine
  pattern_database: moodle
       replacement: SELECT @@default_storage_engine
           enabled: YES
           message: Parse error in pattern: >>Unknown system variable 'storage_e
ngine'<<
É possível concluir que as variáveis de ambiente são verificadas pelo Banco de Dados antes da etapa de parsing, pois o Rewriter é um postparse QR plugin. Desta forma, teremos que usar um preparse QR plugin.

Tentativa 2: criar um postparse Query Rewrite Plugin

Podemos criar postparse QR plugins customizados, que funcionarão de forma parecida com o rewrite_example mostrado no post anterior.
Para nossa necessidade específica, precisamos apenas de um novo plugin que intercepte a query SELECT @@storage_engine e a reescreva para SELECT @@default_storage_engine antes que ela chegue ao parser.
O rewrite_example é justamente um exemplo de implementação de preparse QR plugin. O seu código-fonte está disponível junto com o código do MySQL 5.7 no GitHub.
Com pequenas modificações, criei um novo plugin para resolver nosso problema, o Rewrite Status Variable Storage Engine, disponível no GitHub. Se usar Linux 64-bit, você pode baixar o plugin já compilado e não precisa preocupar-se com os detalhes de codificação ou processo de compilação.
Desenvolver um plugin para MySQL requer conhecimento em C++, o que pode não ser trivial para alguns leitores. Não trataremos dos detalhes sobre o código-fonte de um novo plugin neste post, talvez em um artigo futuro. Se quiser aprofundar-se no tema, recomendo o ótimo livro Expert MySQL.
Uma vez que você tenha o plugin compilado em forma de biblioteca (arquivos .so no Linux ou .dll no Windows), basta instalá-lo normalmente no MySQL. Se estiver usando Linux 64-bit pode testar usando o seguinte procedimento:
  1. Baixe o plugin compilado rewrite_status_storage_engine.so
  2. Verifique qual seu diretório de plugins no MySQL com o comando SELECT @@plugin_dir e mova o arquivo rewrite_status_storage_engine.so para lá
  3. Certifique-se que o arquivo possui as permissões corretas com chmod 755 rewrite_status_storage_engine.so
  4. Instale o novo plugin normalmente no MySQL com INSTALL PLUGIN rewrite_status_storage_engine SONAME 'rewrite_status_storage_engine.so';
  5. Verifique se o plugin foi instalado com sucesso e está ativo:
    mysql> SHOW PLUGINS \G
    Name: rewrite_status_storage_engine
    Status: ACTIVE
    Type: AUDIT
    Library: rewrite_status_storage_engine.so
    License: GPL
Agora é só confirmar que o erro não ocorre mais:
mysql>  SELECT @@storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'SELECT @@storage_engine' rewritten to 'SELECT @@default_storage_
engine' by a query rewrite plugin
1 row in set (0.00 sec)

Seguindo com a instalação do Moodle 2.3

Agora você deve conseguir instalar o Moodle 2.3 com MySQL 5.7 sem erros. Siga os passos do manual do Moodle mantendo o rewrite_status_storage_engine ativo.
Instação Moodle 2.3 com sucesso

Conclusão

Query Rewrite Plugins são bastante poderosos e podem ajudar em migrações evitando alterações na aplicação. Podem ser desenvolvidos plugins cutomizados em C++ ou usar o plugin Rewriter, dependendo da necessidade específica do projeto.
Envie seus comentários e sugestões.

Referências

Migrando Legados para MySQL 5.7 com Query Rewrite Plugin - Parte 1

Uma funcionalidade que pode fazer a diferença e viabilizar projetos de migração de legado para MySQL é a Query Rewrite Plugin. Com ela é possível interceptar comandos que são enviados ao MySQL e modificá-los online, sem a necessidade de alterar a aplicação.
Neste post, vamos entender como utilizar este recurso, com um exemplo real usando Moodle 2.3 no MySQL 5.7.

O que é o Query Rewrite Plugin

A partir do MySQL 5.7.6 há suporte para os Rewrite Plugins, que podem examinar e seletivamente reescrever comandos recebidos pelo servidor antes que sejam executados.
Quando uma instrução é recebido pelo MySQL Server, por exemplo um SELECT * FROM tabela where id = 1, ele passa por um parser que vai validar sintaxe e semântica, entre outros passos. Depois disso, o Otimizador de Consultas entra em ação, gerando e selecionando o melhor plano de execução para a query. Finalmente, o melhor plano é executado e o resultado retornado para o cliente que enviou a instrução.
Execução de Query no MySQL
Plugins Query Rewrite podem ser de dois subtipos:
- preparse: interceptam instruções antes do parser.
- postparse: agem logo depois do parser realizar seu trabalho.

Query Rewrite Plugins disponíveis

No pacote do MySQL Server 5.7 há dois Query Rewrite Plugins já disponíveis:
- rewrite_example: um exemplo de como funciona e como pode ser implementado um preparse Query Rewrite Plugin
- rewriter: um postparse plugin de uso mais genérico, para reescrever queries com SELECT, como veremos a seguir.

Como usar o plugin Rewriter (postparse)

A maneira mais fácil de enteder o funcionamento do Rewriter plugin é testando-o.
Passo 1 - Instale e configure o Rewriter plugin com:
cd /usr/share/mysql
mysql -uroot -p <install_rewriter.sql
Passo 2 - Como um teste simples, crie uma regra para transformar automaticamente SELECT ? em SELECT ? + 1:
mysql> INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement ) VALUES ('SELECT ?', 'SELECT ? + 1');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
                id: 1
           pattern: SELECT ?
  pattern_database: NULL
       replacement: SELECT ? + 1
           enabled: YES
           message: NULL
    pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)
Passo 3 - Carregue a regra recém criada na memória:
mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected (0.06 sec)
Passo 4 - Execute um SELECT:
mysql> SELECT 1;
+-------+
| 1 + 1 |
+-------+
|     2 |
+-------+
1 row in set, 1 warning (0.01 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'SELECT 1' rewritten to 'SELECT 1 + 1' by a query rewrite plugin
1 row in set (0.00 sec)
Você pode desabilitar apenas regras específicas com:
mysql> UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;
Veja que o Rewriter tem a capacidade de reescrever SELECTs. Ele examina instruções SELECT e pode reescrevê-los, com base nas regras de reescrita definidas na tabela query_rewrite.rewrite_rules. Os DBAs usam o plugin manipulando as regras armazenadas nesta tabela. Para efetivar novas regras (alterações na tabela rewrite_rules), o DBA deve chamar a procedure flush_rewrite_rules(), que carrega as regras na memória. Se houver algum erro durante a operação, o plugin o informará através da coluna message.
O plugin adiciona algumas variáveis ​de sistema para configuração:
SHOW GLOBAL VARIABLES LIKE 'rewriter%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
| rewriter_verbose | 1     |
+------------------+-------+
Também são adicionadas variáveis de status com informações sobre sua operação:
SHOW GLOBAL STATUS LIKE 'rewriter%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Rewriter_number_loaded_rules      | 1     |
| Rewriter_number_reloads           | 1     |
| Rewriter_number_rewritten_queries | 1     |
| Rewriter_reload_error             | OFF   |
+-----------------------------------+-------+
O Rewriter plugin atuará em instruções SELECT e em Prepared Statements, mas não fará reescritas para SELECTs dentro de Stored Programs ou UDFs.

Testando outro Query Rewrite Plugin (preparse)

O Rewriter é um QR plugin postparse. Isto significa que vai atuar depois do trabalho do parser. Vejamos agora como funciona um QR plugin preparse, que vai modificar a query antes de passar pelo parser:
mysql> INSTALL PLUGIN rewrite_example SONAME 'rewrite_example.so';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @@DEFAULT_STORAGE_ENGINE;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'SELECT @@DEFAULT_STORAGE_ENGINE' rewritten to 'select @@default_storage_engine' by a query rewrite plugin
*************************** 2. row ***************************
  Level: Note
   Code: 1105
Message: Query 'SHOW WARNINGS' rewritten to 'show warnings' by a query rewrite plugin
2 rows in set, 1 warning (0.00 sec)
Note que a query foi reescrita pelo plugin para letras minúsculas. Este segundo plugin é apenas um exemplo, talvez sem muita aplicação prática. Porém, talvez você necessite realmente criar um preparse QR plugin em algumas situações, como no estudo de caso que veremos na parte 2.

Conclusão

Query Rewrite Plugins podem ter vários usos e são aliados poderosos quando não é possível alterar o código da aplicação. O Rewriter é uma implementação mais genérica, que pode ajudar se for necessário interceptar e reescrever SELECTs.

Referências