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
-