performance

MySQL & NoSQL – Memcached Plugin

Muita gente já deve ter ouvido falar nos bancos de dados NoSQL e uma das ferramentas NoSQL muito utilizada é o memcached, no qual adicionamos uma camada de cache entre a aplicação e o banco de dados. Desde a versão 5.6 do MySQL, foi disponibilizado um plugin de integração entre o MySQL e o Memcached. Neste artigo publicado no iMasters eu mostro como instalar e configurar esta integração.

MySQL Fabric – Parte 1 Instalação

MySQL Fabric é uma ferramenta que está inclusa no MySQL Utilities que ajuda a gerenciar servidores MySQL.
Ele funciona basicamente adicionando uma nova camada entre a aplicação e os servidores MySQL, que auxilia no processo de sharding e alta disponibilidade.

Para instalar nosso ambiente com MySQL Fabric, vamos precisar de 4 servidores, eu utilizei os seguintes nomes e IPs:

fabric1 (192.168.0.200) - fabric
mysql1 (192.168.0.201) - mysql master
mysql2 (192.168.0.202) - mysql slave
mysql3 (192.168.0.203) - mysql slave

Obs.: Estou rodando CentOS 6.5 em todos os servidores.

1. Adicione o repositório mysql nos 4 servidores, leia Instalar a versão mais recente do MySQL via yum para mais informações:

rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum update

2. Instale os pacotes mysql mysql-server mysql-utilities:

yum install mysql mysql-server mysql-utilities
chkconfig mysqld on
/etc/init.d/mysqld start

3. Nos servidores mysql1,mysql2,mysql3 adicione as seguintes configurações no arquivo my.cnf:

[mysqld]
...
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=192.168.0.201
report-port=3306
server-id=1
log-bin=mysql1-bin.log

4. No servidor mysql1, adicione os usuários para a replicação (um para cada servidor mysql):


GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd'; "

5. No servidor mysql1 adicione o usuário MySQL para o servidor fabric:


GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';"

6. Nos servidores mysql2 and mysql3, configure a replicação:

CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

7. No servidor fabric1 adicione o usuário fabric no MySQL:


GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';

[root@fabric1 ~]# mysql -u root -e "GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';"

8. No servidor fabric1, configure o usuário e password no grupos [storage] and [servers] no arquivo /etc/mysql/fabric.cfg :

[storage]
...
password = fabricpwd
...
[servers]
password = reppwd
user = replication

9. No servidor fabric1, configure o banco de dados que será utilizado pelo MySQL Fabric, será perguntado para configurar uma senha, esta senha será utilizada nas próximas vezes que utilizarmos o mysqlfabric:


mysqlfabric manage setup

[root@fabric1 ~]# mysqlfabric manage setup
[INFO] 1406131468.176740 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc: 
Repeat Password:
Password set.

10. No servidor fabric1, inicie o fabric:


mysqlfabric manage start &

11. No servidor fabric1, adicione um grupo:

mysqlfabric group create GLOBAL1

