Hello Andrei,

Please review the fix for MDEV-20220.

https://github.com/MariaDB/server/commit/92fbb4e911d700576d9b894637a476b18324f1ed

Added a new status variable 'Slave_idle_parallel_worker_count'.
The counter variable gets incremented when a slave worker is in
idle state. On START SLAVE all workers are initially in idle state.

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> show status like '%idle%';

+----------------------------------+-------+

| Variable_name                    | Value |

+----------------------------------+-------+

| Innodb_master_thread_idle_loops  | 61    |

| Slave_idle_parallel_worker_count | 4     |

| Threadpool_idle_threads          | 0     |

+----------------------------------+-------+

3 rows in set (0.005 sec)

MariaDB [(none)]> show processlist;

+----+-------------+-----------+------+--------------+------+--------------------------------------------------------+------------------+----------+

| Id | User        | Host      | db   | Command      | Time | State                                                  | Info             | Progress |

+----+-------------+-----------+------+--------------+------+--------------------------------------------------------+------------------+----------+

|  4 | root        | localhost | NULL | Query        |    0 | starting                                               | show processlist |    0.000 |

|  5 | system user |           | NULL | Slave_IO     |   13 | Waiting for master to send event                       | NULL             |    0.000 |

|  8 | system user |           | NULL | Slave_worker |   13 | Waiting for work from SQL thread                       | NULL             |    0.000 |

| 10 | system user |           | NULL | Slave_worker |   13 | Waiting for work from SQL thread                       | NULL             |    0.000 |

|  7 | system user |           | NULL | Slave_worker |   13 | Waiting for work from SQL thread                       | NULL             |    0.000 |

|  9 | system user |           | NULL | Slave_worker |   13 | Waiting for work from SQL thread                       | NULL             |    0.000 |

|  6 | system user |           | NULL | Slave_SQL    |   13 | Slave has read all relay log; waiting for more updates | NULL             |    0.000 |

+----+-------------+-----------+------+--------------+------+--------------------------------------------------------+------------------+----------+

On master:

MariaDB [test]> create table t ( f int) engine=innodb;

Query OK, 0 rows affected (0.064 sec)

On slave: One of the workers will execute the above DDL and
on completion will get back to idle state. Hence the count gets
incremented.

