Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.6.9, 10.6.18, 10.6.19, 10.6.20, 10.11.10
-
mariadbd Ver 11.4.4-MariaDB-log for Linux on x86_64 (MariaDB Server)
user: root
Rocky Linux 8.6 (Green Obsidian)
Linux MDB-11-4-4-R86-111 4.18.0-372.9.1.el8.x86_64 #1 SMP Tue May 10 14:48:47 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
IOPS: 186, 11904 fio equivalent, flush=O_DIRECT
MemTotal: 824104 kB
CPU counts: physical 1mariadbd Ver 11.4.4-MariaDB-log for Linux on x86_64 (MariaDB Server) user: root Rocky Linux 8.6 (Green Obsidian) Linux MDB-11-4-4-R86-111 4.18.0-372.9.1.el8.x86_64 #1 SMP Tue May 10 14:48:47 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux IOPS: 186, 11904 fio equivalent, flush=O_DIRECT MemTotal: 824104 kB CPU counts: physical 1
Description
Profiling shows the problem to be in the "Sending data" step:
10.5: Duration: 0.00146 seconds
10.6: Duration: 0.010226 seconds
Queries such as this:
INSERT IGNORE INTO LegArg ( LegHdl, ID, NumVal, StrVal )
|
SELECT LegHdl,10005,19388,'gDRRVDkky9PfbYTAkmVzDNWPZyZM1S63qxivoXaMBK53FfcsxjKMl2yFBX6iyYjs' |
FROM Leg
|
WHERE LHash=x'00013A942AE53E7279946CBC428FF7B815867A39' |
;
|
...when executed several thousand times, as in normal ingest jobs, result in an overall execution time 3,750% slower on average in MariaDB 10.6 than 10.5. Here's some timing results:
10.5.27: |
839KiB 0:00:00 [1.90MiB/s] |
real 0m0.586s
|
|
|
839KiB 0:00:00 [1.77MiB/s] |
real 0m0.617s
|
|
|
839KiB 0:00:00 [1.94MiB/s] |
real 0m0.566s
|
|
|
839KiB 0:00:00 [2.02MiB/s] |
real 0m0.531s
|
|
|
10.6.20: |
870KiB 0:00:19 [45.7KiB/s] |
real 0m20.167s
|
|
|
870KiB 0:00:19 [45.6KiB/s] |
real 0m20.215s
|
|
|
870KiB 0:00:17 [48.4KiB/s] |
real 0m19.096s
|
|
|
870KiB 0:00:19 [45.6KiB/s] |
real 0m20.262s
|
By contrast, separating the operation out so that SELECTS populate a temporary table and then the INSERT is done directly from the temporary table with no selective predicate results in comparable performance in 10.6.20:
create temporary table tLegArg like LegArg;
|
INSERT IGNORE INTO tLegArg ( LegHdl, ID, NumVal, StrVal ) SELECT `LegHdl`,10003,51944,NULL FROM `Leg` WHERE `LHash`=x'e5d701b83032f1dd2551e230adee7f0b5721d86b'; |
INSERT IGNORE INTO LegArg select * from tLegArg;
|
|
|
839KiB 0:00:00 [2.02MiB/s] |
real 0m0.557s
|
|
|
839KiB 0:00:00 [1.98MiB/s] |
real 0m0.577s
|
|
|
839KiB 0:00:00 [1.92MiB/s] |
real 0m0.577s
|
|
|
839KiB 0:00:00 [2.02MiB/s] |
real 0m0.556s
|
The problem appears in all minor releases of 10.6, and persists in 10.11.10. However, 11.4.4 is not affected by the regression.
To reproduce see internal comments.
Attachments
Issue Links
- is caused by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
monty, my fix is basically an improved fix of the recovery failure that prompted the filing of MDEV-25910, which consists of removing one call from a code path that may be executed outside ALTER TABLE…ALGORITHM=COPY and adding an equivalent invocation to the ALTER TABLE…ALGORITHM=COPY code path. I think that these changes logically belong together.
serg pointed out an alternative fix that avoids adding a call to ha_flush_logs(). It will introduce an unnecessary durable InnoDB log write during the execution of CREATE…SELECT, but I think that we can live with that.
I tried to create a regression test case for this bug. Hopefully it is stable enough. Log writes can be triggered by various reasons in other threads than those that execute SQL statements.
I revised the regression test to be compatible with ./mtr --cursor-protocol and revised it further to reduce the chances of an unexpected log write that could make the test intermittently fail.
BTW, I discovered another oddity: the server status variables COM_{begin, commit, insert, select} and Handler_commit do not increase. The workload however contains 4000 INSERT ... SELECT statements and 8 BEGIN and COMMIT pairs.
I think this also has to do with the "shortcut" evaluation of INSERT ... SELECT when both tables are InnoDB.
This behavior is however the same for 10.5 and 10.6.