Alto desempenho com banco de dados MySQL

Alto desempenho
com banco de dados MySQL
PHP Conference
30/11/2007
Boa tarde!
Jonas Silveira...
• Programador PHP Sênior
(7 anos de experiência)
• Focado em sistemas
corporativos de grande porte
• Gerente de Projetos da
Ynpar Tecnologia Ltda.
• Gestão de projetos, programação e
administração de servidores
Conteúdo da Palestra
• Sobre performance...
• PHP
- Erros comuns e
- Boas práticas
• MySQL
- Projeto Físico
- Utilização de índices
- Técnicas para ganho de desempenho
- Replicação de dados
• Dúvidas?
Como obter performance?
•
•
•
•
Tirando máximo proveito do hardware
Conhecendo os riscos
Conhecendo os “gargalos”
Reduzindo excessos
Projeto físico
Projeto físico: Localizando a lentidão
• Lentidão no servidor de dados
• Lentidão na comunicação entre
os servidores
• Lentidão no Servidor WWW
Lentidão no servidor WWW
• Pouca memória: ocorre muito swap
• Processamento insuficiente: muita lentidão
• Excesso de IO:
- disco muito cheio?
- disco lento ou danificado?
• Recursos físicos limitados na configuração do
PHP ou do Servidor WWW
• Aplicação precisa ser otimizada
Lentidão na comunicação
• Placas de rede lentas ou defeituosas
• Switch/Hub impróprio (lento) ou defeituoso
• Evitar canal de comunicação via WEB: utilizar
canal exclusivo (outras placas de rede em outro
switch ou vlan)
• Demora para conectar no MySQL:
- utilize o IP do servidor e não o nome
- thread de resolução de nomes pode ser a causa
- usar no my.cnf a opção “skip-name-resolve”
Lentidão no servidor MySQL
• Excesso de dados / Hardware inadequado
• Projeto físico inadequado
• Utilização (consultas) inadequada
Erros comuns / Boas práticas:
Contagem de registros
• SELECT COUNT(*) FROM...
- Conta o número de linhas da consulta;
- Quando a consulta é feita em mais de uma
tabela, não há otimização
• COUNT(expr): Conta o número de valores
diferentes de null de uma coluna
• Boa prática:
SELECT COUNT(1) FROM...
Erros comuns / Boas práticas:
Contagem de registros
• Ao invés da seqüência:
SELECT COUNT(*) FROM...
e depois
SELECT [...] FROM...
LIMIT 0,50
• Utilizar:
SELECT sql_calc_found_rows [...] FROM...
LIMIT 0,50
e depois
SELECT found_rows();
• Muito bom em casos em que se usa ordenação
Erros comuns / Boas práticas:
Contagem de registros
• Evite utilizar uma consulta cheia apenas para
obter o total:
$res = mysql_query(‘SELECT * FROM ...’);
$total = mysql_num_rows($res);
• Utilizar sempre função de contagem:
$res = mysql_query(‘SELECT COUNT(1) FROM ...’);
$total = mysql_result($res, 0);
Erros comuns / Boas práticas:
Transações demoradas
• Tansações: garantem que os dados foram
gravados no banco ou que nenhum dado foi
gravado
• Outros processos podem ficar aguardando
• Não devem ser demoradas
mysql_query(“BEGIN”);
#seqüencia de código demorada
mysql_query(“COMMIT”);
Erros comuns / Boas práticas:
Conexões abertas
• Feche sempre as conexões quando não precisar
mais delas
• Evite abrir duas conexões com o mesmo servidor
• As conexões podem atingir limite no banco de
dados e consomem recursos do servidor web
• Boa prática: utilização de “objetos” de conexão
utilizando destrutores (POO)
Erros comuns / Boas práticas:
Conexões persistentes
• Vantagem em poucos casos
• Conexões persistentes são boas se a sobrecarga
(overhead) de criar uma conexão ao seu servidor
SQL for alta
• Acaba gerando muitas conexões simultâneas
• Se um script realizar “lock” e for finalizado por
um erro, por exemplo, o “lock” permanece –
recomenda-se usar a função
“register_shutdown_function”
Erros comuns / Boas práticas:
Variáveis de resources
• Resource: produto de consultas
• Se possível evitar usar um nome de variável para
cada resource gerado
• Elimine resources grandes se for precisar de
recursos extras do servidor (mysql_free_result)
• Boa prática:
Defina um nome padrão (ex: $res) e sobrescreva
os resources se não forem mais utilizados
Isso poupa o servidor e facilita o Debug
Projeto físico:
Tamanho dos campos
• Cada tipo ocupa certo espaço em disco
• INT(3) ocupa o mesmo espaço que INT(11)
• Escolher o campo apropriado - ex:
Tipo
Signed
Unsigned
TINYINT
-128 a 127
0 a 255
SMALLINT
-32.768 até 32.767
0 até 65.535
MEDIUMINT
-8.388.608 até 8.388.607
0 até 16.777.215
INT
-2.147.483.648 até 2.147.483.647
0 até 4.294.967.295
Indexação de Campos
• Funcionam com índices de livros
• Organizam o acesso aos dados da memória e o
layout dos dados nos discos: consultas mais
rápidas
Índices - Armadilhas
•
•
•
•
Aumenta o espaço utilizado em disco
Inserções/Atualizações podem ficar lentas
Não utilize em campos comumente alterados
Ao realizar vários inserts, utilize inserções
extendidas
Índices - recomendações
• Crie apenas índices necessários
• Crie índices combinados de acordo com o tipo de
e pesquisa que será realizada
Ex: combinar um campo “data” com um campo
“cliente”.
• Indexe apenas a quantia necessária de caracteres
Ex: para um campo longo, como “nome” de uma
pessoa, indexe apenas os 10 primeiros caracteres
Índices - recomendações
• Nas consultas, utilize a maior quantidade de
índices possíveis
• O comando “EXPLAIN” pode ajudar a otimizar
consultas
Consultas aninhadas com
tabelas temporárias
• Bom para mesclagem de duas tabelas grandes;
• Procedimentos:
- Consulte uma das tabelas e grave o resultado
em uma tabela temporária
- Realize a consulta na outra tabela fazendo
mesclagem com a tabela temporária
• CUIDADO com a memória do servidor!
Partição Vertical
tab1a
tab1b
• Transferência das colunas (campos)
pouco utilizadas para outra tabela
• Ou seja, divisão de uma tabela
em duas, onde em uma ficam os
campos mais utilizados
• Busca aos dados mais acessados
mais rápida, pois a tabela
correspondente ficou menor
Partição Horizontal
• Transferência de registros para diferentes
tabelas ou bancos de dados
• Normalmente transfere-se registros antigos
(histórico) ou remove-os após backup
• Busca aos dados mais rápida, pois a tabela fica
menor, inclusive os arquivos de índices
Redundância de dados
• Evita consultas complexas, na maioria da vezes
que possuem JOIN, SUM, GROUP BY
• Exemplo clássico: pedidos -> pedidos_itens
(deseja-se obter o valor total do pedido somando
o valor dos itens * quantidade)
• Fácil implementação utilizando triggers
Dica: criação de histórico
• Crie um novo banco de dados, e transfira conteúdo
antigo para ele (partição horizontal
• Se puder, utilize um disco exclusivo (“multiple
tablespaces” + links simbólicos)
• Refaça as buscas utilizando UNION – ex:
Antes:
SELECT * FROM tabela1;
Depois:
SELECT * FROM tabea1
UNION
SELECT * FROM db_hist.tabela1;
Utilização de discos exclusivos
•
•
•
•
•
Permite concorrência no acesso aos dados
Bom para criar banco de dados histórico
Bom para armazenar tabelas grandes
Bom para o Log Binário
Armazene o banco de dados em disco diferente
do Sistema Operacional
Discos: utilize RAID 10
• Promove stripping
(divisão) dos dados entre
os arrays
• Promove redundância
dos dados
Servidores de Replicação
Servidores de Replicação
• Utilizado para realização de backups
• Pode-se criar um servidor “atrasado”, por
questões de segurança
• Permite escalabilidade: utilize-os para relatórios
• Para casos de espelhamento, o servidor não pode
ser lento (inferior ao servidor master)
• Se o “master” falhar, um escravo pode assumir
Servidores de Replicação
Dúvidas?
• Sites recomendados:
dev.mysql.com/doc/refman/5.0/en/
www.mysqlperformanceblog.com/
• Essa apresentação está disponível em
www.jonas-silveira.com
www.ynpar.com
Consultoria MySQL e outros serviços...
• Ynpar Tecnologia Ltda. (Alphaville - Barueri/SP)
• Serviços de Registro de Domínios,
Hospedagem, Software House,
Colocation, Administração de
Servidores, etc.
• Saiba mais em: www.ynpar.com
your network partner
• Contato:
[email protected]
Brasil: 11 4208 7476
Uma empresa do grupo
USA (Miami): 305 728 6354
Total Express