Manutenção em tabelas – MySQL

Ola pessoal,

Manter a integridade dos dados e efetuar uma manutenção das tabelas em sua base de forma períodica, é importante para sempre manter um resultado eficaz em produção.

As tabelas em sua base podem ficar corrompidas devido a uma falha no servidor, principalmente quando ele é reiniciado de forma incorreta. Com isso, você pode perceber lentidões em consultas que geralmente são rapidas, porém podemos ter certeza ao consultar os logs de erro do MySQL.

Segue abaixo a relação de instruções SQL que podemos utilizar no dia a dia:

Analyze table:

Analisa e armazena a distribuição de chave de uma tabela, para uma tomada de decisão referente a junção das tabelas ou na utilização dos indices. Durante esta analise, a tabela ganha um nível de bloqueio para os engines InnoDB e MyISAM.

Esta instrução esta disponível para os Engines: InnoDB e MyISAM.

Ex feito em tabelas MyISAM e InnoDB:

Caso a tabela não tenha sido alterada desde o ultimo analyze, a mesma não será analisada novamente: ‘Table is already up to date’.

Check table:

Verifica a integridade da tabela a fim de identificar algum erro ou inconsistência.

É valida para os Engines: InnoDB, MyISAM, Archive. Caso seja executada em uma tabela MyISAM, as estatisticas dos indices também são atualizadas. Abaixo segue demais opções que podem ser incluidas apenas ao MyISAM:

– Quick: Não verifica os registros buscando as ligações incorretas. Validas para o Engine InnoDB e MyISAM e views.
– Fast: Verifica apenas as tabelas que não foram fechadas de forma correta. Valido apenas para tabelas MyISAM e views, e inválido para o InnoDB.
– Changed: Verifica somente as tabelas que foram alteradas desde a ultima analise ou que não foram fechadas de forma correta. Aplica-se para tabelas MyISAM e views, e inválido ao InnoDB.
– Medium: Efetua uma varredura para verificar se os registros/ligações removidas são válidas. Calcula também uma chave de conferência para os registros com base no check sum calculado para as chaves. Aplica-se para tabelas MyISAM e views, e inválido ao InnoDB.
– Extended: Faz uma consulta completa para todas as chaves de cada registro. Aplica-se para tabelas MyISAM e views, e inválido ao InnoDB.

Segue um exemplo de execução em tabelas MyISAM e InnoDB:
Continue lendo

Locks no MySQL

Ola pessoal,

Depois de falarmos sobre transações e níveis de isolamento, vamos fechar este assunto com os conceitos de bloqueio utilizados no MySQL.

O MySQL usa uma arquitetura multi-thread para gerenciar as conexões a todos os usuários, onde cada conexão tera uma thread como um controlador. O bloqueio adotado pelo MySQL impede a ocorrência de problemas com dados acessados de forma simultânea por vários clientes, onde uma conexão não ira interferir na outra.

Quando varios clientes tentam manipular o mesmo dado, o MySQL atribui um bloqueio a um cliente com base na thread, onde enquanto este bloqueio não seja liberado, os outros clientes não conseguem manipular o mesmo dado, ou seja, cada um tem a sua vez.

O efeito do mecanismo de bloqueio é serializar o acesso aos dados, onde clientes que tentam efetuar operações conflitantes deverão aguardar a sua vez.

Mas nem todo o tipo de acesso gera um conflito, um exemplo:

– Caso a conexão “A” e “B” precisem ler algum dado, isso não irá gerar um conflito e os dois poderão ler ao mesmo tempo.

– Caso a conexão “A” inicie uma leitura e a “B” precise gravar um novo dado, a conexão “B” deverá aguardar a leitura da conexão “A” ser finalizada, para assim conseguir gravar o novo dado.

– Agora se a conexão “A” iniciar uma gravação de dados, todas as conexões abaixo deverão aguardar a gravação ser concluida, seja ela uma conexão de leitura ou gravação.

Uma operação de leitura deve bloquear uma operação de gravação, porém uma operação de gravação deve bloquear as demais seja leitura ou gravação até a sua finalização

Tipos de bloqueio:

— Implicito

