[MDEV-31964] Unexpected performance issue, Join_buffer_space_limit becomes smaller, but the query speed is faster Created: 2023-08-20  Updated: 2023-09-04

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 11.0.3
Fix Version/s: 11.0

Type: Bug Priority: Major
Reporter: fuying Assignee: Oleg Smirnov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

docker mariadb:11.0


Attachments: File init_tables.sql     PNG File join_buffer_space_limit = 2048.png     PNG File join_buffer_space_limit = default.png     File select_query.sql    

 Description   

When Join_buffer_space_limit is set to the minimum value, the query speed is increased dozens of times, which is unreasonable.

Steps to reproduce:
1. Use the mariadb:11.0 container image to create a container, and create a database for the test
2. Use the SQL statement in init_tables.sql to build a table
3. Use select_query.sql to query, the query time on my machine is about 9s

But when performing the same steps 1 and 2, and before performing step 3, SET Session join_buffer_space_limit = 2048;
The query time will drop to about 0.8s

Theoretically, `join_buffer_space_limit` is merely an upper limit, and a larger value should result in faster query speeds.


Generated at Thu Feb 08 10:27:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.