@marko, please review 79280cf5dd22082373595aa0dc6178e2483727ca

wlad, one option wold be to use small hard-coded limits if --gdb was specified. Or add debug-build-only options. Both approaches would avoid adding yet another element to the HUGE list of options that users can possibly tune.

That's right, we have huge list of stuff users can tune.
But is this a problem to add variables that have appropriate defaults, so that users to do have to tune them?
Perhaps, we can hide it from users even, by using the tricky hidden option (don't recall the name, but which I've heard about recently in conjunction with encryption plugins)?

I'd say yes. One more option is one more something a user need to know about, to learn, and to remember. If something can be configured automatically, it should be configured automatically.

There is no tricky hidden option that I know of.

PLUGIN_VAR_NOSYSVAR , was not that invisible as system variable? This was the tricky I meant

Ah, yes. But it's still visible as a command-line option.

Alright, I'd like to get the opinions by monty, since it was him, who requested the feature in the first place, as system variable.
and maybe marko can contribute his view to discussion.

Thanks, it looks OK to me; I sent a couple cosmetic comments.

I agree with serg, we already have way too many parameters, and we do not have a good policy of deprecating and removing parameters in the future, as subsystems are refactored and old parameters become obsolete.

If the main motivation is to reduce the number of threads when debugging, maybe instead of introducing these parameters, we could tie it to opt_debugging, so that when the server is invoked as mariadbd --gdb, fewer threads will be created initially.

@marko, initially, 0 threads are created, apart from the Linux AIO one. The threads are created when there are tasks, and there are no threads to serve them, and the current concurrency is less that desired concurrency, and desired concurrency depends on number of cores. And then also, there is a throttling for the thread creation, although throttling is activated based on current number of threads,and again, that logic depends on the number of cores. The more cores you have, the more threads are created without throttling

wlad, can you please set a breakpoint to the code where the thread pool creates a new thread, extract stack traces for each invocation, and then tabulate and summarize the reasons for creating new threads at server startup (for a trivial test that only creates and drops an InnoDB table)? Maybe we could fight the thread creation at the source. (That might end up being a separate bug or task, possibly not doable in a stable release branch.)

@Marko, I do not know that much about gdb automation? If you can, set such a breakpoint in std::thread::detach(), and run your test cases.

Tickers/timer tasks are known to create (possibly excessive) threads, also because they seem to run at about the same time.

For a minimal test, before ha_innobase::create() was invoked, there were 5 invocations of std::thread::detach(), 4 from process_timers() in timer_handler() and 1 from trx_purge_wait_for_workers_to_complete() in purge_coordinator_callback().

The timer_handler() invocations were there even if I started the server with innodb_purge_threads=1.

How can I view the initiator of the timer_handler() calls?

A little later into the test, we got a large number of std::thread::detach() invocations from tpool::aio_linux::getevent_thread_routine(). wlad, could that be limited somehow?

srv_thread_pool->set_timer is the initiator of the timer_handler calls.
there are quite a few different timers in innodb.

a large number of threads from tpool::aio_linux::getevent_thread_routine probably means that IO callbacks is something I can look at. there is a task_group specific queue, and maybe we could populate it, if all IO write threads are busy (we have limited number of parallel executed tasks in group), instead of pushing task into common queue, and waking threads. I can try to provide a patch

wlad, I believe that you were referring to create_timer. The following calls to it look unavoidable:

• srv0srv.h (srv_start_periodic_timer(): invoked by srv_start() for srv_error_monitor_task, srv_monitor_task, srv_master_callback
• srv0srv.cc (srv_init_purge_tasks(): creating purge_coordinator_timer_callback if innodb_force_recovery<2
• srv0start.cc (srv_start() creating lock_wait_timeout_task if skip_innodb_read_only

However, the following could be seen as omissions of MDEV-16264 that could be fixed in 10.5:

• btr0defragment.cc (btr_defragment_init(). Please, let us move the creation to an update callback function of SET GLOBAL innodb_defragment=ON.
• dict0stats_bg.cc (dict_stats_start(). We could defer this until SET GLOBAL innodb_stats_persistent=ON.
• fts0opt.cc (fts_optimize_init(). We could defer this until the first table containing FULLTEXT INDEX is successfully opened, in dict_load_table_one().

marko, nope, I'm referring to set_time(). Creating timer does almost nothing. set_time() does something

Following periodic timers are permanent, and are started when the server starts, if server is not readonly.
srv/srv0start.cc: srv_start_periodic_timer(srv_error_monitor_timer, srv_error_monitor_task, 1000);
srv/srv0start.cc: srv_start_periodic_timer(srv_monitor_timer, srv_monitor_task, 5000);
srv/srv0start.cc: srv_start_periodic_timer(srv_master_timer, srv_master_callback, 1000);

If those do not deadlock each other, and are not time-critical, you can merge them, I.e have a single timer, which runs every second, executes srv_master_callback, srv_error_monitor_task, and on every 5th run, executes srv_monitor_task.

Others are started when needed. I think.

It seems to me that the following calls could be deferred, like I suggested in my previous comment:

• btr0defragment.cc: btr_defragment_timer->set_time(sleep_ms, 0); (only if innodb_defragment=ON, or maybe we could remove this altogether)
• dict0stats_bg.cc: dict_stats_timer->set_time(ms,0); (probably only needed if innodb_stats_auto_recalc=ON and innodb_stats_persistent=ON)
• fts0opt.cc: timer->set_time(5000, 0); (we could defer the call to fts_optimize_init() to dict_load_table_one(), like I suggested previously)

all these timers are is not periodic. If they were periodic, the second parameter to set_time would be != 0. The first parameter to set_time is initial delay, the second is period.

So, this is a use of timer, that removes "sleeps"

From the comment to the btr_defragment_chunk(void*)

Throttling "sleep", is implemented via rescheduling the threadpool timer, which, when fired, will resume the work again, where it is left.

I believe it is the same thing for other cases as well. I think, is better not to use a thread for sleep, but reschedule work for later. This way, less threads can be used.

It did not find broad support, apparently @serg was not excited. I'll close it for now