Tipos de Tabela do MariaDB - Databases - Software - Computers

Tipos de Tabela do MariaDB

Índice

Tabelas MyISAM
Espaço Necessário para Chaves
Formatos de Tabelas MyISAM
Problemas com Tabelas MyISAM
Tabelas MERGE
Problemas com Tabelas MERGE
Tabelas ISAM
Tabelas HEAP
Tabelas InnoDB
Visão Geral de Tabelas InnoDB
InnoDB no MariaDB Versão 3.23
Opções de Inicialização do InnoDB
Criando Tablespaces no InnoDB
Criando Tabelas InnoDB
Adicionando e Removendo Arquivos de Dados e Log do InnoDB
Fazendo Backup e Recuperando um Banco de Dados InnoDB
Movendo um Banco de Dados InnoDB para Outra Máquina
Modelo Transacional do InnoDB
Dicas de Ajuste de Desempenho
Implementação de Multi-versioning
Estrutura de Tabelas e Índices
Gerenciamento do Espaço de Arquivos e E/S de Disco
Tratando Erros
Restrições em Tabelas InnoDB
Histórico de Alterações do InnoDB
Informações de Contato do InnoDB
Tabelas BDB ou BerkeleyDB
Visão Geral de Tabelas BDB
Instalando BDB
Opções de Inicialização do BDB
Características de Tabelas BDB:
Itens a serem corrigidos no BDB num futuro próximo:
Sistemas operacionais suportados pelo BDB
Restrições em Tabelas BDB
Erros Que Podem Ocorrer Usando Tabelas BDB

No MariaDB Versão 3.23.6, você pode escolher entre 3 formatos de tabelas básicos (ISAM, HEAP e MyISAM). Versões mais novas do MariaDB suportam tipos de tabelas adicionais (InnoDB ou BDB), dependendo de como você o compila. Um banco de dados pode conter tabelas de diferentes tipos.

Ao criar uma nova tabela, você pode dizer ao MariaDB que tipo de tabela criar. O tipo de tabela padrão é, normalmente, MyISAM.

MySQL sempre criará um arquivo .frm para guardar as definições de coluna e tabela. Os índices e dados da tabela serão armazenados em um ou mais arquivos, dependendo do tipo de tabela.

Se você tentar utilziar um tipo de tabela que não está ativa ou não foi compilada com o MySQL, ele irá criar uma tabela do tipo MyISAM. Este comportamento é conveniente quando você quer copiar tabelas entre servidores MariaDB que suportam tipos de tabel;as diferentes. (Talvez o seu servidor master suporte mecanismos de armazenamento tarnsacionais para aumento de segurança, enquanto o servidor slave só utiliza mecanismos de aramazenamento não-transacionais para maior velocidade.)

Esta mudançaautomatica de tipos de tabela podem confuso para novos usuários MySQL. Planejamos arrumar isto introduzindo avisos no protocolo cliente/servidor na versão 4.1 e gerar um aviso quando uma tipo de tabela é automaticamente alterado.

Você pode converter tabelas entre tipos diferentes com a instrução ALTER TABLE. Leia 'Sintaxe ALTER TABLE'.

Note que o MariaDB suporta dois tipos diferentes de tabelas: tabelas seguras com transação (InnoDB and BDB) e tabelas não seguras com tarnsação HEAP, ISAM, MERGE, e MyISAM).

Vantagens de tabelas seguras com transação (TST):

Note que para utilizar tabelas InnoDB você tem que usar pelo menos a opção de inicialização innodb_data_file_path. Leia 'Opções de Inicialização do InnoDB'.

Vantagens de tabelas não seguras com transação (NTST):

Você pode combinar tabelas TST e NTST na mesma instrução para obter o melhor dos dois mundos.

Tabelas MyISAM

Espaço Necessário para Chaves
Formatos de Tabelas MyISAM
Problemas com Tabelas MyISAM

MyISAM é o tipo de tabela padrão no MariaDB Versão 3.23. Ela é baseada no código ISAM e possui várias extensões úteis.

O índice é armazenado em um arquivo com extensão .MYI (MYIndex), e os dados são armazenados em um arquivo com a extensão .MYD (MYData). Você pode verificar/reparar tabelas MyISAM com o utilitário myisamchk. Leia 'Uso do myisamchk para Recuperação em Caso de Falhas'. Você pode compactar tabelas MyISAM com myisampack para utilizar menos espaço. Leia 'myisampack, O Gerador de Tabelas Compactadas de Somente Leitura do MariaDB'. O itens seguintes são novos no MyISAM:

MyISAM também suporta os seguintes itens, os quais o MariaDB estará apto a utilizar em um futuro próximo:

Note que os arquivos de índice são muito menores com MyISAM que com ISAM. Isto significa que MyISAM usará normalmente menos recursos do sistema que ISAM, mas precisará de mais tempo de CPU quando inserir dados em um índice compactado.

As seguintes opções para mysqld podem ser usadas para alterar o comportamento de tabelas MyISAM. Leia 'SHOW VARIABLES'.

Opção Descrição
--myisam-recover=# Recuperação automática de tabelas com falhas.
-O myisam_sort_buffer_size=# Buffer utilizado ao recuperar tabelas.
--delay-key-write=ALL Não desarrega buffers de chaves entre escritas para qualquer tabela MyISAM
-O myisam_max_extra_sort_file_size=# Usada paa ajudar o MariaDB a decidir quando utilzar o método lento, mas seguro, de criação de índices de cache de chaves. Note este parâmetro é dado em megabytes antes da versão 4.0.3 e em bytes a partir desta versão.
-O myisam_max_sort_file_size=# Não utilzia o método rápido de ordenação de índice para criar índices se o arquivo temporário se tornasse maior que o valor dado. Note que este parâmetro é dado em megabytes antes da versão 4.0.3 e em bytes a partir desta versão.
-O bulk_insert_buffer_size=# Tamanho da arvore cache utilizado na otimização de inserções em bloco. Note que este é um limite por thread!

A recuperação automática é ativada se você iniciar o mysqld com --myisam-recover=#. Leia 'Opções de Linha de Comando do mysqld'. Na abertura, é verificado se a tabela está marcada como quebrada ou se a variavel de contagem de abertura para esta tabela não é 0 e você a está executando com --skip-external-locking. Se nenhuma das verificações acima forem verdadeiras o seguinte ocorre.

Se a recuperação não estiver apta a recuperar todas as linhas de uma instrução completada previamente e você não especificou FORCE como uma opção para myisam-recover, então a reparação automática abortará com uma mensagem de erro no arquivo de erros:

Error: Couldn't repair table: test.g00pages

Caso você tenha utilizado a opção FORCE, você irá obter um aviso no arquivo de erro:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note que se você executar uma recuperação automática com a opção BACKUP, você deve ter um script cron que mova automaticamente arquivos com nome como tablename-datetime.BAK do diretório de banco de dados para uma media de backup.

See 'Opções de Linha de Comando do mysqld'.

Espaço Necessário para Chaves

O MariaDB pode suportar diversos tipos de índices, mas o tipo normal é ISAM ou MyISAM. Eles utilizam um índice de árvore-B, e você pode calcular aproximadamente o tamanho do arquivo de índice como (key_length+4)/0.67, somado sobre todas as chaves. (Isto é para o pior caso, quando todas as chaves são inseridas ordenadamente e nós não temos nenhuma chave compactada.)

Índices string são compactados em espaços. Se a primeira parte do índice é uma string, ele também será compactado em prefixo. Compactação em espaço torna o arquivo de índice menor que o indicado acima se a coluna string tem muitos espaços no fim ou é uma coluna VARCHAR não usada em sua totalidade. Compactação de prefixo é usado em chaves que iniciam com uma string. A Compactação de prefixo ajuda se existirem muitas strings com o prefixo idêntico.

Em tabelas MyISAM, você também pode utilizar prefixos em números comprimidos especificando PACK_KEYS=1 quando você cria a tabela. Isto ajuda quando você tem muitas chaves inteiras que têm prefixo idêntico quando o número é armazenado com o byte mais alto primeiro.

Formatos de Tabelas MyISAM

Características de Tabelas Estáticas (Tamanho Fixo)
Características de Tabelas Dinâmicas
Características de Tabelas Compactadas

MyISAM suporta 3 tipos diferentes de tabelas. Dois deles são escolhidos automaticamente dependendo do tipo das colunas que você está usando. O terceiro, tabelas compactadas, só pode ser criado com a ferramenta myisampack.

