Alto desempenho com banco de dados MySQL

1. Alto desempenho com banco de dados MySQL PHP Conference 30/11/2007

2. Boa tarde! Jonas Silveira... • Programador PHP Sênior(7 anos de experiência) • Focado em sistemascorporativos de grande porte • Gerente de Projetos da Ynpar Tecnologia Ltda. • Gestão de projetos, programação e administração de servidores

3. 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?

4. Como obter performance? • Tirando máximo proveito do hardware • Conhecendo os riscos • Conhecendo os “gargalos” • Reduzindo excessos

5. Projeto físico

6. 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

7. 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

8. 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”

9. Lentidão no servidor MySQL • Excesso de dados / Hardware inadequado • Projeto físico inadequado • Utilização (consultas) inadequada

10. 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...

11. Erros comuns / Boas práticas:Contagem de registros • Ao invés da seqüência:SELECT COUNT(*) FROM...e depoisSELECT [...] FROM...LIMIT 0,50 • Utilizar:SELECT sql_calc_found_rows [...] FROM...LIMIT 0,50 e depoisSELECT found_rows(); • Muito bom em casos em que se usa ordenação

12. 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);

13. 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 demoradasmysql_query(“BEGIN”);#seqüencia de código demoradamysql_query(“COMMIT”);

14. 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)

15. 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”

16. 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 utilizadosIsso poupa o servidor e facilita o Debug

17. 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:

18. 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

19. Í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

20. Índices - recomendações • Crie apenas índices necessários • Crie índices combinados de acordo com o tipo de e pesquisa que será realizadaEx: combinar um campo “data” com um campo “cliente”. • Indexe apenas a quantia necessária de caracteresEx: para um campo longo, como “nome” de uma pessoa, indexe apenas os 10 primeiros caracteres

21. Índices - recomendações • Nas consultas, utilize a maior quantidade de índices possíveis • O comando “EXPLAIN” pode ajudar a otimizar consultas

22. 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!

23. Partição Vertical • 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 acessadosmais rápida, pois a tabela correspondente ficou menor tab1a tab1b

24. 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

25. 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

26. 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 (“multipletablespaces” + links simbólicos) • Refaça as buscas utilizando UNION – ex:Antes:SELECT * FROM tabela1;Depois: SELECT * FROM tabea1UNION SELECT * FROM db_hist.tabela1;

27. 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

28. Discos: utilize RAID 10 • Promove stripping(divisão) dos dados entreos arrays • Promove redundânciados dados

29. Servidores de Replicação

30. 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

31. Servidores de Replicação

32. Dúvidas? • Sites recomendados:dev.mysql.com/doc/refman/5.0/en/www.mysqlperformanceblog.com/ • Essa apresentação está disponível emwww.jonas-silveira.comwww.ynpar.com

33. 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 • Contato: contato@ynpar.com.br Brasil: 11 4208 7476USA (Miami): 305 728 6354 your network partner Uma empresa do grupo Total Express