Aqui temos o mesmo problema, ao gerar um relatório observamos um consumo de 100% da CPU e todos os usuários ficam congelados, só voltando a funcionar quando o relatório é concluído.
No relatório 'Serviços codificados', dependendo da quantidade de dias solicitados, chega a dar erro HTTP ERROR 504 (demorou muito para responder).
Temos dados desde maio de 2017 na tabela histórico, sei que a quantidade é muito grande.
Vi no banco de dados que algumas tabelas são FIXED e outras DYNAMIC
show table status
where engine = 'MyISAM'
and lower(name) like '%atend%'
and rows > 0
atendimentos Dynamic 311 registros
atend_codif Fixed 294 registros
historico_atendimentos Dynamic 800118 registros
historico_atend_codif Fixed 886632 registros
Para não perder o histórico (podem ser necessários para dados comparativos), estou planejando mover os dados de 2017 e 2018 para tabelas separadas, mantendo nas tabelas atuais apenas os de 2019 e 2020. Não localizei no NovoSGA uma rotina pronta para isso, preciso dos comandos SQL para executar direto no banco de dados.
Vou relatar a seguir o que já analisamos por aqui. Nosso ambiente:
- MySQL 5.5.60-0+deb8u1-log
- Ambiente Linux
- NovoSGA versão 1.51
#Dúvidas para o @rogeriolino e quem mais possa ajudar...
1) Posso mudar as tabelas atendimentos e historico_atendimentos de Dynamic para Fixed?
ALTER TABLE atendimentos ROW_FORMAT=FIXED;
ALTER TABLE historico_atendimentos ROW_FORMAT=FIXED;
2) Já que as views de relatórios usam as tabelas atendimentos e atend_codif, podemos mudar apenas elas de MyISAM (lock por tabela) para InnoDB (lock por registro)?
3) Alguém tem algum script pronto para as ações abaixo?
copiar as tabelas de histórico de atendimento com dados e índices para novas tabelas
apagar nas tabelas de histórico as senhas de anos selecionados
para tabela com formato FIXED, quais parâmetros devo usar no OPTIMIZE TABLE para otimizar o espaço dos dados apagados?
4) É recomendável fazer um OPTIMIZE para todas as tabelas periodicamente para otimizar índices e o espaço usado pelas tabelas?
O que já analisamos:
Este foi um select executado ontem que 'congelou' a aplicação por 3.5 minutos: Relatório 'Serviços Codificados' da unidade 2 para atendimentos de 1 mês (16 Jun a 15 Jul).
SELECT COUNT(s0.id) AS sclr_0, s0.nome AS nome_1 FROM view_historico_atend_codif v1 INNER JOIN servicos s0 ON v1.servico_id = s0.id INNER JOIN view_historico_atendimentos v2 ON v1.atendimento_id = v2.id WHERE v2.unidade_id = 2 AND v2.dt_cheg >= '2020-06-16' AND v2.dt_cheg <= '2020-07-15 23:59:59' GROUP BY s0.nome, s0.descricao, s0.status, s0.peso, s0.id, s0.macro_id ORDER BY s0_.nome ASC LIMIT 3000;
Query_time: 201.233820 Lock_time: 0.000350 Rows_sent: 10 Rows_examined: 4260159
Vi no banco de dados que as tabelas de atendimento e historico_atendimento são MyISAM e tem outras InnoDB.
Em https://www.oficinadanet.com.br/artigo/789/mysql-diferenca_entre_innodb_e_myisam tem a informação de que:
InnoDB funciona mais rápido que MyISAM quando há modificações constantes nos dados, uma vez que este tipo de armazenagem usa a proteção por registros (row locking) e não a proteção por tabelas (table locking) como o faz o MyISAM.
Em várias situações, InnoDB é mais lento por funcionar com transações.
MyISAM é apropriado para tabelas cujos dados não mudam com frequência.
Vi na internet que existe diferença na velocidade de acesso aos dados entre os 2 tipos de tabelas.
Em um artigo antigo https://dba.stackexchange.com/questions/17431/which-is-faster-innodb-or-myisam existe a informação de que MyISAM com formato de registro fixo seria mais rápido.
Nesse artigo de 2012 https://www.soliantconsulting.com/blog/mysql-optimization-myisam/ tem a informação de que tabelas MyISAM tem "table level locking", isso significa que enquanto uma query está sendo executada, todas as outras ficam aguardando 'na fila'.
Neste outro link https://en.wikipedia.org/wiki/MyISAM (sim, sei que não é informação 100% tecnica...) existem as seguintes informações:
- MyISAM is optimized for environments with heavy read operations, and few writes, or none at all.
- records can be quickly read, especially when the format is FIXED. Thus, the rows are of constant length.
- However, delete and update operations are more problematic: they must leave an empty space,
- To defragment rows and claim empty space, the OPTIMIZE TABLE command must be executed.
- The major deficiency of MyISAM is the absence of transactions support. Also, foreign keys are not supported. In normal use cases, InnoDB seems to be faster than MyISAM.
- Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints, and higher concurrency.
Neste link do MariaDB (semelhante ao MySQL) https://mariadb.com/kb/en/myisam-storage-formats/ explicam que:
- existem 3 tipos de registros FIXED, DYNAMIC and COMPRESSED
- DEFAULT = tipo é escolhido automaticamente dependendo dos tipos de colunas das tabelas. Se tem algum campo com coluna de tamanho variável ( Ex. VARCHAR, TEXT, BLOB ) é usado o tipo DYNAMIC. Tabelas com campos BLOB ou TEXT não permitem o tipo FIXED.