Quando você cria (CREATE) ou altera (ALTER uma tabela, você pode, para tabelas que não possuem BLOBs, forçar o formato da tabela para DYNAMIC ou FIXED com a opção de tabela ROW_FORMAT=#. No futuro você estará apto a compactar/descompactar tabelas especificando ROW_FORMAT=compressed | default para ALTER TABLE. Leia 'Sintaxe CREATE TABLE'.

Características de Tabelas Estáticas (Tamanho Fixo)

Este é o formato padrão. É usado quando a tabela não contém colunas VARCHAR, BLOB, ou TEXT.

Este é o formato mais e simples e seguro. É também o mais rápidos dos formatos em disco. A velocidade vem da facilidade de se encontrar dados no disco. Procurar por algo com um índice no formato estático é muito simples. Apenas multiplique o número de linhas pelo seu tamanho.

Também, ao varrermos uma tabela, é muito simples ler um número contante de registros a cada leitura de disco.

A segurança é evidenciada se o seu computador falha ao escrever em um arquivo MyISAM de tamanho fixo, caso no qual o myisamchk pode facilemente descobrir onde cada linha começa e termina. Assim, geralmente pode se recuperar todos os registros, exceto os escritos parcialmente. Note que no MariaDB todos os índices sempre podem ser reconstruídos.

Características de Tabelas Dinâmicas

Este formato é usado se a tabela contém colunas VARCHAR, BLOB ou TEXTou se as tabelas são criadas com ROW_FORMAT=dynamic.

Este formato é um pouco mais complexo porque cada linha tem que ter um cabeçalho que diz o seu tamanho. Um registro também pode acabar em mais de um local quando fica maior em uma atualização.

Você pode utilizar OPTIMIZE tabela ou myisamchk para desfragmentar uma tabela. Se você tiver dados estáticos que você acessa/altera demias na mesma tabela, como alguma coluna VARCHAR ou BLOB, pode ser uma boa idéia mover as colunas dinâmicas para outra tabela apenas para evitar fragmentação.

Características de Tabelas Compactadas

Este é um tipo somente leitura que é gerado com a ferramenta opcional myisampack (pack_isam para tabelas ISAM):

Problemas com Tabelas MyISAM

Tabelas MyISAM Corrompidas
O Cliente está usando a tabela ou não a fechou de forma apropriada

O formato do arquivo que o MariaDB usa para armazenar dados tem sido testado extensivamente, mas sempre há circunstâncias que podem fazer com que tabelas de banco de dados sejam corrompidas.

Tabelas MyISAM Corrompidas

Mesmo se o formato MyISAM for muito confiável (todas as alterações na tabela são escritas antes da instrução SQL retornar), você ainda pode ter tabelas corrompidas se algum dos seguintes itens ocorrer:

Os sintomas típicos de uma tabela corrompida são:

Você pode verificar se uma tabela está ok com o comando CHECK TABLE. Leia 'Sintaxe de CHECK TABLE'.

Você pode repara um tabela corrompida com REPAIR TABLE. Leia 'Sintaxe do REPAIR TABLE'. Você também pode repará-la, quando o mysqld não estiver em execução com o comando myisamchk. sintaxe myisamchk.

Se a sua tabela estiver muito corrompida você deve tentar encontrar o razão! Leia Seção A.4.1, 'O Que Fazer Se o MariaDB Continua Falhando'.

Neste caso, a coisa mais importante de saber é se a tabela foi corrompida porque o mysqld foi finalizado (pode se verificar isto facilmente verificando se há uma linha restarted mysqld recente no arquivo de erro do mysql. Se este não é o caso, então você deve tentar fazer um caso de teste disto. Leia Seção E.1.6, 'Fazendo um Caso de Teste Se Ocorre um Corrompimento de Tabela'.

O Cliente está usando a tabela ou não a fechou de forma apropriada

Cada arquivo .MYI do MyISAM tem um contador no cabeçalho que pode ser usado para verificar se uma tabela foi fechada apropriadamente.

Se você obteve o seguinte aviso de CHECK TABLE ou myisamchk:

# clients is using or hasn't closed the table properly

isto significa que este contador eta fora de sincronia. Insto não significa que a tabela está corrompida, mas significa que você poderia pelo menos fazer uma verificação na tabeal para verificar se está ok.

O contador funciona da seguinte forma:

Em outras palavras, o único modo dele ficar fora de sincronia é:

Tabelas MERGE

Problemas com Tabelas MERGE

Tabelas MERGE são novas no MariaDB Versão 3.23.25. O código ainda está em gamma, mas deve estar razoavelmente estável.

Uma tabela MERGE (também conhecida como tabela MRG_MyISAM) é uma coleção de tabelas MyISAM idênticas que podem ser usada como uma. Você só pode fazer SELECT, DELETE, e UPDATE da coleção de tabelas. Se você fizer um DROP na tabela MERGE, você só está apagando a especificação de MERGE.

Note que DELETE FROM tabela_merge usado sem um WHERE só limpará o mapeamento a tabela, não deletando tudo nas tabeals mapeadas. (Planejamos consertar isto na versão 4.1).

Com tabelas idênticas queremos dizer que todas as tabelas são criadas com informações de colunas e chaves idênticas. Você não pode fundir tabelas nas quais as colunas são empacotadas de forma diferente, não tenham as mesmas colunas ou tenham as chaves em ordem diferente. No entanto, algumas das tabelas podem ser compactadas com myisampack. Leia 'myisampack, O Gerador de Tabelas Compactadas de Somente Leitura do MariaDB'.

Ao criar uma tabela MERGE, você obterá uma arquivo de definição de tabela .frm e um arquivo de lista de tabela .MRG. O arquivo .MRG contém apenas a lista de arquivos índices (arquivos .MYI) que devem ser usados como um. Antes da versão 4.1.1, todas as tabelas usadas devem estar no mesmo banco de dados assim como a própria tabela MERGE.

Atualmente você precisa ter os privilégios SELECT, UPDATE e DELETE em tabelas mapeadas para uma tabela MERGE.

Tabelas MERGE podem ajudá-lo a resolver os seguintes problemas:

As desvantagens de tabelas MERGE são:

Quando você cria uma tabela MERGE você deve especificar com UNION=(lista-de-tabelas) quais tabelas você quer usar com uma. Opcionalmente você pode especificar com INSERT_METHOD se você quer que inserções em tabelas MERGE ocorram na primeira ou na última tabela da lista UNION. Se você não especificar INSERT_METHOD ou especificar NO, entaão todos os comandos INSERT na tabela MERGE retornarão um erro.

O seguinte exemplo lhe mostra como utilizaqr tabelas 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 PRIMARY KEY, message CHAR(20))
 TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
SELECT * FROM total;

Note que não criamos uma chave UNIQUE ou PRIMARY KEY na tabela total já que a chave não será única na tabela total.

Note que você também pode manipular o arquivo .MRG diretamente de fora do servidor MySQL:

shell> cd /mysql-data-directory/current-database
shell> ls -1 t1.MYI t2.MYI > total.MRG
shell> mysqladmin flush-tables

Agora você pode fazer coisas como:

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+

Note que a coluna a, declarada como PRIMARY KEY, não é unica, já que tabelas MERGE não podem forca a unicidade sobre um conjunto de tabelas MyISAM selecionadas.

Para remapear uma tabela MERGE você pode fazer o seguinte:

Problemas com Tabelas MERGE

Segue abaixo os problemas conhecidos com tabelas MERGE:

Tabelas ISAM

O tipo de tabela ISAM, obsoleto, desaparecerá na versão 5.0. Ele está incluído no fonte do MariaDB é mas não é mais compilado. MyISAM é uma implementação melhor deste handler de tabela e você deve converter todas as tabelas ISAM para tabelas MySAM o mais rápido possível.

ISAM usa um índice B-tree. O índice é armazenado em um arquivo com a extensão .ISM, e os dados são armazenados em um arquivo com a extensão .ISD. Você pode verificar/reparar tabelas ISAM com o utilitário isamchk. Leia 'Uso do myisamchk para Recuperação em Caso de Falhas'.

ISAM tem os seguintes recursos/propriedades:

A maioria das coisas que são verdadeiras para tabelas MyISAM também são verdadeiras para tabelas ISAM. Leia 'Tabelas MyISAM'. As maiores diferenças comparados a tabelas MyISAM são:

Se você quiser converter uma tabela ISAM em uma tabela MyISAM de forma a se poder utilizar utilitários tais como mysqlcheck, use uma instrução ALTER TABLE:

mysql> ALTER TABLE nome_tabela TYPE = MYISAM;

A versões embutidas do MariaDB não supoortam tabelas ISAM.

Tabelas HEAP

Tabeals HEAP usam índices hash e são armazenadas na memória. Isto as torna muito rápidas, mas se o MariaDB falhar você irá perder todos os dados armazenados nela. HEAP é muito útil para tabelas temporárias!

As tabelas HEAP do MariaDB utilizam hashing 100% dinâmico sem áreas em excesso. Não há espaços extras necessários para listas livres. Tabelas HEAP também não têm problemas com deleção + inserção, o que normalmente é comum em tabelas com hash:

mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down
 -> FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

Aqui seguem algumas coisas que você deve considerar ao utilizar tabelas HEAP:

A memória necessária para uma linha na tabela HEAP é:

SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

sizeof(char*) é 4 em uma máquina de 32 bits e 8 em uma máquina de 64 bits.

Tabelas InnoDB

Visão Geral de Tabelas InnoDB
InnoDB no MariaDB Versão 3.23
Opções de Inicialização do InnoDB
Criando Tablespaces no InnoDB
Criando Tabelas InnoDB
Adicionando e Removendo Arquivos de Dados e Log do InnoDB
Fazendo Backup e Recuperando um Banco de Dados InnoDB
Movendo um Banco de Dados InnoDB para Outra Máquina
Modelo Transacional do InnoDB
Dicas de Ajuste de Desempenho
Implementação de Multi-versioning
Estrutura de Tabelas e Índices
Gerenciamento do Espaço de Arquivos e E/S de Disco
Tratando Erros
Restrições em Tabelas InnoDB
Histórico de Alterações do InnoDB
Informações de Contato do InnoDB

Visão Geral de Tabelas InnoDB

O InnoDB prove o MariaDB com um mecanismo de armazenamento seguro com transações (compatível com ACID) com commit, rollback, e recuperação em caso de falhas. InnoDB faz bloqueio a nível de registro e também fornece uma leitura sem bloqueio em SELECT em um estilo consistente com Oracle. Estes recursos aumentam a performance e a concorrência de multi usuários. Não há a necessidade de escalonamento de bloqueios em InnoDB, pois o bloqueio a nível de registro no InnoDB cabe em um espaço muito pequeno. InnoDB é o primeiro gerenciador de armazenamento no MariaDB que suportam restrições FOREIGN KEY.

InnoDB foi desenvolvido para obter o máximo de performance ao processar grande volume de dados. Sua eficiência de CPU provavelmente não é conseguido por nenhum outro mecanismo de banco de dados relacional com base em disco.

InnoDB é usado na produção de vários sites com banco de dados grandes e que necessitam de alto desempenho. O famoso site de notícias Slashdot.org utiliza InnoDB. Mytrix, Inc. armazena mais de 1 TB de dados em InnoDB, em outro site trata uma carga média de 800 inserções/atualizações por segundo em InnoDB.

Tecnicamente, InnoDB é um banco de dados completo colocado sob o MariaDB. InnoDB tem sua própria área de buffer para armazenar dados e índices na memória principal. InnoDB armazena suas tabelas e índices em um espaco de tabela, o qual pode consistir de vários arquivos (ou partições de disco raw). Isto é diferente, por exemplo de tabelas MyISAM, onde cada tabela é armazenada como um arquivo separado. Tabelas InnoDB podem ser de qualquer tamanho, mesmo em sistemas operacionais onde o sistema de arquivo é limitado a 2 GB.

Você pode encontrar as últimas informações sobre InnoDB em http://www.innodb.com/. A versão mais atualizada do manual do InnoDB sempre é colocada lá.

InnoDB é publicade sob a mesma Licença GNU GPL, Versão 2 (de Junho de 1991) que MySQL. Se você distribuir MySQL/InnoDB, e sua aplicação não satisfaz as restrições da licença GPL, você deve comprar uma lincença comercial MySQL Pro em https://order.mysql.com/?sub=pg&pg_no=1.

InnoDB no MariaDB Versão 3.23

A partir do MariaDB versão 4.0, InnoDB está habilitado por padrão. A seguinte informação só se aplica a série 3.23.

Tabelas InnoDB estão incluídas na distribuição fonte a partir do MariaDB 3.23.34a e está ativado no binário MariaDB -Max da série 3.23. No Windows os binários -Max estão contidos na distribuição padrão.

Se você tiver feito o download de uma versão binária do MariaDB que inclui suporte para InnoDB, simplesmente siga as instruções do manual do MariaDB para instalar um vrsão binária do MariaDB. Se você já tem o MySQL-3.23 instalado, então o modo mais simples de instalar MariaDB -Max é substituir i executável do servidor mysqld com o executável correspondente na distribuição -Max. MariaDB e MariaDB -Max diferem apenas no executável do servidor. Leia 'Instalando uma Distribuição Binária do MariaDB'. Leia 'mysqld-max, om servidor mysqld extendido'.

Para compilar o MariaDB com suoprte a InnoDB, faça o download do MariaDB-3.23.34a ou posterior de https://mariadb.com/ e configure o MariaDB com a opção --with-innodb. Veja o manual MariaDB sobre como instalar uma distribuição fonte. Leia 'Instalando uma distribuição com fontes do MariaDB'.

cd /caminho/para/fonte/mysql-3.23.37
./configure --with-innodb

Para utiliar tabelas InnoDB no MySQL-Max-3.23 você deve especificar parâmetros de configuração na seção [mysqld] do arquivo de configuração my.cnf, ou no Windows opcionalmente em my.ini.

No mínimo, na versão 3.23 você deve especificar innodb_data_file_path onde você especificar o nome e tamanho dos arquivos de dados. Se você não mencionar innodb_data_home_dir em my.cnf o padrão é criar estes arquivoas no diretorio_dados do MariaDB. Se você especificar innodb_data_home_dir como uma string vazia, então você pode dar caminhos absolutos ao seu arquivo de dados em innodb_data_file_path.

O modo mínimo de modificar é de adicionar a seção [mysqld] a linha

innodb_data_file_path=ibdata:30M

mas para obter melhor desempenho é melhor que você especifique as opções como recomendado. Leia 'Opções de Inicialização do InnoDB'.

Opções de Inicialização do InnoDB

Para habilitar tabelas InnoDB no MariaDB versão 3.23, veja 'InnoDB no MariaDB Versão 3.23'.

No MySQL-4.0 não é necessário se fazer nada específico para habilitar tabelas InnoDB.

O comportamento padrão no MariaDB e MariaDB é criar um arquivo ibdata1 auto-extensível de 10 MB no diretório de dados do MariaDB e dois ib_logfiles de 5MB em datadir. (No MySQL-4.0.0 e 4.0.1 o arquivo de dados é 64 MB e não é auto-extensível.)

Note: Para obter uma boa performance você deve definir explicitamente os parâmetros listados nos seguintes exemplos.

Se você não quiser utilizar tabelas InnoDB, você pode adicionar a opção skip-innodb ao seu arquivo de oção do MariaDB.

A partir das versões 3.23.50 e 4.0.2 InnoDB permite que o último arquivo de dados n linha innodb_data_file_path seja especificado como auto-extensível. A sintaxe de innodb_data_file_path é a seguinte:

caminhodados:tamanhoespec;caminhodados:tamanhoespec;...
... ;caminhodados:tamanhoespec[:autoextend[:max:tamanhoespec]]

Se você especificar o último arquivo de dados coma a opção autoextend, InnoDB extenderá o último arquivo de dados se ele ficar sem espaço no tablespace. O aumento é de 8 MB a cada vez. Um exemplo:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

instrui InnoDB a criar apenas um único arquivo de dados com tamanho inicial de 100 MB e que é extendido em blocos de 8 MB quando o espaço acabar. Se o disco ficar cheio você pode querer adicionar outro arquivo de dados a outro disco, por exemplo. Então você tem que olhar o tamanho de ibdata1, arredondar o tamanho para baixo até o múltiplo de 1024 * 1024 bytes (= 1 MB) mais próximo, e especificar o tamanho arredondado de ibdata1 explicitamente em innodb_data_file_path. Depois disto você pode adicionar outros arquivos de dados:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Tenha cuidado com sistema de arquivos onde o tamanho máximo do arquivo é 2 GB. O InnoDB não está ciente disto. Neste sistemas de arquivos você pode querer especificar o tamanho máximo para o arquivo de dados:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

Um exemplo de my.cnf simples. Suponha que você tenha um computador com 128 MB RAM e um disco rígido. Abaixo está o exemplo dos parâmetros de configuração possíveis para my.cnf ou my.ini para o InnoDB. Nós consideramos que você está executando MySQL-Max-3.23.50 ou posterior, our MySQL-4.0.2 ou posterior. Este exemplo serve para a maioria dos usuários, tanto em Unix e Windows, que não querem distribuir arquivos de dados InnoDB e arquivos de log em vários discos. Isto cria um arquivo de dados ibdata1 auto-extensível e dois arquivos de log ib_logfile0 e ib_logfile1 do InnoDB no datadir do MariaDB (normalmente /mysql/data). O arquivo de log ib_arch_log_0000000000 do InnoDB também fica em datadir.

[mysqld]
# Você pode escrever outras opções do servidor MariaDB aqui
# ...
# Arquivos de dados deve estar aptos
# a guardar os seus dados e índices.
# Esteja certo que você tem espaço
# livre suficiente em disco.
innodb_data_file_path = ibdata1:10M:autoextend
# Defina o tamanho da área de buffer com
# 50 - 80 % da meória do seu computador set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M
# Defina o tamanho do seu arquivo log
# para 25 % da tamanho da área de buffer set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M
# Defina ..flush_log_at_trx_commit
# com 0 se você puder perder
# algumas das ultimas trnsações innodb_flush_log_at_trx_commit=1

Check that the MariaDB server has the rights to create files in datadir.

Note que os arquivo de dados devem ser < 2 GB em alguns sistemas de arquivos! O tamanho combinado do arquivos de log devem ser < 4 GB. O tamanho combinado dos arquivos de dados devem ser >= 10 MB.

Quando você criar um banco de dados pela primeira vez, é melhor que você inicie o servidor MariaDB do prompt de comando. Então InnoDB irá imprimir a informação sobre a criação do banco de dados na tela e você poderá ver o que está acontecendo. Veja abaixo na próxima seção como a saída na tela se parece. Por exemplo, no Windows você pode iniciar mysqld-max.exe com:

your-path-to-mysqld\mysqld-max --console

Onde colocar o my.cnf ou my.ini no Windows? As regras para o Windows são o seguinte:

Onde especificar as opções no Unix? No Unix o mysqld lê opções dos seguintes arquivos, se eles existirem, na seguinte ordem:

COMPILATION_DATADIR é o dirertório de dados do MariaDB o qual foi especificado como uma opção do ./configure quando o mysqld foi compilado. (normalmente /usr/local/mysql/data para uma instalação binária ou /usr/local/var para uma instalação fonte).

Se você não estiver certo de onde mysqld lê o seu my.cnf ou my.ini, você pode dar o caminho como a primeira opção de linha de comando para o servidor: mysqld --defaults-file=your_path_to_my_cnf.

O InnoDB forma o caminho do diretório a um arquivo de dados concatenando textualmente innodb_data_home_dir a um nome de arquivo de dados ou caminho em innodb_data_file_path, adicionando uma possível barra ou barra invertida entre eles se for necessário. Se a palavra-chave innodb_data_home_dir não é mencionada em my.cnf, o padrão para ele é o diretório 'ponto' ./ que significa o datadir de MySQL.

Um exemplo de my.cnf avançado. Suponha que você tenha um computador Linux com 2 GB RAM e três disco rígidos de 60 GB (no caminho de diretórios /, /dr2 e /dr3). Abaixo esta um exemplo de parâmetros de configuração possíveis no arquivo my.cnf para o InnoDB.

Note que o InnoDB não cria diretórios: você mesmo deve criá-los. Use o comando mkdir do Unix ou MS-DOS para criar o diretório base do grupo de dados e de log.

[mysqld]
# Você pode escrever outras opções do servidor MariaDB aqui
# ...
innodb_data_home_dir =
# Os arquivos de devem estar aptos a
# guardar seus dados e índices innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
# Defina o tamanho da área de buffer para
# 50 - 80 % da memória do seu computador,
# mas esteja certo, no Linux x86, que o
# total de memória usada é < 2 GB set-variable = innodb_buffer_pool_size=1G set-variable = innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /dr3/iblogs
# .._log_arch_dir deve ser o mesmo
# que .._log_group_home_dir innodb_log_arch_dir = /dr3/iblogs set-variable = innodb_log_files_in_group=3
# Defina o tamanho do arquivo de log
# para cerca de 15% do tamanho da
# área da buffer set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M
# Defina ..flush_log_at_trx_commit com
# 0 se você puder permitir a perda de
# algumas das ultimas transações.
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#innodb_flush_method=fdatasync
#set-variable = innodb_thread_concurrency=5

Note que nós colocamos os dois arquivos de dados em discos diferentes. O InnoDB preencherá o tablespace de tabela formado pelos arquivos de dados de baixo para cima. Em alguns casos ele aumentará o desempenho do banco de dados se todos os dados não forem colocados no mesmo disco físico. Colocar os arquivos de log em discos diferentes dos de dados é geralmente, benéfico para o desempenho. Você pode usar partições de discos raw (dispositivos raw) como arquivos de dados. Em alguns Unixs eles aumentam a E/S. Vejam a seção sobre gerenciamento de espaço de arquivos no InnoDB para saber como especificá-los no my.cnf.

Aviso: no Linux x86 você deve ter cuidado par não definir um uso de memória muito alto. glibc permitirá que o área do processo cresça acima da pilha da thread, o que fará com que o seu servidor falhe. Isto é um risco se o valor de

innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + read_buffer_size) + max_connections * 2 MB

