什么会启动 MariaDB 开始解析 IP 地址？

Question

squarefighter

Asked: 2024-08-28 02:57:09 +0800 CST2024-08-28 02:57:09 +0800 CST 2024-08-28 02:57:09 +0800 CST

将转储从 mariadb 10.4.22 恢复到 11.2.4 后数据库性能缓慢，ANALYZE TABLE 命令不起作用

772

我在我的两台服务器中的一台上运行着 moodle，他的 moodle 数据库安装在 mariadb 10.4.22 上并且正在维护中，因此我将 moodle 转移到了另一台安装有 mariadb 11.2.4 的服务器上，但是第二台服务器的 moodle 数据库性能存在问题，我在 moodle 论坛上找到了一个帖子，讨论了将 10.4.2 mariadb 的数据库转储恢复到 11 之后的版本时出现的问题，那里说我需要在数据库上运行 ANALYZE TABLE 才能使其更好地工作：

https://moodle.org/mod/forum/discuss.php?d=446712

我尝试在数据库的每个表上执行此命令，但是它不起作用，数据库仍然非常慢（主要是消息），我尝试将转储恢复到旧服务器并且它正常工作，但是 moodle 不再支持 mariadb 10.4.2，所以我必须更新，我该怎么办？我可以运行其他命令吗？我该如何解决问题？（我对故障排除不是很有经验，所以如果你们想要一些信息，就说我会提供，因为我不知道解决这个问题需要什么信息）

当我在新服务器（装有 mariadb 11.2.4 的服务器）上运行 mysqltuner 时有结果，但似乎没有太大帮助。

mysqltuner1 mysqltuner2 mysqltuner3 mysqltuner4

编辑：我收到了来自 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;

编辑2：我在两台服务器上的查询中使用命令 EXPLAIN：

11.2.4 mariadb服务器（存在性能问题的服务器，这里的数据库称为my_db）：

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

10.4.2 mariadb服务器（没有问题的服务器，这里的数据库称为moodle）：

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

看起来两个服务器的索引之间没有太大差别。

编辑 3：这些是针对来自 slowlog 的查询所涉及的表的命令 SHOW CREATE TABLE 的返回值：

为服务器 mariadb 11.2.4（存在性能问题的服务器）创建表

https://pastebin.com/LFTyXvcJ

为服务器 mariadb 10.4.2（没有问题的服务器）创建表

https://pastebin.com/FSvj9fCV

2 个回答

Voted

Rick James · Answer 1 · 2024-08-28T07:47:47+08:00

Rick James

2024-08-28T07:47:47+08:002024-08-28T07:47:47+08:00

“分析不起作用”——你的意思是它对性能没有帮助？还是它以某种方式崩溃了？（ANALYZE TABLE通常对 InnoDB 表无用。）

Moodle 有“471 个表”？？我预计不会超过十几个 -- 1 个用于数据，几个用于登录。

“[--] 等待时间：13 秒（529 q [40.692 qps]”——至少等待一小时。很多东西需要缓存在 RAM 中才能提高速度

“[OK] 慢速查询：0% (0/529)”——降低long_query_time至“1”并收集慢速日志。

“[--] 物理内存 : 4.0G” 和 “[OK] InnoDB 缓冲池 / 数据大小: 2.0G / 1.2G” -- 这个数字太高了，很危险；有交换吗？（我建议innodb_buffer_pool_size在 4GB RAM 的机器上只使用 1G。）

我假设您不能修改查询？您提出的查询相当复杂：

OR难以优化
ORDER BY expression LIMIT ...闻起来像无法提高效率的分页。
也许可以添加一些复合索引。请为表提供“SHOW CREATE TABLE”。

0

squarefighter · Answer 2 · 2024-08-29T03:53:56+08:00

通过刷新数据库上的所有表、INDEX_STATISTICS、QUERY CACHE、TABLE_STATISTICS 和 RESET QUERY CACHE，然后将查询缓存添加到 my.cnf 文件，我能够让 mariadb 11.2.4 服务器运行得更好一些：

~~查询缓存大小=64M~~

~~第一次运行仍然很慢，但随后加载速度会快得多，我仍然试图弄清楚为什么消息查询仍然有问题，但现在它有一个缓解的解决方案。~~

我解决了这个问题！

事实证明问题在于 moodle 的消息表上的列冲突，早在六月份，我截断了表 ehvp_messages 以清理 moodle 上的消息，这会在注册有关这些消息信息的其他表上产生冲突，因为它开始在这些表上创建重复项，为了解决这个问题，我只需截断 moodle 上的所有消息表，在您的数据库上运行此查询（它将从您的 moodle 中删除所有消息）：

 TRUNCATE TABLE ehvp_message;
 TRUNCATE TABLE ehvp_message_airnotifier_devices;
 TRUNCATE TABLE ehvp_message_contact_requests;
 TRUNCATE TABLE ehvp_message_contacts;
 TRUNCATE TABLE ehvp_message_conversation_actions;
 TRUNCATE TABLE ehvp_message_conversation_members;
 TRUNCATE TABLE ehvp_message_conversations;
 TRUNCATE TABLE ehvp_message_email_messages;
 TRUNCATE TABLE ehvp_message_popup;
 TRUNCATE TABLE ehvp_message_popup_notifications;
 TRUNCATE TABLE ehvp_message_processors;
 TRUNCATE TABLE ehvp_message_providers;
 TRUNCATE TABLE ehvp_message_read;
 TRUNCATE TABLE ehvp_message_user_actions;
 TRUNCATE TABLE ehvp_message_users_blocked;
 TRUNCATE TABLE ehvp_messageinbound_datakeys;
 TRUNCATE TABLE ehvp_messageinbound_handlers;
 TRUNCATE TABLE ehvp_messageinbound_messagelist;
 TRUNCATE TABLE ehvp_messages;

将转储从 mariadb 10.4.22 恢复到 11.2.4 后数据库性能缓慢，ANALYZE TABLE 命令不起作用

编辑：我收到了来自 slowlog 的回复

编辑2：我在两台服务器上的查询中使用命令 EXPLAIN：

编辑 3：这些是针对来自 slowlog 的查询所涉及的表的命令 SHOW CREATE TABLE 的返回值：

新安装后 postgres 的默认超级用户用户名/密码是什么？

SFTP 使用什么端口？

命令行列出 Windows Active Directory 组中的用户？

什么是 Pem 文件，它与其他 OpenSSL 生成的密钥文件格式有何不同？

如何确定bash变量是否为空？

将转储从 mariadb 10.4.22 恢复到 11.2.4 后数据库性能缓慢，ANALYZE TABLE 命令不起作用

编辑：我收到了来自 slowlog 的回复

编辑2：我在两台服务器上的查询中使用命令 EXPLAIN：

编辑 3：这些是针对来自 slowlog 的查询所涉及的表的命令 SHOW CREATE TABLE 的返回值：

2 个回答

相关问题