1 Topic by mediatis

  • mediatis
  • Member
  • Offline
  • Registered: 2022-04-19
  • Posts: 9

Topic: way to long running query when loging in.

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 1.6.5 OPENLDAP edition
- Deployed with iRedMail Easy or the downloadable installer? downloadable installer
- Linux/BSD distribution name and version: Ubuntu 22.04 LTS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): LDAP
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? Yes
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

We are having trouble with logins to iRedAdmin-Pro, a longrunning SQL querry runs into an timeout.

Responsible Querry:

MariaDB [amavisd]> SELECT COUNT(msgs.mail_id) AS total
    ->                         FROM msgs
    ->                         LEFT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
    ->                         LEFT JOIN maddr AS sender ON (msgs.sid = sender.id)
    ->                         LEFT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
    ->                         WHERE msgs.quar_type <> 'Q'  AND recip.domain IN ('<LIST OF DOMAINS>') AND msgs.time_num > 1696837022
    -> ;
+-------+
| total |
+-------+
|  3419 |
+-------+
1 row in set (47.928 sec)

we for now did a workaround by adding an uwsgi timeout to the nginx config:

/etc/nginx/templates/iredadmin.tmpl 

uwsgi_param SCRIPT_NAME /iredadmin;
uwsgi_read_timeout 600s;

# Access control

As this is for us only a workaround, we would like to know how we could fix it.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by Cthulhu

  • Cthulhu
  • Member
  • Offline
  • Registered: 2019-06-04
  • Posts: 826

Re: way to long running query when loging in.

this seems more a problem with the mariadb database, the SQL query should not take that long

how many domains?

you can:
empty tables msgs, madrr, msrcpt

this doesn't brick anthing, you just cannot see infos about send and recived mails in admin interface anymore

my best guess is, that you are missing indexes on your database and this wasn't a new installation, but an update

3 Reply by Cthulhu

  • Cthulhu
  • Member
  • Offline
  • Registered: 2019-06-04
  • Posts: 826

Re: way to long running query when loging in.

could you pls run this statement and tell me the results:

SELECT COUNT(msgs.mail_id) AS total
FROM msgs
LEFT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
LEFT JOIN maddr AS sender ON (msgs.sid = sender.id)
LEFT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
WHERE msgs.quar_type <> 'Q';

i only need the total runtime of the statement

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,793

Re: way to long running query when loging in.

- each column used in query is indexed separately, although no composite index, but it should be still fast enough.
- How many records in each table: msgs, msgrcpt, quarantine?
- Please show us your indexes, and compare them with the ones created by iRedMail: https://github.com/iredmail/iRedMail/bl … visd.mysql

SHOW INDEX FROM msgs;
SHOW INDEX FROM msgrcpt;
SHOW INDEX FROM quarantine;

5 Reply by mediatis

  • mediatis
  • Member
  • Offline
  • Registered: 2022-04-19
  • Posts: 9

Re: way to long running query when loging in.

Cthulhu wrote:

could you pls run this statement and tell me the results:

SELECT COUNT(msgs.mail_id) AS total
FROM msgs
LEFT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
LEFT JOIN maddr AS sender ON (msgs.sid = sender.id)
LEFT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
WHERE msgs.quar_type <> 'Q';

i only need the total runtime of the statement

Runtime here is: 1 row in set (13.307 sec)

6 Reply by mediatis

  • mediatis
  • Member
  • Offline
  • Registered: 2022-04-19
  • Posts: 9

Re: way to long running query when loging in.

Cthulhu wrote:

this seems more a problem with the mariadb database, the SQL query should not take that long

how many domains?

you can:
empty tables msgs, madrr, msrcpt

this doesn't brick anthing, you just cannot see infos about send and recived mails in admin interface anymore

my best guess is, that you are missing indexes on your database and this wasn't a new installation, but an update

yep it was an upgrade and always kept up to date. the last two years, while before was not really cared about...

7 Reply by mediatis (edited by mediatis 2023-10-11 15:28:38)

  • mediatis
  • Member
  • Offline
  • Registered: 2022-04-19
  • Posts: 9

Re: way to long running query when loging in.

ZhangHuangbin wrote:

- each column used in query is indexed separately, although no composite index, but it should be still fast enough.
- How many records in each table: msgs, msgrcpt, quarantine?
- Please show us your indexes, and compare them with the ones created by iRedMail: https://github.com/iredmail/iRedMail/bl … visd.mysql

SHOW INDEX FROM msgs;
SHOW INDEX FROM msgrcpt;
SHOW INDEX FROM quarantine;

the indexes seem to be fitting.
Records in:
msgs:              407995
msgrcpt:         407950
quarantine:          189