MariaDB [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t              |

+----------------+

1 row in set (0.002 sec)

MariaDB [test]> show status like '%idle%';

+----------------------------------+-------+

| Variable_name                    | Value |

+----------------------------------+-------+

| Innodb_master_thread_idle_loops  | 122   |

| Slave_idle_parallel_worker_count | 5     |

| Threadpool_idle_threads          | 0     |

+----------------------------------+-------+

3 rows in set (0.004 sec)

MariaDB [test]> stop slave;

Query OK, 0 rows affected (0.003 sec)

MariaDB [test]> show status like '%idle%';

+----------------------------------+-------+

| Variable_name                    | Value |

+----------------------------------+-------+

| Innodb_master_thread_idle_loops  | 177   |

| Slave_idle_parallel_worker_count | 5     |

| Threadpool_idle_threads          | 0     |

+----------------------------------+-------+

3 rows in set (0.004 sec)

Counter gets cleared during START SLAVE.

MariaDB [test]> start slave;

Query OK, 0 rows affected (0.004 sec)

MariaDB [test]> show status like '%idle%';

+----------------------------------+-------+

| Variable_name                    | Value |

+----------------------------------+-------+

| Innodb_master_thread_idle_loops  | 187   |

| Slave_idle_parallel_worker_count | 4     |

| Threadpool_idle_threads          | 0     |

+----------------------------------+-------+

3 rows in set (0.004 sec)

Hello GeoffMontee

A draft patch has been implemented as per the task description.
Please refer the following comment for detailed description.

https://jira.mariadb.org/browse/MDEV-20220?focusedCommentId=152038&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-152038

Would it be helpful, if the status variable displays the total
count of idle time spent by workers instead of proposed counter.

Please let us know.

I would go with the most "lazy" one of FLUSH STATUS.

Hi sujatha.sivakumar and Elkin,

Would it be helpful, if the status variable displays the total count of idle time spent by workers instead of proposed counter.

This raises a question to me. Let's say that you have a slave with 4 parallel worker threads. And let's say that during a 1 second window, 3 of those worker threads were idle because their queues were full.

What would the "the total count of idle time spent by workers" be in this case?

Would it be "1 second", because all 3 worker threads were idle for the same 1 second time period?

Or would it be "3 seconds", because there were 3 workers threads, and each one was idle for 1 second?

Or would it be something else?

Thanks!

GeoffMontee And let's say that during a 1 second window, 3 of those worker threads were idle because their queues were full. - that means 3 are 'busy', are not? I am guessing you meant that, as 'idle' that is one with an empty queue.

Then we count the total idleness. If all N have nothing to do within time t then the idle period is N*t.

Hi Elkin,

that means 3 are 'busy', are not? I am guessing you meant that, as 'idle' that is one with an empty queue.

No, I meant what I said for the example--that 3 worker threads are idle because their queues do not have room, and only 1 worker thread is performing work. I am specifically referring to cases like the one shown in the ticket description, where the worker thread has the state "Waiting for work from SQL thread", and the SQL thread has the state "Waiting for room in worker thread event queue":

|  4 | system user |           | NULL | Connect |    139 | Waiting for work from SQL thread              | NULL             |    0.000 |

...

| 21 | system user |           | NULL | Connect |     45 | Waiting for room in worker thread event queue | NULL             |    0.000 |

...

I don't completely understand why this happens. It seems somewhat contradictory to me. If the worker thread isn't performing work, then shouldn't the worker thread's queue have room? I assume that this happens because of some internal implementation details. Do you know?

Then we count the total idleness. If all N have nothing to do within time t then the idle period is N*t.

OK, I see. So if 3 worker threads are idle for the same 1 second period, then the status variable would say that the total idle time is 3 seconds. Thanks!

3 worker threads are idle because their queues do not have room

Well, they are not really idle, unless

Waiting for work from SQL thread 

The other form of idling is by the Driver thread

Waiting for room in worker thread event queue 

In your paste the idler is really one Worker.

So if 3 worker threads are idle for the same 1 second period, then the status variable would say that the total idle time is 3 seconds. Thanks!

Hi Elkin,

Well, they are not really idle, unless

Waiting for work from SQL thread

The other form of idling is by the Driver thread

Waiting for room in worker thread event queue

Yes, I understand that.

In your paste the idler is really one Worker.

Yes, I understand that. I do not have example text showing "3 idle worker threads, 1 active worker thread". That was a hypothetical example that I came up with to ask you how your new status variable would behave in that scenario.

Hello serg, GeoffMontee and Elkin

As part of current task can we add a new `performance schema` table similar to upstream?
The table can capture following details. It can be extended further to include few more details.

performance_schema.replication_applier_status_by_worker

Hi sujatha.sivakumar,

That sounds good to me.

Hello serg

Can you please review the changes for MDEV-20220.

Patch: https://github.com/MariaDB/server/commit/0922edb1b162c5aa73c11867157e0e118f79bbc6
Test results: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.5-sujatha

Hello serg

Since fix version is 10.6, ported my 10.5 changes to 10.6 and tested them.

Please find the 10.6 patch: https://github.com/MariaDB/server/commit/1d5f0db0158c5a4c2e82abd3b881de46a719412d
Test results: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-sujatha

This patch is for 'replication_applier_status_by_worker' table alone.
Rest of the tables are implemented as part of MDEV-16437.

Current patch will be used as base of MDEV-16437 and each PFS table specific change will be added as a separate commit.

Thank you.

Hello Sergei,

Good Morning.

I have addressed your review comments. Please review the new set of changes.

Patch: https://github.com/MariaDB/server/commit/9abbb589e6d6f61e03cad0fc5c5aee31077dd00c

BuildBot Results: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-sujatha

Thank you.