Performance do banco de dados MySQL

Um dos primeiros Sistemas de Gerenciamento de Banco de Dados, também conhecido pela sigla SGBD, surgiu no final da década de 60. Nesta época, até pela sua incipiência, já contava com grandes problemas como inconsistência, dificuldade de acesso às informações contidas em arquivos e falta de controle de acesso concorrente em ambiente multiusuário e de muitos processos. Exemplos destes primeiros sistemas são IBM IMS e Total.

A grande melhoria veio com o Gênesis na década de 70 que implementando o modelo relacional, tendo como estrutura interna somente relações que se interligavam através das formas normais, cálculo e álgebra relacional, além dos índices B-tree para grande volume de dados.

Os SGBDs passaram a ter grande importância devido ao interesse gerado pelas grandes organizações em armazenar informação para registrarem seus processos, desde a gestão do conhecimento sobre suas operações até informações de fluxo de produção. Assim, acabaram por evoluírem desses sistemas menos robustos, de pouca segurança, confiança e de difícil armazenamento de dados para aqueles que hoje de fato, são conhecidos por SGBDs comerciais.

Ainda hoje, eles contam com o armazenamento de informações em arquivos em disco, mas agora, sendo gerenciados por um conjunto de softwares que controlam o acesso aos bancos de dados. Com esta evolução, são consideradas novas estruturas com o objetivo de organizar o armazenamento dos vários tipos de informação, como XML, binários e outros.

Neste cenário, realizar operações de forma eficiente e eficaz sobre grandes coleções de dados é uma questão fundamental, já que o desempenho de um SGBD é medido a partir destes dois fatores. Cita-se a eficiência, mediante a execução de consultas e alterações constantes nos dados armazenados. A eficácia se refere ao bom funcionamento e atenção aos interesses do indivíduo que utiliza esta ou aquela marca dentre as várias que existem no mercado, o que pode ser relacionado também com a qualidade de um produto.

A maioria das empresas que utilizam servidores de bancos de dados hoje em dia, convivem com o problema da lentidão ao inserir e recuperar dados de seus sistemas gerenciadores de bancos de dados. Este fato é devido aos gargalos não identificados no momento da concepção do modelo do banco de dados. Verifica-se então que houve uma preocupação somente com o crescimento vertical da base de dados e não se atentou para o desenvolvimento de uma estratégia para seu crescimento horizontal evitando assim problemas de falta de performance e fatoração do projeto ou mesmo o abandono do sistema por completo.

Atualmente, são encontrados no mercado diversos SGBDs, os quais na maioria dos casos são instalados, configurados e utilizados com todos os seus parâmetros em valores defaults (padrões). Não é levado para o tipo de aplicação para o qual serão utilizados, o hardware, e até mesmo o sistema operacional onde estão instalados. Com isso, nem sempre se obtém o melhor desempenho do sistema, visto que, diversos parâmetros podem ser considerados e ajustados em um SGBD.

Objetivos: Serão empregadas técnicas de tuning em um banco de dados, a fim de retratar a melhor configuração para que o mesmo ofereça maior eficiência no armazenamento e recuperação de dados. Será apresentado o tuning no banco de dados MySQL para aumento da performance, evidenciando que o mesmo pode ser utilizado em um ambiente corporativo, assim como outros bancos, comumente utilizados hoje e adquirir ganho em performance.

O Sistema Gerenciador de Banco de Dados é o software que manipula todos os acessos ao Banco de Dados. O SGBD funciona como uma interface entre o usuário e o Banco de Dados, ou seja, o SGBD manipula todas as solicitações efetuadas pelos usuários, como criação de tabelas, inserção de dados, recuperação de dados. Um SGBD possui como objetivos isolar o usuário de detalhes mais internos do Banco de Dados (abstração de dados) e prover a independência de dados às aplicações (estrutura física de armazenamento e estratégia de acesso).

Existem algumas características da nova geração de SGBDs que se destacam sobre os antigos programas de banco de dados da década de 60. Estas características são conhecidas também pela sigla ACID:

