Técnicas de backup e restauração de dados no MySQL

O backup consistente do banco de dados é de extrema importância para que possamos manter a integridade dos dados caso haja uma falha do sistema, hardware ou até mesmo para corrigir eventuais falhas de usuários, como por exemplo, a remoção acidental de um banco de dados. Para isto, é importante a adoção de uma política consistente de backup (diariamente), bem como conhecer as possíveis técnicas para fazê-lo. No MySQL é possível fazermos backup binário do banco, isto é, será guardado uma cópia da estrutura de arquivos e diretórios que constituem os seus dos bancos de dados e tabelas. Além disto, pode-se optar pelo backup dos dados, onde serão armazenados os dados em formato texto ou em forma de comandos SQL. Vamos descrever aqui como utilizar estas duas formas de backup para a execução de uma cópia consistente de dados.

Ao realizar o procedimento de backup cria-se uma imagem dos seus dados no momento da execução da rotina de backup. Quando houver problemas com o seu banco de dados que necessite do backup, você pode utilizar o seu último backup retornando só os dados para a situação em que o banco se encontrava no momento deste backup. O que acontece com os dados alterados ou inseridos entre o backup e a falha? No MySQL você pode habilitar um log binário de alterações (opção log-bin no arquivo de configuração), que armazenam todos os comandos que modificam a estrutura do banco de dados, sendo que estes podem ser utilizados para recuperar os dados não contidos no backup. Os logs são criados com a extensão que indica o número de sequência do log, que é incrementado sempre que um novo log é criado. Para "traduzir" o log binário em comandos SQL, utilize a ferramenta mysqlbinlog, sendo que a saída deste poderá ser utilizada diretamente como entrada para o MySQL, como no exemplo:

shell>mysqlbinlog mysql-bin.012 | mysql

ou ainda,

shell> mysqlbinlog mysql-bin.012 > dump.log
shell> mysql < dump.log

Estes comandos deverão ser executados após a restauração do backup. Para facilitar a manipulação do log na restauração, isto é, como identificar quais os comandos foram executados após o backup, é importante manter o sincronismo entre o log binário e o backup. Como os logs possuem um número sequencial, utilize o comando FLUSH LOGS para criar um novo arquivo de log no momento de backup. Assim, estará garantido que todas alterações após o backup serão armazenadas nos logs criados a partir deste momento. Na recuperação dos dados basta executar
todos os logs a partir do momento do backup, uma vez que as alterações dos logs anteriores já estarão contidas no próprio backup.

Uma vez apresentada a utilização dos arquivos de log para restauração de dados, analisamos a primeira forma de backup que é baseada na cópia dos arquivos do banco de dados. Esta cópia pode ser feita manualmente com os comandos de cópia do sistema operacional (SO) ou utilizando ferramentas de backup do próprio SO. Vale ressaltar que para garantir uma cópia consistente destes arquivos é preciso garantir que não haverá escritas na base de dados durante a execução da rotina de backup. Esta condição pode ser garantida através de uma parada no gerenciador de banco de dados (SGBD) ou por meio de um bloqueio das tabelas permitindo apenas a leituras dos dados (lock) durante o backup. É possível fazer um

Novidades da versão 5.0 do MySQL

A MySQL AB, juntamente com a O'Reilly, realizou a terceira MySQL Users Conference entre os dias 18 e 21 de abril de 2005, em Santa Clara, Califórnia nos Estados Unidos. O evento contou com a participação de mais de 1300 pessoas vindas dos mais variados países do mundo, inclusive 5 brasileiros. Neste evento a MySQL AB anunciou a versão 5.0 beta do MySQL, bem como os novos recursos presentes nesta versão. Neste artigo apresento e discuto os principais recursos que compõe o MySQL 5.0, bem como as suas aplicações.

Os primeiros recursos adicionados ao MySQL 5.0 são as stored procedures, funções e triggers, sendo que o MySQL implementa stored procedures segundo o padrão ANSI 2003. Este recurso permite criar procedimentos para a manipulação da base de dados, padronizando o acesso a estes dados. Por exemplo, pode-se criar uma rotina para a inserção de clientes, que faça a checagem do endereço do mesmo em uma outra tabela antes da gravação, onde este ficaria armazenado no servidor podendo ser acionado pelos usuários. As aplicações poderiam invocar este procedimento de inserção de cliente, garantindo que a verificação do endereço sempre seria aplicada, com isto as aplicações mantêm-se simples, enquanto toda a complexidade dos dados é encapsulada pelo servidor de banco de dados.

