[MDEV-26520] Make innodb_purge_threads settable without server restart Created: 2021-09-01  Updated: 2023-11-27  Resolved: 2021-09-23

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 10.7.1

Type: Task Priority: Major
Reporter: Marko Mäkelä Assignee: Vladislav Vaintroub
Resolution: Fixed Votes: 0
Labels: None

Attachments: File MDEV-26520.test     Text File output.txt    
Issue Links:
Duplicate
is duplicated by MDEV-11024 Make number of purge threads variable... Closed
Relates
relates to MDEV-16260 Scale the purge effort according to t... Open
relates to MDEV-26356 Performance regression after dict_sys... Closed

 Description   

To enable better control of the InnoDB purge subsystem, it would be good if the maximum number of purge tasks were settable by the user while the server is running:

SET GLOBAL innodb_purge_threads=1;
 
# run a heavy workload
 
# let the history be discarded faster while the server is less busy
 
SET GLOBAL innodb_purge_threads=32;

Independently of this, MDEV-16260 or MDEV-26356 may improve the way how the purge threads are scheduled. This task would only make the maximum number of concurrent purge tasks configurable.

 Comments   
Comment by Matthias Leich [ 2021-09-14 ] 

origin/bb-10.7-MDEV-26520 2a6739a650489ad34f5abfea938cd0442fa665cd 2021-09-07T22:10:41+02:00
 
 
 
A RQG test invoking rr harvests
 
# 2021-09-14T11:37:12 [1318743] | [rr 1321150 22022]safe_mutex: Trying to lock mutex at /data/Server/bb-10.7-MDEV-26520/sql/sql_class.cc, line 1245, when the mutex was already locked at /data/Server/bb-10.7-MDEV-26520/sql/sys_vars_shared.h, line 56 in thread T@18
 
# 2021-09-14T11:37:12 [1318743] | [rr 1321150 22041]210914 11:35:07 [rr 1321150 22044][ERROR] mysqld got signal 6 ;
 
 
 
# 2021-09-14T11:37:12 [1318743] | Thread 3 (Thread 1321150.1321940):
 
# 2021-09-14T11:37:12 [1318743] | #0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
# 2021-09-14T11:37:12 [1318743] | #1  0x00007fb31d4fd859 in __GI_abort () at abort.c:79
 
# 2021-09-14T11:37:12 [1318743] | #2  0x0000556d791546fa in safe_mutex_lock (mp=0x556d7b38f900 <LOCK_global_system_variables>, my_flags=0, file=0x556d7962c2a0 "/data/Server/bb-10.7-MDEV-26520/sql/sql_class.cc", line=1245) at /data/Server/bb-10.7-MDEV-26520/mysys/thr_mutex.c:262
 
# 2021-09-14T11:37:12 [1318743] | #3  0x0000556d77642142 in inline_mysql_mutex_lock (that=0x556d7b38f900 <LOCK_global_system_variables>, src_file=0x556d7962c2a0 "/data/Server/bb-10.7-MDEV-26520/sql/sql_class.cc", src_line=1245) at /data/Server/bb-10.7-MDEV-26520/include/mysql/psi/mysql_thread.h:750
 
# 2021-09-14T11:37:12 [1318743] | #4  0x0000556d7764badd in THD::init (this=0x62b0000b6218) at /data/Server/bb-10.7-MDEV-26520/sql/sql_class.cc:1245
 
# 2021-09-14T11:37:12 [1318743] | #5  0x0000556d77649018 in THD::THD (this=0x62b0000b6218, id=0, is_wsrep_applier=false) at /data/Server/bb-10.7-MDEV-26520/sql/sql_class.cc:849
 
# 2021-09-14T11:37:12 [1318743] | #6  0x0000556d7766636e in create_background_thd () at /data/Server/bb-10.7-MDEV-26520/sql/sql_class.cc:4915
 
# 2021-09-14T11:37:12 [1318743] | #7  0x0000556d78793791 in innobase_create_background_thd (name=0x556d79fed300 "InnoDB purge worker") at /data/Server/bb-10.7-MDEV-26520/storage/innobase/handler/ha_innodb.cc:1794
 
# 2021-09-14T11:37:12 [1318743] | #8  0x0000556d78bfc488 in purge_create_background_thds (n=8) at /data/Server/bb-10.7-MDEV-26520/storage/innobase/srv/srv0srv.cc:1810
 
# 2021-09-14T11:37:12 [1318743] | #9  0x0000556d78bfbd9d in srv_update_purge_thread_count (n=8) at /data/Server/bb-10.7-MDEV-26520/storage/innobase/srv/srv0srv.cc:1707
 
