Bancos de Dados Mysql com Alta Performance

Tuning de Banco de Dados MySQL

É de grande importância que empresas/corporações tenham banco de dados cada vez mais rápidos e estáveis, para alcançar rapidez e estabilidade em servidores de banco, vai muito além de hardware e SO, o processo de tuning é crucial para alcançarmos tal performance, entre vários servidores de banco, temos o MySQL recém adquirido pela Oracle, além de ser Open Source, é um dos bancos mais utilizados no mundo, podendo ser customizado de acordo com cada necessidade, este processo é chamado de Tuning de MySQL.

O processo de tuning consiste em melhorar as queries no banco, customizações das variáveis sort, join, key, innodb, pool, commit, max_connection, concurrency, entre outras variáveis disponíveis. Abaixo, uma breve explicação da função dessas variáveis. Acompanhe!

Variáveis do MySQL

Join: Palavra chave para consulta de duas ou mais tabelas relacionadas.

Sort: Classificar por coluna em ordem crescente ou decrescente, na consulta da tabela.

Key: Chave para classificação de dados, seja primaria ou estrangeira.

InnoDB: Mais adequado para dados alterados com frequência, do contrário MyISAM.

Pool: Utilizado para limitar o uso de CPU, memória, IO de disco, entre outras sérias de funções.

Commit: Efetiva a transação corrente, fazendo suas mudanças serem permanentes.

Max_connection: Limitar o número máximo de conexões no banco, ajustando pelo número de usuários.

Concurrency: Limitar o uso do recurso do banco por determinado meio ou um usuário.

Para um servidor mysqld em execução , você pode ver os valores atuais de suas variáveis do sistema , passando o argumento variable para listar todas as variáveis pré definidas no MySQL.

Para um servidor mysqld em execução , você pode ver os valores atuais de suas variáveis do sistema , passando o argumento variable para listar todas as variáveis pré definidas no MySQL.

SHOW VARIABLES;

Você também pode ver algumas estatísticas e indicadores de status para um servidor em execução executando com o comando status.

SHOW STATUS;

A variável de sistema e informações de status também pode ser obtido usando mysqladmin que mostra estas informações dentre muitas funções e finalidades.

shell> mysqladmin variables;

shell> mysqladmin extended-status;

O MySQL usa algoritmos que são muito escaláveis, para que normalmente podem ser executados com muito pouca memória. No entanto, normalmente melhores resultados de desempenho do MySQL e definir um valor maior de memória.

Quando estiver ajustando um servidor MySQL, as duas variáveis mais importantes para configurar são key_buffer_size e table_open_cache. Você deve se sentir confiante de que você tem duas estejam corretas antes de tentar alterar quaisquer outras variáveis com valores de sua preferência e/ou necessidades.

Os exemplos a seguir indicam alguns valores variáveis típicas para diferentes configurações de tempo de execução.

Se você tem pelo menos 1-2GB de memória e várias tabelas e deseja o máximo desempenho com um número moderado de clientes, use algo como a sentença abaixo.

shell> mysqld_safe --key_buffer_size=384M --table_open_cache=4000 --sort_buffer_size=4M --read_buffer_size=1M & 

Se você tiver apenas 256 MB de memória e apenas algumas mesas , mas você ainda fazer um monte de triagem , você pode usar algo como isto.

shell> mysqld_safe --key_buffer_size=64M --sort_buffer_size=1M 

Se houver muitas conexões simultâneas , problemas de trocas podem ocorrer a menos que o mysqld tenha sido configurado para usar muito pouca memória para cada conexão. mysqld tem melhor desempenho se você tem memória suficiente para todas as conexões .

Com pouca memória e muitas conexões , utilize algo como isto.

shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K --read_buffer_size=100K &

E se você estiver executando GROUP BY ou ORDER BY operações em tabelas que são muito maiores que sua memória disponível , aumentar o valor da read_rnd_buffer_size para acelerar a leitura de registros seguintes operações de classificação.

Se você especificar uma opção na linha de comando para o mysqld , ou mysqld_safe ele permanece em efeito somente para aquela chamada do servidor . Para usar a opção toda vez que o servidor é executado , colocá-lo em um arquivo de opção.

Para ver os efeitos de uma alteração de parâmetros , fazer algo parecido com isto.

shell> mysqld --key_buffer_size=128M --verbose –help

Otimização de Tabelas

Uma vez que seus dados atinge um tamanho estável, ou uma tabela de crescimento aumentou em dezenas ou algumas centenas de megabytes, considere a instrução OPTIMIZE TABLE para reorganizar a tabela e compacto qualquer espaço desperdiçado. As tabelas reorganizados exigem menos disco I / O para executar varreduras de tabela cheia. Esta é uma técnica simples que pode melhorar o desempenho quando outras técnicas, como a utilização do índice melhorando ou código de aplicação de sintonia não são práticos.

OPTIMIZE TABLE copia a parte de dados da tabela e reconstrói os índices. Os benefícios vêm de melhorou a embalagem de dados dentro de índices, e reduziu a fragmentação dentro dos espaços de tabela e no disco. Os benefícios são ajustados de acordo com os dados de cada tabela. Você pode achar que há ganhos significativos para alguns e não para outros, ou que os ganhos diminuem ao longo do tempo até que você próximo otimizar a tabela. Esta operação pode ser lenta se a tabela for grande ou se os índices sendo reconstruído não se encaixam na área de buffer. A primeira execução após a adição de uma grande quantidade de dados a uma tabela é frequentemente muito mais lento do que execuções posteriores.

Em InnoDB, tendo uma chave primária de comprimento (ou uma única coluna com um valor longo, ou várias colunas que formam um valor composto de comprimento) desperdiça muito espaço em disco. O valor da chave primária para uma linha é repetido em todos os registros de índices secundários que apontam para a mesma linha. (Veja a Seção 14.2.5, "Tabela InnoDB e estruturas de índice".) Criar uma coluna AUTO_INCREMENT como a chave primária se a sua chave primária é longa, ou indexar um prefixo de uma coluna VARCHAR longo em vez de toda a coluna.

Use o tipo de dados VARCHAR em vez de CHAR para armazenar cadeias de comprimento variável ou para colunas com muitos valores nulos. Uma coluna CHAR (N) sempre leva N caracteres para armazenar dados, mesmo se a cadeia é mais curto ou o seu valor é NULL. tabelas menores se encaixam melhor na área de buffer e reduzir disco I / O.

Ao usar o formato compacto da linha (o formato InnoDB padrão) e conjuntos de caracteres de comprimento variável, como utf8 ou sjis, CHAR (n) colunas ocupam uma quantidade variável de espaço, mas ainda pelo menos N bytes.

Para tabelas que são grandes, ou contêm grande quantidade de texto repetitivo ou de dados numéricos, considere o uso de formato de linha comprimido. Menos de disco I / O é necessária para trazer dados para o pool de buffer, ou para realizar varreduras de tabela cheia. Antes de tomar uma decisão definitiva, medir a quantidade de compressão que você pode conseguir usando COMPRIMIDO contra o formato de linha COMPACT. 

E você, pretende aprender otimizar seus servidores de bancos de dados MySQL? Conte com nosso curso Tuning MySQL da Escola Linux.