Details
-
Bug
-
Status: In Review (View Workflow)
-
Blocker
-
Resolution: Unresolved
-
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
This patch has two bug fixes. Both looks good and ok to push. However, I would suggest that we separate it into two commits to make future bisects easier.
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.
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.
https://github.com/MariaDB/server/pull/3888