Autor Tópico: Scriptcase, banco de dados e otimização de performance. Postem suas experiências  (Lida 11225 vezes)

Alexandre Pereira Bühler

  • Expert
  • *****
  • Mensagens: 1658
  • Nunca estabeleça um teto para os seus rendimentos.
    • Simão & Bühler Ltda
    • Email
Olá amigos do Fórum,

Não estou fazendo propaganda de meus serviços.
Crio este tópico para que cada um coloque suas experiências ao otimizar suas aplicações Scriptase e SGDB

Como hospedo para várias pessoas já passei por muita coisa, então posto aqui as experiências mais comuns que tenho.
Seguem meu relato mas espero que vocês também contribuam.

Tenho um provedor de hospedagem compartilhada para scriptcase e quero dizer que sofro com a performance das aplicações scritpcase e SGDB.
Mas de quem será a culpa? hardware? SGDB? Scritpcase?

Cheguei a perder clientes por causa da performance.

Então comecei a verificação geral.

Usei o comando top e etc para ver o que acontecia com meu hardware.
Comentários sobre comando top do GNU/Linux --> https://www.4site.com.br/blog/explicando-o-load-average/
O I/O do disco está ótimo.
O consumo de memória mais alto que tive no mysql foi de 11%, apache 25% e etc. Nada que ultrapasse os 50% durante o dia.
Fiz uma manutenção geral preventiva no hardware de madrugada
Mas meu consumo de CPU, este era drástico no mysql. Chegava a 100%
Dentre as possíveís causas estão:
a) I/O em disco, problemas gerais no hardware
b) configuração errada do mysql
c) querys (select, insert, updates e etc) mal construídas;
d) joins mal estruturados;
e) querys e joins que não fazem uso de indexes corretamente.
f) e etc


Como a causa "a" já foi descartada fui para causa "b" mesmo sabendo que cpu alto geralmente são as causas "c, d, e".

Usei os arquivos tuning-primer.sh e mysqltuner.pl para verificar o que podia ser melhorado em minha configuração do mysql.
Também olhei o status do mysql.
Enfim, mysql otimizado.
Era para tudo ocorrer normal.

No outro dia clientes me falaram que havia selects demorando mais de 15 minutos para serem concluídos.

Como a causa "a Hardware" e "b configuração do mysql" foram eliminadas somente restou o mais comum: query e joins.

Ativei o log "mysql slow querys" do msyql para acompanhar as querys lentas.

Descobri dados interessantes e porque meu serivdor está "abrindo o bico".

E aqui vai o que aprendi com meus erros e dos erros de alguns clientes ao "programar e criar suas databases"

1) Há coisas que o servidor da aplicação não faz sozinhos por vocês. Nem mesmo a empresa onde vocês contrataram a hospedagem, cloud e etc.
 Aprenda o que é optimizar, desfragmentar e reparar tabelas no mysql.
 Não espere as tabelas ficarem com mais de 1Gb ou terem mais de 100000 registro para fazerem uso destes recursos.
 No Firebird e postgresql há comando semelhantes como o vacum do postgresql e etc
 Se não fizerem uso destes recursos pode se ter o melhor link do mundo, mas o servidor será um carroça para suas aplicações.
No phpmyadmin ao clicar na tabela vá em operações, lá poderá usar este recursos.

2) Aprenda a fazer uso de indexes corretamente. Eles estão ai para ajudar.
Primary key nem sempre é o index que você irá usar.

Vejam: crio um tabela "teste" campos "codigo", "nome" e etc

crio minha primary key no campo "codigo". Beleza index criado.

Mas na hora de fazer uma query: select bla, bla, bla "where nome" = bla, bla

Vocês acham que para "where nome =" o select irá fazer uso do index ou do primary key?
O index do campo nome nem foi criado somente o primary key para o campo código

É apropriado criar um index para o campo nome.

Deixe suas tabelas ficarem grandes que saberão que falo a verdade.

Para mais detalhes vejam o manual em inglês:
http://dev.mysql.com/doc/refman/5.5/en/optimization.html
http://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html
Se você não sabe inglês pode consultar este manual do mysql em português:
http://xoops.net.br/docs/mysql/manual/ch05s04.php


E olha que já vi muita gente fazer isto, inclusive eu. Achar que a query/join esta indexada e não está. Lentidão na certa.

3) Querys e joins com cláusulas mal estruturadas

leiam este site:
http://www.intellinews.com.br/blog/2008/04/24/optimizacoes-simples-e-eficazes-no-mysql/

