Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.5
-
None
-
None
-
CentOS 7, x86_64, MariaDB-server-10.6.5-1.el7.centos built by MariaDB Foundation
Description
Two tables:
MariaDB [opendmarc]> describe messages;
|
+---------------+---------------------+------+-----+---------------------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+---------------+---------------------+------+-----+---------------------+----------------+
|
| id | int(11) | NO | PRI | NULL | auto_increment |
|
| date | timestamp | NO | MUL | current_timestamp() | |
|
| jobid | varchar(128) | NO | | NULL | |
|
| reporter | int(10) unsigned | NO | MUL | NULL | |
|
| policy | tinyint(3) unsigned | NO | | NULL | |
|
| disp | tinyint(3) unsigned | NO | | NULL | |
|
| ip | int(10) unsigned | NO | | NULL | |
|
| env_domain | int(10) unsigned | NO | | NULL | |
|
| from_domain | int(10) unsigned | NO | | NULL | |
|
| policy_domain | int(10) unsigned | NO | | NULL | |
|
| spf | tinyint(4) | NO | | NULL | |
|
| align_dkim | tinyint(3) unsigned | NO | | NULL | |
|
| align_spf | tinyint(3) unsigned | NO | | NULL | |
|
| sigcount | tinyint(3) unsigned | NO | | NULL | |
|
| arc | tinyint(3) unsigned | NO | | NULL | |
|
| arc_policy | tinyint(3) unsigned | NO | | NULL | |
|
+---------------+---------------------+------+-----+---------------------+----------------+
|
16 rows in set (0.001 sec)
|
|
|
MariaDB [opendmarc]> describe domains;
|
+-----------+--------------+------+-----+---------------------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-----------+--------------+------+-----+---------------------+----------------+
|
| id | int(11) | NO | PRI | NULL | auto_increment |
|
| name | varchar(255) | NO | UNI | NULL | |
|
| firstseen | timestamp | NO | | current_timestamp() | |
|
+-----------+--------------+------+-----+---------------------+----------------+
|
3 rows in set (0.001 sec)
|
This query hangs and pegs the CPU on the mariadb server at 100%:
select count(*) from messages join domains d1 on messages.from_domain = d1.id join domains d2 on messages.env_domain = d2.id where messages.from_domain='10';
|
If I change from_domain='10' to from_domain=10 the query no longer hangs.
Note the difference in explain output for the query that hangs vs the query does doesn't:
MariaDB [opendmarc]> explain select count(*) from messages join domains d1 on messages.from_domain = d1.id join domains d2 on messages.env_domain = d2.id where messages.from_domain='10';
|
+------+-------------+----------+-------+---------------+------+---------+------+-------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+------+---------+------+-------+--------------------------------------------------------+
|
| 1 | SIMPLE | d1 | index | PRIMARY | name | 257 | NULL | 1 | Using index |
|
| 1 | SIMPLE | d2 | index | PRIMARY | name | 257 | NULL | 1 | Using index; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | messages | ALL | NULL | NULL | NULL | NULL | 22674 | Using where; Using join buffer (incremental, BNL join) |
|
+------+-------------+----------+-------+---------------+------+---------+------+-------+--------------------------------------------------------+
|
3 rows in set (0.001 sec)
|
|
|
MariaDB [opendmarc]> explain select count(*) from messages join domains d1 on messages.from_domain = d1.id join domains d2 on messages.env_domain = d2.id where messages.from_domain=10;
|
+------+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------------------------------------------+
|
| 1 | SIMPLE | d1 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
|
| 1 | SIMPLE | d2 | index | PRIMARY | name | 257 | NULL | 1 | Using index |
|
| 1 | SIMPLE | messages | ALL | NULL | NULL | NULL | NULL | 22674 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------------------------------------------+
|