é próximo de 2 GB ou exceda 2 GB. Cada thread usará uma pilha (geralmente 2 MB, mas no binário da MariaDB Foundation é somente 256 KB) e no pior caso usará tmabém sort_buffer + read_buffer_size de memória adicional.

Como sintonizar outros parâmetros do servidor mysqld? Valores comuns que servem para a maioria dos usuários são:

skip-locking set-variable = max_connections=200
set-variable = read_buffer_size=1M set-variable = sort_buffer=1M
# Defina key_buffer com 5 - 50%
# de sua RAM dependendo de quanto
# você usa tabelas MyISAM, mas
# mantenha key_buffer + tamanho da
# área de buffer do InnoDB < 80% de
# sua RAM set-variable = key_buffer=...

Note que alguns parâmetros são dados usando o formato do parâmetro numérico de my.cnf: set-variable = innodb... = 123, outros (parâmetros string e booleanos) com outro formato: innodb_... = ... .

O significado dos parâmetros de configuração são os seguintes:

Opção Descrição
innodb_file_per_table Disponível a partir da versão 4.1.1. Esta opção faz com que o InnoDB armazene cada tabela criada em seu próprio arquivo .ibd. Veja a seção sobre multiplos tablespaces.
innodb_data_home_dir A parte comum do caminho do diretório para todos arquivos de dados InnoDB. Se você não mencionar esta opção em my.cnf, o padrão é o datadir do MariaDB. Você pde especificá-lo também como uma string vazia, e neste caso você poderá utilizar caminhos de arquivos absolutos em innodb_data_file_path.
innodb_data_file_path Caminho para os arquivos de dados individuais e os seus tamanhos. O caminho do diretório completo para cada arquivo de dados é obtido concatenando innodb_data_home_dir ao caminho especificado aqui. O tamanho do arquivo é especificado em megabytes, adicionando o 'M' depois da especificação do tamanho. InnoDB também entende a abreviação 'G', 1 G significa 1024 MB. A partir da versão 3.23.44 você pode definir o tamanho do arquivo maior que 4 GB em sistemas operacionais que seuportam que suportam arquivos grandes. Em alguns sistemas operacionais arquivos devem ser menor que 2 GB. Se você não especificar innodb_data_file_path, o comportamento padrão a partir do versão 4.0 é criar um arquivo de dados ibdata1 de 10 MB auto-extensível. A soma do tamanho dos arquivos devem ser menores que 10 MB.
innodb_mirrored_log_groups Número de cópias idênticas de grupos de log mantidos para os banco de dados. Atualmente deve ser definido com 1.
innodb_log_group_home_dir Caminho do diretório de arquivos de log do InnoDB. Se você não mencionar esta opção no my.cnf o padrão é o datadir do MariaDB.
innodb_log_files_in_group Número de arquivos de log no grupo de log. O InnoDB escreve nos arquivos de modo circular. O valor recomendado aqui é 2. O valor padrão é 2.
innodb_log_file_size Tamanho de cada arquivo de log em um grupo de logs em megabytes. Faixa de valores sensíveis de 1M a 1/n-th do tamanho do área de buffer especificado abaixo, onde n é o número de arquivos de log no grupo. Quanto maior é o valor, menos atividade de descarga é necessária na área de buffer, economizando E/S de disco. Mas arquivos de log maiores também significa que a recuperação será lenta no caso de falhas. O tamanho combinado do arquivo de log deve ser menor que 4GB em comutadores de 32 bits. O padrão é 5M.
innodb_log_buffer_size O tamanho do buffer que o InnoDB utiliza para escrever o log em aruivos no disco. Faixa de valores sensíveis de 1M a 8M. Um buffer de log grande permite aumentar transações para executarem sem precisar de escrever o log em até se fazer um commit da transação. iAlem disso, se você tiver grande transações, fazer um buffer de log maior economiza E/S de disco.
innodb_flush_log_at_trx_commit Normalmente é atribuido 1, significando que em um commit de uma transação o log é descarregado para o disco e as modificações feitas pela transação se tornam permanentes, sobrevivendo a uma falha no banco de dados. Se você estiver disposto a comprometer esta segrança e está executando transações pequenas, você pode definí-lo com 0 ou 2 para reduzir E/S de discos nos logs. O valor 0 significa que o log só é escrito no arquivo e este é descarregado pro disco aproximadamente uma vez por segundo. O valor 2 significa que o log é escrito no arquivo a cada commit, mas o arquivo de log só é descarregado em disco aproximadamente uam vez por segundo. O valor padrão é 1 a partir do MariaDB-4.0.13; antes era 0.
innodb_log_arch_dir O diretório onde arquivos de log totalmente escritos seriam escritos se usarmos arquivamento de log. Atualmente o valor deste parâmetro deve ser definido igual a innodb_log_group_home_dir.
innodb_log_archive Atualmente este valor deve ser definido com 0. Como a recuperação ai partir de um backup deve ser feito pelo MariaDB usando os seus próprios arquivos de log, não há nenhuma necessidade de se arquivos os arquivos de log do InnoDB.
innodb_buffer_pool_size O tamanho do buffer de memória que o InnoDB usa para armazenar dados e índices de suas tabelas. Quanto maior for este valor, menor será a necessidade de E/S de disco para acessar dados na tabela. Em um servidor de banco de dados dedicado você pode definir este parâmetro até 80% do tamanho da memória física da máquina. Não atribua um valor muito alto, pois a competição da memória física pode causar paginação no sistema operacional.
innodb_buffer_pool_awe_mem_mb Tamanho da área de buffer em Mb, se estiver localizado na memória AWE do Windows 32 bits. Deiponível a partir da versão 4.1.0 e relevante apenas no Windows 32 bits. Se o seu Windows suporta mais 4GB de memória, chamado Address Windowing Extensions, você pode alolcar a área de buffer do InnoDB em uma memória física AWE usando este parâmetro. O maior valor possível para isto é 64000. Se este parâmetro for especificado, então innodb_buffer_pool_size é a janela no espaço de endereço de 32 bits do mysqld onde o InnoDB mapeia aquela memória AWE. Um bom valor para innodb_buffer_pool_size é 500M.
innodb_additional_mem_pool_size Tamanho do pool da memória que o InnoDB utiliza para armazenar informações de dicionário de dados e outras estruturas de dados internas. Um bom valor aqui pode ser 2M, mas quanto mais tabelas você tiver em sua aplicação, mais você precisará alocar aqui. Se o InnoDB ficar sem memória neste pool, ele l começara a alocar memória do sistema operacional e a escrever mensagens de aviso no log de erro do MariaDB.
innodb_file_io_threads Número de threads de E/S de arquivos no InnoDB. Normalmente ele deve ser 4, mas no Windows E/S de disco pode se beneficiar de um número maior.
innodb_lock_wait_timeout Tempo limite em segundos que uma transação InnoDB pode esperar por uma trava antes de fazer um roll back. InnodDB detecta automaticamente deadlocks de transações em sua própria tabela bloqueada e faz um roll back da transação. Se você utiliza o comando LOCK TABLES, ou outro mecanismo de armazenamento seguro com transações diferente do InnoDB na mesma transação, então um deadlock pode crescer, o que não seria notificado pelo InnoDB. Nestes casos o tempo limite é útil para resolver a situação.
innodb_flush_method (Disponível a partir da versão 3.23.40.) O valor padrão para este parâmetro é fdatasync. Outra opção é O_DSYNC.
innodb_force_recovery Aviso: esta opção só deve ser definida em uma situação de emergência quando você quiser um dump de suas tabelas em um banco de dados corropido! Os valores possíveis são de 1 - 6. Veja abaixo na seção 'Forçando a recuperação' sobre o significado dos valores. Como uma medida segura o InnoDB previne que um usuário modifique os dados quando esta opção é > 0. Esta opção está disponível a partir da versão 3.23.44.

Criando Tablespaces no InnoDB

Se Alguma Coisa Der Errado Na Criação Do Banco de Dados

Suponha que você instalou o MariaDB e editou my.cnf para que ele contenha os parâmetros de configuração do InnoDB necessários. Antes de iniciar o MariaDB você deve verificar se os diretórios que você especificou para os arquivos de dados e de log do InnoDB existem e se você tem direito de acesso a estes diretórios. InnoDB não pode criar diretórios, apenas arquivos. Verifique também se você têm espaço suficiente em disco para or arquivos de dados e de log.

Quando iniciar o MySQL, InnoDB começara criando os seus arquivos de dados e de log. O InnoDB irá imprimir algo como o mostrado a seguir:

~/mysqlm/sql > mysqld InnoDB: The first specified datafile /home/heikki/data/ibdata1
did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728
InnoDB: Database physically writes the file full: wait...
InnoDB: datafile /home/heikki/data/ibdata2 did not exist:
new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist:
new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist:
new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist:
new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880
InnoDB: Started mysqld: ready for connections

Um novo banco de dados InnoDB foi criado. Você pode se conectar ao servidor MariaDB com o programa cliente MariaDB de costume como MariaDB. Quando você finaliza o servidor MariaDB com mysqladmin shutdown, a saída do InnoDB será como a seguinte:

010321 18:33:34 mysqld: Normal shutdown
010321 18:33:34 mysqld: Shutdown Complete InnoDB: Starting shutdown...
InnoDB: Shutdown completed

Agora você pode ver os diretórios de arquivos de dados e logs e você verá os arquivos criados. O diretório de log também irá conter um pequeno arquivo chamado ib_arch_log_0000000000. Este arquivo foi resultado da criação do banco de dados, depois do InnoDB desligar o arquivamento de log. Quando o MariaDB for iniciado novamente, a saída será a seguinte:

~/mysqlm/sql > mysqld InnoDB: Started mysqld: ready for connections

Se Alguma Coisa Der Errado Na Criação Do Banco de Dados

Se o InnoDB imprmir um erro do sistema operacional em uma operação de arquivo normalmente o problema é um dos seguintes:

Se ocorrer algum erro na criação de banco de dados InnoDB, você deve deletar todos os arquivos criados pelo InnoDB. Isto significa todos os arquivos de dados, de log, o pequeno log arquivado e no caso de você já ter criado algumas tableas InnoDB, delete também os arquivos .frm correspondentes a estas tabelas do diretório de banco de dados do MariaDB. Então você pode tentar criar o banco de dados InnoDB novamente.

Criando Tabelas InnoDB

Convertendo Tabelas MyISAM para InnoDB
Restrições FOREIGN KEY
Multiplos tablespaces - colocando cada tabela em seu próprio arquivo .ibd

Suponha que você tenha iniciado o cliente MariaDB com o comando mysql test. Para criar uma tabela no formato InnoDB você deve especificar TYPE = InnoDB no comando SQL de criação da tabela:

CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

Este comando SQL criará uma tabela e um índice na coluna A no tablespace do InnoDB consistindo dos arquivos de dados que você especificou em my.cnf. Adicionalmente o MariaDB criará um arquivo CUSTOMER.frm no diretório de banco de dados test do MariaDB. Internamente, InnoDB adicionará ao seu próprio diretório de dados uma entrada para tabela 'test/CUSTOMER'. Assim você pode criar uma tabela de mesmo nome CUSTOMER em outro banco de dados do MariaDB e os nomes de tabela não irão colidir dentro do InnoDB.

Você pode consultar a quantidade de espaço livre no tablespace do InnoDB utilizabdo o comando de status da tabela do MariaDB para qualquer tabela que você criou com TYPE = InnoDB. Então a quantidade de espaço livre no tablespace aparecerá na seção de comentário da tabela na saída de SHOW. Um exemplo:

SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'

Note que a estatísticas SHOW dada sobre tabelas InnoDB são apenas aproximadas: elas não são usadas na otimização SQL. Tamanho reservado de tabelas e índices em bytes estão acurado.

Convertendo Tabelas MyISAM para InnoDB

O InnoDB não tem uma otimização especial para criação de índices separados. Assim não há custo para exportar e importar a tabela e criar índices posteriormente. O modo mais rápido de se alterar uma tabela para InnoDB é fazer as inserções diretamente em uma tabela InnoDB, isto é, use ALTER TABLE ... TYPE=INNODB, ou crie uma tabela InnoDB vazia com definições idênticas e insira os registro com INSERT INTO ... SELECT * FROM ....

