XOOPS Brasil

 

Capítulo 5. Otimização do MySQL

Índice

5.1. Visão Geral da Otimização
5.1.1. Limitações do Projeto MySQL/Trocas
5.1.2. Portabilidade
5.1.3. Para que Utilizamos o MySQL?
5.1.4. O Pacote de Benchmark do MySQL
5.1.5. Utilizando seus Próprios Benchmarks
5.2. Otimizando SELECTs e Outras Consultas
5.2.1. Sintaxe de EXPLAIN (Obter informações sobre uma SELECT)
5.2.2. Estimando o Desempenho de uma Consulta
5.2.3. Velocidade das Consultas que Utilizam SELECT
5.2.4. Como o MySQL Otimiza Cláusulas WHERE
5.2.5. Como o MySQL Otimiza IS NULL
5.2.6. Como o MySQL Otimiza Cláusulas DISTINCT
5.2.7. Como o MySQL Otimiza LEFT JOIN e RIGHT JOIN
5.2.8. Como o MySQL Otimiza Cláusulas ORDER BY
5.2.9. Como o MySQL Otimiza Cláusulas LIMIT
5.2.10. Performance das Consultas que Utilizam INSERT
5.2.11. Performance das Consultas que Utilizam UPDATE
5.2.12. Performance das Consultas que Utilizam DELETE
5.2.13. Mais Dicas sobre Otimizações
5.3. Detalhes sobre Locks
5.3.1. Como o MySQL Trava as Tabelas
5.3.2. Detalhes sobre Lock de Tabelas
5.4. Otimizando a Estrutura de Banco de Dados
5.4.1. Opções do Projeto
5.4.2. Deixando os Dados com o Menor Tamanho Possível
5.4.3. Como o MySQL Utiliza Índices
5.4.4. Índices de Colunas
5.4.5. Índices de Múltiplas Colunas
5.4.6. Como o MySQL Conta as Tabelas Abertas
5.4.7. Como o MySQL Abre e Fecha as Tabelas
5.4.8. Desvantagem em Criar um Número Grande de Tabelas no Mesmo Banco de Dados
5.5. Otimizando o Servidor MySQL
5.5.1. Sintonia dos Parâmetros em Tempo de Sistema/Compilação e na Inicialização
5.5.2. Parâmetros de Sintonia do Servidor
5.5.3. Como a Compilação e a Ligação Afetam a Velocidade do MySQL
5.5.4. Como o MySQL Utiliza a Memória
5.5.5. Como o MySQL Utiliza o DNS
5.5.6. Sintaxe de SET
5.6. Detalhes de Disco
5.6.1. Utilizando Links Simbólicos

Otimização é uma tarefa complicada porque necessita um entendimento do sistema como um todo. Enquanto for possível fazer algumas otimizações com pequeno conhecimento de seu sistema ou aplicação, quanto mais otimizado você desejar que o seu sistema esteja, mais terá que saber sobre ele.

Este capítulo tentará explicar e fornecer alguns exemplos de diferentes formas de otimizar o MySQL. Lembre-se, no entanto, que sempre existirão (cada vez mais difíceis) formas adicionais de deixar seu sistema mais rápido.

5.1. Visão Geral da Otimização

A parte mais importante para obter um sistema rápido é com certeza o projeto básico. Você também precisa saber quais tipos de coisas seus sistema estará fazendo, e quais são gargalos existentes.

Os gargalos mais comuns são:

  • Pesquisa em disco É necessário tempo para o disco encontrar uma quantidade de dados. Com discos modernos em 1999, o tempo médio para isto era normalmente menor que 10ms, portanto em teoria poderíamos fazer 100 buscas por segundo. Este tempo melhora moderadamente com discos novos e isso é muito difícil otimizar para uma única tabela. A maneira de otimizar isto é colocando os dados em mais de um disco.

  • Leitura de disco/Escrita (I/O) Quando o disco estiver na posição correta precisaremos que os dados sejam lidos. Com discos mais modernos em 1999, um disco retorna algo em torno de 10-20Mb/s. Isto é mais fácil de otimizar que as buscas porque você pode ler vários discos em paralelo.

  • Ciclos de CPU. Quando tivermos os dados na memória principal (ou se eles já estiverem lá) precisaremos processá-los para conseguir nosso resultado. O fator de limitação mais comum é ter ppequenas tabelas, comparadas com a memória. Mas, com pequenas tabelas, normalmente não teremos problemas com velocidade.

  • Largura de banda da memória. Quando a CPU precisa de mais dados que podem caber no cache da CPU a largura da banda da memória principal se torna um gargalo. Isto é um gargalo muito incomum para a maioria dos sistema, mas é bom estarmos ciente dele.

