MySQL: ferramentas de monitoramento

A comunidade do MySQL está bem servida de ferramentas de monitoramento de seus bancos de dados.
Neste texto, vou apresentar 4 delas, para monitorar o andamento das atividades no seu servidor MySQL — elas podem mostrar dados diversos, tais como o uptime, carga e performance do servidor no Linux.
O uptime, no MySQL tal como no Linux, se refere ao tempo em que o servidor está rodando ininterruptamente, desde a ultima vez em que foi ligado ou reiniciado.
Com as ferramentas, que seguem, é possível obter informações sobre o funcionamento do servidor do banco de dados — quantas queries por segundo estão sendo executadas, quantas threads, se há requisições lentas, entre outros dados estatísticos.

Mytop

Feito pra rodar no console, tal como o seu (quase) homônimo top, escrito em Perl, por Jereme Zawodny, o mytop roda no terminal e exibe as estatísticas do seu servidor MySQL — total de consultas, requisições lentas, uptime, carga etc. em formato tabular.

mysql mytop monitoramento.
Clique para ampliar.

O programa ajuda administradores a otimizar e melhorar a performance do MySQL na manipulação de grandes requisições e a reduzir a carga do servidor.

Mtop

O mtop é uma ferramenta similar ao mytop — também foi escrito em Perl e apresenta os dados de forma tabular similar ao top e ao mytop.
O aplicativo monitora as queries MySQL que estejam demorando mais a ser concluídas e as termina/interrompe após um determinado tempo.
Adicionalmente, permite acompanhar problemas relacionados à baixa performance, informações de configuração, estatísticas do funcionamento de servidor.
A última versão do mtop, para download, é de 2004 e, portanto, não se trata de uma ferramenta atualizada — e pode não funcionar na sua versão do MySQL.

Innotop

O aplicativo innotop é uma ferramenta de linha de comando em tempo real, usada para investigar e monitorar servidores MySQL locais ou remotos, rodando mecanismos de armazenamento (storage engines) InnoDB.
A ferramenta tem vários recursos e inclui opções e modos de operação que ajudam a monitorar diversos aspectos da performance do MySQL e ajuda a encontrar, se houver, problemas no servidor.
O aplicativo não tem pacotes nos repositórios oficiais na maioria das distribuições Linux.
No Red Hat, é preciso ativar o repositório (de terceiros) epel e, em seguida, instalar com o comando yum.

Mysqladmin

De todas as ferramentas, esta é a única que vem instalada por padrão, junto com todos os outros aplicativos do MySQL.
Pronta para uso imediato após a instalação, o mysqladmin realiza uma incontável quantidade de operações — tais como o monitoramento de processos, verificação das configurações do servidor, recarga de privilégios, visualização do status corrente, ajuste e mudança da senha root do MySQL, criação e remoção de bancos de dados etc.
Você pode verificar o status da sua instalação MySQL com o seguinte comando do mysqladmin:

mysqladmin -u root -p version

que deve retornar algo semelhante a isto;

Enter password: 
mysqladmin  Ver 8.42 Distrib 5.5.35, for debian-linux-gnu on i686
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		5.5.35-0ubuntu0.13.10.2
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			2277 days 1 hour 27 min 47 sec

Threads: 3  Questions: 3204  Slow queries: 0  Opens: 235  Flush tables: 1  Open tables: 57  Queries per second avg: 0.000

LEIA MAIS

Conclusão

Das ferramentas descritas neste texto, para monitoramento de servidores MySQL, se destacam seguramente, mytop e innotop — sendo que a primeira é de facílima instalação em distribuições derivadas do Debian (Ubuntu, por exemplo) e a segunda em distribuições baseadas no Red Hat (CentOS, Fedora etc).
Se você precisa fazer monitoramento de transações, o innotop é a opção para você.
O mtop fica no artigo na posição do “só pra constar”. Ainda que se tratasse de um software que tivesse atingido o status de perfeito, o MySQL passou por diversas mudanças nos últimos 10 anos e ele não as acompanhou.
Se você não tem privilégios para instalar novos aplicativos no servidor, o mysqladmin é a opção para fazer o trabalho, uma vez que ele já vem no pacote do MySQL.

