[MDEV-30355] set names effects performance significantly when compiling function Created: 2023-01-06  Updated: 2023-03-09  Resolved: 2023-03-09

Status: Closed
Project: MariaDB Server
Component/s: Character Sets, Prepared Statements
Affects Version/s: 10.8.6
Fix Version/s: 10.8.7

Type: Bug Priority: Major
Reporter: Anton Avramov Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Attachments: File set_names_test.sql    
Issue Links:
Relates
relates to MDEV-29988 Major performance regression with 10.... Closed

 Description   

Having a different character set when compiling a function can impact the performance of the function significantly.
The function itself has the proper character sets for input parameters and the result.

Have a look at the attached function. In the following sequence you would observe that the execution is almost 10 times slower for just 10000 invocation and the only difference is the character set set when compiling the function. 

root@lukav::/vagrant# mysql test --init-command='set names utf8mb4' < ./set_names_test.sql 
root@lukav::/vagrant# time mysql test --init-command='set names utf8mb4' -e "SELECT set_names_test(CONCAT('a', seq)) from seq_1_to_10000" > /dev/null 
 
 
real	0m0,190s
 
user	0m0,007s
 
sys	0m0,004s
 
 
 
 
 
root@lukav::/vagrant# mysql test --init-command='set names utf8' < ./set_names_test.sql 
root@lukav::/vagrant# time mysql test --init-command='set names utf8mb4' -e "SELECT set_names_test(CONCAT('a', seq)) from seq_1_to_10000" > /dev/null 
 
 
real	0m8,008s
 
user	0m0,000s
 
sys	0m0,010s

If this is expected behaviour it should be well documented, but I couldn't find anything on the subject.

 Comments   
Comment by Sergei Golubchik [ 2023-01-07 ]

Please, try again when 10.8.7 is out. May be it's MDEV-29988

Comment by Sergei Golubchik [ 2023-03-06 ]

10.8.7 is out. Did it help?

Comment by Anton Avramov [ 2023-03-09 ]

It seams so.

I wasn't able to reproduce the effect with the latest version, so I guess it is fixed.

Thank you very much

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