Performance MySQL - como melhorar?

VeraVera Entry Level
Alguém pode me orientar em relação à performance do banco de dados?

Por acaso o controle de log pode causar lentidão?

Existe alguma recomendação para limpar os logs periodicamente, configurar o 'log rotation', fazer um 'flush' ou algo semelhante?

A quantidade de usuários acessando o sistema está aumentando (hoje tive 114 usuários ativos) e temos tido problemas da mesma senha ser chamada por 2 balcões distintos.

Hoje resolvi analisar as variáveis e estatísticas de comandos do MySQL e encontrei os seguintes valores que me chamaram a atenção:

Total de comandos Select: 17.830.391 (quase dezoito milhões!)

Status (alguns itens):
Innodb_buffer_pool_dump_status - Dumping of buffer pool not started
Innodb_buffer_pool_load_status - Buffer pools load completed at 171228 17:32:13
Max_used_connections_time - 2018-01-09 15:08:54
Bytes_sent - 8,7 GiB
Bytes_received - 5,3 GiB
Innodb_data_written - 4 bilhões!
2.143.343.104 - 2 bilhões!
Uptime_since_flush_status - 1.040.907
Uptime - 1.040.907

Variáveis:
max_connections - 151

Alguma luz?

Obrigada.
Tagged:

Comments

  • O problema de escalabilidade do Novo SGA até a versão 1.5.1 é devido a grande quantidade de requisição assíncrona (ajax polling) que são feitas manter a interface atualizada. Requisições a cada 7 segundos (v1.5.1). Aonde cada requisição faz uma ou mais consultas.

    - Requisições assíncronas para atualizar a fila do atendente
    - Requisições assíncronas para atualizar as filas do monitor
    - Requisições assíncronas para exibir senhas no painel
    - Fora requisições normais ao acessar as páginas

    Na versão 2.0 foi introduzido o servidor websocket (WS) que ao invés dos clientes ficaram fazendo requisições a cada x segundos para verificarem o que mudou, o servidor WS que emite eventos alertando aos cliente que a atualização deve ser feita. Desta forma reduz significantemente a quantidade de requisições por minuto.
  • VeraVera Entry Level
    @rogeriolino,

    Obrigada pelas explicações.

    Enquanto a versão 2.0 não é a oficial, tem algum ajuste que eu possa fazer de imediato para melhorar a performance no meu ambiente?

    Acredito que melhorar a velocidade de resposta das páginas vai resolver os seguintes inconvenientes:

    a) eliminar a geração de senha duplicada quando tenho mais de um balcão de triagem
    http://forum.novosga.org/discussion/comment/2665/#Comment_2665

    b) eliminar o problema da tela de atendimento mostrar a mesma senha chamada para balcões diferentes
    http://forum.novosga.org/discussion/comment/2520

    c) ter as páginas do NovoSGA abrindo mais rápido

    d) eliminar os alertas de alto consumo de CPU no servidor

    Acessei os links abaixo, mas não sei avaliar o que é mais importante ajustar no Apache, PHP e no MySQL para atender as necessidades específicas do NovoSGA.

    https://httpd.apache.org/docs/2.4/misc/perf-tuning.html
    http://searchitoperations.techtarget.com/tip/Optimize-PHP-with-finely-tuned-IT-resources-and-settings
    https://dev.mysql.com/doc/refman/5.7/en/optimization.html
    https://dev.mysql.com/doc/refman/5.7/en/log-file-maintenance.html
    https://chrisgilligan.com/consulting/tuning-apache-and-mysql-for-best-performance-in-a-shared-virtual-hosting-environment/

    Aqui o Apache, PHP e MySQL estão no mesmo servidor, instalamos pelo Wamp:
    - Windows 2008 r2 64 bits, virtualizado
    - 4 CPUs (2 de 2 Core) que estão trabalhando acima de 70%, batendo em 100% com frequência
    - 8 GB memória RAM, o uso não passa de 5 GB
    - 40 GB de disco, está com 14 GB livres
    - Apache 2.4.23 Win32
    - PHP 5.6.25 32 bits
    - MySQL 5.7.14 32 bits

    Pelo visto o que 'pega' são as requisições feitas a cada 7 segundos e que consomem muita CPU.

    DÚVIDAS:
    1) O fato de eu ter Wamp 32 bits em um servidor 64 bits afeta algo?
    2) Como identificar se o consumo de CPU é do Apache, PHP ou MySQL?
    3) Vi que no PHP o opcache.enable = OFF, devo habilitar?
    4) Devo limpar logs periodicamente ou rotacionar para não trabalhar com 1 arquivo muito grande? Por exemplo, habilitar o log rotation do Apache?
    5) Se eu montar um servidor 64 bits com Wamp 64 bits, como faço para migrar os dados do MySQL32 bits para o MySQL 64 bits?

    Fico no aguardo de dicas / orientações.

    Obrigada!


  • A dica principal é separar os servidores. Um para o banco de dados e outro para a aplicação.

    Como não trabalho com Windows, não tenho como opinar sobre o tuning desse ambiente.
  • VeraVera Entry Level
    edited January 2018
    @rogeriolino,

    Eu de novo, há cerca de uma hora os usuários reclamaram de muita lentidão para abrir as telas e resolvemos 'apelar'...

    Usamos a ferramenta 'Wampp empty log' e limpamos apenas o access.log do Apache (o arquivo estava com mais de 3GB), sem interromper o acesso ao sistema.

    Não afetou os usuários logados e apesar do processo Apache (httpd.exe) continuar a consumir muita CPU, deixou de atingir os 100%, está mantendo perto de 80% e o acesso às telas está 'menos ruim'.

    Como fizemos isso com usuários logados, ainda não reestartamos o servidor, vamos fazer após o expediente. Na 2a feira vamos monitorar para ver o impacto.

    Referência: Em uma postagem de 2011 a pessoa associa o arquivo de log muito grande ao alto consumo de CPU: http://pilotaz.blogspot.com.br/2011/01/httpdexe-apache-cpu-100-load.html

    Comentários e dicas são bem-vindos...

  • VeraVera Entry Level
    @rogeriolino,

    Já consideramos separar os servidores, mas não conseguimos fazer isto neste momento.

    Quanto aos ajustes para melhorar a performance no ambiente Windows, vamos ver se alguém com experiência pode registrar alguma dica aqui.

    Conseguimos preparar uma máquina 64 bits com Wampp 64, preciso de ajuda para migrar os dados de MySQL 32 bits para MySQL 64. Não localizei nada aqui no fórum, você pode uma pista?

    Como vou migrar de 32 para 64 bits entendo que não devo apenas copiar a pasta SGA no servidor, pois devem existir diferenças nos 2 ambientes.

    Imagino que o ideal seja usar o mysqldump, mas também sei que devo tomar cuidado com autocommit durante a importação, com logs, com a ordem de criação das tabelas, com as sequences e o melhor é fazer sem nenhum usuário acessando a aplicação.

    No manual do MySQL encontrei algumas informações, mas são muitos detalhes, preciso de uma solução rápida.

    Alguém aqui já migrou os dados do MySQL Windows e 32 bits para 64 bits e pode me dar o caminho das pedras?

    https://dev.mysql.com/doc/refman/5.7/en/innodb-migration.html
    https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

    Obrigada!
  • VeraVera Entry Level
    Alguém pode me explicar como solucionar o erro abaixo que está registrado no arquivo php_error.log?

    O arquivo está com 16453 linhas e o primeiro registro em 02-Feb-2017 11:13:52, quando o servidor foi instalado.

    Ocorre diariamente, às vezes mais de uma vez por dia, em horários diferentes:

    [12-Jan-2018 13:05:02 America/Sao_Paulo] PHP Fatal error: Uncaught exception 'Twig_Error_Loader' with message 'Unable to find template "C:/wamp/www/sga/templates/error/500.html.twig" (looked into: C:\wamp\www\sga\templates).' in C:\wamp\www\sga\vendor\twig\twig\lib\Twig\Loader\Filesystem.php:232
    Stack trace:
    #0 C:\wamp\www\sga\vendor\twig\twig\lib\Twig\Loader\Filesystem.php(150): Twig_Loader_Filesystem->findTemplate('C:\\wamp\\www\\sga...')
    #1 C:\wamp\www\sga\vendor\twig\twig\lib\Twig\Environment.php(329): Twig_Loader_Filesystem->getCacheKey('C:\\wamp\\www\\sga...')
    #2 C:\wamp\www\sga\vendor\twig\twig\lib\Twig\Environment.php(419): Twig_Environment->getTemplateClass('C:\\wamp\\www\\sga...')
    #3 C:\wamp\www\sga\vendor\slim\views\Twig.php(87): Twig_Environment->loadTemplate('C:\\wamp\\www\\sga...')
    #4 C:\wamp\www\sga\vendor\slim\slim\Slim\View.php(255): Slim\Views\Twig->render('C:\\wamp\\www\\sga...', NULL)
    #5 C:\wamp\www\sga\vendor\slim\slim\Slim\View.php(243): Slim\View->fetch('C:\\wamp\\www\\sga...', NULL)
    #6 C:\wamp\www\sga\vendor\slim\slim\Slim\Slim.php(755): Slim in C:\wamp\www\sga\vendor\twig\twig\lib\Twig\Loader\Filesystem.php on line 232

    Alguém mais está com esse erro no php_error.log??

    Obrigada.
  • VeraVera Entry Level
    Bom dia @rogeriolino,

    Hoje cedo reiniciamos o servidor e o access.log (log de requisições do Apache).

    O consumo de CPU continua no mesmo nível de 6ª feira quando só reiniciamos o log pela ferramenta 'Wampp empty log' - deixou de atingir os 100% e está mantendo perto de 80%.

    Portanto aparentemente não faz diferença nenhuma reiniciar o servidor após limpar o log do Apache.

    Copiei o access.log para o Excel (importei dados usando o - como separador) e usando o recurso de filtro identifiquei que:

    1) As requisições estão sendo feitas:
    - a cada 3 segundos para a tela do painel e de atendimento
    - a cada 10 segundos para a tela do monitor
    e não a cada 7 segundos como você mencionou em uma resposta acima.

    [12/Jan/2018:15:11:06 -0200] "GET /sga/public/api/painel/1?servicos=...
    [12/Jan/2018:15:11:09 -0200] "GET /sga/public/api/painel/1?servicos=...

    [13/Jan/2018:10:00:02 -0200] "GET /sga/public/modules/sga.atendimento/ajax_update?...
    [13/Jan/2018:10:00:05 -0200] "GET /sga/public/modules/sga.atendimento/ajax_update?...

    [12/Jan/2018:15:40:08 -0200] "GET /sga/public/modules/sga.monitor/ajax_update?...
    [12/Jan/2018:15:40:19 -0200] "GET /sga/public/modules/sga.monitor/ajax_update?...

    2) Quando a tela de atendimento está aberta, são feitas 18 a 20 requisições por minuto para o servidor e ela não dá timeout como as outras.

    Isso faz sentido pois o timeout faria com que o atendente precisasse fazer novo login se o atendimento fosse demorado.

    Como ela não dá timeout, ao agrupar por IP e filtrar pela palavra 'atendimento' descobri que esqueceram 2 micros ligados o fim de semana todo com a tela de atendimento aberta...

    3) Ao agrupar por IP e filtrar pela palavra 'painel', 'monitor' ou 'estatísticas' consigo identificar o IP de todos os micros usados no painel de atendimento, na tela monitor ou que visualizaram gráficos / relatórios.


    Análise rápida nos outros logs:

    apache_error.log (485 linhas desde 02/02/2017) - registra quando o servidor foi criado e os momentos onde o servidor foi reiniciado.

    mysql.log (34 linhas desde 02/02/2017) - tem os registros de quando foi criado e 2 warnings de IP não encontrado no início de Dezembro.

    php_error.log (16400 linhas desde 02/02/2017)- em alguns dias apresenta erro por não encontrar algum template do TWIG, imagino que seja alguma tela que é acessada eventualmente, só não sei qual...

    [12-Jan-2018 13:05:02 America/Sao_Paulo] PHP Fatal error: Uncaught exception 'Twig_Error_Loader' with message 'Unable to find template "C:/wamp/www/sga/templates/error/500.html.twig" (looked into: C:\wamp\www\sga\templates).' in C:\wamp\www\sga\vendor\twig\twig\lib\Twig\Loader\Filesystem.php:232
    Stack trace:

    ***********
    Ou seja, não localizei erros relevantes nos logs do Apache, MySQL e PHP.
    Parece que a lentidão é reflexo apenas da quantidade de acessos feitas no servidor pelas telas do Painel e de Atendimento.

    Para que o arquivo access.log não fique muito grande e possa ser analisado periodicamente vamos rotacionar uma vez por semana.

    Perguntas para 'os universitários':
    a) Posso aumentar o intervalo de requisições das telas Painel e Atendimento?
    b) Qual o valor máximo recomendado para cada tela?
    c) Onde faço esta alteração?
    ************

    Toda ajuda é bem-vinda!
  • >Posso aumentar o intervalo de requisições das telas Painel e Atendimento?

    Pode sim.


    >Qual o valor máximo recomendado para cada tela?

    Isso será uma definição de vocês. Quando maior o tempo, menor o número de requisição e maior o tempo para os atendentes receberem o alerta de novos atendimentos.


    >Onde faço esta alteração?

    Nos arquivos script.js dos módulos, e no js/painel.js do painel-web.
  • VeraVera Entry Level
    @rogeriolino,

    Obrigada, vou alterar para aumentar apenas 1 segundo no tempo da tela do atendimento e ver o impacto.

    Também recomendamos aos usuários não deixar a tela de atendimento ativa nos micros que não estão sendo usados.

    Em paralelo hoje cedo fizemos as seguintes alterações no ambiente e reiniciamos o servidor.

    C:\WAMP\BIN\MYSQL\MYSQL5.7.14\MY.INI
    key_buffer_size = 256M
    max_connections = 500

    Isso já gerou uma diminuição nos tempos de acesso e no consumo de CPU. Os usuários reportaram que o sistema está mais rápido.

    O key_buffer_size estava em 64M e o a linha max_connections não existia, portanto usava o padrão de 151 do MySQL.

  • VeraVera Entry Level
    @rogeriolino,

    Não fui capaz de localizar o local exato para alterar o tempo de atualização da tela de atendimento e do painel.

    O que consegui identificar até o momento:

    \sga\painel\js\painel.js
    linha 12 - o default da variável 'interval' é 2
    linha 138 - encontrei este trecho: painel.options.interval * 1000

    Isso significa que o Painel é atualizado a cada 2000 milisegundos = 2 segundos?

    No Google Chrome usei as ferramentas de desenvolvedor (CTRL + Shift + I) e no item Application / Frames / Top / Scripts e vi que existem 2 arquivos script.js na tela de atendimento:

    \sga\public\js\script.js
    linha 10: default para updateInterval é 6000

    \sga\modules\sga\atendimento\public\js\script.js
    linha 15: default para 'timeoutId' é 0
    linhas 84 e 88 : SGA.Atendimento.timeoutId = setTimeout(SGA.Atendimento.ajaxUpdate, SGA.updateInterval);
    linha 144: var delay = prop.enableDelay || 0;
    linha 155 e 178: enableDelay: 5000,

    Tenho 5 unidades, uma delas com 2 paineis e cerca de 45 guichês abertos simultaneamente. Já que o intervalo de atualização parece ser em milisegundos, gostaria de iniciar aumentando em meio segundo.

    DÚVIDAS:

    1) No \sga\painel\js\painel.js - posso mudar na linha 12 para interval: 2.5, ou devo somar 500 na linha 138??

    2) Nos scripts usados na tela de atendimento

    \sga\public\js\script.js - posso mudar a linha 10 para updateInterval: 6500 ??

    \sga\modules\sga\atendimento\public\js\script.js - posso mudar as linhas 155 e 178 para enableDelay: 5500 ??

    3) Após alterar os arquivos .JS é necessário limpar o cache? Ou no próximo acesso à página já será apresentada a alteração?

    4) Na versão 2.0 estes valores vão continuar fixos no código ou serão parâmetros que poderemos alterar pela tela de administração?

    Alguém mais já fez esta alteração?

    Obrigada.
  • VeraVera Entry Level
    Olá,

    Conseguimos finalmente melhorar a performance da aplicação e as telas passaram a abrir e atualizar rapidamente.

    Paramos de mexer nos arquivos de configuração e não alteramos o intervalo de atualização.

    A solução aqui foi criar um novo servidor Windows 2012 64 bits com Wamp 64 bits (na instalação anterior era Wamp 32 bits).

    Migrei os dados usando o utilitário HeidiSQL.

    Está funcionando perfeitamente desde ontem e super rápido. Nas próximas semanas vamos colocar em uso em 3 novas unidades e ver como se comporta.

    No momento está em uso em 5 unidades, emitindo no total 1100 senhas / dia, com cerca de 80 usuários.
Sign In or Register to comment.