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

Engines MySQL – MyISAM

Olá pessoal,

Neste e nos próximos posts, iremos tratar dos engines disponíveis no MySQL! Em alguns tópicos já falamos sobre a diferença entre InnoDB e MyISAM e como consultar o Status das Engines no servidor.

Vamos explorar um pouco mais as características de cada engine, tanto como a estrutura e vantagens, começando hoje com o MyISAM.

O MyISAM foi o engine default do MySQL em versões anteriores a 5.5, ou seja, se uma tabela fosse criada sem a especificação do engine, ela era atribuída ao MyISAM.

Uma tabela MyISAM tem referência de três arquivos físicos no servidor, são eles:

.frm: armazena o formato da tabela
.MYD: armazena os dados da tabela
.MYI: armazena os índices da tabela

A referência é feita com o nome da tabela.extensão, e todos os arquivos são armazenados no datadir (diretório de dados) do servidor. Com isso, já podemos apontar a primeira vantagem ao utilizar o MyISAM:

Os índices de cada tabela são separados em arquivos diferentes dos dados, trazendo um aumento de performance no momento da consulta.

Devido a esta estrutura, as tabelas MyISAM ocupam pouco espaço no disco e também são portáteis, ou seja, você consegue copiar os arquivos de cada tabela em um servidor, e ‘exporta-los’ para outro servidor de forma direta, sem comprometer a integridade dos dados.

Porém antes de fazer isso, certifique-se que o servidor MySQL de destino tenha a mesma versão da origem, que não exista nenhum processo de escrita na tabela a ser copiada/migrada, ou de um stop no mysqld para copiar os arquivos.
Continue lendo