Além das stored procedures, os usuários podem criar suas próprias funções, como por exemplo, uma função para formatar dinheiro em moeda brasileira, por exemplo. Com isto é possível estender o conjunto de funções do MySQL de forma a faci

Topologias de replicação no MySQL

No meu último artigo http://www.devmedia.com.br/visualizacomponente.aspx?comp=2054,
foi apresentado o mecanismo de replicação de dados do MySQL. Este recurso chamado de replicação Master-Slave permite que um servidor MySQL (slave) copie todas as alterações realizadas em um outro servidor, denominado de master. A restrição imposta por este recurso é a de que um slave poderá replicar apenas de um único master, sendo que a replicação multi-master está prevista para a versão 5.1.

Neste caso, é possível configurar várias topologias de replicação sem violar a condição apresentada anteriormente. A topologia mais comumente utilizada é aquela onde existe um servidor master e vários outros servidores atuando como slaves, como apresentado na Figura 1.


Figura 1. Topologia de replicação mais comum.

O modelo anterior permite o balanceamento de carga entre os acessos de leitura e escrita, isto é, a aplicação se conecta ao master quando for executar uma escrita nos dados, e pode escolher um dos slaves para se conectar no momento em que for realizar uma leitura. Além disto, permite uma distribuição dos acessos de leitura entre os slaves existentes aumentando o desempenho do sistema como um todo. Ainda, caso haja uma falha no servidor master, um dos slaves pode ser colocado em seu lugar, já que estes mantêm uma cópia de tudo que acontece no servidor master. Vale ressaltar que esta troca de papéis deve ser implementada por uma ferramenta externa desenvolvida pelo administrador do banco de dados, já que o MySQL não provê mecanismos de fail-over embutidos no servidor.

Existem outras topologias de replicação que permitem uma distribuição dos acessos de escrita, bem como

Replicação de dados no MySQL

O objetivo de um mecanismo de replicação de dados é permitir a manutenção de várias cópias idênticas de um mesmo dado em vários servidores de banco de dados (SGBD). Os principais benefícios da replicação de dados é a redundância, o que torna o sistema menos sensível às falhas, a possibilidade de um balanceamento de carga do sistema, já que o acesso pode ser distribuído entre as réplicas, e finalmente, ter-se um backup on-line dos dados, já que todos as replicas estariam sincronizadas. Este artigo, apresenta uma introdução ao mecanismo de replicação do MySQL, bem como as configurações básicas do SGBD para a realização desta tarefa.

O MySQL permite um tipo de replicação conhecido como Master-Slave, neste caso temos um servidor MySQL atuando como Master e um ou mais servidores MySQL atuando como Slaves. O servidor Master grava em um log binário de alterações todos os comandos de atualização da base de dados. Os slaves por sua vez se conectam ao master, lêem o arquivo de log binário e executam os comandos encontrados neste log. Desta forma, todas as alterações ocorridas no master são imediatamente replicadas para os outros servidores slave.

A replicação Master-Slave é um processo assíncrono, isto é, poderá existir um atraso de informações entre o master e os slaves dependendo do meio de comunicação entre eles. Além disto, como os slaves copiam as alterações do master, todas as modificações dos dados devem ser aplicadas ao master, caso contrário os servidores ficarão sem sincronismo. Este processo incremental com a execução de todas as alterações indicadas no log binário a partir de um determinado momento, não garante que os dados existentes antes do início da replicação eram idênticos. Neste caso, antes de iniciar a replicação é preciso sincronizar todos os servidores, colocando em todos eles uma cópia da base de dados a ser replicada.

Para configurar a replicação cada servidor MySQL deverá possuir um server-id único e o master deve estar com o log binário habilitado. Além disto, crie um usuário no master com o privilégio FILE (3.23.x) ou REPLICATION SLAVE (4.x ou superior), para que os slaves possam se conectar a este servidor e fazer a leitura do seu log binário. Nos slaves é preciso indicar o endereço do servidos master e o usuário que será utilizado para fazer a conexão. A seguir esta um exemplo de configuração do master e slave:

Como Trabalhar com Tabelas MyISAM Grandes

As tabelas MyISAM são o tipo padrão de tabelas no MySQL. Elas se destacam pelo seu alto desempenho, já que não suportam determinados recursos, tais como o controle de transações. Neste artigo descrevo algumas técnicas para que possamos trabalhar com tabelas MyISAM que possuam um número elevado de registros.