Não temos controle em como o Scriptcase faz suas cláusulas (filtros) sql.
Mas deduzimos que filtros como o "qualquer parte" fazem uso da claúsula like %palavraprocurada%

Se sua base tiver muitos registros ao liberar para seu cliente o filtro "qualquer parte" não reclame se ele dizer que esta demorando muito para achar o registro.

Agora tiremos o scriptcase fora da jogada e vamos para claúsulas montadas por vocês dentro do Scriptcase.

No mysql se usar tabelas mysam ao invés de "like" use os indexes como  text e a claúsula "match against" a diferença de tempo é brutal.
Vi um like demorar 20 segundos e o no mesmo select o match against demorar menos de 1 segundo.

Leiam mais uma vez o site: http://www.intellinews.com.br/blog/2008/04/24/optimizacoes-simples-e-eficazes-no-mysql/
E veja que onde ele diz lento e lentooooooooooooooooooo mesmo quando a quantidade de registro é grande.

Use ferramentas que monitorem suas querys, o tempo de execução e se usaram indexes.

Houve casos no meu servidor de tabelas bem indexadas mas cujos joins não faziam uso correto dos indexes e toda uma tabela de 430000 registros era lida para satisfazer o join.

Quem não observa estas coisas terá:
"Resultado cliente liga para você. E ai?
Mudar de servidor? faço upgrade de servidor? Compro outro hardware mais potente para servidor?
Mudei ficou tudo rápido. A base cresce e a lentidão volta. E ai? muda de servidor de novo? Já viu o transtorno que é mudar de servidor?
Não é mais fácil otimizar sua aplicação no SC e sua database no SGDB do que ficar pulando de servidor em servidor?"

Se você possui vários sistemas num servidor lembre-se que geralmente, há execções é claro, mas a próxima query no mysql so entra em ação quando ele finalizar a que esta presa na frente. Então vamos otimizar ai gente!
« Última modificação: Setembro 26, 2012, 03:45:30 pm por Alexandre Pereira Bühler »
--
Alexandre Pereira Bühler
https://www.simaoebuhler.com.br
Hospedagem compartilhada Scriptcase desenvolvimento e produção. Temos servidores dedicados Scriptcase.
Eu RTFM todo dia e você?

Kleyber

  • Expert
  • *****
  • Mensagens: 2239
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #1 Online: Setembro 26, 2012, 06:27:11 pm »
Alexandre,

É por essas e outras que te admiro bastante. Muito obrigado por esta aula! Eu mesmo irei dar uma boa olhada nos sistemas que tenho e nos bancos de dados. Obrigado mesmo!
Kleyber Derick

ITIL® V3 Foundation Certified
Analista de Sistemas
São Luís - Maranhão
www.tkinformidia.net

nigri

  • Intermediário
  • ***
  • Mensagens: 163
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #2 Online: Setembro 26, 2012, 06:28:02 pm »
Boa tarde Alexandre,

muito obrigado pela aula.

Para quem está começando agora são informações importantes.

E continue colocando suas experiencias

um abraço

Nigri

MikeC

  • Iniciante
  • **
  • Mensagens: 57
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #3 Online: Setembro 27, 2012, 06:40:41 am »
Aló Alexandre boa explicação.

Como uso MsSql uso muito uma opção Show Execution Plan que faz um detalhe de todo o query

Cumprimentos

weber

  • Expert
  • *****
  • Mensagens: 516
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #4 Online: Setembro 28, 2012, 12:35:31 am »
Ah uma coisinha, claro que para quem programa com o SC não tem esse problema mas tb ja vi muitos SELECTS em PHP
onde o pessoal faz um SELECT * FROM clientes
e na verdade so queria pegar o o id e o nome ....

Alexandre Pereira Bühler

  • Expert
  • *****
  • Mensagens: 1658
  • Nunca estabeleça um teto para os seus rendimentos.
    • Simão & Bühler Ltda
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #5 Online: Outubro 10, 2012, 02:34:03 am »
Quero lembrar também que é bom acompanhar o apache. As vezes seu site está lento é o problema é o apache mal configurado.
Sempre o dimensione de acordo com sua quantidade de memória e capacidade de processamento.
Não existe uma configuração padrão, pois cada caso é diferente.
É preciso ver o que melhor se adeque ao seu ambiente
Veja se é bom manter o keepalive on ou off. Keepalive on é bom para sites com muitas imagens, mas consome mais memória RAM. Se não for bem configurado irá deixar o servidor uma carroça na hora de responder as requisições web.
Ajuste os valores de MaxKeepAliveRequests e KeepAliveTimeout.
Detalhe importante: O scriptcase não se da bem com KeepAliveTimeout menor que 15 segundos.
Veja também as variáveis:
StartServers
MinSpareServers
MaxSpareServers
MaxClients
MaxRequestsPerChild