Os bloqueios sobre os dados podem ser de forma implícita, explícita ou com advisory locks.

Caso você trabalhe com o autocommit habilitado no MySQL, o servidor adquiri bloqueios implicitamente para cada comando, e cancela o mesmo após a conclusão.
Continue lendo

Niveis de Isolamento – MySQL

Ola pessoal,

Daremos continuidade ao assunto de transação, porém agora falando dos níveis de isolamento e os possíveis problemas.

O padrão SQL define quatro níveis de isolamento de transação, com o intuito de evitar três fenomenos indesejados em transações simultaneas. Assumimos que um ambiente de produção pode ter várias transações simultâneas, no mínimo uma por sessão.

Com isso, é possível que ocorra a:

– Leitura suja: Suponhamos que a transação “A” modifique algum campo da tabela, porém que ainda não o tenha commitado. Se uma transação “B” efetua um select nesta campo e ve o valor modificado pela transação “A” sem ter o commit efetuado, essa é uma leitura suja.

Isso é um problema em ambientes de tomada de decisão, Relatórios entre outros, pois caso a transação “A” sofra um Rollback, a transação “B” não saberá disso e ja tera informado o valor “erroneo”.

Leitura não repetitiva: Ocorre quando um select(leitura) reproduz resultados diferentes quando ela é repetida posteriormente na mesma transação.

Exemplo: A transação “A” le o valor de um campo. Se outra transação “B” atualizar este valor e commita-lo no banco, caso a transação “A” volte a consultar o mesmo campo, ela trará o valor comitado pela transação “B”, ou seja, trata valores diferentes do mesmo campo na mesma transação executada.

Isso é grave caso você opte por alterar algum registro com a condição de um campo x, onde ele pode assumir um valor y em seguida.
Continue lendo

Metadados Information_schema – MySQL

Ola pessoal,

Sabemos que um banco de dados é um conjunto estruturado de dados com o objetivo de retornar alguma informação.

Quando falamos de “metadados”, tratamos sobre os dados sobre outros dados. Um pouco confuso, mas o principal objetivo é saber que os metadados tratados no MySQL, estão disponíveis na information_schema. Saber manusear os metadados nesta base é tarefa diária do DBA.

A information_schema é a estrutura de dicionário de dados utilizada pelo MySQL, funcionando como um repositório central para metadados de qualquer banco de dados.

Nela podemos obter:

– Todo o schema de objetos
– Estatisticas do servidor (Conexões, uso de CPU, Bytes enviados e recebidos)
– Privilégios
– Store procedure e Views

Porém ele é um banco de “dados virtual”, já que em nenhum momento ele esta armazenado no disco. Mas contem tabelas como qualquer outra base, onde o seu conteudo pode ser acessado com a instrução select.

O primeiro exemplo, podemos verificar os grants atribuidos a um usuário:

Acompanhar os processos que são executados na instância. Geralmente, esta opção é mais completa do que utilizar o “show processlist”, já que você consegue atribuir critérios para filtrar a sua busca:
Continue lendo

Engines MySQL – BlackHole

Ola pessoal,

Para finalizarmos o assunto das principais Engines no MySQL, falaremos hoje sobre o Blackhole.

Sim ele funciona como um buraco negro, onde ele aceita os dados porém os descarta em seguida sem armazena-los no banco.

Um exemplo:

Estranho? De inicio sim. Porém isso pode ser muito bem visto para ambientes de testes, principalmente para acompanhar algum processo em conjunto ao general logs.

Continue lendo

Transações no MySQL

Ola pessoal,

Falaremos agora sobre transações, para então dar continuidade a outro assunto que é importante, os níveis de isolamento.

Primeiramente, uma transação é um meio de executar uma ou mais instruções SQL com uma única unidade de trabalho, onde todas ou nenhuma das instruções são bem sucedidas.

Ou seja, se todas as instruções foram concluidas sem erros, você poderá registrar estas em definitivo no banco de dados. Porém caso ocorra algum erro, você poderá retornar a um ponto de salvamento ou efetuar um rollback, cancelando a alteração.

Um exemplo simples via SQL:

Continue lendo