1. Atomicidade (atomicity) - Existem operações sobre os dados que devem ser processadas de forma conjunta e indivisível, ou seja, todas ou nenhuma das operações são realizadas;
2. Consistência (consistency) – como consequência da atomicidade, uma transação é executada de forma isolada, promovendo a consistência dos dados;
3. Isolamento (isolation) – controle do acesso aos dados por diversos usuários, sem perder a consistência dos mesmos. Uma transação não toma conhecimento de outras transações concorrentes do sistema;
4. Durabilidade (durability) – promove a persistência e a durabilidade após as alterações dos dados em um meio de armazenamento confiável, podendo se recuperar depois de algum tipo de falha física ou queda de energia.

Os SGBDs relacionais, através de suas estruturas, consolidaram-se sobre os bancos de dados tradicionais principalmente no desenvolvimento de aplicativos, pois muitos dos controles de manipulação de dados, manutenção e consultas foram delegados ao SGBD, facilitando e reduzindo o trabalho para os desenvolvedores.

Curso: Curso Completo de MySQL

A arquitetura de banco de dados mais difundida pela literatura é a “Three-Schema” (também conhecida como arquitetura ANSI/SPARC), sendo a mesma proposta por Tsichritzis & Klug em 1978.

A seguir a meta desta estrutura:

• Nível interno: descreve uma estrutura de armazenamento físico. Para tanto, é utilizado um modelo de dados físico, que descreve os detalhes de armazenamento de dados bem como os caminhos de acesso à base de dados;
• Nível conceitual: descreve a estrutura de toda a base de dados. A base de dados é descrita de forma global, são omitidos os detalhes da estrutura de armazenamento físico, concentra-se na descrição de entidades, tipos de dados, relacionamentos e restrições. Neste nível, pode-se utilizar um modelo de dados de alto-nível ou um modelo de dados de implementação.
• Nível externo: os esquemas externos descrevem as visões da base de dados de um dado grupo de usuários. Cada visão apresenta a parte da base de dados que um grupo de usuários pode ter acesso e esconde deste o restante da base de dados. Geralmente utiliza-se neste nível um modelo de dados de alto-nível ou um modelo de dados de implementação.

Performance do banco de dados MySQL – Parte 2

Veja abaixo a segunda parte do artigo - Agora todas as partes foram compiladas em um único artigo. Bons estudos :)

O MySQL se tornou o mais popular banco de dados open source do mundo porque possui consistência, alta performance, confiabilidade e é fácil de usar. Atualmente é usado em mais de 70 milhões de instalações em todos os continentes (inclusive na Antártica), que vão desde instalações em grandes corporações a aplicações embarcadas.

Além disso, o MySQL se tornou a escolha de uma nova geração de aplicações que utilizam Linux, Apache, MySQL, PHP (modelo LAMP) e WEB 2.0. Ele pode ser utilizado tranquilamente em mais de 20 plataformas, incluindo Linux, Windows, HP UX, AIX, Netware, Solaris e outros, oferecendo flexibilidade e controle.

Coleta de estatísticas

O primeiro caminho para conseguir um desempenho adequado de um SGBD, é tomar boas decisões durante seu projeto. Várias considerações deverão ser feitas durante a fase de projeto, entre elas: o volume esperado de dados em cada relação do sistema, quais consultas serão realizadas com mais frequência, entre outros.

Mas, percebe-se na maioria dos sistemas que seu real desempenho só pode ser conseguido após algum tempo de uso e muitas das considerações que os projetistas haviam feito podem mostrar-se incorretas.

Portanto, uma fase subsequente de ajuste do sistema torna-se necessária, com base em dados reais de seu comportamento e com o objetivo de maximizar o desempenho e a estabilidade.

Esta fase é chamada de database tuning ou simplesmente tuning - palavra a qual se pronuncia como se lê. As informações coletadas no projeto físico podem ser revisadas por meio da coleta de estatísticas sobre os padrões de uso da corporação.

A utilização dos recursos, bem como o processamento interno do SGBD pode ser monitorada para revelar gargalos, tais como a disputa pelos mesmos dados por vários usuários ou processos simultaneamente.

Assim, o volume de atividades e o tamanho dos dados podem ser estimados com maior precisão. Dessa forma, surge a necessidade de monitoramento e revisão do projeto físico do banco de dados constantemente visto que os objetivos do tuning (sintonia) são os seguintes:

1. Fazer com que as aplicações sejam executadas mais rapidamente,
2. Diminuir o tempo de resposta de consultas/transações;
3. Melhorar o desempenho geral das transações.
4. A revisão das decisões de projeto na fase de sintonia consiste em um ajuste do projeto, as informações de entrada para o processo de sintonia incluem estatísticas relacionadas a diversos fatores.

Um SGBD em particular, pode coletar internamente as seguintes estatísticas:

• Tamanho de tabelas individuais;
• Número de valores distintos em uma coluna;
• Número de vezes que uma consulta ou transação em particular é submetida/executada em um intervalo de tempo.

Essas e outras estatísticas criam um perfil do conteúdo e do uso do banco de dados. Podem ser obtidas ainda as seguintes informações a partir desse monitoramento de atividades do banco de dados:

• Estatísticas de armazenamento: Informações sobre a alocação de recursos para armazenamento de tabelas e de índices, e, portas de buffer.
• Estatísticas de desempenho de entrada/saída (I/O): Atividade total de leitura/escrita (paginação) do disco.
• Estatísticas de processamento de consultas: Tempos de execução de consultas, tempos de otimização de consultas.
• Estatísticas relacionadas a bloqueios/registro de log: Taxas de definição de diferentes tipos de bloqueios, taxas de desempenho de transações e registros de log de atividades.

A maioria das estatísticas citadas se refere a transações, controle de concorrência e recuperação de dados. Mas o tuning de bancos de dados envolve tratar diretamente as excessivas disputas por bloqueios, a concorrência entre as transações, a sobrecarga de registrar logs, o armazenamento desnecessário de dados, a otimização do tamanho do buffer, o escalonamento de processos e, finalmente, a alocação de recursos tais como discos, memória e processos para uma utilização mais eficiente.

Saiba mais: Criando meu primeiro banco de dados no MySQL

Principais configurações para ajustes de desempenho

Pode-se determinar o tamanho padrão do buffer utilizado pelo servidor MySQL usando através do comando:

Este comando produz uma lista contendo todas as opções do MySQL e as variáveis configuráveis do sistema. Para um servidor MySQL em execução no momento, pode-se ver os valores atuais das variáveis do sistema através do comando:

Utilizando o comando MySQ> SHOW STATUS , pode-se verificar algumas estatísticas e indicadores de status do servidor MySQ. Com isso, é possível verificar as variáveis de status para observação de possíveis gargalos.

Caso existam muitas conexões simultâneas, os problemas de troca de dados podem ocorrer a menos que o MySQd estiver configurado para utilizar um mínimo de memória para cada conexão. Para este problema, podem ser utilizados os seguintes recursos:

ou ainda:

Caso seja necessário executar um GROUP BY ou ORDER BY em tabelas que são muito maiores do que a memória disponível, deve-se aumentar o valor de record_rnd_buffer_size para acelerar a leitura de registros após a operação de ordenação.

Se for especificada uma opção na linha de comando para o MySQd, ou MySQd_safe ele permanece em efeito somente para aquela chamada do servidor.

Para usar a opção toda vez que o servidor executa, coloque-o em um arquivo de opção. Para ver os efeitos de uma mudança de parâmetro, basta executar o comando:

Bem, encerrarei aqui por enquanto e voltarei para apresentar as características dos Tipos denStorage Engines (MyISAM, Maria, InnoDB e Falcon);

Performance do banco de dados MySQL – Parte 3

Veja abaixo a segunda parte do artigo - Agora todas as partes foram compiladas em um único artigo. Bons estudos :)

O engine MyISAM foi projetado para ambientes com uma baixa concorrência de dados ou seja ambientes onde as leituras (Selects) de dados são predominantes não ocorrendo processos de escrita (Updates/Inserts) com frequência, neste ambiente, o MyISAM apresenta-se como uma solução extremamente eficiente.

Para aplicações que necessitam de uma maior granularidade de locks(Bloqueio de dados durante a utilização por outro usuário ou processo.) isto é, número elevado de concorrência e predominância de comandos de escrita sobre os comandos de leitura, o MyISAM não se apresenta como uma boa solução.

Suas principais características são:

1. O MyISAM é o tipo de tabela padrão no MySQL;
2. Suporta apenas o lock de tabela, isto é, faz com que uma escrita bloqueie completamente o acesso a todos os dados armazenados na tabela que está sendo alterada;
3. Manipulação mais flexível da coluna AUTO_INCREMENT;
4. Suporta índices FULLTEXT e comprimidos;
5. Pode ser convertido em tabelas rápidas, pequenas e de leitura apenas;
6. Grande desempenho para leitura e escrita quando é baixa a concorrência;
7. Os índices são armazenados em árvores binárias balanceadas;
8. Não provê controle de transações e nem integridade referencial;

O storage engine Maria

O objetivo do storage engine Maria é ser uma versão com as características do MyISAM porém, com suporte a recuperação de falhas e logs mais detalhados (infelizmente o MyISAM não possui estas funcionalidades). São suportadas tabelas não-transacionais, com gravação imediata em disco ou transacionais, que podem ser recuperadas em caso de falhas e com alterações rigidamente armazenadas no log.

A recuperação de falhas ocorre automaticamente, as operações que devem ser protegidas contra falhas deverão ser inseridas dentro de cláusulas LOCK TABLES(travar tabelas) e UNLOCK TABLES (destravar tabelas).

Ao ser implementado, as transações serão executadas com Atomicidade, Consistência, Isolamento e Durabilidade (princípio ACID) ou seja, as transações são atômicas por serem indivisíveis(ou é realizado todo um conjunto de instruções ou nenhum deles), são consistentes porque devem obedecer as regras do banco de dados, são isoladas porque instruções concorrentes não devem ter acesso aos dados no estado intermediário de uma transação, e são permanentes porque o resultado da transação deve ser consolidado, mesmo em caso de falhas.

O storage engine InnoDB foi desenvolvido para atender ambientes computacionais onde é necessário o controle mais apurado das transações (commit/rollback) e de níveis de isolamento, e para sistemas onde há um número elevado de acessos de leitura e escrita simultâneos. Possui suporte a integridade referencial (foreign key e constraints), bem como uma ferramenta de backup online chamada InnoDB Hot Backup, que pode ser adquirida separadamente.

Desenvolvido pela InnoDBase Oy , o InnoDB é um tipo de tabela transacional que a partir da versão 4.0 do MySQL passou a ser parte integrada das distribuições do MySQL. Ele apresenta, além da capacidade transacional, outros recursos importantes tais como:

1. Suporte para transações de Atomicidade, Consistência, Isolamento e Durabilidade (ACID);
2. Suporta FOREIGN KEYS e integridade referencial com implementação dos constraints SET NULL, SET DEFAULT , RESTRICT e CASCADE;
3. Alto desempenho com grandes volumes de dados e um número elevado de concorrência entre leitura e escrita;
4. Implementa o lock de registro o que fornece um melhor gerenciamento da concorrência, como Oracle, DB2...
5. Facilidade de implementação dos níveis de isolamento;
6. Ferramenta de backup on-line (deve ser adquirida separadamente).

O InnoDB não exige nenhum recurso específico, mas, em determinadas situações, este é utilizado de forma inadequada, tornando-se um pouco mais lento que o MyISAM, por isso a escolha do motor de armazenamento ou Engine é um fator importantíssimo para a performance da aplicação. Ele tem se mostrado extremamente rápido se comparado com outros SGBDs transacionais.

Segundo BIANCHI (Pós Graduação FAGOC, 2008 Discussão em sala de aula), o InnoDB fornece ao MySQL as características de funcionamento de outro SGBD comercial, o ORACLE, por trazer consigo os recursos de tablespace, redo log, undo log e segmentos de rollback.

A única diferença é que o tablespace do InnoDB é compartilhado por todas as tabelas localizadas em um ou mais bancos de dados gerenciados por uma Instância MySQ, enquanto que no ORACLE podemos trabalhar melhor a quantidade e a localização destas estruturas lógicas.

Uma das principais características do MySQL é o poder de agregar em um mesmo servidor de bancos de dados pontos similares a outros tipos de armazenamento. Tido como um plugable database, o MySQL agora traz o storage engine chamado Falcon, que por sua vez, conta com características muito relevantes como integridade referencial, suporte à transações e a criação de múltiplos tablespaces e dá ao MySQL um comportamento parecido com ORACLE e o SQL Server, mas isto, veremos no próximo artigo, espero vocês lá, Abraços.

Performance do banco de dados MySQL – Parte 4

Veja abaixo a segunda parte do artigo - Agora todas as partes foram compiladas em um único artigo. Bons estudos :)