MyISAM: vantagens e desvantagens.

O mecanismo de armazenamento MyISAM, padrão no MySQL até a versão 5.5, é o mais usado na WEB, em armazéns ou depósitos de dados e vários outros tipos de aplicações.
Neste texto, vou mostrar algumas de suas características e as situações onde o seu uso é indicado e quando não é.
Escolher o mecanismo de armazenamento (ou storage engine) mais adequado pras suas aplicações é crucial e uma decisão errada tomada nesta escolha pode ser de difícil reversão.
Antes do MySQL 5.5.5, ao criar uma nova tabela, o padrão escolhido pelo sistema será o MyISAM, a menos que você especifique outro. A partir desta versão, o padrão será o InnoDB.

SAIBA MAIS:
O mecanismo de armazenamento MyISAM é derivado de um outro, mais antigo, chamado ISAM — Indexed Sequential Access Method, originalmente desenvolvido pela IBM, para ser usado em mainframes. Ainda dentro da IBM, o ISAM evoluiu pro VSAM — Virtual Storage Access Method.
Atualmente, a IBM promove o uso do DB2.
Era possível usar o mecanismo de armazenamento ISAM até o MySQL 3.23. A partir de então, ele se tornou indisponível, embora seu código ainda estivesse incluído até a versão 4.1.
Conheça outros mecanismos de armazenamento para MySQL.

As vantagens de usar o MyISAM

Atualmente, muitas distros Linux oferecem o MySQL em seus repositórios e, no Ubuntu 14.04 é a versão 5.5 que ainda estará lá, para ser baixada — onde o mecanismo de armazenamento padrão é o MyISAM. A Canonical tem planos de mudar do MySQL para MariaDB em um futuro próximo.
Eu posso citar, pelo menos, 4 boas razões para usar o MyISAM storage engine.

1 – a simplicidade

As tabelas MyISAM são simples. Se você é novato no assunto “bancos de dados” ou no que concerne ao MySQL, é recomendado começar a usar o MyISAM, antes de sair experimentando os outros.
Segue um exemplo de como criar um novo banco de dados e uma tabela no MySQL.
(Se você ainda não tem o MySQL instalado, dê uma olhada no artigo Como instalar o MySQL).
Para criar um banco de dados, usamos a declaração CREATE DATABASE. Em seguida, o selecionamos para uso, com USE. Veja como:

CREATE DATABASE teste;
USE teste;

Uma vez criado o banco e selecionado para uso, vamos criar uma nova tabela dentro dele:

CREATE TABLE teste.meuslivros (
id int UNSIGNED NOT NULL AUTO_INCREMENT,
titulo TEXT NOT NULL;
autor VARCHAR(30)
genero VARCHAR(20),
PRIMARY KEY(id))
ENGINE = MyISAM;

O exemplo, acima, mostra como é simples e rápida a criação de uma nova tabela no MySQL. Se você tiver interesse, o artigo – como criar tabelas no MySQL –, mostra o processo com mais exemplos e detalhes.

2 – otimização e base de conhecimento

Eu poderia dizer que o MyISAM é muito rápido – mais rápido que muitos outros storage engines, mas os benchmarks feitos por várias empresas mostram resultados muito diferentes entre si. Esta variação se deve às tarefas executadas, às configurações dos servidores e do mysqld, entre outros fatores. A melhor medição, quem faz, é você, dentro do seu ambiente de trabalho. Ainda assim, é notória a performance do mecanismo de armazenamento MyISAM, nos testes.
Em função do tempo em que o MyISAM tem estado “na estrada”, há muitos sistemas plenamente otimizados para usa-lo.
Se você sente que o seu sistema não está perfeitamente otimizado para uso do MyISAM, há uma extensa base de conhecimento disponível na Internet para ajudá-la(o) a resolver isto.

