Este artigo tem como principal objetivo exibir as técnicas e práticas para consolidarmos a utilização da “Engenharia de Armazenamento” ou “Motor de Armazenamento” ou ainda, o mais conhecido, “Storage Engine” INNODB. Além de apresentarmos as principais características, trabalharemos também com as configurações de parâmetros referente a habilitação e funcionamento. O INNODB é um “Storage Engine” distribuído e com suporte oferecido pela MySQL AB, mas, desenvolvido pela finlandesa INNOBASE Oy, que mantém um time de desenvolvedores que distribuem o utilitário de backup chamado INNODB Hot Backup, ferramenta esta que não é gratuita.

Nota:

Todo conteúdo que será apresentado, colaborará para continuação dos artigos sobre Stored Procedures ou Procedimentos Armazenados onde falaremos com mais detalhes do isolamento nas transações.

Após ler este artigo você estará apto à:

• Definir a arquitetura do MySQL 5.0 e entender seu funcionamento;
• Citar quais são os principais tipos de Engenharia de Armazenamento do MySQL;
• Descrever as principais características do INNODB;
• Entender como adicionar o INNODB ao seu MySQL e como torná-lo padrão;
• Conceituar e entender o que é um tablespace, bem como o que este armazena;
• Configurar tablespaces em locais personalizados.

Conhecer a arquitetura do MySQL é de fundamental importância para entendermos o que é um “Storage Engine”, sendo assim, antes de qualquer conceituação, vejamos como são e onde ficam as “Engenharias de Armazenamento”. Daqui por diante, trataremos os “Storage Engines” pela sua tradução, Engenharia de Armazenamento.

A arquitetura interna do MySQL é provida de duas camadas principais, a primeira camada, que é a camada de parser e otimização de consultas SQL e a segunda que é onde estão conectadas as Engenharias de Armazenamento de forma modular. Assim como o INNODB, existem outras Engenharias como MyISAM, FEDERATED, MERGE, MEMORY, BDB, ARCHIVE, CSV, EXAMPLES, BLACKHOLE e a novidade do MySQL 6.0, a FALCON, cada qual com suas características próprias. A Figura 01 ilustra as camadas citadas e outros componentes participantes.

Figura 01. Arquitetura do MySQL Server.

As Engenharias de Armazenamento funcionam como plugins, que são, mediante a alguma configuração, adicionados ao servidor MySQL que apresenta uma estrutura 100% modular. São estas engenharias que gerenciam diretamente os arquivos no disco. Cada tabela que é criada utilizando a Engenharia de Armazenamento INNODB, armazena no diretório do banco de dados, sob o diretório de dados (datadir), um arquivo .frm, este por sua vez armazena a estrutura da tabela. Os dados e os índices ficam localizados em um tablespace sobre o datadir. O INNODB tem um tablespace padrão de nome ibdata que é utilizado para armazenar índices e dados de todas as tabelas de todos os bancos de dados contidos em um servidor MySQL. Podemos também, configurar tablespaces em locais personalizados do filesystem ou utilizar um tablespace para cada tabela criada.

Principais Características do INNODB

• Todas as tabelas INNODB são representadas em disco por um arquivo de extensão .frm no diretório da banco de dados e armazena dados e índices em tablespaces. Tais tablespaces são padronizadas e são localizadas no diretório data ou datadir. Também por padrão, todas as tabelas INNODB de um servidor compartilham um mesmo tablespace. O INNODB ainda nos permite configurar que cada tabela tenha sua própria tablespace, evitando problemas com arquivos grandes;
• INNODB dá suporte à transações e é totalmente adequada às propriedades ACID. O multi-versionamento é utilizado para garantir o isolamento de uma transação em relação à outra;
• INNODB provê auto_recovery após um crash ou queda no servidor onde o MySQL roda;
• INNODB tem multi-versionamento e bloqueio em nível de linha, ou seja, caso uma transação A esteja atualizando uma determinada linha de uma tabela, esta será bloqueada até que haja um COMMIT ou ROLLBACK. O multi-versionamento provê à transação uma visão própria do estado do banco de dados, combinado com o bloqueio em nível de linha, provê o mínimo possível de contenções;
• Prevalece a “good query concurrency” ou boa concorrência entre consultas. Isso é estabelecido através do multi-versionamento, que diminui o tempo de contenção, mas, sendo possível o acontecimento de Deadlocks, que são detectados pelo SGBD e a menos transação envolvida é finalizada;
• INNODB tem suporte a integridade referencial, ou seja, tem suporte a criação de FOREIGN KEY, incluindo cascateamentos em DELETE e UPDATE. As propriedades de integridade referencial suportados pelo INNODB são: { RESTRICT | CASCADE | SET NULL | NO ACTION }
• O INNODB trabalha basicamente utilizando dois recursos em disco, os tablespaces que armazenam dados e índices e um conjunto de arquivos de log que registram todas as transações que manipulam dados em tabelas INNODB no servidor MySQL;
• O tablespace do INNODB é portável e pode ser copiado diretamente para outro servidor, juntamente com os arquivos .frm. Alguns pontos devem ser analisados com cuidado para que não haja problemas com o backup físico do banco de dados. Considere fazer um backup lógico com o mysqldump a copiar arquivos;