Para obter um melhor controle sobre o processo de inserção, pode ser bom inserir grandes tabelas em pedaços:

INSERT INTO newtable SELECT * FROM oldtable
 WHERE yourkey > something AND yourkey <= somethingelse;

Depois de todos os dados serem inseridos você pode renomear as tabelas.

Durante a canversão de tabelas grandes você deve configurar á área de buffer com um tamanho grande para reduzir a E/S de disco. Não deve ser maior que 80% da memória física. Você deve configurar o arquivo de log do InnoDB grande, assim como o buffer de log.

Certifique-se de que você não irá ocupar todo o tablespace: tabelas InnoDB gasta muito mais espaço que tabelas MyISAM. Se um ALTER TABLE ficar sem espaço, ele irá iniciar um rollback, que pode levar horas se ele estiver no limite de disco. Para inserções, o InnoDB utiliza o buffer de inserção para fundir registros de índices secundários a índices em grupos. Isto economiza muito a E/S de disco. No rollback tal mecanismo não é usado e o rollback pode demorar 30 vezes mais que a inserção.

No caso de um rollback demorado, se você não tiver dados valiosos e seu banco de dados, é melhor que você mate o processo de banco de dados, delete todos os arquivos de dados e de log do InnoDB e todos os arquivos de tabela .frm e inicie o seu trabalho de novo, do que esperar que milhões de E/Ss de disoc de complete.

Restrições FOREIGN KEY

A partir da versão 3.23.43b, o InnoDB disponibiliza restrições de chaves estrangeiras. O InnoDB é o primeiro tipo de tabela da MySQL, que permite definir restrições de chaves estrangeiras para guardar a integridade dos seus dados.

A sintaxe da definição das restriçõess de chaves estrangeiras no InnoDB:

[CONSTRAINT [symbol]] FOREIGN KEY (index_col_name, ...)
 REFERENCES nome_tabela (index_nome_coluna, ...)
 [ON DELETE {CASCADE | SET NULL | NO ACTION
 | RESTRICT}]
 [ON UPDATE {CASCADE | SET NULL | NO ACTION
 | RESTRICT}]

Ambas as tabelas devem ser do tipo InnoDB, na tabela deve existir um índice onde as colunas de chaves estrangeiras listadas como as PRIMEIRAS colunas e na tabela indicada deve haver um índice onde as colunas indicadas são listadas como as PRIMEIRAS colunas e na mesma ordem. O InnoDB não cria índices automaticamente em chaves estrangeiras para chaves referênciadas: você tem que criá-las explicitamente. Os índices são necessários para verificação de chaves estrangeiras para ser rápido e não exigir a varredura da tabela.

Colunas correspondentes nas chaves estrangeiras e a chave referenciada devem ter tipos de dados internos parecidos dentro do InnoDB para que possam ser comparados sem uma conversão de tipo. O tamanho e a sinalização de tipos inteiros devem ser o mesmo. O tamanho do tipos string não precisam ser o mesmo. Se você especificar uma ação SET NULL, esteja certo de que você não declarou as colunas na tabela filha como NOT NULL.

Se o MariaDB retornar o erro de número 1005 de uma instrução CREATE TABLE, e a string de mensagem de erro se referir ao errno 150, então a criação da tabela falhou porque um restrição de chaves estrangeiras não foi formada corretamente. Similarmente, se uma ALTER TABLE falhar e se referir ao errno 150, sgnifica que um definição de chave estrangeira foi formada incorretamente na tabela alterada. A partir da versão 4.0.13, você pode usar SHOW INNODB STATUS para ver uma explicação detalhada do ultimo erro de chave estrangeira do InnoDB no servidor.

A partir de versão 3.23.50, InnoDB não verifica restrições de chaves estrangeiras naqueles valores de chaves estrangeiras ou chaves referênciadas que contenham uma coluna NULL.

Um desvio do padrão SQL: se na tabela pai existirem diversos registros têm o mesmo valor de chave referência, então o InnoDB atua na verificação da chave estrangeira como o outro registro pai como se o mesmo valor de chave não existisse. Por exemplo, se você tiver definido uma restrição de tipo RESTRICT, e existir um registro filho com diversos registros pais, o InnoDB não permite a deleção de qualquer um dos registros pais.

A partir da versão 3.23.50, você também pode associar a cláusula ON DELETE CASCADE ou ON DELETE SET NULL com a restrição de chave estrangeira. Opções correspondentes do ON UPDATE estão disponíveis a partir da versão 4.0.8. Se ON DELETE CASCADE for especificado, e um registro na tabela pai for deletado, então o InnoDB automaticamente também deleta todos aqueles registros na tabela filha cujos valores de chaves estrangeiras são iguais ao valor da chave referênciada no registro pai Se ON DELETE SET NULL for especificado, os registros filhos são automaticamente atualizados e assim as colunas na chave estrangeira são definidas com o valor NULL do SQL.

Um desvio dos padrões SQL: se ON UPDATE CASCADE ou ON UPDATE SET NULL retornam para atualizar a MESMA TABELA que ja tenha sido atualizada durante o processo cascata, ele atua como RESTRICT. Isto é para prevenirloops infinitos resultantes de atualizações em cascata. Um ON DELETE SET NULL auto referêncial, por outro lado, funciona desde a versão 4.0.13. ON DELETE CASCADE auto referêncial já está funcionando.

Um exemplo:

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
 FOREIGN KEY (parent_id) REFERENCES parent(id)
 ON DELETE SET NULL
) TYPE=INNODB;

Um exemplo complexo:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
 price DECIMAL,
 PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
 PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
 product_category INT NOT NULL,
 product_id INT NOT NULL,
 customer_id INT NOT NULL,
 PRIMARY KEY(no),
 INDEX (product_category, product_id),
 FOREIGN KEY (product_category, product_id)
 REFERENCES product(category, id)
 ON UPDATE CASCADE ON DELETE RESTRICT,
 INDEX (customer_id),
 FOREIGN KEY (customer_id)
 REFERENCES customer(id)) TYPE=INNODB;

A partir da versão 3.23.50 o InnoDB lhe permite adicionar novas restriçoões de chaves estrangeiras a uma tabela.

ALTER TABLE seunomedetabela ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES anothertablename(...)
[on_delete_and_on_update_actions]

Lembre-se de criar os índices necessários primeiro.

A partir da versão 4.0.13, o InnoDB suporta

ALTER TABLE suatabela DROP FOREIGN KEY id_chave_estrangeira_gerada_internamente

Você tem que usar SHOW CREATE TABLE para daterminar as id's de chaves estrangeiras geradas internamente quando você apaga uma chave estrangeira.

Na versão anterior a 3.23.50 do InnoDB, ALTER TABLE ou CREATE INDEX não devem ser usadas em conexões com tabelas que têm restrições de chaves estrangeiras ou que são referênciadas em restrições de chaves estrangeiras: Qualquer ALTER TABLE remove todas as restrições de chaves estrangeiras definidas na tabela. Você não deve utilizar ALTER TABLE para tabela referenciadas também, mas utilizar DROP TABLE e CREATE TABLE para modifcar o esquema. Quando o MariaDB faz um ALTER TABLE ele pode usar internamente RENAME TABLE, e isto irá confundir a restrição de chave estrangeira que se refere a tabela. Uma instrução CREATE INDEX é processada no MariaDB como um ALTER TABLE, e estas restrições também se aplicam a ele.

Ao fazer a verificação de chaves estrangeiras, o InnoDB define o bloqueio a nivel de linhas compartilhadas em registros filhos e pais que ele precisa verificar. O InnoDB verifica a restrição de chaves estrangeiras imediatamente: a verificação não é aplicada no commit da transaçao.

Se você quiser ignorar as restrições de chaves estrangeiras durante, por exemplo um operação LOAD DATA, você pode fazer SET FOREIGN_KEY_CHECKS=0.

O InnoDB lhe permite apagar qualquer tabela mesmo que ela quebre a restrição de chaves estrangeira que referencia a tabela. Ao apagar um tabela restrição que é definida na instrução create também é apagada.

Se você recriar uma tabela que foi apagada, ela deve ter uma definição de acordo com a restrição de chaves estrangeiras que faz referência a ela. Ela deve ter os nomes e tipos de colunas corretor e deve ter os índices na chave referenciada como indicado acima. Se esta condição não for satisfeita, o MariaDB retornará o erro de número 1005 e se refere ao errno 150 na string de mensagem de erro.

A partir da versão 3.23.50 o InnoDB retorna da definição de chave estrangeira de uma tabela quando você chama

SHOW CREATE TABLE seunometabela

Assim o mysqldump também produz as difinições de tabelas corretas no arquivo dump e não se esquece das chaves estrangeiras.

Você também pode listar as restrições de chaves estrangeiras de uma tabela T com

SHOW TABLE STATUS FROM seubancodedados LIKE 'T'

As restrições de chaves estrangeiras são listadas no comentário da tabela impresso na saída.

Multiplos tablespaces - colocando cada tabela em seu próprio arquivo .ibd

NOTA IMPORTANTE: se você atualizar para o InnoDB-4.1.1 ou posterior, será difícil retornar a versão 4.0 ou 4.1.0! Isto ocorre porque versões anteriores do InnoDB não permitem vários tablespaces. Se você precisar retornar para a versão 4.0, você deverá fazer um dump das tabelas e recriar todo o tablespace do InnoDB. Se você não tiver criado novas tabelas InnoDB em versões posteriores a 4.1.1, e e precisar retornar a versão anterior rapidamente, você pode fazer um downgrade direto para a versão 4.0.18 do MariaDB, ou outra da série 4.0. Antes de fazer o downgrade diretamente para a versão 4.0.xx, você terá que finalizar todas as conexões a versões >= 4.1.1 e deixar o mysqld to run purge and the insert buffer merge to completion, so that SHOW INNODB STATUS shows the Main thread in the state waiting for server activity. Then you can shut down mysqld and start 4.0.18 or later in the 4.0 series. A direct downgrade is not recommended, however, because it is not extensively tested.

Starting from MySQL-4.1.1, you can now store each InnoDB table and its indexes into its own file. This feature is called multiple tablespaces, because then each table is stored into its own tablespace.

You can enable this feature by putting the line

innodb_file_per_table

in the [mysqld] section of my.cnf. Then InnoDB stores each table into its own file tablename.ibd in the database directory where the table belongs. This is like MyISAM does, but MyISAM divides the table into a data file tablename.MYD and the index file tablename.MYI. For InnoDB, both the data and the indexes are in the .ibd file.

If you remove the line innodb_file_per_table from my.cnf, then InnoDB creates tables inside the ibdata files again. The old tables you had in the ibdata files before an upgrade to >= 4.1.1 remain there, they are not converted into .ibd files.

InnoDB always needs the system tablespace, .ibd files are not enough. The system tablespace consists of the familiar ibdata files. InnoDB puts there its internal data dictionary and undo logs.

You CANNOT FREELY MOVE .ibd files around, like you can MyISAM tables. This is because the table definition is stored in the InnoDB system tablespace, and also because InnoDB must preserve the consistency of transaction id's and log sequence numbers.

You can move an .ibd file and the associated table from a database to another (within the same MySQL/InnoDB installation) with the familiar RENAME command:

RENAME TABLE olddatabasename.tablename TO newdatabasename.tablename;

If you have a clean backup of an .ibd file taken from the SAME MySQL/InnoDB installation, you can restore it to an InnoDB database with the commands:

ALTER TABLE tablename DISCARD TABLESPACE; /* CAUTION: deletes the current .ibd file! */
<put the backup .ibd file to the proper place>
ALTER TABLE tablename IMPORT TABLESPACE;

Clean in this context means:

You can make such a clean backup .ibd file with the following method.

Another (non-free) method to make such a clean .ibd file is to

It is in the TODO to allow moving clean .ibd files also to another MySQL/InnoDB installation. That requires resetting of trx id's and log sequence numbers in the .ibd file.

Adicionando e Removendo Arquivos de Dados e Log do InnoDB

A partir da versão 3.23.50 e 4.0.2 você pode especificar o último arquivo de dados InnoDB com autoextend. De forma alternativa, pode se aumentar o seu tablespace especificando um arquivo de dados adicional. Para fazer isto você tem que finalizar o servidor MySQL, edite o arquivo my.cnf adicionando um novo arquivo de dados no final de innodb_data_file_path, e entao iniciar o servidor MariaDB de novo.

Atualmente você não pode remover um arquivo de dados do InnoDB. Para reduzir o tamanho de seu banco de dados você tem que utilizar mysqldump para fazer um dump de todas as suas tabelas, criar um novo banco de dados e importar suas tabelas para um novo banco de dados.

Se você quiser alterar o número ou o tamanho do seu arquivo de log InnoDB, você tem que finalizar o MariaDB e certificar que ele finalizou sem erros. Copie então o arquivo de log antigo em um local seguro apenas para o caso de algo der errado ao finalizar e você precisar recuperar o banco de dados. Delete os arquivos de log antigo do diretório de arquivos de logm edite o my.cnf e inicie o MariaDB novamente. O InnoDB lhe dirá no inicio que ele está criando novos arquivos de log.

Fazendo Backup e Recuperando um Banco de Dados InnoDB

Forçando a recuperação
Ponto de Verificação

A chave para um gerenciamento seguro de banco de dados é tirar backups regularmente.

O InnoDB Hot Backup é uma ferramenta de backup online que você pode utilizar pra fazer backup dos seus banco de dados InnoDB enquanto ele está executando. O InnoDB Hot Backup não exige que você finalize o seu banco de dados e não realiza nenhum bloqueio ou cria disturbio no seu processo normal de banco de dados. O InnoDB Hot Backup é uma ferramenta adcional paga e que não está incluída na distribuição padrão do MariaDB. Veja o site do InnoDB Hot Backup http://www.innodb.com/manual.php para informações detalhadas e telas do produto.

Se você puder desativar o servidor MySQL, então, para fazer um backup de 'binario' do seu banco de dados você deve fazer o seguinte:

Além de fazer um backup de binário descrito acima, você também deve fazer um dump da sua tabela com mysqldump. A razão para se fazer isto é que um arquivo binário pode ser corrompido cem você perceber. Dumps de tabelas são armazenados em um arquivo texto legível e muito mais simples que arquivos binários de banco de dados. Ver tabelas corropidas através de arquivos de dump é mais fácil e, como o seu formato é simples, a chance dos dados se corromperem seriamente são bem menores.

Uma boa idéia é fazer dumps ao mesmo tempo que você faz o backup de binário do seu banco de dados. Você tem que fechar todos os bancos de dados nos clientes para ter uma cópia consistente de todas as suas tabelas em seu dump. Então você pode fazer o backup de binário e você terá uma cópia consistente de seu banco de dados em dois formatos.

Para se poder recuperar o seu banco de dados InnoDB através do backup de binário descrito acima, você tem que executar o seu banco de dados MariaDB com o sistema de log geral e o arquivamento de log do MariaDB ligado. Com sistema de log geral nós queremos dizer o mecanismo de log do servidor MariaDB que é independente dos logs do InnoDB.

Para recuperação de falhas do seu processo do servidor MySQL, a única coisa que você deve fazer é reiniciá-lo. InnoDB verificará automaticamente os logs e realizará um roll-forward do banco de dados para o situação atual. O InnoDB fará automaticamente um roll back de transações sem commit existentes no momento da falha. Durante a recuperação, InnoDB irá imprimir algo como o seguinte:

~/mysqlm/sql > mysqld InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed InnoDB: Rollback of uncommitted transactions completed InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed InnoDB: Started mysqld: ready for connections

Se o seu banco de dados for corrompido ou o seu disco falhar, você terá que fazer recuperações de um backup. no caso de dados corropidos, você deve primeiro encontrar um backup que não está corrompido. A partir de um backup, faça a recuperação a partir do arquivo de logs gerais do MariaDB de acordo com a instrução no manual do MariaDB.

Forçando a recuperação

Se ocorre o corrompimento de uma página do banco de dados, você pode desejar fazer um dump de suas tabelas no banco de dados com SELECT INTO OUTFILE, e normalmente a maioria dos dados estará intacto e correto. Mas o corrompimento pode fazer com que SELECT * FROM table, ou operações de background do InnoDB falhe ou apresentem avisos, ou até mesmo a recuperação roll-forward do InnoDB falhe. A partir do InnoDB 3.23.44, existe uma opção do my.cnf com a qual você pode forçar o InnoDB a inicializar, e você também pode prevenir que operações de background sejam executadas, e assim você poderá fazer um dump de suas tabelas. Por exemplo, você pode configurar

set-variable = innodb_force_recovery = 4

no my.cnf.

As alternativas para innodb_force_recovery estão listadas abaixo. O banco de dados não deve ser usado com estas opções! Como medida de segurança o InnoDB previne um usuário de fazer um INSERT, UPDATE, ou DELETE quando esta opção é > 0.

A partir da versão 3.23.53 e 4.0.4, você tem permissão de se fazer um DROP ou CREATE de uma tabela mesmo se a recuperação forçada está sendo usada. Se você sabe que determinada tabela está causando uma falha no rollback, você pode deletá-la. Você pode usar isto também para para um rollback em execução causado por uma falha importanta ou ALTER TABLE. Você pode matar o processo mysqld e usar a opção do my.cnf innodb_force_recovery=3 para trazer o seu banco de dados sem o rollback. Apague então a tabela que está causando o rollback.

Um número maior abaixo significa que todas as precauções de números menores estão incluídas. Se você puder fazer um dump de todas as suas tabelas com uma opção de no máximo 4, então você está relativamente seguro que apenas alguns dados em paginas individuais corrompidas são perdidos. A opção 6 é mais dramática, porque páginas de bancos de dados são deixadas e um estado obsoleto, que podem introduzir mais corrompimento em árvores-B e outras estruturas de banco de dados.

Ponto de Verificação

O InnoDB implementa um mecanismo de ponto de verificação chamado fuzzy checkpoint. O InnoDB descarregará páginas de banco de dados modificados da áres de buffer em pequenos grupos. Não há necessidade de descarregar a área de buffer em um único grupo, o que iria, na prática, para o processamento da instrução SQL do usuário por um instante.

Na recuperação de falhas o InnoDB procura por um rotulo de ponto de verificação escrito nos arquivos de log. Ele sabe que todas as modificações no banco de dados anteriores ao rótulo já estão presentes na imagem em disco do banco de dados. O InnoDB varre os arquivos de log a partir do ponto de verificação apicando as modificações registradas no banco de dados.

O InnoDB escreve no arquivo de log de um modo circular. Todas as modificações efetivadas que tornam a pagina de banco de dados na área de buffer diferente das imagens em disco devem estar disponíveis no arquivo de log no caso do InnoDB precisar fazer uma recuperação. Isto significa que quando O InnoDB começa a reutilizar um arquivo de log no modo circular, ele deve estar certo de que imagens em disco da pagina de banco de dados já contém as modificações registradas no arquivo de log que o InnoDM irá utilizar. Em outras palavras, o InnoDB precisa criar um ponto de verificação e geralmente isto envolve descarga de páginas de banco de dados modificados para o disco.

O exposto acima explica o porque que fazer o seu arquivo de log muito maior pode economizar E/S de disco com pontos de verificação. Pode fazer sentido configurar o tamanho do arquivo de log tão grande quanto a àrea de buffer ou mesmo maior. O problema com arquivos de log grandes é que a recuperação de falhas pode ser mais demorada pois haverá mais itens a se aplicar ao banco de dados.

Movendo um Banco de Dados InnoDB para Outra Máquina

No Windows o InnoDB armazena os nomes de banco de dados e tabelas internamente sempre em letras minúsculas. Para mover bancos de dados em um formato binário do Unix para o Windows ou do Windows para o Unix você deve ter todas os nomes de tabelas e banco de dados em letras minúscula. Um modo conveniente de fazer isto é adicionar no Unix a linha

set-variable=lower_case_table_names=1

na seção [mysqld] de seu my.cnf antes de você iniciar a criação de sua tabela. no Windows o valor 1 é o padrão.

Arquivos de dados e log do InnoDB são binários compatíveis com todas as plataformas se o formato do número de ponto flutuante nas máquinas é o mesmo. Você pode mover um banco de dados InnoDB simplesmente copiando todos os arquivos relevantes, os quais nós já listamos na seção anterior sobre backup do banco de dados. Se o formato de ponto flutuante nas máquinas são diferentes mas você não utiliza tipos de dados FLOAT ou DOUBLE em suas tabelas então o procedimento é o mesmo; apenas copie os arquivos relevantes. Se os formatos são diferentes e suas tabelas contenham dados de ponto flutuante, você tem que utilizar mysqldump e mysqlimport para mover estas tabelas.

Uma dica de desempenho é desligar o modo auto-commit quando você importa dados em seu banco de dados, assumindo que o seu tablespace tem espaço suficiente para o grande segmento de roolback que a transação de importação ira gerar. Só faça o commit depois de importar toda a tabela ou um segmento de uma tabela.

Modelo Transacional do InnoDB

InnoDB e SET ... TRANSACTION ISOLATION LEVEL ...
Leitura Consistente sem Lock
Lock de Leitura SELECT ... FOR UPDATE e SELECT ... LOCK IN SHARE MODE
Lock da Chave Seguinte: Evitando Problemas com Fantasmas
Locks Definidos por Diferentes Instruções SQL no InnoDB
Detecção de Deadlock e Rollback
Um Exemplo de Como a Leitura Consistente Funciona no InnoDB
Como lidar com deadlocks?

No modelo transacional do InnoDB o objetivo é combinar as melhores propriedades de um banco de dados multi-versioning a um bloqueio de duas fases tradicional. O InnoDB faz bloqueio a nivel de registro e execulta consultas como leitura consistente sem bloqueio, por padrao, no estilo do Oracle. A tabela travada no InnoDB é armazenada com tanta eficiência em relação ao espaço que a escala de bloqueio não é necessária: normalmente diversos usuários tem permissão para bloquear todos os registros no banco de dados, ou qualquer subconjunto aleatório de regitsros, sem que o InnoDB fique sem memória.

No InnoDB todas as atividades de usuários acontecem dentro de transações. Se o modo autocommit é usado no MySQL, então cada instrução SQL forma uma única transação. O MariaDB sempre inicia uma nova conexão com o modo autocommit ligado.

Se o modo autocommit é desligado com SET AUTOCOMMIT = 0, então podemos achar que um usuário sempre tem uma transação aberta. Se for executada uma instrução SQL COMMIT ou ROLLBACK, a transação atual é finalizada e uma nova é iniciada. Ambas instruções liberarão todas as travas do InnoDB que foram definidas durante a transação atual. Um COMMIT significa que as alterações feitas na transação atual se tornam permanentes e visíveis a outros usuários. Uma instrução ROLLBACK, por outro lado, cancela todas as modificações feitas pela transação corrente.

Se a conexão tem AUTOCOMMIT = 1, então o usuário pode ainda relaizar uma transação multi-instrução iniciando-a com START TRANSACTION ou BEGIN e finalizando-a com COMMIT ou ROLLBACK.

InnoDB e SET ... TRANSACTION ISOLATION LEVEL ...

Em termos de níveis de isolamento transacional SQL-92, o padrão InnoDB é REPEATABLE READ. A partir da versão 4.0.5, InnoDB oferece todos os níveis de isolamento transacional diferentes descritos pelo padrão SQL-92. Você pode definir o nível de isolamento padrão para todas as conexões na seção [mysqld] do my.cnf:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
 | REPEATABLE-READ | SERIALIZABLE}

Um usuário pode alterar o nível de isolamento de um única seção ou todas as próximas seções com a instrução SQL SET TRANSACTION. Sua sintaxe é a sseguinte:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
 {READ UNCOMMITTED | READ COMMITTED
 | REPEATABLE READ | SERIALIZABLE}

Note que não há hífens no nome dos níveis na sintaxe SQL.

O comportamento padrão é definir o nível de isolamento para a próxima transação (não iniciada). Se você especificar a palavra chave GLOBAL na instrução acima, ela determinará o nível de isolamento globalmente para todas as novas conexões criadas a partir deste ponto (mas não conexão exitentes). Você precisa do privilégio SUPER para fazer isto. Usar a palavra chave SESSION difine a transação padrão para todas as transações realizadas futuramente na conexão atual. Qualquer cliente é livre para alterar o nível de isolamento da sessão (mesmo no meio de uma transação), ou o nível de isolamento para a próxima transação.

Você pode consultar o nível de isolamento da transação global ou da sessão com:

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

Nos travamentos de registro, InnoDB usa o chamado bloqueio de chave seguinte (next-key locking). Isto significa que além dos registros de índices, o InnoDB também pode bloquear a lacuna antes de um registro de índice para bloquear inserções por outros usuários imediatamente antes do registro de índice. Um bloqueio de chave seguinte significa um bloqueio que trava um registro de índice e a lacuna antes dele. O bloqueio de lacuna significa um bloqueio que só trava a lacuna antes do registro de índice.

Uma descrição detalhada de cada nível de isolamento em InnoDB:

Leitura Consistente sem Lock

Uma leitura consistente significa que o InnoDB utiliza multi-versioning para apresentar a uma consulta uma cópia do banco de dados em um dado momento. O consulta verá as mudanças feitas por aquelas transações que fizeram o commit antes daquele momento e não verá nenhuma mudança feita por transações posteriores ou que fizeram o commit. A exceção a esta regra é que a consulta verá as mudanças feitas pela transação que executar a consulta.

Se você está utilizando o nível de isolamento padrão REPEATABLE READ, então todas as leituras consistentes dentro da mesma transação lêem a mesma cópia estabelacida pela primeira leitura naquela transação. Você pode obter uma cópia recente para sua consulta fazendo um commit da transação atual e executando uma nova consulta.

Leituras consistentes é o modo padrão no qual o InnoDB processa instruções SELECT em níveis de isolamento READ COMMITTED e REPEATABLE READ. Uma leitura consistentes não configura nenhuma trava em tabelas que ela acessa e assim outros usuários estão livres para modificar estas tabelas ao mesmo tempo que uma leitura consistente esta sendo feita na tabela.

Lock de Leitura SELECT ... FOR UPDATE e SELECT ... LOCK IN SHARE MODE

Uma leitura consistente não é conveniente em alguma circunstâncias. Suponha que você queira adicionar uma nova linha em sua tabela CHILD, e está certo que ela já possui um pai na tabela PARENT.

Suponha que você utilize leitura consistente para ler a tabela PARENT e certamente veja o pai do filho na tabela. Agora você pode adiciona com segurança o registro filho na tabela CHILD? Não, porque pode ter acontecido de outro usuário ter deletado o registro pai da tabela PARENT, e você não estar ciente disto.

A solução é realizar o SELECT em um modo de travamento, LOCK IN SHARE MODE.

SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Realizar uma leitura em modo compartilhado significa que lemos o dado disponível por último e configuramos travas de leitura nos registros lidos. Se o este dado pertencer a uma transação de outro usuário que ainda não fez commit, esperaremos até que o commit seja realizado. Uma trava em modo compartilhado previne que ocorra atualizações ou deleções de registros já lidos. Depois de vermos que a consulta acima retornou o pai 'Jones', podemos com segurança adicionar o seu filho a tabela CHILD, e realizar o commit de nossa transação. Este exemplo mostra como implementar integridade referêncial no código de sua aplicação.

Deixe-nos mostrar outro exemplo: temos um compo de contador inteiro em uma tabela CHILD_CODES que usamos para atribuir um identificador único para cada filho que adicionamos na tabela CHILD. Obviamente, usar uma leitura consistente ou uma leitura em modo compartilhado para ler o valor atual do contador não é uma boa idéia, já que dois usuários do banco de dados podem ver o mesmo valor para o contador e, assim, teríamos um erro de chave duplicada ao adicionarmos os dois filhos com o mesmo identificador para a tabela.

Neste caso existem dois bons modos de se implementar a leitura e o incremento do contador: (1) atualizar o contador primeiro aumentando-o de 1 e só depois disto lê-lo, ou (2) ler o contador primeiro com um modo de bloqueio FOR UPDATE, e incrementá-lo depois disto:

SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;

Um SELECT ... FOR UPDATE irá ler o dado disponível por último atribuindo travas exclusivas a cada linha que ele ler. Assim ele atribui uma mesma trava que um UPDATE SQL pesquisado atribuiria nos registros.

Lock da Chave Seguinte: Evitando Problemas com Fantasmas

Em um lock de registro o InnoDB utiliza um algoritmo chamado trava de chave seguinte. O InnoDB faz o lock de registro, assim quando ele faz uma busca ou varre a tabela, ele atribui travas compartilhadas ou exclusivas nos registros que ele encontra. Assim o bloqueio de registro é mais precisamente chamado lock de registro de índice.

A trava que o InnoDB atribui em registro de índices também afetas as 'lacunas' antes daquele registro de índice. Se um usuário tem uma trava compartilhada ou exclusiva no registro R em um índice, então outro usuário não pode inserir um novo registro de índice imediatamente antes de R na ordem do índice. Este bloqueio de lacunas é feito para prevenir o chamado problema de fantasma. Suponha que eu queira ler e travar todos os filhos com identificador maior que 100 da tabela CHILD e atualizar alguns campos nos registros selecionados.

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

Suponha que exista um índice na tabela CHILD na coluna ID. Nossa consulta varrerá aquele índice começando do primeiro registro onde ID é maior que 100. Agora, se a trava atribuída no registro de índice não travasse inserções feitas nas lacunas, um novo filho poderia ser inserido na tabela. Se agora eu executasse em minha transação

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

novamente, eu veria um novo filho no resultado que a consulta retorna. Isto é contra o princípio de isolamento das transações: uma transação deve executar sem que os dados que ele estaja lendo sejam alterados durante a transação. Se considerarmos um conjunto de registros como um item de dados, então o novo filho 'fantasma' quebrará o principio do isolamento.

Quando o InnoDB varre um índice ele também pode bloquear a lacuna depois do último registro no índice. Assim como no exemplo anterior: a trava atribuida pelo InnoDB irá previnir que seja feita qualquer inserção na tabela onde ID seja maior que 100.

Você pode utilizar trava de chave seguinte para implementar uma verificação de unicidade em sua aplicação: se você ler os seus dados em modo compartilhado e não ver um registro que duplique o que você irá inserir, então você pode inserí-lo com segurança e saber que o trava de chave seguinte atribuida ao registro sucessor ao seu durante a leitura irá previnir que alguém insira um registro que duplique o seu neste intervalo. Assim a trava de chave seguinte permite que você 'bloqueie' a não existência de algo em sua tabela.

Locks Definidos por Diferentes Instruções SQL no InnoDB

Detecção de Deadlock e Rollback

O InnoDB detecta automaticamente o deadlock de transações e faz um roll back da(s) transação(ões) para prevenir o deadlockck. A partir da versão 4.0.5, o InnoDB tentará escolher pequenas transações para se fazer roll back. O tamanho de uma transação é determinado pelo número de linhas que foram inseridas, atualizadas ou deletadas. Antes da versão 4.0.5, InnoDB sempre fazia roll back da transação cujo pedido de bloqueio fosse o último a criar o deadlock, isto é, um ciclo no grafo de espera da transação.

O InnoDB não pode detectar deadlocks onde uma trava atribuida por uma instrução MariaDB LOCK TABLES está envolvida ou se uma trava definida em outro mecanismo de banco de dados diferente de InnoDB está envolvida. Você tem que resolver estas situações usando innodb_lock_wait_timeout configurado em my.cnf.

Quando o InnoDB realiza um rollback completo de uma transação, todos as travas da transação são liberadas. No entanto, se é feito o rollback de apenas uma única instrução SQL como um resultado de um erro, algumas das travass definidas pela instrução podem ser preservadas. Isto ocorre porque o InnoDB armazena as travas de registro em um formato onde ele não pode saber qual trava foi definida por qual instrução SQL.

Um Exemplo de Como a Leitura Consistente Funciona no InnoDB

Suponha que você esteja utilizando o nível de isolamento padrão REPEATABLE READ. Quando você executa uma leitura consistente, isto é, uma instrução SELECT comum, o InnoDB dará a sua transação um ponto no tempo de acordo com o que a sua consulta viu no banco de dados Assim, se a transação B deleta uma linha e faz um commit depois que o ponto no tempo foi atribuido, então você não verá a linha deletada. Inserções e atualização são feitos de forma parecida.

Você pode avançar o seu ponto no tempo fazendo um commit da transação e fazendo outro SELECT.

Isto é chamado controle de concorrência multi-version.

 User A User B
 SET AUTOCOMMIT=0; SET AUTOCOMMIT=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
 empty set
 COMMIT;
 SELECT * FROM t;
 empty set;
 COMMIT;
 SELECT * FROM t;
 ---------------------
 | 1 | 2 |
 ---------------------

Assima o usuário A vê a linha inserida por B apenas quando B fizer um commit da inserção e A tiver feito um commit de sua própria transação pois assim o ponto no tempo é avançado para depois do commit de B.

Se você deseja ver o estado mais atual do banco de dados, você deve utilizar uma trava de leitura:

SELECT * FROM t LOCK IN SHARE MODE;

Como lidar com deadlocks?

Deadlocks são um problema clássico em banco de dados transacionais, mas eles não são perigosos, a menos que eles sejam tão frequentes que você não possa executar certas transações. Normalmente você tem que escrever suas aplicações de forma que elas sempre estejam preparada a reexecutar uma transação se for feito um roll back por causa de deadlocks.

O InnoDB utiliza bloqueio automático de registro. Você pode obter deadlocks mesmo no caso de transações que inserem ou deletam uma única linha. Isto ococrre porque estas operações não são realmente 'atômicas': elas automaticamente atribuem travas aos (possivelmente muitos) registros se índices da linha inserida/deletada.

Você pode lidar com deadlocks e reduzí-lo com os seguintes truques:

Dicas de Ajuste de Desempenho

SHOW INNODB STATUS e o Monitor InnoDB
  1. Se o aplicativo top do Unix ou o Gerenciado de Tarefas do Windows mostrar que percentual de uso da CPU com sua carga de trabalho é menor que 70%, provavelmente sua carga de trabalho está no limite do disco. Talvez você esteja fazendo muitos commits de transações ou a área de buffer é muito pequena. Tornar o buffer maior pode lhe ajudar, mas não o configure com mais de 80% da memória física.
  2. Envolva diversas modificações em uma transação. O InnoDB deve descarregar o log em disco a cada commit da transação se esta transação fizer modificações no banco de dados. Uma vez que o velocidade de rotação do disco é normalmente 167 revoluções/segundo, o número de commits fica limitado aos mesmos 167/segundo se o disco não enganar o sistema operacional.
  3. Se você puder ter perda dos últimos commits feitos em transações, você pode configurar o parâmetro innodb_flush_log_at_trx_commit no arquivo my.cnf com 0. O InnoDB tenta descarregar o log uma vez por segundo de qualquer forma, embora a descarga não seja garantida.
  4. Torne os seus arquivos de log maiores, tão grande quanto a área de buffer. Quando o InnoDB escrever o arquivo de log totalmente, ele terá que escrever o conteúdo modificado da área de buffer no disco em um ponto de verificação. Arquivos de log menores causarão muitos escrita desnecessárias em disco. O ponto negativo em arquivos grandes é que o tempo de recuperação será maior.
  5. O buffer de log também deve ser grande, cerca de 8 MB.
  6. (Relevante para versão 3.23.39 e acima.) Em algumas versões do Linux e Unix, descarregar arquivos em disco com o comando fdatasync do Unix e outros métodos parecido é surpreendentemente lento. O método padrão que o InnoDB utiliza é a função fdatasync. Se você não estiver satisfeito com o desempenho da escrita do banco de dados, você pode tentar configurar innodb_flush_method em my.cnf com O_DSYNC, embora O_DSYNC pareça ser mais lento em alguns sistemas.
  7. Ao importar dados para o InnoDB, esteja certo de que o MariaDB não está com autocommit=1 ligado. Assim cada inserção exige uma descarga de log em disco. Coloque antes da linha de importação de arquivo do SQL 

    SET AUTOCOMMIT=0;

    e depois dele

    COMMIT;

    Se você utilizar a opção mysqldump --opt, você obterá arquivos dump que são mais rápidos de importar também em uma tabela InnoDB, mesmo sem colocá-los entre SET AUTOCOMMIT=0; ... COMMIT;.

  8. Tome ciência dos grandes rollbacks de inserções em massa: o InnoDB utiliza o buffer de inserção para economizar E/S de disco em inserções, mas em um rollback correspondente tal mecanismo não é usado. Um rollback no limite de disco pode demorar cerca de 30 vezes mais que a insserção correspondente. Matar o processa de banco de dados não irá ajudar pois o rollback irá reiniciar ao se entrar no banco de dados. O único modo de se livrar de um rollback deste tipo é aumentar a área de buffer de forma que o rollback dependa do limite de CPU e seja executado rápidamente ou deltar todo o banco de dados InnoDB.
  9. Tome ciência também de outras grandeas operações com limite de disco. Use DROP TABLE ou TRUNCATE (a partiir do MariaDB-4.0) para esvaziar uma tabela, não DELETE FROM suatabela.
  10. Utilize INSERT multi-line para reduzir a sobrecarga de comunicação entre o cliente e o servidro se você precisar inserir muitas linhas: 

    INSERT INTO suatabela VALUES (1, 2), (5, 5);

    Esta dica é válida para inserções em qualquer tipo de tabela, não apenas no InnoDB.

SHOW INNODB STATUS e o Monitor InnoDB

A partir da versão 3.23.41, o InnoDB inclui o Monitor InnoDB que imprime informações sobre o estado interno do InnoDB. A partir das versões 3.23.52 e 4.0.3 você pode usar o comando SQL SHOW INNODB STATUS para trazer a saída do Monitor InnoDB padrão para o cliente SQL. os dados são úteis para ajuste do desempenho. Se você estiver usando o cliente SQL interativo MariaDB, a saída é mais legível se você substituir o ponto e vírgula normalmente usado no final das instruções por \G:

SHOW INNODB STATUS\G

Outro modo de usar os Monitores InnoDB é deixá-los gravando dados continuamente na saída padrão do servidor mysqld (nota: o cliente MariaDB não exibirá nada). Ao ser ligado, os Monitores InnoDB exibirá dados um vez a cada 15 segundos. Se você executar mysqld como um daemon então esta saída é normalmente direcionada para o log .err no datadir do MariaDB. Este dado é útil para ajuste do desempenho. No Windows você deve iniciar o mysqld-max a partir do Prompt do MSDOS com a opção --standalone --console para direcionar a saída para a janela do prompt do MS-DOS.

Existe um innodb_lock_monitor separada que imprime a mesma informação que innodb_monitor mais informações sobre travas configuradas por cada transação.

A informação impressa inclui dados sobre:

Você pode iniciar o Monitor InnoDB com o seguinte comando SQL:

CREATE TABLE innodb_monitor(a INT) type = innodb;

e pará-lo com

DROP TABLE innodb_monitor;

A sintaxe CREATE TABLE é só um modo de passar um comando ao mecanismo InnoDB através do analisador SQL do MariaDB: a tabela criada não é relevante para o Monitor InnoDB. Se você fechar o banco de dados quando o manitor estiver em execução, e você quiser iniciar o monitor novamente, você deve apagar a tabela antes de executar um novo CREATE TABLE para iniciar o monitor. A sinstaxe pode alterar em distribuição futuras.

Uma saída padrão do Monitor InnoDB:

================================
010809 18:45:06 INNODB MONITOR OUTPUT
================================
--------------------------
LOCKS HELD BY TRANSACTIONS
--------------------------
LOCK INFO:
Number of locks in the record hash table 1294
LOCKS FOR TRANSACTION ID 0 579342744
TABLE LOCK table test/mytable trx id 0 582333343 lock_mode IX RECORD LOCKS space id 0 page no 12758 n bits 104 table test/mytable index PRIMARY trx id 0 582333343 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 74; 1-byte offs FALSE;
info bits 0
 0: len 4; hex 0001a801; asc ;; 1: len 6; hex 000022b5b39f; asc ';;
 2: len 7; hex 000002001e03ec; asc ;; 3: len 4; hex 00000001;
...
-----------------------------------------------
CURRENT SEMAPHORES RESERVED AND SEMAPHORE WAITS
-----------------------------------------------
SYNC INFO:
Sorry, cannot give mutex list info in non-debug version!
Sorry, cannot give rw-lock list info in non-debug version!
-----------------------------------------------------
SYNC ARRAY INFO: reservation count 6041054, signal count 2913432
4a239430 waited for by thread 49627477 op. S-LOCK file NOT KNOWN line 0
Mut ex 0 sp 5530989 r 62038708 sys 2155035;
rws 0 8257574 8025336; rwx 0 1121090 1848344
-----------------------------------------------------
CURRENT PENDING FILE I/O'S
--------------------------
Pending normal aio reads:
Reserved slot, messages 40157658 4a4a40b8
Reserved slot, messages 40157658 4a477e28
...
Reserved slot, messages 40157658 4a4424a8
Reserved slot, messages 40157658 4a39ea38
Total of 36 reserved aio slots Pending aio writes:
Total of 0 reserved aio slots Pending insert buffer aio reads:
Total of 0 reserved aio slots Pending log writes or reads:
Reserved slot, messages 40158c98 40157f98
Total of 1 reserved aio slots Pending synchronous reads or writes:
Total of 0 reserved aio slots
-----------
BUFFER POOL
-----------
LRU list length 8034
Free list length 0
Flush list length 999
Buffer pool size in pages 8192
Pending reads 39
Pending writes: LRU 0, flush list 0, single page 0
Pages read 31383918, created 51310, written 2985115
----------------------------
END OF INNODB MONITOR OUTPUT
============================
010809 18:45:22 InnoDB starts purge
010809 18:45:22 InnoDB purged 0 pages

Algumas notas sobre a saída:

Implementação de Multi-versioning

Como o InnoDB é um banco de dados multi-version, ele deve mantar informações de versões antigas de seus registros na tablespace. Esta informação é armazenada na estrutura de dados que chamamos de segmento rollback como uma estrutura de dados anoga no Oracle.