3 – indexação FULLTEXT e busca dentro do banco de dados

Considerando a tabela que criamos, imagine que precisamos realizar uma pesquisa, percorrendo títulos e gêneros dos livros.
Uma solução simples, possibilitada pelo MyISAM é adicionar um índice FULLTEXT. Veja o exemplo:

ALTER TABLE teste.livros ADD FULLTEXT alltext (autor, genero);

Agora, fica fácil encontrar todos os livros que contenham as palavras “mauro vasconcelos” e “infanto-juvenil”, dentro da tabela:

SELECT * FROM teste.livros WHERE MATCH(autor, genero) AGAINST ('mauro vasconcelos infanto-juvenil');

Com uma alteração na declaração acima, é possível obter o mesmo resultado, só que ordenado por relevância. Veja:

SELECT *, MATCH(autor, genero) AGAINST ('mauro vasconcelos infanto-juvenil') FROM teste.livros ORDER BY rel DESC;

4 – uso de recursos em ambientes limitados

Uma das vantagens de qualquer sistema que tem longa estrada percorrida é a estabilidade conquistada pelos anos de uso, pessoas envolvidas no projeto e seu contínuo desenvolvimento.
O MyISAM é o mais indicado em sistemas de poucos recursos, em servidores de menor capacidade de processamento e/ou com pouco espaço de armazenamento em disco ou memória RAM.
Nesta arena, ele se mostra imbatível nos benchmarks de que falamos no item anterior, mesmo que você não dedique algum tempo a otimizar o hardware e o mysqld.

As desvantagens do mecanismo de armazenamento de tabelas MyISAM

Se há situações em que o MyISAM é o mais indicado para uso, há várias outras em que ele é superado por outros mecanismos.
Aqui, vou mostrar alguns dos problemas que administradores e programadores enfrentam ao usar o MyISAM, no MySQL.

1 – integridade de dados

O MyISAM não tem suporte a transações ou a restrições de chaves estrangeiras.
Leve em conta uma aplicação bancária, onde ocorre uma transferência monetária – o que envolveria duas declarações SQL UPDATE: uma para debitar o valor de uma conta; outra para creditar o mesmo valor à outra conta.
Se houvesse uma falha no servidor, exatamente neste momento, você poderia acabar com o valor nas duas contas ou em nenhuma delas. O recurso de “transações”, presente no InnoDB, é o que evita este tipo de situação.

2 – recuperação de acidentes

O MySQL é estável e confiável. Contudo, as tabelas MyISAM podem voltar corrompidas, após uma falha.
O problema pode ser resolvido com o uso de um REPAIR TABLE – o que é um trabalho administrativo a mais, na verdade.

3 – travamento de tabelas

Ao adicionar ou atualizar um registro em uma tabela MyISAM, todas as outras mudanças são impedidas pelo travamento, até que aquela operação seja completada.
É difícil demonstrar ou provar que este comportamento diminui a performance ou causa problemas à sua aplicação web, mas há um consenso entre especialistas de que o mecanismo mais adequado para você não é o MyISAM, se a sua aplicação realiza grandes quantidades de inserções e atualizações à tabela.

LEIA MAIS:
  • Busca FULLTEXT — leia mais sobre pesquisas via FULLTEXT Index no MySQL.
  • Storage engines — conheça os mecanismos de armazenamento suportados pelo MySQL.
  • Tipos de dados — conheça os tipos de dados suportados dentro das tabelas MySQL
  • mysqlcheck — veja como restaurar uma tabela corrompida.
  • Bancos de dados — veja como criar novos bancos de dados no MySQL.
  • Tabelas — aprenda, através de exemplos, como criar tabelas no MySQL.

Conclusão: devo usar o MyISAM?