Controle de transações com COMMIT, ROLLBACK, ROLLBACK TO SAVEPOINT e SAVEPOINT;

Para checarmos quais Engenharias de Armazenamento estão disponíveis atualmente, basta entrarmos com o comando SHOW ENGINES no terminal do Linux/Unix ou no prompt do Windows, através do mysql client, como mostra a Figura 02.

O comando SHOW ENGINES exibe as Engenharias de Armazenamento disponíveis.

Na Figura 02 podemos verificar que nosso servidor MySQL já possui o INNODB habilitado, mas não é a Engenharia de Armazenamento padrão. Através do arquivo my.ini ou my.cnf, na seção [mysqld], que se refere às configurações de inicialização do servidor MySQL, podemos configurar o parâmetro default-storage-engine=Innodb. Após reiniciarmos o servidor MySQL, o INNODB será colocado como a Engenharia de Armazenamento padrão, como mostra a Figura 03.

Figura 03. Habilitamos o INNODB como padrão no servidor MySQL.

Figura 03. Habilitamos o INNODB como padrão no servidor MySQL.

Caso a sua MySQL esteja apontando o INNODB como DISABLED, siga os passos abaixo para habilitar esta Engenharia e coloca-la como padrão, tanto no Linux/Unix quanto no Windows:

1-) Pare o servidor MySQL e abra o arquivos de configuração my.cnf ou my.ini. Localize a linha

skip-innodb e coloque um sinal # para comentar esta linha, como mostra a Figura 04.

Figura 04. No Linux, na maioria das instalações, o INNODB já vem habilitado por padrão.

2-) Caso se tenha intenção de colocar o INNODB como DEFAULT em uma sessão, reinicie o MySQL, entre no MySQL Client e entre com o comando “SET STORAGE_ENGINE =INNODB“, caso queira torná-lo padrão, coloque no grupo de configuração do servidor [mysqld], no arquivo my.cnf ou my.ini a opção default-storage-engine=innodb, salve o arquivo e reinicie o servidor. Uma amostra de como fica o arquivo de configuração com esta opção pode ser conferido no Figura 05.

Figura 05. Adicionando a opção default-storage-engine para definir a Engenharia de Armazenamento padrão.

Tendo reiniciado o MySQL, conecte-se a ele através do mysql client e emita o comando show engines e verifique as mudanças. Um detalhe interessante é que em todas as instalações feitas a partir de um binário ou a partir do *source, o INNODB por padrão está incluso no pacote a menos que se instale desabilitando-o com –without-innodb. Caso sua instalação conte com esta Engenharia e você não deseja utilizá-la, abra o arquivo my.ini ou my.cnf e desabilite a linha skip-innodb tirando o símbolo # anterior a ela.

Ainda utilizando o mysql client, podemos fazer o teste do funcionamento do INNODB, criando uma tabela omitindo a declaração ENGINE. O comportamento do MySQL nesse momento será buscar nas configurações que você acabou de fazer e manter esta tabela com gerencia direta da Engenharia de Armazenamento DEFAULT, que no momento é o INNODB. Veja na Figura 06 esse teste.

Figura 06. Veja que a tabela, criada omitindo a cláusula ENGINE, foi definida como INNODB.

INNODB e TABLESPACE

Citado anteriormente neste artigo, como umas das principais características do INNODB, esta Engenharia de Armazenamento mantém por padrão um tablespace que está localizado no diretório de dados do MySQL, também conhecido por datadir. O tablespace padrão do INNODB, é compartlhado entre todas as tabelas gerenciadas por esta Engenharia de Armazenamento, armazenando os dados e os índices, além de um dicionário de dados próprio.

Mas, o que é um tablespace? Ao “pé da letra”, alguns autores ou traduções de alguns livros tratam o tablespace como um “espaço de tabelas”, mas somente este conceito seria um pouco pobre. Na verdade, um tablespace é uma estrutura que une a memória ao disco, mantendo um paralelo em relação às requisições, sendo que o conceito de tablespace é completamente lógico. O MySQL trata o tablespace com uma área de armazenamento lógico, podendo esta consistir em um ou mais arquivos. Esses arquivos podem ser facilmente configurados para auto_expandir automaticamente.