Internamente o InnoDB adiciona dois campos a cada linha armazenada no banco de dados. Um campo de 6 bytes diz ao identificador da transação sobrea a última transação que inseriu ou atualizou um registro. Uma deleção também é tratada internamente como uma atualização ande um bit especial é definido para indicae a daleção. Cada linha contém também um campo de 7 bytes chamado roll pointer. O roll pointer aponta para um registro log de itens a desfazer escrito no segmento rollback. Se o registro foi atualizado, então este registro de log contém a informação necessária para reconstruir o conteúdo da linha antes de ela ter sido atualizada.

O InnoDB usa a informação no segmento rollback para realizar o operação de desfazer necessária em um rollback de uma transação. Ele também usa a informação para construir versões mais novas de um registro para uma leitura consistente.

Os logs de itens a desfazer em um segmwnto rollback são divididos en logs de inserção e atualização. Logs de inserção só são necessários em rollback das transações e podem ser discartados assim que se fizer o commit das transações. Logs de atualização também são utilizados em leituras consistentes, e eles só podem ser descartados quando não houver mais transações para as quais o InnoDB atribuiu uma cópia do banco de dados que precisasse das informações do log de atualizações em uma leitura consistente para construir uma versão mais nova do registro do banco de dados.

Você deve se lembrar de fazer commit em suas transaçãoes regularmente, inclusive aquelas transações que só fazem leituras consistentes. Senão o InnoDB não pode descartar dados do log de atualização e o segmento rollback pode crescer demias, enchendo o seu tablespace.

O tamanho físico de um registro log de itens a desfazer em um segmento rollback é normalmente menor que o registro inserido ou atualizado correspondente. Você pode usar esta informação para calcular o espaço necessário para o seu segmento rollback.

Neste esquema multi-versioning uma linha não é fisicamente removida do banco de dados imediatamente quando você a deleta com uma instrução SQL. Apenas quando o InnoDB puder descartar o registro de log de itens a desfazer da atualização ele pode, também, remover fisicamente a linha correspondente e seu registros de índices do banco de dados. Esta operação de remoção é chamada `purge' e é bem rápida, tendo, normalmente, a mesma ordem de tempo da instrução SQL que fez a deleção.

Estrutura de Tabelas e Índices

Estrutura Física do Índice
Buffer de Inserção
Índices Hash Adaptativos
Estrutura dos Registros Físicos
Como Funciona uma Coluna AUTO_INCREMENT no InnoDB

O MariaDB armazena suas informações de dicionários de dados de tabelas em arquivos .frm no diretório de banco de dados. Mas todo tabela do tipo InnoDB também tem sua própria entrada no dicionários de dados interno do InnoDB dentro da tablespace. Quando o MariaDB apaga uma tabela ou um banco de dados, ele tem que deletar o(s) arquivo(s) .frm e a entrada correspondente dentro do dicionário de dados do InnoDB. Esta é a razão pela qual você não pode mover tabelas InnoDB entre banco de dados simplesmente movendo os arquivos .frm e porque DROP DATABASE não funcionava em tabelas do tipo InnoDB em versÕes do MariaDB anteriores a 3.23.43.

Toda tabela InnoDB tem um índice especial chamado de índice agrupado onde os dados dos registros são armazenados. Se você definir um chave primaria (PRIMARY KEY) na sua tabela, então o índice da chave primária será o índice agrupado.

Se você não definir uma chave primária para a sua tabela, o InnoDB irá gerar internamente um índice agrupado qonde as linhas são ordenadas pela ID da linha que o InnoDB atribui as linhas nestas tabelas. O ID da linha é um campo de 6 bytes que cresce quando novas linhas são inseridas. Assim as linhas armazenadas pela sua ID estarão fisicamente na ordem de inserção.

Acessar uma linha pelo índice agrupado é rápido porque os dados do registro estarão na mesma página que a busca de índice nos indicar. Em muitos bancos de dados, os dados são armazenados em página diferente daquela em que se encontra os registros de índices, Se uma tabela é grande, a arquitetura do índice agrupado geralmente economiza E/S de disco se coparado a solução tradicional.

O registro em índices não agrupados (também os chamamos de índices secundários) em InnoDB contém o valor da chave primária para a linha. O InnoDB usa este valor de chave primária para buscar o registro do índice agrupado. Note que se a chave primária for grande, os índices secundários irão utilizar ainda mais espaço.

Estrutura Física do Índice

Todos os índices no InnoDB são árvores-B onde os registros de índice são armazenados na página de folhas da árvore, O tamanho padrão de uma página de índice é 16 Kb. Quando novos registros são inseridos, InnoDB tenta deixar 1 / 16 de paginas livre para futuras inserções e atualzações de registro de índices.

Se registros de índice são inseridos em ordem sequencial (ascendente ou descendente, os páginas de índices resultantes estarão cerce de 15/16 completa. Se os registros são inseridos em ordem aleatoria, então as páginas estarão de 1/2 a 15/16 completos. Se o fator de preenchimento de uma página índice ficar abaixo de 1/2, o InnoDB tentará contrair o árvore de índice para liberar a página.

Buffer de Inserção

É uma situação comum em aplicativos de banco de dados que a chave prmária seja um identificador único e os novos registros são inseridos em ordem crescente de acordo com a chave primária. Assim a inserção nos índices agrupados não exigem leituras aleatorias a disco.

Por outro lado, índices secundários são normalmente não são únicos e inserções acontecem em uma ordem relativamente aleatória nos índices secundários. Isto causaria diversos acessos de E/S aleatórios em disco sem um mecanismo especial usado em InnoDB.

Se um registro de índice deve ser inserido a um índice secundário que não é único, o InnoDB verifica se a página de índice secundário já está na área de buffer. Se este for o caso, o InnoDB fará a inserção diretamente ná página do índice. Mas, se a página de índice não for encontrada na área de buffer, O InnoDB insere o registro em uma estrutura de buffer de inserção especial. O buffer de inserção é mantido tão pequeno que ele cabe totalmente na área de buffer e inserções nele podem ser feitas muito rápido.

O buffer de inserção é unido periodicamente à árvore de índices secundários no banco de dados. Geralmente nós podemos juntar diversas inserções na mesma página na árvore índice o que economiza E/S de disco. Buffers de inserções podem aumentar a velocidade das inserções em uma tabela em cerca de 15 vezes.

Índices Hash Adaptativos

Se um banco de dados couber quase totalmente na memória principal, então o modo mais rápido de realizar consultas nela é usar índices hash. O InnoDB tem um mecanismo automatico que monitora as buscas em índices feitas nso índices definidos na tabela e, se o InnoDB notar que as consultas podiam ser beneficiadas da construçã de índices hash, tal índice é automaticamente construído.

Mas note que um índice hash é sempre construído com base em um índice de árvore-B existente na tabela. O InnoDB pode construir um índice hash em um prefixo de qualquer tamanho da chave definida pela árvore-B, dependendo de que padrão de busca o InnoDB observa em índices de árvore-B. Um índice hash pode ser parcial: não é exigido que todo o índice seja armazenado na área de buffer. O InnoDB contruirá índices hash por demanda naquelas páginas de índice que são frequentemente acessadas.

Deste forma, Através do mecanismo de índice hash adptativo o InnoDB se adapta a uma memória principal ampla, aporoximando-se da arquitetura dos bancos de dados de memória principal.

Estrutura dos Registros Físicos

Como Funciona uma Coluna AUTO_INCREMENT no InnoDB

Depois que um banco de dados inicia, quando um usuário faz a primeira inserção em uma tabela T onde uma coluna auto-increment foi definida, e o usuário não fornece um valor explicito para a coluna, então o InnoDB executa SELECT MAX(auto-inc-column) FROM T, e atribui aquele valor incrementado de um a coluna e ao contador de auto incremento da tabela. Dizemos que o contador de auto incremento para a tabela T foi inicializado.

O InnoDB segue o mesmo procedimento na inicialização do contador de auto incremento para uma tabela recem criada.

Note que se o usuário especifica em uma inserção o valor 0 a coluna auto-increment. o InnoDM trata a linha como se o valor não tivesse sido especificado.

Depois do contador de auto incremento tiver sido inicializado, se um usuário insere uma linha onde especificamos explicitamente o valor da coluna e o valor é maior que o valor atual do contador, então o contador é configurado com o valor especificado. Se o usuário não especificar um valor explicitamente, o InnoDB incrementa a contador de um e atribui o seu novo valor a coluna.

O mecanismo de auto incremento, ao atribuir valor ao contador, desvia de manipuladores de travas e transações. De outra forma você também pode obter lacuas na sequência de números se você fizer um roll back da transação que tiver obtido números do contador.

O comportamento do auto incremento não é definido se um usuário passar um valor negativo a coluna ou se o valor se tornar maior que o valor inteiro máximo que pode ser armazenado no tipo inteiro especificado.

Gerenciamento do Espaço de Arquivos e E/S de Disco

E/S de Disco
Gerenciamento do Espaço de Arquivo
Desfragmentando uma Tabela

E/S de Disco

Na E/S de disco o InnoDB usa E/S assíncrona. No Windows NT ele usa a E/S assíncrona nativa fornecida pelo sistema operacional. No Unix, o InnoDB usa E/S assíncrona simulada construída dentro do InnoDB: o InnoDB cria um número de threads de E/S que cuidam das operações de E/S, tais como leitura. Em uma versão futura adcionaremos suporte para E/S simulada no Windows NT e E/S nativa nas versões de Unix que possuam este recurso.

No Windows NT o InnoDB usa E/S sem buffer. Isto significa que as páginas de disco que o InnoDB lê ou escreve não são armazenadas na cache de arquivo do sistema operacional. Isto economiza um pouco da banda de memória.

A partir da versão 3.23.41, o InnoDB usa uma técnica de descarga de arquivo da novel chamado escrita dupla (doublewrite). Ela adiciona segurança a recuperação em falhas depois de uma falha do sistema operacional ou queda de força e aumenta o desempenho na maioria dos sistemas Unix, reduzindo a necessidade de operações fsinc.

Escrita dupla significa que antes do InnoDB escrever páginas em um arquivo de dados, ele primeiro os escreve em área de tablespaces contínuos chamados de buffer de escrita dupla (doublewrite buffer). Apenas após a escrita e a descarga no buffer de escrita dupla tiver sido completada, o InnoDB escreve a página em sua posição apropriada na arquivo de dados. Se o sistema operacional falhar no meio da escrita da página, o InnoDB irá fazer a recuperação procurando uma cópia da página no buffer de escrita dupla.

A partir da versão 3.23.41 você também pode usar uma partição de disco raw como um arquivo de dados, mas insto ainda não foi testado. Quando você cria um navo arquivo de dados você tem que colocar a palavra chave newraw imediatamente depois do tamanho do arquivo de dados em innodb_data_file_path. A partição deve ter, pelo menos, o tamanho que você especificou. Note que 1M no InnoDB é 1024 x 1024 bytes, enquanto na especificação de disco 1 MB normalmente significa 1000 000 bytes.

innodb_data_file_path=/dev/hdd1:5Gnewraw;/dev/hdd2:2Gnewraw

Quando você reinicia o banco de dados você deve alterar a palavra chave para raw. Senão o InnoDB escreverá sobre a sua partição!

innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

Usando um disco raw você pode ter E/S sem buffer em algumas vesões de Unix.

Quando você usar partições de disco raw, certifique-se de que você tem permissões que permitem acesso de leitura e escrita na conta usada para executar o servidor MySQL.

Existem duas heurísticas read-ahead no InnoDB: read-ahead sequencial e read-ahead aleatória. Na read-ahead sequencial o InnoDB percebe que o padrão de acesso a um segmento no tablespace é sequencial. então o InnoDB enviará uma grupo de leitura das paginas do banco de dados para o sistema de E/S. No read-ahead aleatório o InnoDB percebe que algumas áreas no tablespace parecem estar no processo de serem totalmente lidas na área de buffer. O InnoDB envia as leituras remanescente para o sistema de E/S.

Gerenciamento do Espaço de Arquivo

Os arquivos de dados definido no arquivo de configuração forma o tablespace do InnoDB. Os arquivos são simplesmente concatenado para formar o tablespace, não há nenhuma listagem em uso. Atualmente você não pode definir onde suas tabelas serão alocadas no tablespace. No entanto, em um tablespace criado recentemente, o InnoDB alocará

espaço a partir do low end

O tablespace consiste de páginas de banco de dados cujo tamanho padrão é 16 KB. As páginas são agrupadas numa extendsão de 64 páginas consecutivas. Os 'arquivos' dentro de um tablespace são chamados segmentos no InnoDB. O Nome do segmento rollback é um tanto enganador porque na verdade ele contém vários segmentos no tablespace.

Para cada índice no InnoDB nós alocamos dois segmentos: um é para nós que não são folhas da árvore-B e outro é para nós de folhas. A idéia aqui é conseguir melhorar a sequencialidade dos nós de folhas, que comtêm os dados.

Quando um segmento cresce dentro da tablespace, o InnoDB aloca as primeiras 32 páginas para ele, individualmente. Depois disto o InnoDB inicia a alocação de toda a extensão do segmento. O InnoDB pode adicionar a um grande segmento até 4 extensões de uma vez para assegurar a boa sequencilidade dos dados.

Algumas páginas na tablespace contém bitmaps de outras páginas e dessa forma algumas poucas extensões em um tablespace do InnoDB não podem ser alocadas ao segmento como um todo, mas apenas como páginas individuais.

Quando você executa uma consulta SHOW TABLE STATUS FROM ... LIKE ... para saber sobre o espaço livre disponível no tablespace, o InnoDB irá relatar as extensões que estejam definitivamente livres na tabelspace. O InnoDB sempre reserva algumas extensões para limpeza e outros propósitios internos; estas extensões reservadas não estao incluídas no espaço livre.

Quando você deletar dados de uma tabela, o InnoDB contrairá o índice de árvore-B correspondente. Ele depende do padrão de deleções se isto liberar páginas individuais ou extensões da tablespace, assim que o espaço liberado estiver disponível para outros usuários. Apagar a tabela ou deletar todos os registros dela garante a liberação do espaço para outros usuários, mas lembre-se que registros deletados só podem ser fisicamente removidos em uma operação de remoção (`purge'), depois que não houver mais necessidades de rollback em trasações ou leituras consistentes.