5.1.1. Limitações do Projeto MySQL/Trocas

Quando usamos o mecanismos de armazenamento MyISAM, o MySQL utiliza travamento de tabela extremamente rápidos (múltiplas leituras / única escrita). O maior problema com este tipo de tabela ocorre quando você tem uma mistura do fluxo fixo de atualizações e seleções lentas na mesma tabela. Se isto for um problema com algumas tabelas, você pode usa outro tipo de tabela. Veja mais informações sobre isto na Capítulo 7, Tipos de Tabela do MySQL.

O MySQL pode trabalhar com tabelas transacionais e não transacionais. Para trabalhar sem problemas com tabelas não transacionais (nas quais não se pode fazer um rollback se alguma coisa der errada), o MySQL tem as seguintes regras:

  • Todas as colunas possuem valor padrão.

  • Se você inserir um valor 'errado' em uma coluna, como um NULL em uma coluna NOT NULL ou um valor numérico muito grande em uma coluna numérica, o MySQL definir a coluna com o 'melhor valor possível' em vez de dar um erro. Para valores numéricos isto é 0, o menor valor possível ou o maior valor possível. Para strings into é tanto uma string vazia quanto a maior string possível que possa estar na coluna.

  • Todas as expressões calculadas retornam um valor que pode ser usado em vez de apresentar uma condição de erro. Por exemplo, 1/0 retorna NULL

Para mais informações sobre isto, veja Veja mais informações sobre isto na Seção 1.8.5, “Como o MySQL Lida com Restrições”.

O mostrado acima quer dizer que não se deve usar o MySQL para verificar o conteúdo dos campos, mas deve se fazer isto no aplicativo.

5.1.2. Portabilidade

Como todos os servidores SQL implementam diferentes partes de SQL, é trabalhoso escrever aplicativos SQL portáveis. Para selects/inserts muito simples é muito fácil, mas quanto mais recursos você precisa, mais difícil se torna. Se você quiser uma aplicação quue é rápida com muitos bancos de dados ela se torna ainda mais difícil.

Para fazer um aplicativo portável complexo você precisa escolher um número de servidores SQL com o qual ele deve trabalhar.

Você pode utilizar o MySQL programa/web-page crash-me - http://www.mysql.com/information/crash-me.php - para encontrar funções, tipos e limites que você pode utilizar com uma seleção de servidores de bancos de dados. O Crash-me agora testa quase tudo possível, mas continua compreensível com aproximadamente 450 itens testados.

Por exemplo, você não deve ter nomes de colunas maior do que 18 caracteres se desejar utilizar o Informix ou DB2.

Os programas de benchmarks e crash-me do MySQL são bastante independentes do bancos de dados. Dando uma olhada em como nós os tratamos, você pode sentir o que é necessário para escrever sua aplicação independente do banco de dados. Os benchmarks podem ser encontrados no diretório sql-bench na distribuição fonte do MySQL. Eles são escritos em Perl com a interface de banco de dados DBI (que resolve a parte do problema de acesso).

Veja http://www.mysql.com/information/benchmarks.html para os resultados deste benchmark.

Como pode ser visto nestes resultados, todos os bancos de dados tem alguns pontos fracos. Isto é, eles possuem diferentes compromissos de projeto que levam a comportamentos diferentes.

Se você procura por independencia de banco de dados, precisará ter uma boa idéia dos gargalos de cada servidor SQL. O MySQL é muito rápido para recuperação e atualização de dados, mas terá problemas em misturar leituras/escritas lentas na mesma tabela. O Oracle, por outro lado, possui um grande problema quando você tentar acessar registros que foram recentemente atualizados (até eles serem atualizados no disco). Bancos de dados transacionais geralmente não são muito bons gerando tabelas de resumo das tabelas log, nestes casos o travamento de registros é praticamente inútil.

Para fazer sua aplicação realmente independente de banco de dados, você precisará definir uma interface que possa ser expandida, por meio da qual você fará a manipulação dos dados. Como o C++ está disponível na maioria dos sistemas, faz sentido utilizar classes C++ para fazer a interface ao banco de dados.

Se você utilizar algum recurso específico para algum banco de dados (como o comando REPLACE no MySQL), você deve codificar um método para os outros serviodores SQL para implementar o mesmo recurso (mas mais lento). Com o MySQL você pode utilizar a sintaxe /*! */ para adicionar palavras chave específicas do MySQL para uma query. O código dentro de /**/ será tratado como um comentário (ignorado) pela maioria dos servidores SQL.