O storage engine Falcon

A arquitetura do Falcon embute em si dois pontos que chamam a atenção que é ter recursos avançados com muita simplicidade, que provê ótima performance em bancos de dados com grande volume de transações, e a necessidade de pouca manutenção por apresentar poucos problemas.

Sua aplicação é recomendada também em arquiteturas habilitadas para suportar grande quantidade de memória, multi-threaded (Processamento múltiplo de dados) ou multi-core CPUs (Central de processamento único (CPU) com arquitetura de Múltiplos núcleos).

Sua arquitetura baseia-se nos sete componentes a seguir:

• User Tablespaces: contém os dados e índices atuais armazenados em um banco de dados do storage engine;
• Falcon Serial Log: contém as modificações mais atuais que receberam um COMMIT, modificações nos índices e informações sobre transações. Tal log também controla atividades relacionadas ao retorno do servidor MySQL após um crash do banco de dados.
• Page Cache: região de memória destinada ao armazenamento de páginas de dados que estão sendo escritas ou lidas;
• System metadata: tablespace interno ao storage engine que armazena informações internas e relevantes ao seu funcionamento;
• Record cache: região de memória que armazena dados ainda não receberão um COMMIT e continuam a ser manipulados;
• System cache: região de memória que contém informações do contexto das transações correntes, index accelerators e sistemas de metadados;
• Worker threads: são threads que movem os dados já escritos no log do storage engine para dentro do Page Cache, do Page Cache para o disco e também gerencia dados BLOB.

O Falcon é uma das novidades que a MySQL AB já apresentou em alguns releases disponíveis para testes e exibição deste storage engine é incorporado na versão 6.0 do SGBD MySQ.

Este storage engine apresenta ainda uma série de opções que podem ser adicionadas ao arquivo de configuração (my.ini ou my.cnf) do servidor de bancos de dados MySQL para torná-lo mais adequado ao sistema computacional em uso. Com o Falcon, todos os dados pertencentes a um banco de dados são armazenados em um arquivo de tablespace em um diretório do localizado no sistema de arquivos.

Por padrão, o tablespace falcon_user será utilizado para armazenar tabelas, independente de qual banco de dados está pertencer.

Uma preocupação frequente é a fragmentação do espaço causada por dados excluídos onde os SGBD"s não conseguiam por si só reutilizar tais espaços, sendo necessário reorganizar o banco a fim de se obter uma forma mais eficiente de armazenamento de objetos individuais dentro da tabela.

O Falcon reutiliza completamente as páginas em branco que resultaram de dados apagados, por isso não se terá necessidade de realizar reestruturações constantes de tabelas.

Bom, terminamos assim este estudo sobre desempenho de banco de dados, aqui foi apresentado o SGBD MySQL, mas estas práticas podem ser adotadas nos demais bancos de dados a fim de torná-los cada vez melhores de acordo com a aplicação e o hardware disponível.

Considerações finais

É possível verificar a simplicidade da utilização do MySQL bem como o seu poder de controle e administração dos dados nele armazenados.

Verifica-se que o tuning, que é um processo de evolução da análise e otimização que pode ser empregada nos SGBDs, objetiva a melhora do seu desempenho de acordo com a aplicação utilizada.

Foi enfatizada a configuração dos parâmetros do SGBD MySQL para otimizá-lo, visto que, um ambiente que apresenta inúmeras transações concorrentes executando consultas simples ou mesmo complexas pode apresentar problemas de gargalos, onde uma pessoa despreparada pode culpar até mesmo o próprio SGBD.

Com isso, fica visível a capacidade do mesmo de gerenciar grandes bases de dados com segurança e confiabilidade.

Com tudo, deve-se observar que ao modificar certos parâmetros do MySQL ou de qualquer outro SGBD, há uma melhora significativa de desempenho, mas, liberar demasiadamente recursos do sistema para o SGBD nem sempre resulta em aumento de desempenho, muito pelo contrário, às vezes provoca perda do mesmo.

Conclui-se assim, que no momento de escolher um SGBD para uma aplicação, é necessário também saber quais recursos serão necessários e o que o SGBD oferece afim de não prejudicar o desempenho do sistema como um todo.

Bom, é isto o que tinha para compartilhar, espero que gostem.