Há uma série de situações em que o MyISAM tende a ser a opção mais indicada, de acordo com a relação de vantagens e desvantagens apresentada, até agora. Nas situações, abaixo, a resposta é sim:

  • Você é iniciante no MySQL;
  • Sua aplicação web é simples e não precisa de transações;
  • Você precisa de velocidade;
  • Deseja usar buscas FULLTEXT;
  • Tem recursos de hardware limitados.

Já, se precisa fazer uso de “transactions” e a integridade dos dados é prioritária e crítica, outras opções devem ser consideradas.
Note que é comum se usar mais de um tipo de mecanismo de armazenamento dentro de um mesmo banco de dados. Algumas tabelas precisam usar o InnoDB, outras o MyISAM e outras podem usar o CSV – e todo mundo convive bem, sem problema algum.
Os tipos de aplicações mais indicados para usar o MyISAM são:

  • CMS – Content Management Systems, ou sistemas de gestão de contúdo;
  • Ferramentas de marcação de páginas favoritas online;
  • Leitores RSS;
  • Mecanismos de busca na web etc.

Espero que este texto tenha lhe sido útil e, se este for o caso, compartilhe com os seus amigos, nas redes sociais. Esta é sempre a melhor forma de agradecer e incentivar.

Mecanismos de armazenamento no MySQL ou Storage Engines

Tutorial Mysql - Storage Types - Mecanismos de armazenamentoEntenda os tipos de tabelas do MySQL ou os mecanismos de armazenamento (storage engines) e conheça os mais comuns.
O MySQL suporta diversos tipos de tabelas ou mecanismos de armazenamento. Ao entender os recursos presentes em cada tipo, você poderá criar e usar tabelas com maior eficiência e melhorar a performance de seus bancos de dados.
Neste texto, vou abordar 8 tipos de tabelas MySQL e explicar suas características, seus recursos, vantagens e desvantagens, no sentido de te ajudar a obter máxima eficiência e melhor performance do banco de dados.
Os mecanismos de armazenamento de dados são componentes de software dos bancos de dados que permitem criar, ler, atualizar e apagar dados do sistema.

MyISAM

O MyISAM estende os recursos do antigo ISAM. É otimizado para compressão, velocidade e as tabelas neste formato são portáveis entre várias plataformas e sistemas operacionais.
As tabelas neste formato, padrão até a versão 5.5, podem ter até 256 TB de tamanho – o que é um tamanho considerável. As tabelas MyISAM podem ser convertidas para tabelas comprimidas e somente leitura (read-only).
Ao iniciar, o sistema de banco de dados verifica as tabelas deste tipo e pode encontrar e corrigir setores defeituosos nas tabelas.
Este tipo é plenamente apropriado a ambientes com intensa leitura de dados e pouca escrita – melhor ainda, com nenhuma escrita. Sua principal deficiência é não ter suporte a transações e chaves estrangeiras.
Leia mais: No artigo “vantagens e desvantagens do MyISAM“, são delineados mais detalhadamente os casos em que este mecanismo de armazenamento é mais indicado ou não.

InnoDB

Este é, atualmente, o mecanismo padrão do MySQL. A Oracle é a mantenedora e dona do InnoDB (após ter adquirido, em 2005, a empresa responsável pelo seu desenvolvimento, a finlandesa Innobase Oy).
O banco de dados MariaDB e o Percona Server usam um fork deste mecanismo, chamado XtraDB – que costuma incorporar os novos recursos do InnoDB, tão logo eles sejam disponibilizados pela Oracle.
Entre seus recursos, cito o suporte total ao ACID, à transações, chaves estrangeiras, commit, rollback etc. Têm ótima performance e as tabelas neste mecanismo podem chegar a 64 Terabytes.
Tal como o seu antecessor, o InnoDB também é portável entre diferentes plataformas. E o MySQL também faz checagem e reparos nas tabelas, ao ser iniciado.
Resumindo, o InnoDB tem suporte a:

  • transações SQL e XA
  • tablespaces
  • chaves estrangeiras
  • indíces FULLTEXT
  • operações espaciais
  • colunas virtuais