# 2021-09-14T11:37:12 [1318743] | #10 0x0000556d787e77bb in innodb_purge_threads_update (save=0x6290001b8418) at /data/Server/bb-10.7-MDEV-26520/storage/innobase/handler/ha_innodb.cc:18750
 
# 2021-09-14T11:37:12 [1318743] | #11 0x0000556d777ea942 in sys_var_pluginvar::global_update (this=0x625000009698, thd=0x62b0000af218, var=0x6290001b83f8) at /data/Server/bb-10.7-MDEV-26520/sql/sql_plugin.cc:3622
 
# 2021-09-14T11:37:12 [1318743] | #12 0x0000556d774fdad8 in sys_var::update (this=0x625000009698, thd=0x62b0000af218, var=0x6290001b83f8) at /data/Server/bb-10.7-MDEV-26520/sql/set_var.cc:207
 
# 2021-09-14T11:37:12 [1318743] | #13 0x0000556d77502494 in set_var::update (this=0x6290001b83f8, thd=0x62b0000af218) at /data/Server/bb-10.7-MDEV-26520/sql/set_var.cc:863
 
# 2021-09-14T11:37:12 [1318743] | #14 0x0000556d77501900 in sql_set_variables (thd=0x62b0000af218, var_list=0x62b0000b4288, free=true) at /data/Server/bb-10.7-MDEV-26520/sql/set_var.cc:745
 
# 2021-09-14T11:37:12 [1318743] | #15 0x0000556d7777f94e in mysql_execute_command (thd=0x62b0000af218, is_called_from_prepared_stmt=false) at /data/Server/bb-10.7-MDEV-26520/sql/sql_parse.cc:5036
 
# 2021-09-14T11:37:12 [1318743] | #16 0x0000556d777928de in mysql_parse (thd=0x62b0000af218, rawbuf=0x6290001b8238 "SET GLOBAL innodb_purge_threads = 8  /* E_R Thread1 QNO 12 CON_ID 15 */", length=71, parser_state=0x7fb2eec68b20) at /data/Server/bb-10.7-MDEV-26520/sql/sql_parse.cc:8036
 
# 2021-09-14T11:37:12 [1318743] | #17 0x0000556d7776aac5 in dispatch_command (command=COM_QUERY, thd=0x62b0000af218, packet=0x629000d66219 " SET GLOBAL innodb_purge_threads = 8  /* E_R Thread1 QNO 12 CON_ID 15 */ ", packet_length=73, blocking=true) at /data/Server/bb-10.7-MDEV-26520/sql/sql_parse.cc:1896
 
# 2021-09-14T11:37:12 [1318743] | #18 0x0000556d77767e9d in do_command (thd=0x62b0000af218, blocking=true) at /data/Server/bb-10.7-MDEV-26520/sql/sql_parse.cc:1404
 
# 2021-09-14T11:37:12 [1318743] | #19 0x0000556d77b92d4d in do_handle_one_connection (connect=0x608000002db8, put_in_cache=true) at /data/Server/bb-10.7-MDEV-26520/sql/sql_connect.cc:1418
 
# 2021-09-14T11:37:12 [1318743] | #20 0x0000556d77b925de in handle_one_connection (arg=0x608000002ab8) at /data/Server/bb-10.7-MDEV-26520/sql/sql_connect.cc:1312
 
# 2021-09-14T11:37:12 [1318743] | #21 0x00007fb31da24609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
# 2021-09-14T11:37:12 [1318743] | #22 0x00007fb31d5fa293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
 
 
 
I would like to add something of the test code and output.
 
But the grandious JIRA does not seem to have a mode where I can easy add raw code
 
containing an "at sign".
 
So please look into the uploaded
 
- test MDEV-26520.test and run it
 
- output.txt  == What I get from my MTR test with backtrace ...
 
 
 
Already
 
SET GLOBAL innodb_purge_threads = <value >= 5> leads to crash

Comment by Vladislav Vaintroub [ 2021-09-14 ]

I decided to preallocate all possibly needed THDs at startup, since other variations did not work out well.
I added a test for "set global innodb_purge_threads={0,1 -1,32,33}" , too

Comment by Matthias Leich [ 2021-09-22 ] 

The corresponding development tree
 
origin/bb-10.7-MDEV-26520 6e8ef61814d659b9e3c791570b1912f453138bb7 2021-09-14T19:01:26+02:00
 
behaved well in RQG testing.

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