Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
I run sysbench on MariaDB on Windows with Visual Studio sampling profiler , comparing 5.2 and 5.3 performance.
sysbench version is 0.5 and for the test I created select1.lua, which does nothing else but running prepared "SELECT 1".
Performance reports show a big difference inside JOIN constructor, and for this specific test JOIN::JOIN is the most expensive function with 24.23% exclusive samples.
Here is he list of functions that show up as more expensive in 5.3 compared to 5.2
Comparison Column Delta Baseline Value Comparison Value
JOIN::JOIN 23,15 1,08 24,23
RtlpAllocateHeap 3,24 0,00 3,24
RtlpInsertFreeBlock 1,93 0,00 1,93
RtlpFreeHeap 1,42 0,00 1,42
Looking at what has changed from 5.2 to 5.3, JOIN structure grew from around 5K to around 32K. Besides, it contains 2 large arrays of POSITION structures, and POSITION has a non-trivial constructor, since it includes semi-join strategy classes for which virtual function tables must be setup (confirmed by switching to disassembly and single-stepping through JOIN::JOIN)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: JOIN constructors takes a long time in 5.3
Attached in a simple patch that de-virtualizes semijoin strategy picker classes.
It fixes the problem (tested on Windows)
- SELECT 1 test (10 users)
5.3, without fix
read/write requests: 11739503 (39131.57 per sec.)
read/write requests: 11787997 (39293.23 per sec.)
read/write requests: 11776222 (39253.97 per sec.)
5.3, with fix
read/write requests: 12887151 (42957.08 per sec.)
read/write requests: 12876115 (42920.29 per sec.)
read/write requests: 12925002 (43083.25 per sec.)
5.2
read/write requests: 13186290 (43954.19 per sec.)
read/write requests: 13099010 (43663.26 per sec.)
read/write requests: 13115124 (43716.98 per sec.)
Summary on SELECT 1:
- The fix speeds up 5.3 on this test by 9.7%
- 5.3 is stil a bit slower than 5.2 (approx 1%)
Another test : OLTP_RO (64 users, sysbench 0.4)
5.3:
read/write requests: 4869802 (27051.41 per sec.)
read/write requests: 4795000 (26635.60 per sec.)
read/write requests: 4948034 (27485.88 per sec.)
5.3-fix
read/write requests: 5354006 (29741.23 per sec.)
read/write requests: 5327574 (29593.89 per sec.)
read/write requests: 5477906 (30429.07 per sec.)
Summary on OLTP-RO:
Fix makes 5.3 ~ 10.6% faster
Fix to previously posted patch, makes compile with gcc's -fno-implicit-templates
LPexportBug941889_941889.diff
Re: JOIN constructors takes a long time in 5.3
Re: JOIN constructors takes a long time in 5.3
Now we allocate just as much memory that is needed. This shows a notable speedup compared even to MariaDB 5.2!
Launchpad bug id: 941889
Re: JOIN constructors takes a long time in 5.3
Profiling with another test (point_select) shows the same slowdown in JOIN::JOIN. Second expensive call as compared to
5.2 is JON::choose_subquery_plan.
Comparison Delta Baseline Value Comparison Value
JOIN::JOIN 7,83 0,42 8,25
JOIN::choose_subquery_plan 3,47 0,00 3,47
ut_delay 1,55 0,00 1,55
memcpy 1,35 0,00 1,35
RtlpAllocateHeap 1,28 0,00 1,28
For JOIN::choose_subquery_plan function, profiler points at the line at the very start of the function
Join_plan_state save_qep; /* The original QEP of the subquery. */
I suspect that the bottleneck here could be again initialization of the POSITION structures in the best_positions array in
Join_plan_state