Se alta performance REAL é mais importante que exatidão, como em algumas aplicações WEB, uma possibilidade é criar uma camada de aplicação que armazena todos os resultados para lhe fornecer uma performance ainda mais alta. Deixando resultados antigos 'expirar' depois de um tempo, você pode manter o cache razoavelmente atual. Isto é muito bom no caso de uma carga extremamente pesada, pois neste caso você pode aumentar o cache dinamicamente e configurar o tempo de expiração maior até que as coisas voltem ao normal.

Neste caso a informação de criação de tabelas devem conter informações do tamanho inicial do cache e com qual frequência a tabela, normalmente, deve ser renovada.

5.1.3. Para que Utilizamos o MySQL?

Durante o desenvolvimento inicial do MySQL, os recursos do MySQL foram desenvolvidos para atender nosso maior cliente. Eles lidam com data warehousing para alguns dos maiores varejistas na Suécia.

De todas as lojas, obtemos resumos semanais de todas as transações de cartões de bonus e esperamos fornecer informações úteis para ajudar os donos das lojas a descobrir como suas campanhas publicitárias estão afetando seus clientes.

Os dados são bem grandes (cerca de 7 milhões de transações por mês), e armazenamos dados por cerca de 4-10 anos que precisamos apresentar para os utilizadores. Recebemos requisições semanais dos clientes que desejam ter acesso 'instantâneo' aos novos relatórios contendo estes dados.

Resolvemos este problema armazenando todas informações mensalmente em tabelas com transações compactadas. Temos um conjunto de macros (script) que geram tabelas resumidas agrupadas por diferentes critérios (grupo de produto, id do cliente, loja...) das tabelas com transações. Os relatórios são páginas Web que são geradas dinamicamente por um pequeno shell script que analisa uma página Web, executa as instruções SQL na mesma e insere os resultados. Nós usariamos PHP ou mod_perl mas eles não estavam disponíveis na época.

Para dados graficos escrevemos um ferramenta simples em C que pode produzir GIFs baseados no resultado de uma consulta SQL (com alguns processamentos do resultado). Isto também é executado dinamicamente a partir do script Perl que analisa os arquivos HTML.

Na maioria dos casos um novo relatório pode simplesmente ser feito copiando um script existente e modificando a consulta SQL no mesmo. Em alguns casos, precisamos adicionar mais campos a uma tabela de resumo existente ou gerar uma nova, mas isto também é bem simples, pois mantemos todas as tabelas com as transaçõs no disco. (Atualmente possuimos pelo menos 50G de tabelas com transações e 200G de outos dados do cliente.)

Nós também deixamos nossos clientes acessarem as tabelas sumárias diretamente com ODBC para que os utilizadores avançados possam também fazer experimentar com os dados.

Nós não tivemos nenhum problema lidando com isso em um servidor Sun Ultra SPARCstation (2x200 Mhz) bem modesto. Atualmente atualizamos um de nossos servidores para um UltraSPARC com 2 CPUs de 400 Mhz, e planejamos lidar com transações no nível de produto, o que pode significar um aumento de pelo menos dez vezes nosso volume de dados. Acreditamos que podemos lidar com isto apenas adicionando mais disco aos nossos sistemas.

Também estamos experimentando com Intel-Linux para obter mais poder de CPU por um melhor preço. Agora que possuimos o formato binários do bancos de dados portáveis (a partir da versão 3.23), começaremos a utilizá-lo para partes da aplicação.

Nossa sensação inicial é que o Linux irá atuar muito melhor em cargas baixas e médias e o Solaris irá atuar melhor quando você começar a ter uma carga alta pelo uso extremo de IO de disco, mas ainda não temos nada conclusivo sobre isto. Depois de algumas discussões com um desenvolvedor do kernel do Linux, concluímos que isto pode ser um efeito colateral do Linux; alocar muitos recursos para uma tarefa batch que a performance interativa se torna muito baixa. Isto deixa a máquina muito lenta e sem resposta enquanto grandes batches estiverem em execução. Esperamos que isto tenha um tratamento melhor em futuras versões do kernel Linux.

5.1.4. O Pacote de Benchmark do MySQL

Esta seção deve conter uma descrição técnica do pacote de benchmarks do MySQL (e crash-me), mas a descrição ainda não está pronta. Atualmente, você pode ter uma boa idéia do benchmark verificando os códigos e resultados no diretório sql-bench em qualquer distribuição fonte do MySQL.

Este conjunto de benchmark pretende ser um benchmark que irá dizer a qualquer utilizador que operações uma determinada implementação SQL irá realizar bem ou mal.

Note que este benchmark utiliza uma única thead, portanto ele mede o tempo mínimo para as operações realizadas. Planejamos adicionar vários testes multi-threaded no conjunto de benchmark no futuro.

A seguinte tabela mostra alguns resultados comparativos de benchmark para diversos servidores de bancos de dados quando acessados por meio do ODBC em uma máquina Windows NT 4.0.