8 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,793

Re: way to long running query when loging in.

mediatis wrote:

the indexes seem to be fitting.

What's the command output mentioned in my previous reply?

9 Reply by mediatis

  • mediatis
  • Member
  • Offline
  • Registered: 2022-04-19
  • Posts: 9

Re: way to long running query when loging in.

ZhangHuangbin wrote:
mediatis wrote:

the indexes seem to be fitting.

What's the command output mentioned in my previous reply?

Hi, sorry I missed that one. The output is:

MariaDB [amavisd]> SHOW INDEX FROM msgs;
+-------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name                  | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| msgs  |          0 | PRIMARY                   |            1 | partition_tag | A         |           2 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgs  |          0 | PRIMARY                   |            2 | mail_id       | A         |      391126 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgs  |          1 | msgs_idx_sid              |            1 | sid           | A         |      195563 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgs  |          1 | msgs_idx_mess_id          |            1 | message_id    | A         |      391126 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| msgs  |          1 | msgs_idx_time_num         |            1 | time_num      | A         |      391126 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgs  |          1 | msgs_idx_mail_id          |            1 | mail_id       | A         |      391126 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgs  |          1 | msgs_idx_content          |            1 | content       | A         |          14 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| msgs  |          1 | msgs_idx_quar_type        |            1 | quar_type     | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| msgs  |          1 | msgs_idx_content_time_num |            1 | content       | A         |          14 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| msgs  |          1 | msgs_idx_content_time_num |            2 | time_num      | A         |      391126 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgs  |          1 | msgs_idx_spam_level       |            1 | spam_level    | A         |       30086 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+-------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
11 rows in set (0.000 sec)

MariaDB [amavisd]> SHOW INDEX FROM msgrcpt;
+---------+------------+---------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name            | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+---------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| msgrcpt |          0 | PRIMARY             |            1 | partition_tag | A         |           2 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgrcpt |          0 | PRIMARY             |            2 | mail_id       | A         |      311379 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgrcpt |          0 | PRIMARY             |            3 | rseqnum       | A         |      311379 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgrcpt |          1 | msgrcpt_idx_mail_id |            1 | mail_id       | A         |      311379 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| msgrcpt |          1 | msgrcpt_idx_rid     |            1 | rid           | A         |       44482 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------+------------+---------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
5 rows in set (0.000 sec)

MariaDB [amavisd]> SHOW INDEX FROM quarantine;
+------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table      | Non_unique | Key_name         | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| quarantine |          0 | PRIMARY          |            1 | partition_tag | A         |           2 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| quarantine |          0 | PRIMARY          |            2 | mail_id       | A         |          58 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| quarantine |          0 | PRIMARY          |            3 | chunk_ind     | A         |         176 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| quarantine |          1 | quar_idx_mail_id |            1 | mail_id       | A         |          58 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
4 rows in set (0.000 sec)

10 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,793

Re: way to long running query when loging in.

mediatis wrote:

Hi, sorry I missed that one. The output is:

Indexes seem fine, but i found the output of SQL command offered by @Cthulhu is not ideal:

MariaDB [amavisd]> explain SELECT COUNT(msgs.mail_id) AS total FROM msgs LEFT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id) LEFT JOIN maddr AS sender ON (msgs.sid = sender.id) LEFT JOIN maddr AS recip ON (msgrcpt.rid = recip.id) WHERE msgs.quar_type <> 'Q'; 
+------+-------------+---------+------+---------------------+---------------------+---------+----------------------+------+-------------+
| id   | select_type | table   | type | possible_keys       | key                 | key_len | ref                  | rows | Extra       |
+------+-------------+---------+------+---------------------+---------------------+---------+----------------------+------+-------------+
|    1 | SIMPLE      | msgs    | ALL  | msgs_idx_quar_type  | NULL                | NULL    | NULL                 | 7361 | Using where |
|    1 | SIMPLE      | msgrcpt | ref  | msgrcpt_idx_mail_id | msgrcpt_idx_mail_id | 18      | amavisd.msgs.mail_id |    1 |             |
+------+-------------+---------+------+---------------------+---------------------+---------+----------------------+------+-------------+
2 rows in set (0.001 sec)

Same command in different output format:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: msgs
         type: ALL
possible_keys: msgs_idx_quar_type
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7361
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: msgrcpt
         type: ref
possible_keys: msgrcpt_idx_mail_id
          key: msgrcpt_idx_mail_id
      key_len: 18
          ref: amavisd.msgs.mail_id
         rows: 1
        Extra: 
2 rows in set (0.001 sec)

It says "possible_keys: msgs_idx_quar_type", but "key: NULL", seems index `msgs_idx_quar_type` is not used at all.