Desfragmentando uma Tabela

Se houver inserções ou deleções aleatórias nos índices de uma tabela, os índices podem se tornar fragmentados. Com frangmentação queremos dizer que a ordem física das páginas de índice no disco não está próxima a ordem alfabética dos registros nas páginas, ou que existe muitas páginas sem uso no bloco de 64 páginas no qual os índices são alocados.

Isto pode aumentar a varredura de índices de você usar mysqldump periodicamente para se fazer uma cópiad a tabela em um arquivo texto, apagar a tabela e recarregá-la a partir do arquivo texto. Outro modo de se fazer a desfragmentação é realizar uma operação alter table 'nula' ALTER TABLE nometabela TYPE=InnoDB. Isto faz com que o MariaDB reconstrua a tabela.

Se as inserções a um índice são sempre crescentes e os registros só são deletados a partir do fim, então o algoritmo do gerenciamento de espaço de arquivo do InnoDB garante que a fragmentação nos índices não ocorrerão.

Tratando Erros

O tratamento de erro no InnoDB nem sempre é o mesmo que o especificado no padrão SQL. De acordo com o SQL-99, qualquer erro durante uma instrução SQL deve provocar o rollback da instrução. O InnoDB, algumas faz o rollback de apenas parte da instrução, ou de toda instrução. A seguinte lista especifica o tratamento de erro do InnoDB.

Restrições em Tabelas InnoDB

Histórico de Alterações do InnoDB

MySQL/InnoDB-4.1.1, December 4, 2003
MySQL/InnoDB-4.0.16, October 22, 2003
MySQL/InnoDB-3.23.58, September 15, 2003
MySQL/InnoDB-4.0.15, September 10, 2003
MySQL/InnoDB-4.0.14, Junho de 2003
MySQL/InnoDB-3.23.57, June 20, 2003
MySQL/InnoDB-4.0.13, 20 de Maio de 2003
MySQL/InnoDB-4.1.0, 03 de Abril de 2003
MySQL/InnoDB-3.23.56, 17 de Março de 2003
MySQL/InnoDB-4.0.12, 18 Março de 2003
MySQL/InnoDB-4.0.11, 25 de Fevereiro de 2003
MySQL/InnoDB-4.0.10, 04 de Fevereiro de 2003
MySQL/InnoDB-3.23.55, 24 de Janeiro de 2003
MySQL/InnoDB-4.0.9, 14 de Janeiro de 2003
MySQL/InnoDB-4.0.8, 07 de Janeiro de 2003
MySQL/InnoDB-4.0.7, 26 de Dezembro de 2002
MySQL/InnoDB-4.0.6, 19 de Dezembro de 2002
MySQL/InnoDB-3.23.54, 12 de Dezembro de 2002
MySQL/InnoDB-4.0.5, 18 de Novembro de 2002
MySQL/InnoDB-3.23.53, 09 de Outubro de 2002
MySQL/InnoDB-4.0.4, 02 de Outubro de 2002
MySQL/InnoDB-4.0.3, 28 de Agosto de 2002
MySQL/InnoDB-3.23.52, 16 de Agosto de 2002
MySQL/InnoDB-4.0.2, 10 de Julho de 2002
MySQL/InnoDB-3.23.51, 12 de Junho de 2002
MySQL/InnoDB-3.23.50, 23 de Abril de 2002
MySQL/InnoDB-3.23.49, 17 de Fevereiro de 2002
MySQL/InnoDB-3.23.48, 09 de Fevereiro de 2002
MySQL/InnoDB-3.23.47, 28 de Dezembro de 2001
MySQL/InnoDB-4.0.1, 23 de Dezembro de 2001
MySQL/InnoDB-3.23.46, 30 de Novembro de 2001
MySQL/InnoDB-3.23.45, 23 de Novembro de 2001
MySQL/InnoDB-3.23.44, 02 de Novembro de 2001
MySQL/InnoDB-3.23.43, 04 de Outubro de 2001
MySQL/InnoDB-3.23.42, 09 de Setembro de 2001
MySQL/InnoDB-3.23.41, 13 de Agosto de 2001
MySQL/InnoDB-3.23.40, 16 de Julho de 2001
MySQL/InnoDB-3.23.39, 13 de Junho de 2001
MySQL/InnoDB-3.23.38, 12 de Maio de 2001

MySQL/InnoDB-4.1.1, December 4, 2003

MySQL/InnoDB-4.0.16, October 22, 2003

MySQL/InnoDB-3.23.58, September 15, 2003

MySQL/InnoDB-4.0.15, September 10, 2003

MySQL/InnoDB-4.0.14, Junho de 2003

MySQL/InnoDB-3.23.57, June 20, 2003

MySQL/InnoDB-4.0.13, 20 de Maio de 2003

MySQL/InnoDB-4.1.0, 03 de Abril de 2003

MySQL/InnoDB-3.23.56, 17 de Março de 2003

MySQL/InnoDB-4.0.12, 18 Março de 2003

MySQL/InnoDB-4.0.11, 25 de Fevereiro de 2003

MySQL/InnoDB-4.0.10, 04 de Fevereiro de 2003

MySQL/InnoDB-3.23.55, 24 de Janeiro de 2003

MySQL/InnoDB-4.0.9, 14 de Janeiro de 2003

MySQL/InnoDB-4.0.8, 07 de Janeiro de 2003

MySQL/InnoDB-4.0.7, 26 de Dezembro de 2002

MySQL/InnoDB-4.0.6, 19 de Dezembro de 2002

MySQL/InnoDB-3.23.54, 12 de Dezembro de 2002

MySQL/InnoDB-4.0.5, 18 de Novembro de 2002

MySQL/InnoDB-3.23.53, 09 de Outubro de 2002

MySQL/InnoDB-4.0.4, 02 de Outubro de 2002

MySQL/InnoDB-4.0.3, 28 de Agosto de 2002

MySQL/InnoDB-3.23.52, 16 de Agosto de 2002

MySQL/InnoDB-4.0.2, 10 de Julho de 2002

MySQL/InnoDB-3.23.51, 12 de Junho de 2002

MySQL/InnoDB-3.23.50, 23 de Abril de 2002

MySQL/InnoDB-3.23.49, 17 de Fevereiro de 2002

MySQL/InnoDB-3.23.48, 09 de Fevereiro de 2002

MySQL/InnoDB-3.23.47, 28 de Dezembro de 2001

MySQL/InnoDB-4.0.1, 23 de Dezembro de 2001

MySQL/InnoDB-3.23.46, 30 de Novembro de 2001

MySQL/InnoDB-3.23.45, 23 de Novembro de 2001

MySQL/InnoDB-3.23.44, 02 de Novembro de 2001

MySQL/InnoDB-3.23.43, 04 de Outubro de 2001

MySQL/InnoDB-3.23.42, 09 de Setembro de 2001

MySQL/InnoDB-3.23.41, 13 de Agosto de 2001

MySQL/InnoDB-3.23.40, 16 de Julho de 2001

MySQL/InnoDB-3.23.39, 13 de Junho de 2001

MySQL/InnoDB-3.23.38, 12 de Maio de 2001

Informações de Contato do InnoDB

Informções para contato do Innobase Oy, produtor do mecanismo InnoDB. Web site: http://www.innodb.com/. E-mail: <sales@innodb.com>

phone: 358-9-6969 3250 (office) 358-40-5617367 (mobile)
Innobase Oy Inc.
World Trade Center Helsinki Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki Finland

Tabelas BDB ou BerkeleyDB

Visão Geral de Tabelas BDB
Instalando BDB
Opções de Inicialização do BDB
Características de Tabelas BDB:
Itens a serem corrigidos no BDB num futuro próximo:
Sistemas operacionais suportados pelo BDB
Restrições em Tabelas BDB
Erros Que Podem Ocorrer Usando Tabelas BDB

Visão Geral de Tabelas BDB

BerkeleyDB, disponível em http://www.sleepycat.com/ tem provido o MariaDB com um mecanismo de armazenamento transacional. O suporte para este mecanismo de armazenamento está incluído na distribuição fonte do MariaDB a partir da versão 3.23.34 e está ativo no binário do MariaDB-Max. Este mecanismo de armazenamento é chamado normalmente de BDB.

Tabelas BDB podem ter maior chance de sobrevivência a falhas e também são capazes de realizar operações COMMIT e ROLLBACK em transações. A distribuição fonte do MariaDB vem com uma distribuição BDB que possui alguns pequenos patchs para faze-lo funcionar mais suavemente com o MariaDB. Você não pode usar uma versão BDB sem estes patchs com o MariaDB.

Na MariaDB Foundation, nós estamos trabalhando em cooperação com a Sleepycat para manter a alta qualidade da interface do MariaDB/BDB.

Quando trouxemos o suporte a tabelas BDB, nos comprometemos a ajudar os nosso usuários a localizar o problema e criar um caso de teste reproduzível para qualquer problema envolvendo tabelas BDB. Tais casos de teste serão enviados a Sleepycat que nos ajudará a encontrar e arrumar o problema. Como esta é uma operação de dois estágios, qualquer problema com tabelas BDB podem levar um tempo um pouco maior para ser resolvido do que em outros mecanismos de armazenamento. De qualquer forma, como o código do BerkeleyDB tem sido usado em autras aplicações além do MariaDB, nós não vemos nenhum grande problema com isto. Leia 'Suporte Oferecido pela MariaDB Foundation'.

Instalando BDB

Se você tiver feito o download de uma versão binária do MariaDB que inclui suporte a BerkeleyDB, simplesmente siga as instruções de instalação de uma versão binária do MariaDB. Leia 'Instalando uma Distribuição Binária do MariaDB'. Leia 'mysqld-max, om servidor mysqld extendido'.

Para compilar o MariaDB com suporte a BerkeleyDB, faça o download do MariaDB versão 3.23.34 ou mais novo e configure MariaDB com a opção --with-berkeley-db. Leia 'Instalando uma distribuição com fontes do MariaDB'.

cd /path/to/source/of/mysql-3.23.34
./configure --with-berkeley-db

Por favor, de uma olhada no manual fornecido com a distribuição BDB para informações mais atualizadas.

Mesmo sendo o BerkeleyDB muito testado e confiável, a interface com o MariaDB ainda é considerada com qualidade gamma. Nós estamos ativamente melhorando e otimizando para torná-la estável o mais breve possível.

Opções de Inicialização do BDB

Se você estiver executando com AUTOCOMMIT=0 então as suas alterações em tabelas BDB não serão atualizadas até que você execute um COMMIT. No lugar de commit você pode executar um ROLLBACK para ignorar as suas alterações. Leia 'Sintaxe de START TRANSACTION, COMMIT e ROLLBACK'.

Se você estiver execuando AUTOCOMMIT=1 (padrão), será feito um commit das sua alterações imediatamente. Você pode iniciar uma transação estendida com o comando SQL BEGIN WORK, depois do qual não será feito commit de suas alterações ae que você execute COMMIT (ou faça ROLLBACK das alterações.)

As seguintes opções do mysqld podem ser usadas pa alterar o comportamento de tabelas BDB:

Opção Descrição
--bdb-home=directory Diretório base das tabelas BDB. Ele deve ser o mesmo diretório usado para --datadir.
--bdb-lock-detect=# Detecção de travas de Berkeley. Pode ser (DEFAULT, OLDEST, RANDOM, ou YOUNGEST).
--bdb-logdir=directory Diretório de arquivos log de Berkeley DB.
--bdb-no-sync Não sincroniza logs descarregados.
--bdb-no-recover Não inicia Berkeley DB no modo de recuperação.
--bdb-shared-data Inicia Berkeley DB no modo de multi-processos (Não usa DB_PRIVATE ao inicializar Berkeley DB)
--bdb-tmpdir=directory Diretorio de arquivos temporários do Berkeley DB.
--skip-bdb Disabilita o uso de tabelas BDB.
-O bdb_max_lock=1000 Define o número máximo de travas possíveis. Leia 'SHOW VARIABLES'.

Se você utiliza --skip-bdb, MariaDB não irá inicializar o biblioteca Berkeley DB e isto irá economizar muita memória. É claro que você não pode utilizar tabelas BDB se você estiver usando esta opção. Se você tentar criar uma tabela BDB, o MariaDB criará uma tabela MyISAM.

Normalmente você deve iniciar mysqld sem --bdb-no-recover se você pretende usar tabelas BDB. Isto pode, no entanto, lhe trazer problemas quando você tentar iniciar o mysqld e os arquivos de log do BDB estiverem corrompidos. Leia 'Problemas Inicializando o Servidor MySQL'.

Com bdb_max_lock você pode especificar o número mácimo de travas (10000 por padrão) que você pode tar ativas em uma tabela BDB. Você deve aumentá-lo se você obter um erro do tipo bdb: Lock table is out of available locks ou Got error 12 from ... quando você fizer transações longas ou quando mysqld tiver que examinar muitas linhas para calcular a consulta.

Você também pode desejar alterar binlog_cache_size e max_binlog_cache_size se você estiver usando transações multi-linhas. Leia 'Sintaxe de START TRANSACTION, COMMIT e ROLLBACK'.

Características de Tabelas BDB:

Itens a serem corrigidos no BDB num futuro próximo:

Sistemas operacionais suportados pelo BDB

Atualmente sabemos que o mecanismo de armazenamento BDB funciona com os seguintes sistemas operacionais:

Ele não funciona com os seguintes sistemas operacionais.

Nota: A lista acima não está completa; atualizaremos ela assim que recebermos mais informações.

Se você construir o MariaDB como suporte a tabelas BDB e obter o seguinte erro no arquivo de log quando você iniciar o mysqld:

bdb: architecture lacks fast mutexes: applications cannot be threaded Can't init dtabases

Isto significa que as tabelas BDB não são suportadas por sua arquitetura. Neste caso você deve reconstruir o MariaDB sem o suporte a tabelas BDB.

Restrições em Tabelas BDB

Aqui segue as restrições que você tem quando utiliza tabelas BDB:

Erros Que Podem Ocorrer Usando Tabelas BDB

Anterior Próximo
Referência de Linguagem do MariaDB Início Introdução ao MaxDB