Lendo 2000000 linhas por índiceSegundosSegundos
mysql367249
mysql_odbc464 
db2_odbc1206 
informix_odbc121126 
ms-sql_odbc1634 
oracle_odbc20800 
solid_odbc877 
sybase_odbc17614 
Inserindo 350768 linhasSegundosSegundos
mysql381206
mysql_odbc619 
db2_odbc3460 
informix_odbc2692 
ms-sql_odbc4012 
oracle_odbc11291 
solid_odbc1801 
sybase_odbc4802 

Para os testes anteriores, o MySQL foi executado com um cache de índices de 8M.

Temos concentrado alguns resultados de benchmarks em http://www.mysql.com/information/benchmarks.html.

Perceba que a Oracle não está incluída porque eles solicitaram a remoção. Todos benchmarks Oracle devem ser aprovados pela Oracle! Acreditamos que os benchmarks da Oracle são MUITO tendecioso pois os benchmarks acima devem ser executados supostamente para uma instalação padrão para um único cliente.

Para executar a suite de benchmarks, as seguintes exigências devem ser satisfeitas:

  • O pacote de benchamark é fornecido com a distribuição fonte do MySQL, assim você deve ter uma distribuição fonte. Você também pode fazer um download de uma distribuição em http://www.mysql.com/downloads/, ou usar a árvore fonte de desenvolvimento atual. (see Seção 2.3.4, “Instalando pela árvore de fontes do desenvolvimento”).

  • Os scripts do benchmark são escritos em Perl e usam o módulo Perl DBI para acessar o servidor de banco de dados, assim o DBI deve estar instalado. Você também precisará do driver DBD espercífico do servidor para cada um dos servidores que você quer testar. Por exemplo, para testar o MySQL, PostgreSQL, e DB2, os módulos DBD::mysql, DBD::Pg e DBD::DB2 devem estar instalados.

O pacote de benchmark está localizado no diretório sql-bench da distribição fonte do MySQL. Para executar o teste de benchmark, altera a localização dentro daquele diretório e execute o script run-all-tests:

shell> cd sql-bench
shell> perl run-all-tests --server=server_name

server_name é um dos servidores suportados. Você pode obter uma lista de todos parâmetros e servidores suportados executando run-all-tests --help.

crash-me tenta determinar quais recursos um banco de dados suporta e quais suas capacidades e limitações atuais para a execução de consultas. Por exemplo, ele determina:

  • Quais tipos de colunas são suportados

  • Quantos índices são suportados

  • Quais funções são suportadas

  • Qual o tamanho máximo de uma query

  • Qual o tamanho máximo de um registro do tipo VARCHAR

Podemos encontrar o resultado do crash-me para diversos bancos de dados em http://www.mysql.com/information/crash-me.php.

5.1.5. Utilizando seus Próprios Benchmarks

Definitivamente você deve fazer benchmarks de sua aplicação e banco de dados para saber quais são os gargalos. Corrigindo (ou substituindo o gargalho com um ``módulo burro'') você pode facilmente identificar o próximo gargalo (e continuar). Mesmo se a performance geral para sua aplicação atualmente é aceitável, você deve pelo menos criar um plano para cada gargalo e decidir como resolvê-lo se algum dia você precisar de performance extra.

Para um exemplo de programas de benchmarks portáveis, consulte o conjunto de benchmarks do MySQL. Veja mais informações sobre isto na Seção 5.1.4, “O Pacote de Benchmark do MySQL”. Você pode pegar qualquer programa deste conjunto e modificá-lo para suas necessidades. Fazendo isto você pode tentar soluções diferentes para seu problema e testar qual é a mais rápida para você.

Outro pacote de benchmark grátis é o Open Source Database Benchmark disponível em http://osdb.sourceforge.net/.

É muito comum que um problemas ocorram apenas quando o sistema estiver muito carregado. Nós tivemos alguns clientes que nos contactaram quando eles testaram um sistema em produção e encontraram problemas de carga. Na maioria dos casos, problemas de desempenho ocorrem devido a assuntos relacionados ao projeto básico do banco de dados (busca em tabelas não são bons com alta carga) ou problemas com o sistema operacional e de bibliotecaa. A maioria das vezes, estes problemas seriam MUITO mais fáceis de resolver se os sistemas já não estivessem em uso.

Para evitar problemas deste tipo, você deve colocar algum esforço em testar a performance de toda sua aplicação sobre a pior carga possível! Você pode utilizar o Super Smack para isto. Ele está disponível em: http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz. Como o nome sugere, ele pode derrubar seu sistema se você solicitar, portanto, utilize-o somente em sistemas de desenvolvimento.