Estou executando o Moodle em um dos meus dois servidores, ele tem o banco de dados do Moodle no MariaDB 10.4.22 e está em manutenção, então transferi o Moodle para o outro servidor que tem o MariaDB 11.2.4, no entanto, o segundo servidor está tendo problemas com o desempenho do banco de dados do Moodle. Encontrei um tópico nos fóruns do Moodle falando sobre problemas com o despejo do banco de dados do MariaDB 10.4.2 restaurado para uma versão posterior à 11. Lá está escrito que preciso executar ANALYZE TABLE no banco de dados para fazê-lo funcionar melhor:
https://moodle.org/mod/forum/discuss.php?d=446712
Eu tentei esse comando em cada tabela no BD, no entanto, não funcionou e o BD ainda está muito lento (principalmente mensagens), tentei restaurar o dump para o servidor antigo e funcionou normalmente, no entanto, o moodle não suporta mais o mariadb 10.4.2, então terei que atualizar, o que posso fazer? Existe outro comando que eu possa executar? Como posso solucionar o problema? (Não tenho muita experiência com solução de problemas, então se vocês quiserem alguma informação, é só dizer que eu vou fornecer porque não sei quais informações são necessárias para resolver esse problema)
aqui estão os resultados quando executo o mysqltuner no novo servidor (aquele com o mariadb 11.2.4), mas não parece ajudar muito.
mysqltuner1 mysqltuner2 mysqltuner3 mysqltuner4
EDIT: Recebi um retorno do meu slowlog
# Time: 240828 12:55:04
# User@Host: user[user] @ localhost []
# Thread_id: 318 Schema: my_db QC_hit: No
# Query_time: 332.625304 Lock_time: 0.000335 Rows_sent: 32 Rows_examined: 111342578
# Rows_affected: 0 Bytes_sent: 0
use my_db;
SET timestamp=1724849704;
SELECT m.id as messageid, mc.id as id, mc.name as conversationname, mc.type as conversationtype, m.useridfrom,
m.smallmessage, m.fullmessage, m.fullmessageformat, m.fullmessagetrust, m.fullmessagehtml, m.timecreated,
mc.component, mc.itemtype, mc.itemid, mc.contextid, mca.action as ismuted
FROM ehvp_message_conversations mc
INNER JOIN ehvp_message_conversation_members mcm
ON (mcm.conversationid = mc.id AND mcm.userid = '21')
LEFT JOIN (
SELECT m.conversationid, MAX(m.id) AS messageid
FROM ehvp_messages m
INNER JOIN (
SELECT m.conversationid, MAX(m.timecreated) as maxtime
FROM ehvp_messages m
INNER JOIN ehvp_message_conversation_members mcm
ON mcm.conversationid = m.conversationid
LEFT JOIN ehvp_message_user_actions mua
ON (mua.messageid = m.id AND mua.userid = '21' AND mua.action = '2')
WHERE mua.id is NULL
AND mcm.userid = '21'
GROUP BY m.conversationid
) maxmessage
ON maxmessage.maxtime = m.timecreated AND maxmessage.conversationid = m.conversationid
GROUP BY m.conversationid
) lastmessage
ON lastmessage.conversationid = mc.id
LEFT JOIN ehvp_messages m
ON m.id = lastmessage.messageid
LEFT JOIN ehvp_message_conversation_actions mca
ON (mca.conversationid = mc.id AND mca.userid = '21' AND mca.action = '1')
WHERE mc.id IS NOT NULL
AND mc.enabled = 1 AND (mc.type = '1' OR mc.type = '3') AND mc.id <> '13'
ORDER BY (CASE WHEN m.timecreated IS NULL THEN 0 ELSE 1 END) DESC, m.timecreated DESC, id DESC LIMIT 0, 51;
EDIT 2: Eu uso o comando EXPLAIN na consulta em ambos os servidores:
O servidor mariadb 11.2.4 (aquele que sofre com problemas de desempenho, o banco de dados é chamado my_db aqui):
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+--------------------------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+--------------------------------------------+---------+----------------------------------------------+
| 1 | PRIMARY | mcm | ref | ehvp_messconvmemb_con_ix,ehvp_messconvmemb_use_ix | ehvp_messconvmemb_use_ix | 8 | const | 4092 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | mc | eq_ref | PRIMARY,ehvp_messconv_typ_ix | PRIMARY | 8 | my_db.mcm.conversationid | 1 | Using where |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 9 | my_db.mcm.conversationid | 1 | |
| 1 | PRIMARY | m | eq_ref | PRIMARY | PRIMARY | 8 | lastmessage.messageid | 1 | Using where |
| 1 | PRIMARY | mca | ref|filter | ehvp_messconvacti_use_ix,ehvp_messconvacti_con_ix | ehvp_messconvacti_con_ix|ehvp_messconvacti_use_ix | 8|8 | my_db.mcm.conversationid | 1 (11%) | Using where; Using rowid filter |
| 2 | LATERAL DERIVED | m | ref | ehvp_mess_contim_ix,ehvp_mess_con_ix | ehvp_mess_contim_ix | 8 | my_db.mcm.conversationid | 2 | Using where; Using index |
| 2 | LATERAL DERIVED | <derived3> | ref | key0 | key0 | 17 | my_db.m.conversationid,my_db.m.timecreated | 1 | |
| 3 | DERIVED | m | index | ehvp_mess_contim_ix,ehvp_mess_con_ix | ehvp_mess_contim_ix | 16 | NULL | 9664 | Using index; Using temporary; Using filesort |
| 3 | DERIVED | mua | eq_ref | ehvp_messuseracti_usemesac_uix,ehvp_messuseracti_use_ix,ehvp_messuseracti_mes_ix | ehvp_messuseracti_usemesac_uix | 24 | const,my_db.m.id,const | 1 | Using where; Using index; Not exists |
| 3 | DERIVED | mcm | ref | ehvp_messconvmemb_con_ix,ehvp_messconvmemb_use_ix | ehvp_messconvmemb_con_ix | 8 | my_db.m.conversationid | 1 | Using where |
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+--------------------------------------------+---------+----------------------------------------------+
O servidor mariadb 10.4.2 (aquele sem problemas, o banco de dados é chamado moodle aqui):
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+----------------------------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+----------------------------------------------+---------+----------------------------------------------+
| 1 | PRIMARY | mcm | ref | ehvp_messconvmemb_con_ix,ehvp_messconvmemb_use_ix | ehvp_messconvmemb_use_ix | 8 | const | 2988 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | mc | eq_ref | PRIMARY,ehvp_messconv_typ_ix | PRIMARY | 8 | moodle.mcm.conversationid | 1 | Using where |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 9 | moodle.mcm.conversationid | 2 | |
| 1 | PRIMARY | m | eq_ref | PRIMARY | PRIMARY | 8 | lastmessage.messageid | 1 | Using where |
| 1 | PRIMARY | mca | ref|filter | ehvp_messconvacti_use_ix,ehvp_messconvacti_con_ix | ehvp_messconvacti_con_ix|ehvp_messconvacti_use_ix | 8|8 | moodle.mcm.conversationid | 1 (11%) | Using where; Using rowid filter |
| 2 | LATERAL DERIVED | m | ref | ehvp_mess_contim_ix,ehvp_mess_con_ix | ehvp_mess_contim_ix | 8 | moodle.mcm.conversationid | 1 | Using index |
| 2 | LATERAL DERIVED | <derived3> | ref | key0 | key0 | 17 | moodle.m.conversationid,moodle.m.timecreated | 2 | |
| 3 | DERIVED | m | index | ehvp_mess_contim_ix,ehvp_mess_con_ix | ehvp_mess_contim_ix | 16 | NULL | 5005 | Using index; Using temporary; Using filesort |
| 3 | DERIVED | mua | ref | ehvp_messuseracti_usemesac_uix,ehvp_messuseracti_use_ix,ehvp_messuseracti_mes_ix | ehvp_messuseracti_mes_ix | 8 | moodle.m.id | 1 | Using where; Not exists |
| 3 | DERIVED | mcm | ref|filter | ehvp_messconvmemb_con_ix,ehvp_messconvmemb_use_ix | ehvp_messconvmemb_con_ix|ehvp_messconvmemb_use_ix | 8|8 | moodle.m.conversationid | 1 (3%) | Using where; Using rowid filter |
+------+-----------------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------+---------+----------------------------------------------+---------+----------------------------------------------+
Parece que há pequenas diferenças entre os índices de ambos os servidores.
EDIT 3: Esses são os retornos do comando SHOW CREATE TABLE para as tabelas envolvidas na consulta do slowlog:
Crie tabelas para o servidor mariadb 11.2.4 (aquele com problemas de desempenho)
Crie tabelas para o servidor mariadb 10.4.2 (aquele sem problemas)