[root@fabric1 ~]# mysqlfabric group create GLOBAL1
Password for admin: 
Procedure :
{ uuid        = 5e4a6bdb-60f0-4e34-87ba-4c56b7616b35,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

12. No servidor fabric1, adicione mysql1, mysql2 e mysql3 ao grupo GLOBAL1:


mysqlfabric group add GLOBAL1 192.168.0.201
mysqlfabric group add GLOBAL1 192.168.0.202
mysqlfabric group add GLOBAL1 192.168.0.203

[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.201
Password for admin: 
Procedure :
{ uuid        = 39efb9c4-6195-4c41-aa02-0bfdc228bfe2,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.202
Password for admin: 
Procedure :
{ uuid        = c8babfb9-d836-44c0-b4fd-015cd1df8298,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.203
Password for admin: 
Procedure :
{ uuid        = c86bba70-69ac-4923-9c54-1a8aaab6d97e,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

13. No servidor fabric1, pegue o uuid do servidor master:

mysqlfabric group lookup_servers GLOBAL1

[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}

14. No servidor fabric1, adicione o seu master como master do grupo GLOBAL1:

mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'

[root@fabric1 ~]# mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'
Password for admin: 
[WARNING] 1406131951.712366 - Executor-2 - Error () trying to process transactions in the relay log for candidate (('Command (START SLAVE SQL_THREAD, ()) failed: 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO', 1200)).
[INFO] 1406131951.824763 - Executor-2 - Master has changed from None to 2e157d1e-1281-11e4-80dc-080027aa0242.
Procedure :
{ uuid        = 733ae69d-fb12-447b-b86b-041703491315,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'PRIMARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}
[root@fabric1 ~]# 

Agora temos nosso ambiente MySQL Fabric funcionando .
Não perca os próximos post para aprender mais sobre essa nova ferramenta.

Esquentando o InnoDB Buffer Pool

Uma das mais importantes configurações para quem usa InnoDB é o innodb_buffer_pool_size, ele basicamente armazena dados e índices em memória, quando o MySQL recebe uma query e as paginas que contem o resultado da pesquisa/índice estao armazenadas no buffer, o MySQL não precisar ler essas informações do disco, o que é muito mais rápido (velocidade da memória vs velocidade do disco) .

Como estas informações estão armazenadas na memória, cada vez que o MySQL é re-iniciado, o buffer é apagado, e para “esquentar” o buffer normalmente leva-se algum tempo.

Para agilizar este processo, vamos utilizar 2 variáveis para salvar e restaurar as referencias das paginas que estão armazenadas no buffer, esta é uma nova funcionalidade adicionada no MySQL 5.6 (essa opção já pode ser encontrada em algumas versões anteriores do Percona Server e MariaDB )

Se o seu servidor já está rodando e com o buffer “quente”, vamos setar a variável innodb_buffer_pool_dump_at_shutdown para fazer efeito no próximo shutdown ou restart.

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

Agora, no próximo shutdown ou restart, um arquivo chamado ib_buffer_pool será criado no datadir do MySQL (você pode alterar este nome configurando a variável innodb_buffer_pool_filename ).

Próximo passo é configurar o MySQL para que ele leia o conteúdo deste arquivo e carregue os dados de volta para o InnoDB buffer, para isso, vamos adicionar a variável innodb_buffer_pool_load_at_startup ao my.cnf (e também a variável innodb_buffer_pool_dump_at_shutdown, para que o MySQL salve o buffer pool a cada shutdown/restart):

[mysqld]
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Agora podemos reiniciar o MySQL, como mencionado anteriormente, um arquivo será criado no datadir do MySQL, o conteúdo é similar ao seguinte:

[root@marcelodb data]# tail ib_buffer_pool
0,69618
0,69619
0,69620
0,69621
0,69622
0,69623
0,69624
0,69625
0,69626
0,69627

Quando reiniciamos o MySQL, veremos 2 mensagens no log:

2014-01-07 19:53:54 7fad34bc7700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2014-01-07 19:53:54 7fad34bc7700 InnoDB: Buffer pool(s) dump completed at 140107 19:53:54

Isto significa que o MySQL escreveu o arquivo contendo as referencias as paginas que estavam armazenadas no InnoDB buffer poll.

2014-01-07 19:54:01 7f68eee67700 InnoDB: Buffer pool(s) load completed at 140107 19:54:01

Esta mensagem, significa que o MySQL terminou de restaurar o conteúdo do InnoDB buffer pool de volta.

Este progresso pode ser controlado através das variáveis Innodb_buffer_pool_dump_status e Innodb_buffer_pool_load_status:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+------------------------+
| Variable_name                  | Value                  |
+--------------------------------+------------------------+
| Innodb_buffer_pool_load_status | Loaded 5121/6441 pages |
+--------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140108 16:55:05 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

Case deseja, pode se salvar/restaurar o conteúdo do InnoDB buffer pool através da variáveis innodb_buffer_pool_dump_now e innodb_buffer_pool_load_now.

Caso utilize InnoDB como storage engine em alguma tabela, este processo pode trazer grandes benefícios e minimizar os impactos de um restart.

Por que Replicação Atrasa no MySQL?

Recentemente respondi algumas questões referente a lag(atraso) na replicação, o que percebi é que muita gente não intende corretamente como este processo funciona internamente no MySQL e o porque do lag acontecer:

Veja a figura abaixo, ela representa replicação assíncrona no MySQL, recomendo que você leia meu outro post sobre o assunto:

“Como Funciona A Replicação No MySQL?”

How replication works

Como pode ser visto, existe uma grade diferença quanto ao ponto de entrada de dados no master e no slave, enquanto o master possui múltiplas threads inserindo/alterado/deletando dados ao mesmo tempo, no slave, existe somente uma única thread responsável por aplicar todas essas transações no banco de dados.

Vamos imaginar que uma transação(UPDATE por exemplo) demorou 50 segundos para rodar no master, durante e depois este período, outras conexões executaram comandos no master, o que vai acontecer quando o slave receber e executar essa transação? Isso mesmo, as demais transações pendentes e que forem sendo inseridas no relay log, vão ter que esperar a SQL_THREAD terminar de executar a transação(o nosso UPDATE que demorou 50 segundos no master) para serem processadas.

Se você utiliza MySQL a partir da versão 5.6.3 e sua aplicação divide os dados em múltiplos bancos internamente no MySQL, você pode minimizar este “gargalo” utilizando slave-parallel-workers, com esta opção você poderá configurar o numero de SQL_THREAD'S para executar transações simultâneas desde que elas sejam em banco de dados diferentes.

Utilizando o MySQL FullText Index Search

Hoje vamos falar sobre um recurso muito útil no MySQL, o Full Text Index e Search.

Um recurso muito poderoso que hoje nas versões 5.5 esta disponível apenas para a Engine MyISAM mas como podemos ver na documentação do FullText no site do MySQL, nas versoes 5.6 estará também disponível para InnoDB.

Normalmente quando se quer procurar uma palavra ou expressão em uma coluna usamos o LIKE CORINGA EXPRESSAO CORINGA (LIKE ‘%EXPRESSAO%’) caso estejamos procurando por 2 palavras ficaria LIKE ‘%PALAVRA1%PALAVRA2%) o que muita gente não sabe e que este processo eh custoso para o mysql e não performatico, para estes casos podemos usar o FullText index.

O comando eh simples MATCH() … AGAINST (), onde MATCH especificamos a(s) coluna(s) que desejamos procurar, sim, podemos utilizar o full text para procurar em mais de uma coluna, desde que todas as colunas dentro do MATCH esteja especificadas no índice e AGAINST e onde especificamos a(s) palavra(s) que queremos procurar, podemos especificar também o modo de procura, que vou falar mais tarde neste artigo.

Vamos criar a nossa tabela e colocar a mão na massa:

CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`body` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM;
INSERT INTO `articles` VALUES (1,'MySQL Tutorial','DBMS stands for DataBase ...'),(2,'How To Use MySQL Well','After you went through a ...'),(3,'Optimizing MySQL','In this tutorial we will show ...'),(4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),(5,'MySQL vs. YourSQL','In the following database comparison ...'),(6,'MySQL Security','When configured properly, MySQL ...');

Vamos fazer nossa primeira consulta, procurando por artigos que abordem ‘database’

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('database');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.01 sec)

Vamos agora procurar por ‘database tutorial’

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('database tutorial');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
3 rows in set (0.01 sec)

Agora vamos procurar por artigos que abordem o tema ‘MySQL’, detalhe, todos os artigos em nossa tabela contem a palavra MySQL

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('MySQL');

Empty set (0.00 sec)

Por que? Vamos então falar sobre modos de busca, por padrão, o MySQL utiliza o modo de busca ‘Natural Language’ que nos diz que se a palavra procurada estiver presente em 50% ou mais linhas a consulta não vai bater.

Outra função interessante e que o MySQL nos permite trocar o modo de busca, vamos então repetir a consulta em modo Booleano:

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('MySQL' IN BOOLEAN MODE);

+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+

6 rows in set (0.00 sec)

Ok, mas o que muda entre um e outro? pra que serve o modo booleano? Como o nome já diz, true or false, vamos fazer uma busca por ‘database’ mas eu não quero que sejam exibidas linhas que contenham a palavra tutorial, complicou? não, veja como:

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('+database -tutorial' IN BOOLEAN MODE);

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
1 row in set (0.01 sec)

Por hoje e só, no próximo artigo, vamos abordar mais a fundo o modo Booleano, alterando a ordem de relevância

Optimizar consultas em MySQL com query cache

 

EDIT: Cuidado. Query Cache pode causar problemas de performance e escalabilidade em ambientes com muitas sessões simultâneas. Use com cuidado.

Hoje vamos falar de uma ferramenta bem bacana do MySQL, o query cache.
o query cache salva resultados de SELECT’s já executados e que seus dados brutos não tenham sido alterados tornando assim o tempo de resposta da query muito mais otimizada, pois ele vai buscar da memória e não do disco.
Ele pode trabalhar de 3 maneiras diferentes, no nosso my.ini dentro do grupo [mysqld]:
query_cache_type = 0
Desligado
query_cache_type = 1
Ligado para todas as query’s
query_cache_type = 2
Ligado sobre demanda

Mas qual a melhor maneira de utilizar o query cache?
Tendo em vista que toda a vez que você altera dados em alguma tabela, ele invalida o cache da mesma, o mais indicado é você fazer um mapeamento da frequencia de atualização das tabelas e utilizar o cache para as que não tenham uma frequencia grande a atualização / inserção, para que assim, o cache lhe dê um ganho de performance, e não fique sobrecarregando o servidor tendo que invalidar muitas querys.
Por exemplo, a tabela de registros de log do sistema, a cada ação do usuário ele irá inserir um novo dado nesta tabela, logo ela não é uma boa candidata a ser cacheada, já a tabela de noticias do site, recebe atualizações 2 vezes ao dia, tá aí, uma boa candidata a ser cacheada,

Vou mostrar na prática que resultados conseguimos alcançar com o query cache:

estou com ele setado para ON DEMAND – query_cache_type = 2
Possuo uma tabela com 100 milhões de registros, para a opção ON DEMAND temos que especificar no select que queremos que ele utilize o cache com SQL_CACHE:

    Rodando a consulta pela primeira vez:

mysql> SELECT SQL_CACHE * FROM noticia WHERE conteudo LIKE "%8555556%";
. . .
20 rows in set (2 min 15.20 sec)

Neste momento, esquentamos o cache com a nossa consulta, ele demorou o tempo padrão, porém colocou-a em cache, veja o resultado da mesma query:
mysql> SELECT SQL_CACHE * FROM noticia WHERE conteudo LIKE "%8555556%";
...
20 rows in set (0.00 sec)

A mesma consulta demorou 0 segundos para trazer os resultados, pois estava guardada em cache.
Mas qual o tamanho do cache de consulta?
Como tudo no MySQL, ele é configurável através da opção query_cache_size, valores em torno de 16M são recomendados, dependendo da capacidade do seu servidor.

Caso você faça a analise e veja que a maioria das suas tabelas devem ser cacheadas, vocÊ pode usar o processo inverso, ou seja, setar a variavel query_cache_type = 1 isso fará com que todas as consultas sejam cacheadas e expecificar nos selects que não devem cachear com SELECT SQL_NO_CACHE * FROM ...
Utilizar qualquer uma das funções abaixo inutiliza o cache na consulta:
BENCHMARK() CONNECTION_ID() CONVERT_TZ()
CURDATE() CURRENT_DATE() CURRENT_TIME()
CURRENT_TIMESTAMP() CURTIME() DATABASE()
ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK()
LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT()
NOW() RAND() RELEASE_LOCK()
SLEEP() SYSDATE() UNIX_TIMESTAMP(
USER() UUID() UUID_SHORT()

Lembrando que para a utilização do query cache, select * from ... é diferente de SELECT * FROM ...