Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
5.3.12
-
None
-
RedHat EL6 using mariadb-5.3.12-Linux-x86_64.tar.gz
Description
Hello and thank you for mariadb,
I noticed mariadb 5.3.12 using a lot more memory than MySQL 5.1.48 for a very inefficient query (that should never get near production):
CREATE TABLE `table_a` ( |
`field_c` varchar(8) DEFAULT NULL, |
`field_d` varchar(11) DEFAULT NULL, |
UNIQUE KEY `field_c` (`field_c`), |
UNIQUE KEY `field_d` (`field_d`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
CREATE TABLE `table_b` ( |
`field_c` char(9) NOT NULL, |
`field_d` char(12) DEFAULT NULL, |
PRIMARY KEY (`field_c`), |
KEY `field_d` (`field_d`) |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
table_a has 4,834,708 rows,
table_b has 6,522,728 rows, I will upload a tar.gz with mysqldumps of the tables.
The query:
SELECT
|
table_a.field_d
|
FROM
|
table_a
|
INNER JOIN table_b |
ON table_a.field_c = SUBSTR(table_b.field_c, 1, 8) OR table_a.field_d = SUBSTR(table_b.field_d, 1, 11); |
MySQL 5.1.48 takes a while but does return 4,099,991 rows with:
VmPeak: 9891240 kB
VmHWM: 7855352 kB
Staying under a 10 Gigabyte virtual memory ulimit.
+----+-------------+---------+------+-----------------+------+---------+------+---------+----------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+---------+------+-----------------+------+---------+------+---------+----------+------------------------------------------------+
|
| 1 | SIMPLE | table_b | ALL | NULL | NULL | NULL | NULL | 6522728 | 100.00 | |
|
| 1 | SIMPLE | table_a | ALL | field_c,field_d | NULL | NULL | NULL | 4817421 | 100.00 | Range checked for each record (index map: 0x3) |
|
+----+-------------+---------+------+-----------------+------+---------+------+---------+----------+------------------------------------------------+
|
select `test`.`table_a`.`field_d` AS `field_d` from `test`.`table_a` join `test`.`table_b` where ((`test`.`table_a`.`field_c` = substr(`test`.`table_b`.`field_c`,1,8)) or (`test`.`table_a`.`field_d` = substr(`test`.`table_b`.`field_d`,1,11)))
|
mariadb 5.3.12 hits a 20 Gigabyte virtual memory ulimit, RSS is around 16G.
+----+-------------+---------+------+-----------------+------+---------+------+---------+----------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+---------+------+-----------------+------+---------+------+---------+----------+------------------------------------------------+
|
| 1 | SIMPLE | table_b | ALL | NULL | NULL | NULL | NULL | 6522728 | 100.00 | |
|
| 1 | SIMPLE | table_a | ALL | field_c,field_d | NULL | NULL | NULL | 4798671 | 100.00 | Range checked for each record (index map: 0x3) |
|
+----+-------------+---------+------+-----------------+------+---------+------+---------+----------+------------------------------------------------+
|
select `test`.`table_a`.`field_d` AS `field_d` from `test`.`table_a` join `test`.`table_b` where ((`test`.`table_a`.`field_c` = substr(`test`.`table_b`.`field_c`,1,8)) or (`test`.`table_a`.`field_d` = substr(`test`.`table_b`.`field_d`,1,11)))
|
I was using the following when reproducing:
[mysqld]
|
key_buffer = 500M
|
innodb_buffer_pool_size = 2000M
|
Thank you.