Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26520

Make innodb_purge_threads settable without server restart

Details

    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.

      Attachments

        Issue Links

          is duplicated by

          Task - A task that needs to be done. MDEV-11024 Make number of purge threads variable dynamic

          • Major - Major loss of function.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-16260 Scale the purge effort according to the workload

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26356 Performance regression after dict_sys.mutex removal

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            mleich Matthias Leich added a comment - - edited 

            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

            mleich Matthias Leich added a comment - - edited 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
            wlad Vladislav Vaintroub added a comment - - edited

            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

            wlad Vladislav Vaintroub added a comment - - edited 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
            mleich Matthias Leich added a comment - 

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

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

            People

              wlad Vladislav Vaintroub
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.