don't consider binlog dump as active thread in threadpool

Bug #1381848 reported by zhai weixiang
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Triaged
Medium
Unassigned
5.6
Triaged
Medium
Unassigned
5.7
Triaged
Medium
Unassigned

Bug Description

Suppose we have one master and several slaves, each slave connects to master and starts binlog dump . If thread pool is enabled, a thread is always occupied by one slave and can not be used by other normal workload.

Because we may have more than 10 binlog dump requests, so it's possible that some requests will be assigned to the same thread pool group. For example, suppose we use default thread_pool_oversubscribe (3), and have three binlog dump requests assigned to the same group, then the capacity of this group is limited and even unavailable.

Increasing thread_pool_oversubscribe is a simple way to workaround this problem.

Tags: threadpool
tags: added: threadpool
Revision history for this message
Muhammad Irfan (muhammad-irfan) wrote :

I found reported behavior. I configured 3 slaves for master server.

mysql> show full processlist\G
*************************** 1. row ***************************
         User: repl
         Host: slave2:45596
           db: NULL
      Command: Binlog Dump
         Time: 81054
        State: Master has sent all binlog to slave; waiting for binlog to be updated
*************************** 2. row ***************************
         User: repl
         Host: slave1:58470
           db: NULL
      Command: Binlog Dump
         Time: 81054
        State: Master has sent all binlog to slave; waiting for binlog to be updated
*************************** 3. row ***************************
         User: repl
         Host: slave3:59205
           db: NULL
      Command: Binlog Dump
         Time: 81054
        State: Master has sent all binlog to slave; waiting for binlog to be updated
*************************** 4. row ***************************
         User: root
         Host: localhost
           db: NULL
      Command: Query
         Time: 0
        State: init

I found all four threads occupied by thread pool.
Threadpool_threads - Threadpool_idle_threads = 4 threads (1 root user connection thread to MySQL, 3 "Binlog Dump" threads

mysql> show status like 'Thread%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 7 |
| Threadpool_threads | 11 |
| Threads_cached | 0 |
| Threads_connected | 4 |
| Threads_created | 201 |
| Threads_running | 4 |
+-------------------------+-------+

I executed mysqlslap with gradually increasing --concurrency

mysql> show status like 'Thread%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 58 |
| Threadpool_threads | 62 |
| Threads_cached | 0 |
| Threads_connected | 99 |
| Threads_created | 257 |
| Threads_running | 45 |
+-------------------------+-------+

mysql> show status like 'Thread%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 60 |
| Threadpool_threads | 64 |
| Threads_cached | 0 |
| Threads_connected | 45 |
| Threads_created | 261 |
| Threads_running | 22 |
+-------------------------+-------+

mysql> show global variables like 'Thread_pool%';
+-------------------------------+--------------+
| Variable_name | Value |
+-------------------------------+--------------+
| thread_pool_high_prio_mode | transactions |
| thread_pool_high_prio_tickets | 4294967295 |
| thread_pool_idle_timeout | 60 |
| thread_pool_max_threads | 100000 |
| thread_pool_oversubscribe | 5 |
| thread_pool_size | 8 |
| thread_pool_stall_limit | 500 |
+-------------------------------+--------------+

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1548

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.