Cuidado com MaxClients. Não adianta colocar maxclients para 10000 e seu servidor não ter memória para isto.
« Última modificação: Outubro 10, 2012, 02:36:33 am por Alexandre Pereira Bühler »
--
Alexandre Pereira Bühler
https://www.simaoebuhler.com.br
Hospedagem compartilhada Scriptcase desenvolvimento e produção. Temos servidores dedicados Scriptcase.
Eu RTFM todo dia e você?

Alexandre Pereira Bühler

  • Expert
  • *****
  • Mensagens: 1658
  • Nunca estabeleça um teto para os seus rendimentos.
    • Simão & Bühler Ltda
    • Email
--
Alexandre Pereira Bühler
https://www.simaoebuhler.com.br
Hospedagem compartilhada Scriptcase desenvolvimento e produção. Temos servidores dedicados Scriptcase.
Eu RTFM todo dia e você?

Dos Santos

  • Novato
  • *
  • Mensagens: 43
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #7 Online: Janeiro 01, 2013, 11:03:45 am »
Excelente post, vou postar uma experiência...

Consegui reduzir drasticamente o tempo de execução de algumas consultas no MySQL, após aprender usar corretamente os índices. A diferença foi gritante, passou de vários minutos para alguns segundos!

No meu caso, principalmente o uso de índices em campos que são muito usados em cláusulas WHERE, fez muita diferença.
« Última modificação: Janeiro 01, 2013, 11:06:23 am por Dos Santos »

nigri

  • Intermediário
  • ***
  • Mensagens: 163
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #8 Online: Janeiro 01, 2013, 11:50:27 am »
bom dia Santos,

poderia dar exemplos ou fazer um vídeo para ter uma noção melhor

um abraço e feliz 2013

Nigri


Alexandre Pereira Bühler

  • Expert
  • *****
  • Mensagens: 1658
  • Nunca estabeleça um teto para os seus rendimentos.
    • Simão & Bühler Ltda
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #10 Online: Agosto 12, 2013, 11:21:11 am »
Bom dia,
Fiz testes com postgresql, mysql e firebird em uma base que tem uma tabela com 1Gb de dados.

Segue alerta de desempenho do scriptcase com Firebird e postgresql.

Em formulários e grids o scriptcase para fazer a paginação usa um select count(*) from tabela tal.
Para usuário do mysql isto nem faz cosquinhas.
Mas o tipo de versionamento do postgresql e firebird tornam  o uso do count(*) lento.
Ver:
http://wiki.postgresql.org/wiki/Slow_Counting
http://www.firebirdfaq.org/faq5/

Então sempre use o where em seus selects nestes SGDBS com o scriptcase, nunca use um select * from xxxx.
Acredite! Tem gente que ainda faz o select * from como se fosse a coisa mais normal do mundo.
Com muitos registros é um desastre.

No firebird e postgresql na tabela de 1gb o count(*) chegava a demorar 43 segundos. Para depois executar a query em menos de 1 segundo.
No mysql tudo era executado em menos de 1 segundo no myisam e de alguns microsegundos a 3 segundos no innodb

Aproveito para lembrar que isto não é uma situação onde se deva considerar o mysql ótimo para tudo.
Lembro que o mysql é sempre rápido para consultas.
Mas em testes de desempenho para insert ou update com volume alto de transações ele costuma se sair pior que o firebird e o postgresql.
Além do que ele não trabalha com tabelas com campos blob e text em memória e sim sempre em disco.
E etc, etc, etc

De qualquer forma o melhor banco para trabalhar é aquele que você conhece e sabe suas fraquezas, forças, como otimizá-lo e em que situação aplicá-lo.

Para os novatos aconselho também a não utilizar mais de três selects com lookup num form.
Não importa qual seja o SGDB usado, mais de três selects com lookup em base de dados é uma lentidão.
« Última modificação: Agosto 12, 2013, 11:23:25 am por Alexandre Pereira Bühler »
--
Alexandre Pereira Bühler
https://www.simaoebuhler.com.br
Hospedagem compartilhada Scriptcase desenvolvimento e produção. Temos servidores dedicados Scriptcase.
Eu RTFM todo dia e você?

FredKeyster

  • Expert
  • *****
  • Mensagens: 1702
  • DEWENNINMEN
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #11 Online: Agosto 12, 2013, 11:33:58 am »
Ótimo tópico. Muita gente esta fazendo seus sistemas sem uma base que é fundamental, o banco de dados. Relacionamento de tabelas, seus index, FK, joins, etc... são fundamentais para um bom desempenho do sistema.