As tabelas MyISAM são representadas por 3 arquivos: o primeiro arquivo contém a descrição da tabela (.frm), ou seja, armazena o dicionário de dados da tabela. Os outros arquivos são o MYD para armazenar os dados e o MYI que contém os índices da tabela, sendo que todos os índices da tabela são armazenados em um único arquivo. Dado o esquema de armazenamento das tabelas MyISAM percebe-se que o tamanho dos arquivos estão intimamente relacionados à quantidade de registros existentes na tabela. Neste caso, em alguns Sistemas Operacionais (SO) existem restrições quanto ao tamanho máximo de um arquivo, isto é não suportam arquivos grandes. No Linux, por exemplo, existe a limitação de 2/4 GB dependendo do tipo de kernel e arquitetura de processador utilizado. Daí, introduzimos uma limitação em relação ao número máximo de registros em uma tabela MyISAM. Vale ressaltar que esta limitação está relacionada ao SO e não ao MySQL, no Windows por exemplo, o problema não se verifica.

O MySQL possui duas estratégias imediatas para contornar este problema. A primeira possibilidade é a utilização de tabelas Merge, que foram discutidas em um outro artigo, disponível no endereço abaixo:

http://www.devmedia.com.br/visualizacomponente.aspx?comp=1966

e a utilização de tabela MyISAM com a opção de RAID, o que é definido no momento da criação da tabela MyISAM (CREATE TABLE).

As tabelas Merge são uma coleção de tabelas MyISAM idênticas. Na verdade se trata de uma tabela "virtual", onde não são armazenados dados. Este tipo de tabela simples

Verificação e correção de tabelas corrompidas no MySQL

Implementando Integridade Referencial no MySQL

No artigo "Trabalhando com os vários tipos de tabelas do MySQL", vimos que no MySQL é possível escolher o formato de armazenamento da tabela no momento da sua criação, o que dá a flexibilidade de optar por um ou outro tipo de tabela, dependendo do tipo de aplicação a ser desenvolvida. Existem determinados recursos do SGBD (Sistema Gerenciador de Banco de Dados) que estão diretamente relacionados ao tipo de tabela escolhido, tais como, controle de transação, níveis de lock e integridade referencial. Neste artigo vamos exemplificar como definir regras de integridades no MySQL.

Para trabalharmos com integridade referencial, isto é, para adicionarmos restrições de integridade (constraints) às chaves estrangeiras, é necessário criar as tabelas como InnoDB. Este recurso está disponível somente para este tipo de tabela, embora seja possível definir chaves estrangeiras e restrições outros tipos de tabelas por razões de compatibilidade. O detalhe é que neste caso, estas definições terão o efeito apenas de documentação, ou seja, o MySQL não respeitará os constraints definidos.

O InnoDB implementa as restrições de integridade CASCADE, RESTRICT, SET NULL e SET DEFAULT. No primeiro caso, ao se remover um registro da tabela referenciada pela chave estrangeira os registros relacionados àquele removido serão eliminados em todas as tabelas relacionadas. O RESTRICT não permite a remoção de registros que possuam relacionamentos em outras tabelas. Os dois últimos atribuem os valores DEFAULT ou NULL para as chaves estrangeiras cujos registros relacionados foram excluídos. O exemplo abaixo ilustra algumas tabelas que utilizam regras de integridade:

CREATE TABLE aluno (
 

Trabalhando com os Vários Tipos de Tabelas do MySQL

O MySQL possui uma característica um pouco diferente dos outros sistemas gerenciadores de banco de dados, uma vez que no MySQL é possível escolher o tipo da tabela no momento da criação da mesma. O formato de armazenamento dos dados, bem como alguns recursos do banco de dados são dependentes do tipo de tabela escolhido. Neste artigo apresento os tipos de tabelas suportados pelo MySQL e as suas características.

A definição do tipo de tabela é feito através do comando CREATE TABLE, como mostrado a seguir:

CREATE TABLE teste (
        id INT NOT NULL,
        texto CHAR(30) NOT NULL,
        PRIMARY KEY (id)
) TYPE=MyISAM;

No comando acima, TYPE=MyISAM, indica que a tabela criada será do tipo MyISAM, que é o valor default caso não seja informado o TYPE. A partir da versão 4.0.18 você pode utilizar ENGINE como sinônimo de TYPE. A seguir estão apresentados os tipos de tabelas existentes no MySQL:

1. MyISAM:

Este é o tipo de tabela padrão do MySQL. Caso não seja informado o tipo de tabela, o MySQL criará a tabela do tipo MyISAM. O tipo de tabela default pode ser alterado incluindo-se no arquivo de configuração, a opção descrita a seguir: default-table-type=tipo.

As tabelas MyISAM são armazenadas em 3 arquivos: .FRM que armazena a definição da tabela, o arquivo .MYD que contém os dados e o .MYI contendo os índices. Estas tabelas são de grande desempenho para leitura, uma vez que os seus índices são armazenados em árvores binárias balanceadas, o que provê um ganho para o acesso às informações. O MyISAM não provê controle de transações (commit ou rollback) e também não possue integridade referencial, isto é, ao incluir uma chave estrangeira com alguns constraints, esta servirá apenas como documentação, mas as restrições não serão respeitadas pelo banco. Como as tabelas MyISAM são representadas por arquivos no modelo uma tabela para três arquivos, existe, em alguns sistemas operacionais, restrições quanto ao tamanho destas tabelas. No Linux, por exemplo existe a restrição de 2G/4GB por arquivo, com isto uma tabela MyISAM poderia ter somente 2G/4GB de dados. Para superar esta limitação foi introduzido na versão 4.1 a opção RAID_TYPE utilizada no CREATE TABLE, que permite ao próprio MySQL dividir o armazenamento da tabela em vários arquivos. Com isto, o tamanho da tabela MyISAM não fica preso ao tamanho máximo de arquivo do sistema operacional. O principal problema encontrado com as tabelas MyISAM é que elas possuem um mecanismo de locks por tabela. Assim, todas as vezes que há uma escrita na tabela, o MySQL precisa travar a tabela como um todo, o que bloqueia por um instante o acesso à esta tabela, mesmo para processos que tentem acessar registros que estão sendo modificados. Assim, é gerada uma fila de espera até que o lock seja liberado e os outros processos possam ser executados. Por outro lado, estas tabelas são extremamente rápidas, devido à sua arquitetura simplificada.

2. HEAP:

Tabelas HEAP são armazenadas em memória e por isto são extramente rápidas, por outro lado o seu conteúdo é volátil, uma vez que não são gravadas em disco. Caso haja uma queda do SGBD os dados destas tabelas serão perdidos. Além disto, é necessário um processo para dar a carga inicial nos dados quando o servidor de banco iniciar e sua execução. A principal aplicação das tabelas HEAP seria para tabelas que são consultadas com muita frequência, mas que não sofrem muitas alterações (lookup tables).

3. MERGE:

As tabelas MERGE são coleções de tabelas MyISAM idênticas. Este recurso permite a divisão de uma tabela grande em várias partes menores, e ainda assim permite acesso ao conteúdo de todas elas como se fossem uma única tabela. O exemplo a seguir ilustra a criação de uma tabela MERGE:

CREATE TABLE t1 (
        a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        message CHAR(20)
);

CREATE TABLE t2 (
        a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        message CHAR(20)
);

INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

CREATE TABLE total (
        a INT NOT NULL AUTO_INCREMENT,
        message CHAR(20), INDEX(a)
)TYPE=MERGE UNION=(t1,t2);

SELECT * FROM total;

Gerenciamento de Usuários e Controle de Acessos do MySQL

O objetivo principal deste artigo é descrever os mecanismos de controles de acessos do MySQL, bem como apresentar as rotinas para criação de usuários e gerenciamento de seus privilégios.

O MySQL possui um mecanismo que permite limitar o acesso de um usuário a apenas um banco, tabela ou coluna, além de poder controlar o acesso de acordo com o host a partir de onde está sendo feita a conexão com o servidor. Pode-se ainda, conceder privilégios diferentes para cada host de onde o usuário possa estabelecer a conexão. Assim, é possível que determinados comandos possam ser executados somente quando o usuário estiver em um host específico, por exemplo o mesmo host do servidor MySQL (localhost).

O MySQL armazena as informações dos seus usuários em 4 tabelas que estão localizadas no banco de dados mysql. Estas tabelas são a user, db, tables_priv e columns_priv. A tabela user armazena as informações de todos os usuários do banco e os privilégios globais deste usuário. A tabela db armazena os privilégios dos usuários específicos de um banco de dados. Finalmente, as tabelas tables_priv e columns_priv armazenam os privilégios associados a tabelas e colunas, respectivamente. Como estas tabelas possuem as informações dos usuários, bem como os seus privilégios, recomenda-se que apenas o administrador do banco de dados tenha acesso ao banco mysql (usuário root).

Para criar usuários e conceder privilégios no MySQL, utiliza-se o comando GRANT. Ao executar um comando GRANT para um usuário que não existe, o mesmo será criado. Um GRANT para um usuário já existente adicionará os novos privilégios aos já concedidos anteriormente. A sintáxe resumida do comando GRANT é exibida a seguir:

GRANT priv [(colunas)] [, priv [(colunas)]] ...
ON {*.* | db.* | db.tabela}
TO usuario [IDENTIFIED BY 'senha']
[, usuario [IDENTIFIED BY 'senha']] ...
[WITH [GRANT OPTION |
MAX_QUERIES_PER_HOUR contador |
MAX_UPDATES_PER_HOUR contador |
MAX_CONNECTIONS_PER_HOUR contador]]

No comando acima os [ ] indicam que o comando é opcional. O primeiro item a ser informado é(são) o(s) privilégio(s) a ser(em) concedido(s) ao(s) usuário(s). A lista de privilégios existentes no MySQL é descrita abaixo:

Ferramentas Gráficas para Modelagem de Dados e Administração do MySQL

Este artigo tem como objetivo principal introduzir algumas ferramentas gráficas para a manipulação do banco de dados MySQL. Basicamente apresentarei uma ferramenta para modelagem de dados, Modelo Entidade-Relacionamento (MER), e uma ferramenta para a realização de tarefas administrativas. Os procedimentos de instalação destas ferramentas não serão descritos aqui, uma vez que estes podem ser encontrados na página de download dos produtos.

A ferramenta de modelagem de dados no MySQL, DBDesigner 4, foi desenvolvida e otimizada para a utilização com este banco de dados provendo aos seus usuários uma forma simples e centralizada para a definição dos seus modelos de dados. O DBDesigner 4 pode ser obtido a partir do endereço www.fabforce.net/dbdesigner4/, sendo que o mesmo está disponível para o Microsoft Windows e Linux.

A vantagem desta ferramenta para os usuários do MySQL é que ela apresenta todos os recursos compatíveis com o MySQL, tais como os tipos de dados. Ela permite ainda a escolha do tipo de tabela a ser utilizada (InnoDB, MyISAM, dentre outros), e a definição de outros incrementos para a criação de tabelas. Também é possível definir os relacionamentos entre tabelas e construir as restrições (constraints) associadas a cada relacionamento, sendo criadas automaticamente as chaves estrangeiras nas tabelas relacionadas.

Outros recursos importantes do DBDesigner são a engenharia reversa e sincronização do modelo com a base de dados. Assim, torna-se fácil a manutenção do seu esquema de banco de dados com o MER definido no DBDesigner 4. Além disto, é possível elaborar consultas SQL de forma gráfica a partir da utilização do "Query Mode". Esta ferramenta permite a l

Como Fazer um Backup Online no MySQL

No último artigo "Técnicas de backup e restauração de dados no MySQL" foram apresentadas as formas de se fazer um backup consistente do seu banco de dados, bem como a utilização deste backup para a recuperação da base de dados. A principal desvantagem daquelas técnicas é o fato de que os bancos de dados ficam indisponíveis para alterações durante a execução da rotina de backup. Neste artigo vamos apresentar uma forma simples de se fazer backup online utilizando o mysqldump.

O problema crítico no backup online é garantir que todas as alterações realizadas durante a geração do backup estarão presentes na imagem gerada. A cópia de arquivos ou a leitura da base para exportação via SQL ou texto é realizada de forma sequencial, sendo assim pode ocorrer uma inserção em uma tabela que já foi incluída no backup, e esta alteração não estará presente no backup. Isto gera uma cópia inconsistente da sua base de dados, inpossibilitando a restauração completa da base. Podemos contornar esta situação no MySQL utilizando os arquivos de logs binários para armazenar estes dados durante o backup. Imaginemos a seguinte situação: o MySQL está escrevendo o log binário mysql-bin.014, lembre-se de que os logs são sempre sequenciais, e desejamos iniciar um backup online. Para garantir
que teremos no backup todos os dados, criamos um novo log no início da execução da rotina de backup, da seguinte forma:

shell>mysqldump --all-databases -F > backup-online.sql

A opção -F do mysqldump força a criação de um novo arquivo de log antes que se inicie a geração do dump em SQL, no nosso exemplo cria-se o arquivo mysql-bin.015. A opção --all-databases indica que todos os bancos de dados serão copiados. No exemplo todas as a

Técnicas de backup e restauração de dados no MySQL

O backup consistente do banco de dados é de extrema importância para que possamos manter a integridade dos dados caso haja uma falha do sistema, hardware ou até mesmo para corrigir eventuais falhas de usuários, como por exemplo, a remoção acidental de um banco de dados. Para isto, é importante a adoção de uma política consistente de backup (diariamente), bem como conhecer as possíveis técnicas para fazê-lo. No MySQL é possível fazermos backup binário do banco, isto é, será guardado uma cópia da estrutura de arquivos e diretórios que constituem os seus dos bancos de dados e tabelas. Além disto, pode-se optar pelo backup dos dados, onde serão armazenados os dados em formato texto ou em forma de comandos SQL. Vamos descrever aqui como utilizar estas duas formas de backup para a execução de uma cópia consistente de dados.

Ao realizar o procedimento de backup cria-se uma imagem dos seus dados no momento da execução da rotina de backup. Quando houver problemas com o seu banco de dados que necessite do backup, você pode utilizar o seu último backup retornando só os dados para a situação em que o banco se encontrava no momento deste backup. O que acontece com os dados alterados ou inseridos entre o backup e a falha? No MySQL você pode habilitar um log binário de alterações (opção log-bin no arquivo de configuração), que armazenam todos os comandos que modificam a estrutura do banco de dados, sendo que estes podem ser utilizados para recuperar os dados não contidos no backup. Os logs são criados com a extensão que indica o número de sequência do log, que é incrementado sempre que um novo log é criado. Para "traduzir" o log binário em comandos SQL, utilize a ferramenta mysqlbinlog, sendo que a saída deste poderá ser utilizada diretamente como entrada para o MySQL, como no exemplo:

shell>mysqlbinlog mysql-bin.012 | mysql

ou ainda,

shell> mysqlbinlog mysql-bin.012 > dump.log
shell> mysql < dump.log

Estes comandos deverão ser executados após a restauração do backup. Para facilitar a manipulação do log na restauração, isto é, como identificar quais os comandos foram executados após o backup, é importante manter o sincronismo entre o log binário e o backup. Como os logs possuem um número sequencial, utilize o comando FLUSH LOGS para criar um novo arquivo de log no momento de backup. Assim, estará garantido que todas alterações após o backup serão armazenadas nos logs criados a partir deste momento. Na recuperação dos dados basta executar
todos os logs a partir do momento do backup, uma vez que as alterações dos logs anteriores já estarão contidas no próprio backup.

Uma vez apresentada a utilização dos arquivos de log para restauração de dados, analisamos a primeira forma de backup que é baseada na cópia dos arquivos do banco de dados. Esta cópia pode ser feita manualmente com os comandos de cópia do sistema operacional (SO) ou utilizando ferramentas de backup do próprio SO. Vale ressaltar que para garantir uma cópia consistente destes arquivos é preciso garantir que não haverá escritas na base de dados durante a execução da rotina de backup. Esta condição pode ser garantida através de uma parada no gerenciador de banco de dados (SGBD) ou por meio de um bloqueio das tabelas permitindo apenas a leituras do

Otimização do MySQL

Otimização de consultas SQL – Parte 1

por Eber M. Duarte

No artigo introdutório sobre otimização do MySQL foi apresentado o que pode ser otimizado em um sistema de banco de dados. Este artigo tem como objetivo discutir o processo de execução de consultas no MySQL, visando assim possibilitar a elaboração das consultas SQL de forma a serem executadas no menor tempo possível.

O processo de execução de uma consulta no MySQL consiste de várias etapas que são o parser, otimização, execução e retorno dos dados. A Figura 1 apresenta uma visão geral deste processo.

Figura 1. Etapas da execução de consultas SQL

Durante o parser o MySQL faz a leitura do comando SQL enviado pelo cliente, converte o comando para um formato binário interno e então o envia ao otimizador. Neste caso, este processo será executado para cada consulta enviada ao servidor, portanto, é necessário reduzir este tempo para produzir um ganho de desempenho. Uma alternativa interessante é a utilização dos Prepared Statements, disponíveis a partir da versão 4.1. Este recurso permite a criação de um comando no qual será realizado o parser e o binário dele será armazenado no servidor. Desta forma, este comando poderá ser executado várias vezes tendo sido feito apenas um procedimento de parser, o que certamente implicará em redução no tempo de execução. Os Prepared Statements não serão abordados neste artigo, ficando fora do escopo deste texto.

A segunda etapa é a otimização da consulta, onde o otimizador decide a ordem de leitura das tabelas, qual o ín

Otimização do MySQL

Otimização de consultas SQL – Parte 2

por Eber M. Duarte

No artigo “Otimização de consultas SQL – Parte 1”, foi apresentado o mecanismo de execução de consultas no MySQL. Neste artigo, serão apresentadas técnicas para a detecção das consultas lentas e métodos para a avaliação do plano de execução de uma consulta.

O MySQL possui um log chamado “slow log”, onde são armazenadas todas as consultas cujo tempo de execução seja maior que o parâmetro long-query-time, que por padrão é 10 segundos. Além disto, pode-se configurar este log para armazenar também as consultas que não utilizam índices ou que realizam um SELECT *. Por padrão este log vem desabilitado, e pode ser ativado através do parâmetro log-slow-queries. Ao executar o comando STATUS, o MySQL irá exibir dentre outras informações o SLOW QUERIES, que é o número de consultas lentas recebidas pelo servidor, contado desde um o início da execução do MySQL, ou desde o último FLUSH STATUS. Caso o slow log esteja ativo, o que é recomendado, estas consultas serão gravadas neste arquivo e possibilitarão a identificação dos comandos que são os gargalos do seu sistema.

Uma vez detectadas as consultas lentas é preciso avaliar como o MySQL está executando estes comandos. Para isto faz-se uso do comando EXPLAIN, que deve ser colocado antes do comando SELECT a ser estudado. Este comando irá exibir o plano de execução escolhido pelo otimizador. O exemplo da Listagem 1 ilustra este recurso para avaliar uma consulta que lista os países da região Nórdica e suas respectivas capitais.

mysql> EXPLAIN SELECT co.name, ci.name FROM City AS ci, Country AS co WHERE ci.id = co.capital

-> AND co.region LIKE 'Nordic%'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: co

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 239

Extra: Using where

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: ci

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: world.co.Capital

rows: 1

Extra:

2 rows in set (0.01 sec)

Listagem 1. EXPLAIN de uma consulta feita no MySQL.

As tabelas são lidas pelo otimizador na ordem em que elas aparecem no retorno do EXPLAIN. No exemplo da Listagem 1, o MySQL optou por ler primeiro a tabela Country e depois a tabela City, perceba que a ordem em que as tabelas aparecem no FROM não foi seguida pelo otimizador. Por isto, quando for desejado que a ordem do FROM seja preservada, é preciso utilizar-se o STRAIGHT_JOIN para induzir o otimizador neste sentido.

Existem diversas informações apresentadas pelo EXPLAIN, a primeira delas é o SELECT_TYPE que mostra o tipo de consulta que está sendo processada. Estas podem ser consultas simples ou sem sub-consultas (SIMPLE) e SUB_QUERY ou UNION para comando que possuem consultas aninhadas. Além disto, o EXPLAIN fornece quais os índices estão disponíveis para a execução do comando (coluna POSSIBLE_KEYS), e o índice que ele está utilizando para a leitura do dados aparece na coluna KEY (NULL, caso não esteja fazendo uso de índices).

Vale destacar, que será utilizado apenas um índice para cada tabela lida pelo MySQL, por isto a criação do índice deve ser feita com critério, isto é, sempre compondo as colunas que serão empregadas no WHERE.

A coluna ROWS fornece o número de linhas lidas pelo MySQL para buscar o resultado, idealmente este número deve ser igual ao número de linhas retornadas pelo comando. A coluna REF indica a coluna utilizada para referenciar tabelas em JOIN (perceba a tabela City), e o EXTRA fornece informações adicionais sobre a execução, tais como, o uso de tabelas temporárias, ordenação, dentre outros.

A coluna TYPE exibe o algoritmo de busca utilizado para a leitura dos dados, a Tabela 1 apresenta os valores possíveis para esta coluna, indo do melhor para o pior tipo.

Otimização do MySQL
Introdução aos mecanismos de otimização

Este artigo tem como objetivo apresentar o processo de otimização do banco de dados MySQL, bem como introduzir os aspectos que devem ser considerados durante o ajuste deste SGBD (Sistema Gerenciador de Banco de Dados). Este é o primeiro artigo de uma série que discutirá possibilidades de otimização do MySQL, abordando a otimização de consultas, a otimização do SGBD propriamente dito, e o ajuste do Sistema Operacional (SO) e do hardware que suportarão o seu sistema como um todo. Os artigos seguintes apresentarão os detalhes envolvidos em cada uma das partes apresentadas anteriormente.

Para a otimização de um SGBD precisamos eliminar os possíveis problemas de desempenho existentes em todos os níveis do sistema, isto é, precisamos identificar as consultas lentas que eventualmente são submetidas ao banco. Precisamos ainda melhorar as configurações do servidor de banco de dados, do sistema operacional, e finalmente o hardware que suportará toda o sistema. Alguns aspectos da otimização não se aplicam somente ao MySQL, e sim a todos os SGBDs, por isto algumas metodologias apresentadas aqui podem, em alguns casos, ser aplicadas também a outros SGBDs disponíveis no mercado.

Antes de explorarmos os itens apresentados anteriormente é necessário ressaltar que o processo de otimização não é trivial, visto que é preciso medir todos os aspectos do seu sistema para o entendimento preciso do funcionamento da sua aplicação. Assim, pode-se obter o ajuste que seja mais adequado à sua necessidade, por exemplo, o ajuste do SGBD para aplicações de leitura é diferente daquele onde prevalecerão escritas. Além disto, as medições de desempenho do seu sistema são imprescindíveis dado que estas servirão de referência para determinar se uma alteração realizada no SGBD teve efeitos positivos ou não.

O primeiro ponto a ser discutido é a otimização das consultas SQL. A interface da aplicação com o SGBD é feita a partir de consultas SQL, ou seja, esta é a linguagem que permite a extração das informações armazenadas pelo SGBD. Portanto, durante o processo de projeto da sua base de dados, é preciso vislumbrar os tipos de consultas que serão mais comuns e criar a base de forma que o processo de extração de dados seja facilitado. Além disto, é preciso escrever as consultas de forma que as mesmas sejam executadas no menor tempo possível. Mas, também é preciso monitorar as consultas lentas que eventualmente existam, e eliminá-las, seja pela reescrita da consulta ou até mesmo através da alteração da sua aplicação de forma a fazer um acesso mais eficiente ao banco. Este deve ser um ponto de averiguação constante, já que em ambiente onde há um número elevado de consultas e estas consomem muito tempo de serem processadas, isto criará uma deficiência considerável em termos de desempenho. Para a otimização de consultas é preciso entender a forma como as mesmas são processadas pelo MySQL, e assim, deve-se tentar atuar em cada etapa visando a redução do tempo de processamento das mesmas, gerando um ganho global considerável. No próximo serão discutidas as etapas de execução de uma consulta, bem como técnicas para o monitoramento destas consultas e da visualização do plano de execução das mesmas.

Uma vez eliminados os problemas relativos às consultas SQL, pode-se modificar as configurações do MySQL de forma a fazer um uso mais apropriado dos recursos disponíveis no SO, melhorando assim o desempenho do banco. Para isto é preciso entender como o MySQL funciona internamente, isto significa dizer que precisamos entender como o MySQL utiliza memória e disco, bem como quais são os principais parâmetros que podemos alterar para atingir este ganho. O MySQL apresenta um conjunto de ferramentas para o monitoramento do servidor de forma a detectar quais são os gargalos do seu sistema, e assim permitindo a eliminação dos mesmos. Estes aspectos serão abordados em detalhes nos terceiro artigo referente à otimização do MySQL.

Finalmente, precisamos aferir e monitorar o desempenho do SO que suportará todo o sistema, além do hardware e suas configurações. No sistema operacional podemos utilizar recursos mais apropriados para o banco, tais como sistema de arquivos mais eficientes, processos e threads nativas, além da escolha de um SO mais apropriado ao MySQL. Esta escolha pode, em alguns casos, gerar ganhos de desempenho em torno de 50%. Por último, mas não menos importante, é a escolha do hardware adequado. Por exemplo, ao utilizar-se de um processador de 64 bits é possível a utilização de arquivos grandes, além de permitir a alocação de uma quantidade maior de memória. Isto, possibilita a configuração de buffers de memória maiores para o MySQL, melhorando consideravelmente o desempenho. Estes aspectos serão discutidos no último artigo sobre otimização.

Espero que apreciem o assunto de otimização de banco de dados e em breve estará disponível o artigo relativo à otimização de consultas SQL, onde apresentarei todas as ferramentas disponíveis no MySQL para nos auxiliar nesta tarefa.

Abraços e até breve!

Eber M. Duarte.

Eber Duarte é bacharel em Ciência da Computação, pós-graduado em Engenharia Elétrica e MySQL Professional Certified. Trabalha há 3 anos na EAC Software (BH/MG) como Analista e desenvolvedor de sistemas, atuando especialmente no desenvolvimento de sistemas Web. Atualmente, também é consultor e instrutor do banco de dados MySQL. Contatos: eber@eacnet.com.br www.mysqlbrasil.com.br ou www.eacsoftware.com.br