|
Could you please add the output of EXPLAIN EXTENDED .. slow_query - for 10.4.22 and 10.4.23(31?) and SHOW CREATE TABLE History; SHOW CREATE TABLE HistoryEvent ? Also there is probably some error in view creation - could you please recheck - I'm getting ERROR 1060 (42S21): Duplicate column name 'id'
|
|
The requested details have been attached (20231115-feedback-01.txt).
Major concern is why 10.4.31 or 10.11.5 takes that long with the same set of data compared to 10.4.22?
|
|
Is there any update on this?
|
|
I could not repeat the issue, there is no definitions of tables user and UserReportTo. Maybe it is possible for you to construct the test case to demonstrate the problem? Is select of that view also slowly (select * from vrecord )?
Could you please try to set optimizer_switch='split_materialized=off' and check execution time then?
Am I correct -you think that the regression was in 10.4.23? or you tried 10.4.31, but not 10.4.23?
|
|
Extremely sorry for the delay in responding!
We're checking the options to go ahead with version 10.11 since it's an LTS version. Hence we're checking in 10.11.5.
Query, SELECT * FROM `vRecord` has no slowness in 10.11.5 compared to 10.4.22.
There's a huge improvement (almost similar to 10.4.22) with your suggested `split_materialized=off`.
By the way we have tried creating the VIEW with `ALGORITHM=TEMPTABLE`, which gave us a performance improvement comparable to 10.4.22.
But there are exceptions when the VIEW is executed with a WHERE condition. With suggested `split_materialized=off` and without `ALGORITHM=TEMPTABLE`, such queries are also executing as fast as in 10.4.22.
We have no performance issues with 10.4.22, but are experiencing the same VIEW related issues with 10.4.24, 10.4.31 and 10.11.5.
Why is that performance/behavioral difference between even 2 minor versions (10.4.22 vs. 10.4.24)?
What are the suggestions to bring 10.11.5 to the same performance level of 10.4.22?
|
|
I suspect it happened after MDEV-27132 or MDEV-27510 patch - so it is not safe to use 10.4.22 as it could return wrong results with such queries.
For the performance - I still need to reproduce it on my side. Would you be able to provide the schema/data dump? You could upload it to ftp.askmonty.org/private, this way only MariaDB developers will have access to it.
|
|
I just uploaded MDEV-32784-dump-info-20240116.tar.gz to ftp.askmonty.org/private which contains:
- an info text file (contains slow queries and VIEW; the VIEW will be created when sourcing the dump.)
- a database dump (including the VIEW and data for 15,000 users)
Hope you can reproduce the slowness with the dump provided.
Please provide explanations for the performance issues in MariaDB 10.11.5 compared to 10.4.22?
What negative implications of using `split_materialized=off`, `ALGORITHM=TEMPTABLE` in production systems?
|
|
Thank you! I repeated the problem on 10.4-11.4 with InnoBD/Myisam. Performance regression after 00412656719bba79cf9a350db05065b0b7007680 commit(MDEV-27510 ).
On my machine on 10.4 653cb195d30bca678b5b175 - query execution time 0.24s vs 5.3s - for tables with 10 rows on debug build, with more rows - the difference is hours.
--source include/have_innodb.inc
|
--source include/have_sequence.inc
|
|
CREATE TABLE t1 ( id int NOT NULL PRIMARY KEY , id2 int, d date , KEY (id2)) ;
|
insert into t1 select seq+30000, FLOOR(RAND() * 5000+10000), NOW() - INTERVAL FLOOR(RAND() * 14) DAY from seq_1_to_25000;
|
|
CREATE TABLE t2 ( id int primary key, a varchar(100) ) ;
|
insert into t2 select seq+10000,concat(seq,'name') from seq_1_to_10;
|
|
CREATE TABLE t3 ( id int primary key ) ;
|
insert into t3 select seq+10000 from seq_1_to_10;
|
|
analyze table t1,t2,t3;
|
|
SET profiling = 1;
|
|
set optimizer_switch='split_materialized=off';
|
|
SELECT *
|
FROM t2 u
|
JOIN t3 ON (u.id = t3.id)
|
LEFT JOIN t2 s ON (s.id = u.id)
|
JOIN
|
(SELECT h1.*
|
FROM
|
t1 h1
|
JOIN
|
(SELECT sh1.id2, max(sh1.id) AS id
|
FROM
|
(SELECT t1.id, t1.id2, t1.d FROM t1) sh1
|
JOIN
|
(SELECT tmp.id2, max(tmp.d) AS d FROM (SELECT t1.id2, t1.d FROM t1) tmp WHERE tmp.d <= now() GROUP BY tmp.id2) sh2
|
on (sh1.id2 = sh2.id2 AND sh1.d = sh2.d)
|
GROUP BY sh1.id2
|
) h2
|
on(h1.id2 = h2.id2 AND h1.id = h2.id)) dt
|
ON u.id = dt.id2
|
ORDER BY s.a LIMIT 1;
|
|
|
set optimizer_switch='split_materialized=on';
|
|
SELECT *
|
FROM t2 u
|
JOIN t3 ON (u.id = t3.id)
|
LEFT JOIN t2 s ON (s.id = u.id)
|
JOIN
|
(SELECT h1.*
|
FROM
|
t1 h1
|
JOIN
|
(SELECT sh1.id2, max(sh1.id) AS id
|
FROM
|
(SELECT t1.id, t1.id2, t1.d FROM t1) sh1
|
JOIN
|
(SELECT tmp.id2, max(tmp.d) AS d FROM (SELECT t1.id2, t1.d FROM t1) tmp WHERE tmp.d <= now() GROUP BY tmp.id2) sh2
|
on (sh1.id2 = sh2.id2 AND sh1.d = sh2.d)
|
GROUP BY sh1.id2
|
) h2
|
on(h1.id2 = h2.id2 AND h1.id = h2.id)) dt
|
ON u.id = dt.id2
|
ORDER BY s.a LIMIT 1;
|
|
SHOW PROFILES;
|
#SHOW PROFILE FOR QUERY 4;
|
SET profiling = 0;
|
|
DROP TABLE t1,t2,t3;
|
MariaDB [test]> explain extended SELECT *
|
-> FROM t2 u
|
-> JOIN t3 ON (u.id = t3.id)
|
-> LEFT JOIN t2 s ON (s.id = u.id)
|
-> JOIN
|
-> (SELECT h1.*
|
-> FROM
|
-> t1 h1
|
-> JOIN
|
-> (SELECT sh1.id2, max(sh1.id) AS id
|
-> FROM
|
-> (SELECT t1.id, t1.id2, t1.d FROM t1) sh1
|
-> JOIN
|
-> (SELECT tmp.id2, max(tmp.d) AS d FROM (SELECT t1.id2, t1.d FROM t1) tmp WHERE tmp.d <= now() GROUP BY tmp.id2) sh2
|
-> on (sh1.id2 = sh2.id2 AND sh1.d = sh2.d)
|
-> GROUP BY sh1.id2
|
-> ) h2
|
-> on(h1.id2 = h2.id2 AND h1.id = h2.id)) dt
|
-> ON u.id = dt.id2
|
-> ORDER BY s.a LIMIT 1;
|
+------+-----------------+------------+--------+---------------+---------+---------+-----------------------+-------+----------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-----------------+------------+--------+---------------+---------+---------+-----------------------+-------+----------+---------------------------------------------------------------------+
|
| 1 | PRIMARY | u | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | Using temporary; Using filesort |
|
| 1 | PRIMARY | t3 | eq_ref | PRIMARY | PRIMARY | 4 | test.u.id | 1 | 100.00 | |
|
| 1 | PRIMARY | s | eq_ref | PRIMARY | PRIMARY | 4 | test.u.id | 1 | 100.00 | |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 5 | test.u.id | 1 | 100.00 | Using where |
|
| 1 | PRIMARY | h1 | eq_ref | PRIMARY,id2 | PRIMARY | 4 | h2.id | 1 | 100.00 | Using where |
|
| 3 | LATERAL DERIVED | t1 | ref | id2 | id2 | 5 | test.u.id | 4 | 100.00 | Using index condition; Using where; Using temporary; Using filesort |
|
| 3 | LATERAL DERIVED | <derived5> | ref | key0 | key0 | 9 | test.t1.id2,test.t1.d | 1 | 100.00 | |
|
| 5 | DERIVED | t1 | ALL | id2 | NULL | NULL | NULL | 24760 | 100.00 | Using where; Using temporary; Using filesort |
|
+------+-----------------+------------+--------+---------------+---------+---------+-----------------------+-------+----------+---------------------------------------------------------------------+
|
8 rows in set, 1 warning (0,001 sec)
|
|
|
Thanks a lot and hoping a favorable response soon
There's another issue related to the same VIEW.
Below query is taking around 15 minutes in v10.11.5 where it gives the results within 200 milliseconds in v10.4.22.
SELECT * FROM `vRecord` WHERE (`eventId` = 5) LIMIT 10 OFFSET 10000;
|
Shall I report a separate issue on this?
|
|
It seems to be the same issue - again LATERAL DERIVED:
MariaDB [test]> explain extended SELECT * FROM `vRecord` WHERE (`eventId` = 5) LIMIT 10 offset 10000;
|
+------+-----------------+------------+-------+-----------------------------------------------+------------------------+---------+------------------------+-------+----------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-----------------+------------+-------+-----------------------------------------------+------------------------+---------+------------------------+-------+----------+----------------------------------------------+
|
| 1 | PRIMARY | H1 | ref | PRIMARY,fkHistory2User,fkHistory2HistoryEvent | fkHistory2HistoryEvent | 5 | const | 7987 | 100.00 | |
|
| 1 | PRIMARY | HE | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 13 | test.H1.uId,test.H1.id | 2 | 100.00 | |
|
| 3 | LATERAL DERIVED | History | ref | fkHistory2User | fkHistory2User | 4 | test.H1.uId | 1 | 100.00 | Using where; Using temporary; Using filesort |
|
| 3 | LATERAL DERIVED | <derived5> | ref | key0 | key0 | 37 | test.History.uId,func | 2 | 100.00 | Using where |
|
| 5 | DERIVED | History | ALL | fkHistory2User | NULL | NULL | NULL | 15974 | 100.00 | Using where; Using temporary; Using filesort |
|
+------+-----------------+------------+-------+-----------------------------------------------+------------------------+---------+------------------------+-------+----------+----------------------------------------------+
|
6 rows in set, 1 warning (0,002 sec)
|
|
Note (Code 1003): /* select#1 */ select `H1`.`id` AS `historyId`,`H1`.`uId` AS `uId`,`H1`.`jobTitleId` AS `jobTitleId`,`H1`.`joinedDate` AS `joinedDate`,`H1`.`eDate` AS `eDate`,`H1`.`eventId` AS `eventId`,`test`.`HE`.`name` AS `eventName` from `test`.`History` `H1` left join `test`.`HistoryEvent` `HE` on(`test`.`HE`.`id` = 5) join (/* select#3 */ select `History`.`uId` AS `uId`,max(`History`.`id`) AS `id` from `test`.`History` join (/* select#5 */ select `test`.`History`.`uId` AS `uId`,max(ifnull(`test`.`History`.`eDate`,'1970-01-01')) AS `eDate` from `test`.`History` where ifnull(`test`.`History`.`eDate`,'1970-01-01') <= <cache>(curdate()) and `test`.`History`.`uId` = `History`.`uId` group by `test`.`History`.`uId`) `SH2` where ifnull(`History`.`eDate`,'1970-01-01') = `SH2`.`eDate` and `History`.`uId` = `H1`.`uId` and `SH2`.`uId` = `History`.`uId` group by `History`.`uId`) `H2` where `H1`.`eventId` = 5 and `H2`.`uId` = `H1`.`uId` and `H2`.`id` = `H1`.`id` limit 10000,10
|
|
MariaDB [test]> SELECT * FROM `vRecord` WHERE (`eventId` = 5) LIMIT 10 offset 10000;
|
^CCtrl-C -- query killed. Continuing normally.
|
ERROR 1317 (70100): Query execution was interrupted
|
|
MariaDB [test]> set optimizer_switch='split_materialized=off';
|
Query OK, 0 rows affected (0,000 sec)
|
|
MariaDB [test]> SELECT * FROM `vRecord` WHERE (`eventId` = 5) LIMIT 10 offset 10000;
|
+-----------+-------+------------+------------+------------+---------+------------------+
|
10 rows in set (0,103 sec)
|
I will recheck if it is fixed after the patch - if not, then let's report it.
|
|
Thanks a lot!
Looking forward to a favorable response soon
|
|
igor, note that there's GROUP BY tmp.id2
|
|
Any progress on this issue? Any plans of resolving?
This is a blocker for our application and we're stuck in v10.4.22.
|
|
amilwaduwawara: the output from ANALYZE FORMAT=JSON before the upgrade and after the upgrade would help us a lot in the analysis of the problem.
|
20231115--feedback--01.txt