Engines MySQL – Archive

Ola pessoal,

Hoje iremos falar do Engine Archive, suas caracteristicas e vantagens.

O Archive é usado para armazenar grandes volumes de dados em um formato compactado no MySQL, ocupando um espaço muito menor do que os demais engines.

Sabe aquela tabela de log, histórico, contato que você quase não a utiliza? Então, uma boa opção de trabalho é utilizar o Archive, por conta do armazenamento em disco e também na visualização destes dados.

Algumas vantagens e observações:

– Sua estrutura é representada pelo arquivo .frm
– Diferente do MyISAM, seus arquivos de dados são representados pelo: .ARZ , .ARN (apenas no momento de alguma otimização da tabela) e .ARM (Meta dados criado em versões anteriores ao MySQL 5.1.15)
Continue lendo

All privileges x Super privilege MySQL

Ola pessoal,

Essa comparação veio em um café da tarde diário, dai veio a dúvida, qual a diferença entre o All privileges x Super privilege no MySQL?

O All Privileges contempla todas as permissões, seja a nível de objetos como a nível de servidor, sendo padronizado como o ‘root’. O All Privileges pode ser definido a nível de base como a nível global.

Caso queira atribuir o grant a nível de base a um usuário, ou seja, para o usuário ter todos os privilégios nos objetos de uma base, basta seguir o exemplo:

Continue lendo

Engines MySQL – Memory

Ola pessoal,

Voltando a atualizar o blog, vamos falar do Engine Memory. Como o proprio nome já adianta, este Engine armazena as tabelas na memória do servidor. Isso realmente é necessário? Sim, principalmente para aplicações que dependem de algum cache temporário.

Com isso, tanto os dados como os indices nesta tabela são tratados na memória RAM do servidor, trazendo um ganho muito grande em desempenho.

Toda tabela é representada no datadir do mysql com um .frm, e não resistem ao restart do serviço. De forma detalhada, você pode popular uma tabela Memory, porém após efetuar um restart no MySQL, a estrutura da tabela ainda vai existir, porém sem nenhum dado.
Continue lendo

Engines MySQL – InnoDB

Ola pessoal,

Conforme divulgado no post anterior, vamos continuar a explicação sobre os engines do MySQL, falando hoje sobre o InnoDB.

O InnoDB foi implementado como Engine Default no MySQL a partir da versão 5.5, deixando de ser um plugin como nas versões anteriores.

Algumas das características cruciais:

– Prove suporte a transações baseadas em ACID.
– Prove suporte a chaves estrangeiras.
– Nível de bloqueio por campo e não por tabela.

Sim jovens, o InnoDB prove suporte a transações seguras, com a conformidade do ACID provendo todas as opções de commit, rollback e recuperação de falhas para proteger os dados envolvidos. Não sabe o que é o ACID? Recomendo a leitura deste artigo, pois é muito importante.

O seu nível de bloqueio é a partir do campo, logo se você esta efetuando uma operação de insert no campo da tabela, apenas aquele campo ficara em lock, enquanto a tabela estará disponível para novas instruções.

Para uma tabela com diversas instruções, esse recurso é uma mão na roda em questão de desempenho e concorrência. Seguem outras características interessantes sobre o InnoDB:

– Armazenamento: Limite de 64TB per table
– Suporte a Transações e Chave estrangeira;
– Suporte a MVCC;
– Bloqueio de linha ao invés de tabelas
– Backup e Recuperação: Sim
– Recuperação automática de falhas: Sim

Para utilizar uma tabela com o InnoDB, basta definir o engine no final da criação da sua tabela, exemplo:

Caso deseje alterar uma tabela existente, você pode executar:

É possível definir o InnoDB como padrão na criação de novas tabelas , incluindo a variável no arquivo de configuração do MySQL (my.cnf):

*Processo necessário em versões anteriores a 5.5.

Diferente do MyISAM o InnoDB trabalha diretamente na memória, onde utiliza o pool de buffers para manter os dados em cache, e os índices na memória. Ou seja, grandes operações sendo feitas diretamente na memória, traz um ganho muito maior ao invés de ter que ir sempre no disco.
Continue lendo