MERGE

Trata-se de uma tabela virtual, que combina múltiplas tabelas MyISAM, que têm estruturas similares, em uma só tabela. A palavra inglesa merge pode ser usada com o significado de junção, mistura. Também é conhecido como mecanismo MRG_MyISAM. Uma vez que não tem seus próprios índices, este mecanismo usa os dos seus componentes.
Ao fazer uso deste mecanismo de armazenamento, você pode melhorar a performance da junção de várias tabelas. O MySQL só vai permitir as operações de SELECT, DELETE, UPDATE e INSERT em tabelas MERGE.
Se você executar o comando de remoção de tabela DROP TABLE, só as especificações MERGE serão removidas – as tabelas envolvidas “na mistura” não serão afetadas pela operação.

MEMORY (HEAP)

Este storage engine, que já foi conhecido como HEAP, cria tabelas com propósitos específicos, cujo conteúdo fica armazenado na memória.
Uma vez que os dados ficam muito vulneráveis a problemas de hardware, como oscilações na rede elétrica, é seguro usar este mecanismo apenas para tabelas temporárias, com restrições de escrita (que não podem ser alteradas) e com dados emprestados de outras tabelas.
A principal vantagem deste mecanismo é a agilidade com que se pode manipular os seus dados.

ARCHIVE

É otimizado para inserção ágil e rápida de grande quantidade de dados em arquivos comprimidos e não indexados, para economizar espaço.
Os registros são comprimidos ou descomprimidos sob demanda, à medida em que são inseridos ou requisitados.
CSV Storage Engine Mecanismo tipo de armazenamento

CSV

O mecanismo de armazenamento CSV (Comma Separated Values — valores separados por vírgulas)armazena os dados em arquivos de texto, separando os registros com vírgulas. Este é o mecanismo usado em listas de contatos do cartão SIM do seu celular:

"Odair José","1136789890"
"Reginaldo Rossi","8198786543"

É um padrão que ocupa pouquíssimo espaço, por sua simplicidade e tem maior compatibilidade com aplicativos tais como planilhas eletrônicas (Excel, LibreOffice etc).

FEDERATED

Ao criar uma tabela de dados com o uso de um dos mecanismos de armazenamento padrão (MyISAM, CSV ou InnoDB), o arquivo conterá uma definição de seu conteúdo e dados. Uma tabela FEDERATED consiste de uma definição dos dados que armazena – contudo, os seus dados estão fisicamente armazenados em um servidor remoto.
As tabelas deste mecanismo, consistem, portanto de 2 elementos:

  • um servidor remoto com uma tabela de banco de dados, contendo uma definição da tabela (em formato .frm) e a tabela à qual está associada. O tipo de tabela pode estar em qualquer formato que este servidor suporte;
  • um servidor local com uma tabela contendo uma definição que corresponde à da tabela no servidor remoto. Não há um arquivo de dados no servidor local – há uma conexão para a tabela no servidor remoto.

Blackhole

O mecanismo “buraco negro” aceita dados, mas não os armazena. Ao tentar recuperar os dados “armazenados”, você receberá sempre uma resposta vazia. Pode ser usado em projetos de bancos de dados distribuídos, onde estes são automaticamente replicados, mas não armazenados localmente.
É comum ser usado para rodar testes de performance.

LEIA MAIS
  • O MyISAM — Aplicabilidade, vantagens e desvantagens deste mecanismo de armazenamento.
  • Tipos de dados — Conheça os tipos que você pode usar nas tabelas.

Conclusão

Os formatos padrão, InnoDB e MyISAM, acabam por ser as escolhas mais seguras na grande maioria dos casos. Se houver necessidade de fazer transações, escolha o InnoDB (padrão nas versões atuais do MySQL). Em casos fora do comum, um destes outros mecanismos que você conheceu pode ser o mais adequado.