Details
-
Bug
-
Status: Stalled (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2(EOL)
-
None
Description
|
10.2 f2fe65106f, debug build, default configuration |
MariaDB [test]> show processlist;
|
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ |
| Id | User | Host | db | Command | Time | State | Info | Progress | |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ |
| 1 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | |
| 2 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | |
| 4 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | |
| 3 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | |
| 5 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | |
| 10 | root | localhost:39009 | test | Query | 0 | init | show processlist | 0.000 |
|
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ |
6 rows in set (0.00 sec) |
I don't think they should be there
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is caused by
-
MDEV-5800 indexes on virtual (not materialized) columns
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
{code:sql|title=10.2 f2fe65106f, debug build, default configuration}
MariaDB [test]> show processlist; +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 10 | root | localhost:39009 | test | Query | 0 | init | show processlist | 0.000 | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ 6 rows in set (0.00 sec) I don't think they should be there |
{code:sql|title=10.2 f2fe65106f, debug build, default configuration}
MariaDB [test]> show processlist; +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 10 | root | localhost:39009 | test | Query | 0 | init | show processlist | 0.000 | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ 6 rows in set (0.00 sec) {code} I don't think they should be there |
| Assignee | Marko Mäkelä [ marko ] | Sergei Golubchik [ serg ] |
| Labels | 10.2-ga |
InnoDB background threads are no longer invisible for the server, they have THDs, and for many practical purposes they look like regular connection threads. That's why they're now shown.
marko, thanks, will do.
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2.5 [ 22117 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue relates to |
That's a bad user experience. THD has no meaning for users. It is internal information, for developers. There are 40 something threads after startup, if every thread gets a THD, do we want them all to be shown in all GUI tools?
In "show processlist", people look for connections, that have meanings, to people. People get confused if they find something else.
A background THD can be put into any list, which it does not have to be the main "threads" list, that is presented in processlist
A different list could be shown in a different I_S table. I see no reason to clutter the UI, because purge thread (a timer thread, Aria flusher, obscure handle manager, shutdown thread, idle threadpool thread, Innodb io thread, as well as different Innodb coordinator threads, if they all get a THD) can some day have some status.
I also think that if KILL CONNECTION or KILL QUERY cannot be applied to "THD", it should not use a thread_id, that is , first connection should get a connection id 1, and not 8
This is arguable.
If a thread owns a THD, it will open tables, place locks, etc. So the thread id will be visible, say, in the list of metadata locks (metadata_lock_info plugin) and in P_S.
Not counting these threads might be quite difficult, but I can easily hide them in SHOW PROCESSLIST. And say "no such thread" in KILL. Still there must be thread id's assigned, and they'll be visible in some commands.
Or... Make thread_id signed, internally. And assign negative id's to internal threads. Then connection id's will be sequential and internal threads won't be killable. Would it be an improvement? It cannot be done in a bug fix, though.
I think hiding background thread THDs from PROCESSLIST and KILL is good enough. If this is achieved with negative thread_id, this is fine with me.
MDEV-16264 should replace background threads with a configureable or self-adjusting number of worker threads that consume items from a background work queue.
| Link |
This issue relates to |
| Resolution | Fixed [ 1 ] | |
| Status | Closed [ 6 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.2.5 [ 22117 ] |
wlad, negative id would be a separate feature, a easy way to distinguish visually internal from user threads. It cannot be done as a bug fix and is not part of this issue.
Skipping internal threads in PROCESSLIST and KILL can be done based on something like thd->is_internal_thread() (or whatever), I'll do that.
serg, fine with me, too.
marko, does not matter how many threads there are. Even if there is one purge, and one "shutdown proxy", it is still bad enough as user experience.
There is a kind-of-a middle ground between "client connections" and "system threads" – replication-related threads: Binlog Dump on master and all those Slave_IO, Slave_SQL and Slave_worker threads on the slave. Unlike other system threads, they actually show states which make sense to the user and might be important to them; AFAIR they can also be meaningfully killed under some circumstances. I am not really sure how it can and should be treated, I think if we lose them for the sake of cosmetical improvements, users might be unhappy about it.
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Labels | 10.2-ga |
The complete list of system threads:
- replication slave io and sql
- event scheduler and workers
- delayed insert
- binlog background thread
- InnoDB shutdown handler, purge coordinator and workers
Which of those should be hidden from SHOW PROCESSLIST? Only the last item? It's kind of inconsistent.
One point of listing every THD in the process list is, that a THD can hold locks, so it can participate in a deadlock or can block, say, ALTER TABLE or FLUSH TABLES WITH READ LOCK, so there might be a need to kill it.
| Link | This issue relates to MDEV-18698 [ MDEV-18698 ] |
| Link | This issue relates to MDEV-17238 [ MDEV-17238 ] |
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Link |
This issue duplicates |
| Workflow | MariaDB v3 [ 79045 ] | MariaDB v4 [ 143499 ] |
I think that all threads should be reported. In the future, InnoDB purge threads could wait for metadata locks, which would create visible conflicts with SQL execution.
That said, the thread labels are misleading. One of the threads is actually a shutdown helper thread that serg added in
MDEV-5800.I would propose the following patch to add some relevant detail to the output. Possibly SHOW PROCESSLIST should be extended with an option to display/suppress background threads. And possibly it should always omit the thd_destructor_proxy() thread.
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.ccindex 9a278143945..86ab705a3ba 100644--- a/storage/innobase/handler/ha_innodb.cc+++ b/storage/innobase/handler/ha_innodb.cc@@ -329,7 +329,7 @@ thd_destructor_proxy(void *)thd_destructor_myvar = _my_thread_var();THD *thd= create_thd();- thd_proc_info(thd, "InnoDB background thread");+ thd_proc_info(thd, "InnoDB shutdown");mysql_mutex_lock(&thd_destructor_mutex);thd_destructor_myvar->current_mutex = &thd_destructor_mutex;@@ -1788,14 +1788,14 @@ static MYSQL_THDVAR_BOOL(background_thread,"Internal (not user visible) flag to mark ""background purge threads", NULL, NULL, 0);-/** Create a MYSQL_THD for background purge threads and mark it as such.-@returns new MYSQL_THD */+/** Create a MYSQL_THD for a background thread and mark it as such.+@param name thread info for SHOW PROCESSLIST+@return new MYSQL_THD */MYSQL_THD-innobase_create_background_thd()-/*============================*/+innobase_create_background_thd(const char* name){MYSQL_THD thd= create_thd();- thd_proc_info(thd, "InnoDB background thread");+ thd_proc_info(thd, name);THDVAR(thd, background_thread) = true;return thd;}diff --git a/storage/innobase/include/ha_prototypes.h b/storage/innobase/include/ha_prototypes.hindex c10644be832..4231088a7a7 100644--- a/storage/innobase/include/ha_prototypes.h+++ b/storage/innobase/include/ha_prototypes.h@@ -653,10 +653,11 @@ buffer pool size.voidinnodb_set_buf_pool_size(ulonglong buf_pool_size);-/** Create a MYSQL_THD for background purge threads and mark it as such.-@returns new MYSQL_THD */+/** Create a MYSQL_THD for a background thread and mark it as such.+@param name thread info for SHOW PROCESSLIST+@return new MYSQL_THD */MYSQL_THD-innobase_create_background_thd();+innobase_create_background_thd(const char* name);/** Destroy a background purge thread THD.@param[in] thd MYSQL_THD to destroy */diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.ccindex 0eda03f7fd7..c07c68dc649 100644--- a/storage/innobase/srv/srv0srv.cc+++ b/storage/innobase/srv/srv0srv.cc@@ -2742,7 +2742,7 @@ DECLARE_THREAD(srv_worker_thread)(ut_ad(!srv_read_only_mode);ut_a(srv_force_recovery < SRV_FORCE_NO_BACKGROUND);my_thread_init();- THD* thd = innobase_create_background_thd();+ THD* thd = innobase_create_background_thd("purge worker");#ifdef UNIV_DEBUG_THREAD_CREATIONib::info() << "Worker thread starting, id "@@ -3007,7 +3007,7 @@ DECLARE_THREAD(srv_purge_coordinator_thread)(required by os_thread_create */{my_thread_init();- THD* thd = innobase_create_background_thd();+ THD* thd = innobase_create_background_thd("purge");srv_slot_t* slot;ulint n_total_purged = ULINT_UNDEFINED;