MySQL Threadpool with admin port/connection behavior

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hello Percona Team,
I am trying out MySQL threadpool and observed a behavior. Wanted to confirm if it is by design!
Say, I am running threadpool with threadpool_size=1 and threadpool_oversubscribe=3.
Then I try to connect clients to server one by one and let the connection do some heavy select operation.
After 4 client connections, even an ADMIN connection is not go through and hangs.
Is this an expected behavior?

2

Hi @nitin welcome to the Percona forums!
You should be able to make an ADMIN connection to the database using the Administrative Interface via --port=33062

  • There is no limit on the number of administrative connections, but connections are permitted only for users who have the SERVICE_CONNECTION_ADMIN privilege.

https://dev.mysql.com/doc/refman/8.0/en/administrative-connection-interface.html

1 Like
3

The admin connection I am trying do have SERVICE_CONNECTION_ADMIN privilege.
I believe the reason is code percona-server/sql/threadpool_unix.cc at 8.0 · percona/percona-server · GitHub “too_many_busy_threads(*thread_group)” does not allow the admin connection to login!
The worker is not able get/remove the request from the event queue.

4

Hi @nitin
Can you confirm you were connecting with --port=33062 ?

5
  • Started the server options “–thread-handling=pool-of-threads --thread-pool-size=1 --admin_address=127.0.0.1 --admin_port=33062”
  • Connected the client with “–protocol=TCP --port=33062”
  • So i suppose it is an admin client connection, not sure if there is any way to cross check though.
  • The default value of threadpool_oversubscribe=3 and for testing we set threadpool_size to 1.
  • So, 4 clients happily connected (admin or non) and started executing heavy select queries (became busy).
  • Now when trying to establish the 5th connection :
    - A NON-ADMIN connection cannot established as expected, because we have exceeded oversubscription limit. Please confirm my understanding!
    - But I expected an ADMIN connection now should go through and should NOT honor oversubscribe limit. Please confirm my understanding!
6

Probably, related to Jira

7

Looks to be the case. My suggestion is to add yourself as a watcher and upvote that ticket for visibility.