De outra forma, caso você prefira que cada tabela tenha sua própria tablespace ou mesmo que os arquivos do tablespace não fiquem dentro do datadir, uma das opções é iniciar o MySQL com a opção

–innodb_file_per_table. Esta opção criará para cada nova tabela, mais um arquivo no diretório do banco de dados com a extensão “.ibd”, juntamente com os arquivos “.frm”. Suas tabelas criadas anterior a nova configuração continuarão utilizando o tablespace compartilhado.

Outras propriedades interessantes do tablespace do INNODB é que este ainda possui alguns outros arquivos de log que tratam manter um histórico das transações que modificaram dados, ou seja, sofreram um COMMIT. Os comandos que alteraram dados mais recentemente são mantidos no cache de memória e são escritos no arquivos de log em disco posteriormente. Caso haja um crash em meio a uma modificação, o MySQL inicia um auto-recovery à partir do log.

Configuração Padrão

Após fazer as configurações para habilitar o INODB em seu servidor MySQL, os arquivos serão criados no diretório de dados ou datadir com mostra a Figura 07.

Figura 07. O diretório de dado do MySQL, com o arquivo da tablespace do INNODB (ibdata1).

Veja que no momento representado pela Figura 07, o servidor está com as configurações padrão. Podemos configurar o servidor para utilizar um tabelspace para cada nova tabela, editando o arquivo de configurações my.ini (Windows) ou my.cnf (Unix/Linux). Na seção [mysqld], adicione a opção innodb_file_per_table, como mostra a próxima imagem:

Figura 08. Configurando o INNODB para utilizar innodb_file_per_table no o arquivo my.ini.

Após isto, basta reiniciar o servidor e criar novas tabelas. As diferenças entre um momento e outro pode ser notado no quadro abaixo que referencia o banco de dados test antes e depois das configurações:

Perceba que o arquivo “.ibd”, que é uma tablespace individual para a tabela tbl_teste_2, foi criado no diretório de dados juntamente com os arquivos “.frm” que qualquer tabela criada no MySQL, controlado por qualquer Engenharia de Armazenamento apresenta.

No caso de desfazer a configuração, basta somente retirar a opção que colocamos no arquivo de configurações e reiniciar o servidor. O MySQL cuida de recriar o conteúdo das tablespaces individuais na tablespace compartilhada.

Configurando múltiplos tablespaces em local personalizado

Podemos com facilidade, configurar o INNODB de um servidor MySQL para que este trabalhe com os tablespaces em um local personalizado no sistema de arquivos do sistema operacional, Windows, Linux ou Unix. Através do arquivo de configuração my.ini ou my.cnf, podemos utilizar as seguintes opções na seção [mysqld] :

• innodb_data_file_path, que especificará o nome do datafile, que poderá conter o nome de um ou mais datafiles, como no exemplo abaixo (retirado do manual):

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

O exemplo acima configura um datafile ou arquivo de dados chamado ibdata1, com 10MB de espaço e com a opção de autoextend. Esta propriedade garante que o banco de dados não pare de receber dados, estendendo o tamanho do arquivo de dados de acordo com que este fique cheio.

Outro detalhe a ser observado é que M significa MB (Mega Bytes) e ainda pode ser utilizado a letra G para GB (Giga Bytes).

• innodb_data_home_dir, esta opção nos permite explicitar um local personalizado, aonde serão criados os arquivos de dados ou datafiles. Por exemplo, podemos criar os arquivos datafile1, com 50M e datafile2 com 50M também, mas com a propriedade autoextend com o seguinte exemplo:

[mysqld]
innodb_data_home_dir = D:\tbs
innodb_data_file_path=datafile1:50M;ibdata2:50M:autoextend

Este e vários outro parâmetros que podemos analisar ao abrir o arquivo de configuração my.ini ou my.cnf e considerar o fato de termos que criar pastas ou diretórios para armazenar os arquivos. Abaixo segue a configuração completa, já presente no arquivo de configuração do MySQL, que se encontra comentado por padrão.

Parâmetros de configuração personalizada do INNODB:

Figura 10 – Perceba que os diretórios iblogs e ibdata foram criados antes de reiniciar o MySQL.

Após reiniciar o MySQL, podemos verificar que os arquivos de logs foram criados no diretório iblogs e os tablespaces foram criados no diretório ibdata.

Conclusão

Vimos então que o INNODB provê ao MySQL um ambiente seguro, com logs, integridade referencial, transações locais ou distribuídas (XA Transactions), tablespaces e outras características mais para que o ambiente de produção funcione bem como qualquer outro SGBD.

Agora que já conhecemos as principais características do INNODB, poderemos ver no próximo artigo, as principais características desta Engenharia de Armazenamento em consonância com as propriedades ACID.

Happy MySQL´ing!