Parabéns pela iniciativa Alexandre, és um grande profissional.

Abraços!
F. A.
Analista de Sistemas

Alexandre Pereira Bühler

  • Expert
  • *****
  • Mensagens: 1658
  • Nunca estabeleça um teto para os seus rendimentos.
    • Simão & Bühler Ltda
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #12 Online: Janeiro 22, 2014, 01:58:39 am »
Pessoal sei que o tópico é velho.
Mas posto aqui o conteúdo de um script que deixo no cron do Linux.
Mas pode ser adaptado para o windows.
Façam backup sempre antes de usar este script.
E não me responsabilizo se algo der errado. Posto porque o manual do mysql diz que é bom fazer. E porque me ajuda bastante a manter a performance do servidor.
Ele irá reparar tabelas corrompidas.
Otimizar tabelas e analisar.

Segue conteúdo do script.

#!/bin/bash
date
mysqlcheck --repair --all-databases -v -uroot -psuasenha
mysqlcheck --optimize --all-databases -v -uroot -psuasenha
mysqlcheck --analyze --all-databases -v -uroot -psuasenha
mysqlcheck  --all-databases -m -v  -uroot -psuasenha
mysqlcheck  --all-databases --auto-repair -v  -uroot -psuasenha
date

Explicação da diferença entre analyze e optimize :

OPTIMIZE TABLE (http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html) -> Reorganiza o armazenamento físico dos dados da tabela e dados de índice associados, para reduzir o espaço de armazenamento e melhorar a eficiência de I/O ao acessar a tabela. As alterações exatas feitas para cada quadro dependem do mecanismo de armazenamento usado por essa tabela.

ANALYZE TABLE (http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html) -> analisa e armazena a distribuição de chaves para uma tabela. Durante a análise, a tabela é bloqueada com uma trava de leitura para InnoDB e MyISAM. Esta declaração funciona com o InnoDB, NDB e tabelas MyISAM. Para tabelas MyISAM, esta declaração é equivalente a usar myisamchk - analyze.

O MySQL utiliza a distribuição de chaves armazenadas para decidir a ordem em que as tabelas devem ser unidas quando você executar um join em alguma coisa diferente de uma constante. Além disso, a distribuição das chaves pode ser usado no momento de decidir quais índices usar para uma tabela específica em uma consulta.

O script pode ser usado de 1 vez por semana a 1 vez por mês.

Aqui como tenho bancos maiores que 9gb uso duas vezes por semana.

Como há o travamento das tabelas sempre aconselho a rodar de madrugada
« Última modificação: Janeiro 22, 2014, 02:20:04 am por Alexandre Pereira Bühler »
--
Alexandre Pereira Bühler
https://www.simaoebuhler.com.br
Hospedagem compartilhada Scriptcase desenvolvimento e produção. Temos servidores dedicados Scriptcase.
Eu RTFM todo dia e você?

Alexandre Pereira Bühler

  • Expert
  • *****
  • Mensagens: 1658
  • Nunca estabeleça um teto para os seus rendimentos.
    • Simão & Bühler Ltda
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #13 Online: Janeiro 22, 2014, 02:19:45 am »
Seguem três documentos comparando Mysql, Firebird, Postgresql e SQL server.
Um deles é trabalho de bacharelado e outros dois TCC.
http://www.faculdadeexpoente.edu.br/upload/noticiasarquivos/1278624761.PDF
http://www.firebase.com.br/fb/imgdocs/tcc_fbmysql.pdf
Este último feito com versões mais recentes do quatro bancos.
http://www.unipac.br/bb/tcc/tcc-15ee06c022b6b866f2815b76757c667f.pdf
--
Alexandre Pereira Bühler
https://www.simaoebuhler.com.br
Hospedagem compartilhada Scriptcase desenvolvimento e produção. Temos servidores dedicados Scriptcase.
Eu RTFM todo dia e você?

Wennys Carlos

  • Avançado
  • ****
  • Mensagens: 254
    • Email
Re:Scriptcase, banco de dados e otimização de performance. Postem suas experiências
« Responder #14 Online: Janeiro 22, 2014, 03:32:02 pm »
Aprendi uma coisa com o Alexandre ao longo do tempo:

Nunca pergunte nada pra ele e espere uma resposta simples. Rsrs.

Sempre será uma aula. Uma baita aula.

Obrigado, Alexandre.

Wennys Carlos