[MDEV-23124] Eliminate the overhead of repeated my_access() on every new connection/or USE Created: 2020-07-08  Updated: 2020-07-14  Resolved: 2020-07-14

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: None
Fix Version/s: 10.6.0

Type: Bug Priority: Major
Reporter: Vladislav Vaintroub Assignee: Vladislav Vaintroub
Resolution: Fixed Votes: 0
Labels: performance


 Description   

This comes from analyzing benchmark which does "connect/disconnect", mostly

The output from VTune shows a lot of time spent in GetFileAttributes(), which is Windows'
my_access()

Top Hotspots
 
    Function	Module	CPU Time
 
    NtWaitForWorkViaWorkerFactory	ntdll.dll	56.896s
 
    GetFileAttributesA	KERNELBASE.dll	13.874s <--- This one
 
    WaitForSingleObjectEx	KERNELBASE.dll	10.879s
 
    CreateNamedPipeA	KERNEL32.DLL	8.437s
 
    [Unknown stack frame(s)]	[Unknown]	4.612s
 
    [Others]	N/A*	19.807s

Here is the callstack leading to it

CPU Time
 
1 of 1: 100.0% (13.874s of 13.874s)
 
 
 
KERNELBASE.dll ! GetFileAttributesA - [unknown source file]
 
server.dll ! my_access + 0xd - my_access.c:39
 
server.dll ! check_db_dir_existence + 0x67 - sql_db.cc:1935
 
server.dll ! mysql_change_db + 0x226 - sql_db.cc:1610
 
server.dll ! acl_authenticate + 0x853 - sql_acl.cc:14380
 
server.dll ! check_connection + 0x206 - sql_connect.cc:1081
 
server.dll ! login_connection + 0x40 - sql_connect.cc:1142
 
server.dll ! thd_prepare_connection + 0x15 - sql_connect.cc:1327
 
server.dll ! threadpool_add_connection + 0x180 - threadpool_common.cc:261
 
server.dll ! tp_callback + 0x42 - threadpool_common.cc:186
 
ntdll.dll ! TppSimplepExecuteCallback + 0x98 - [unknown source file]
 
ntdll.dll ! TppWorkerThread + 0x689 - [unknown source file]
 
KERNEL32.DLL ! BaseThreadInitThunk + 0x13 - [unknown source file]
 
ntdll.dll ! RtlUserThreadStart + 0x20 - [unknown source file]

Given that NtWaitForWorkViaWorkerFactory is not anything we can influence much (that's a threadpool management), this makes my_access() the top-1 hotspot

However we do not need to instantly recheck if directory is still there. If it was there, and there was no "drop database", it remains in place. We can assume, that removing database directory manually outside of the server does not happen (or those who do punish themselves), we can cache the results of this operation, speeding up the connect, and every "USE db"

 Comments   
Comment by Vladislav Vaintroub [ 2020-07-14 ]

Sergej Vojtovich reported my_access overhead of 2-3% on Linux in connect/disconnect test. Not as big as on Windows, but still quite measurable.

Generated at Thu Feb 08 09:20:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.