first,we use pool-of-threads
past we use percona server 5.7，when the thread pool is full(not reached max_connection,just thread pool), we can use extra_port login in and kill quetion connection.
now we upgrade to percona server 8.0.33, when the thread pool is full, we can’t use admin port login in.
ps: our max thread pool can use is 17,max_connetions and max_user_connetion is 4000 and 3000. when the thread running reached 17,all other connecion is hung, at 5.7 we can login in use extra port, at 8.0.33 we can’t login in use admin port.
I think it’s a BUG
Hi @Chuang_Pan,
Welcome to Percona community.
It is interesting observation if true; so I tried this on PS8033 but couldn’t quiet see this.
You have max_connections and max_user_connections set to 4000/3000 respectively.
Your thread-pool is set to 17. Threadpool specifies number of threads running concurrently and it has no role in limiting user connections really… I could connect !
Can you provide steps to reproduce this please?
Thanks,
K
Thanks for reply!
To display, we set global thread_pool_size=1,as below
±--------------------------------±----------------+
| Variable_name | Value |
±--------------------------------±----------------+
| thread_pool_oversubscribe | 3 |
| thread_pool_size | 1 |
| max_connections | 4000 |
| max_user_connections | 3000 |
| admin_address | 127.0.0.1 |
| admin_port | 23306 |
| create_admin_listener_thread | ON |
Now we have 5 running threads like this
@root@(none) >select sleep(300);
We tried to login in use admin port, it’s hung.
looking forward to your reply，thanks
@Chuang_Pan thanks for the case but I couldn’t reproduce that !
This is what I did:
[k@test ~]$ nohup mysql -umsandbox -pmsandbox -S /tmp/mysql_sandbox8033.sock -e "select sleep(1000);" 2>/dev/null &
[1] 3455822
[k@test ~]$ nohup mysql -umsandbox -pmsandbox -S /tmp/mysql_sandbox8033.sock -e "select sleep(1000);" 2>/dev/null &
[2] 3455823
[k@test ~]$ nohup mysql -umsandbox -pmsandbox -S /tmp/mysql_sandbox8033.sock -e "select sleep(1000);" 2>/dev/null &
[3] 3455824
[k@test ~]$ nohup mysql -umsandbox -pmsandbox -S /tmp/mysql_sandbox8033.sock -e "select sleep(1000);" 2>/dev/null &
[4] 3455825
[k@test ~]$ nohup mysql -umsandbox -pmsandbox -S /tmp/mysql_sandbox8033.sock -e "select sleep(1000);" 2>/dev/null &
[5] 3455826
[k@test ~]$ nohup mysql -umsandbox -pmsandbox -S /tmp/mysql_sandbox8033.sock -e "select sleep(1000);" 2>/dev/null &
[6] 3455827
[k@test ~]$ nohup mysql -umsandbox -pmsandbox -S /tmp/mysql_sandbox8033.sock -e "select sleep(1000);" 2>/dev/null &
[7] 3455828
[k@test ~]$ mysql -umsandbox -pmsandbox -S /tmp/mysql_sandbox8033.sock --port=33062
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 8.0.33-25 Percona Server (GPL), Release 25, Revision 60c9e2c5
Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+--------------------+----------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+-------+------------------------+--------------------+----------+-----------+---------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 58509 | Waiting on empty queue | NULL | 58508977 | 0 | 0 |
| 44 | msandbox | localhost | NULL | Query | 12 | User sleep | select sleep(1000) | 12513 | 0 | 0 |
| 45 | msandbox | localhost | NULL | Query | 11 | User sleep | select sleep(1000) | 11645 | 0 | 0 |
| 46 | msandbox | localhost | NULL | Query | 11 | User sleep | select sleep(1000) | 11404 | 0 | 0 |
| 47 | msandbox | localhost | NULL | Query | 11 | User sleep | select sleep(1000) | 11159 | 0 | 0 |
| 48 | msandbox | localhost | NULL | Query | 11 | User sleep | select sleep(1000) | 10936 | 0 | 0 |
| 49 | msandbox | localhost | NULL | Query | 11 | User sleep | select sleep(1000) | 10735 | 0 | 0 |
| 50 | msandbox | localhost | NULL | Query | 10 | User sleep | select sleep(1000) | 10434 | 0 | 0 |
| 51 | msandbox | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 |
+----+-----------------+-----------+------+---------+-------+------------------------+--------------------+----------+-----------+---------------+
9 rows in set (0.00 sec)
mysql> select @@admin_address, @@admin_port, @@thread_pool_size, @@max_connections, @@max_user_connections;
+-----------------+--------------+--------------------+-------------------+------------------------+
| @@admin_address | @@admin_port | @@thread_pool_size | @@max_connections | @@max_user_connections |
+-----------------+--------------+--------------------+-------------------+------------------------+
| 127.0.0.1 | 33062 | 1 | 10 | 10 |
+-----------------+--------------+--------------------+-------------------+------------------------+
1 row in set (0.00 sec)
mysql> show global status like 'threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 8 |
+-------------------+-------+
1 row in set (0.00 sec)
What happens when you do this:
[k@test ~]$ nc -zv 127.0.0.1 33062
Connection to 127.0.0.1 33062 port [tcp/*] succeeded!
[k@test ~]$
(or telnet 127.0.0.1 33062)
Let us know.
K.
I appreciate your message.
I have tried scores of times.It’s allways hung,that’s my way：
@#mysql -h127.0.0.1 -P 3306 -utt -ptt
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 174
Server version: 8.0.33-25-20230707 Source distribution
Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
@root@(none) 02:51:54>select @@admin_address, @@admin_port, @@thread_pool_size, @@max_connections, @@max_user_connections;
+-----------------+--------------+--------------------+-------------------+------------------------+
| @@admin_address | @@admin_port | @@thread_pool_size | @@max_connections | @@max_user_connections |
+-----------------+--------------+--------------------+-------------------+------------------------+
| 127.0.0.1 | 23306 | 1 | 4500 | 4000 |
+-----------------+--------------+--------------------+-------------------+------------------------+
1 row in set (0.00 sec)
@root@(none) 02:52:01>create user tt@'127.0.0.1' identified by 'tt';
Query OK, 0 rows affected (0.01 sec)
@root@(none) 02:52:01>grant all privileges on *.* to tt@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
@root@(none) 02:52:01>select user,host from mysql.user where user='tt';
+------+-----------+
| user | host |
+------+-----------+
| tt | 127.0.0.1 |
+------+-----------+
1 row in set (0.00 sec)
@root@(none) 02:52:02>exit
Bye
[14:52:07root@dc07-prod-db-product-580137 /root]
@#nohup mysql -h127.0.0.1 -P 3306 -utt -ptt -e "select sleep(200)" &
[1] 268671
nohup: ignoring input and appending output to 'nohup.out'
[14:52:18root@dc07-prod-db-product-580137 /root]
@#nohup mysql -h127.0.0.1 -P 3306 -utt -ptt -e "select sleep(200)" &
[2] 268676
nohup: ignoring input and appending output to 'nohup.out'
[14:52:18root@dc07-prod-db-product-580137 /root]
@#nohup mysql -h127.0.0.1 -P 3306 -utt -ptt -e "select sleep(200)" &
[3] 268681
nohup: ignoring input and appending output to 'nohup.out'
[14:52:18root@dc07-prod-db-product-580137 /root]
@#nohup mysql -h127.0.0.1 -P 3306 -utt -ptt -e "select sleep(200)" &
[4] 268686
nohup: ignoring input and appending output to 'nohup.out'
[14:52:18root@dc07-prod-db-product-580137 /root]
@#nohup mysql -h127.0.0.1 -P 3306 -utt -ptt -e "select sleep(200)" &
[5] 268691
nohup: ignoring input and appending output to 'nohup.out'
[14:52:18root@dc07-prod-db-product-580137 /root]
@#nohup mysql -h127.0.0.1 -P 3306 -utt -ptt -e "select sleep(200)" &
[6] 268696
nohup: ignoring input and appending output to 'nohup.out'
[14:52:18root@dc07-prod-db-product-580137 /root]
@#
[14:52:18root@dc07-prod-db-product-580137 /root]
@#ps aux | grep sleep | grep -v grep
www 264109 0.0 0.0 8868 1108 ? S 14:50 0:00 sleep 164
root 268671 0.0 0.0 44112 11960 pts/0 S 14:52 0:00 mysql -h127.0.0.1 -P 3306 -utt -px -e select sleep(200)
root 268676 0.0 0.0 44112 11868 pts/0 S 14:52 0:00 mysql -h127.0.0.1 -P 3306 -utt -px -e select sleep(200)
root 268681 0.0 0.0 44112 12020 pts/0 S 14:52 0:00 mysql -h127.0.0.1 -P 3306 -utt -px -e select sleep(200)
root 268686 0.0 0.0 44112 11760 pts/0 S 14:52 0:00 mysql -h127.0.0.1 -P 3306 -utt -px -e select sleep(200)
root 268691 0.0 0.0 44112 11936 pts/0 S 14:52 0:00 mysql -h127.0.0.1 -P 3306 -utt -px -e select sleep(200)
root 268696 0.0 0.0 44112 12164 pts/0 S 14:52 0:00 mysql -h127.0.0.1 -P 3306 -utt -px -e select sleep(200)
[14:52:41root@dc07-prod-db-product-580137 /root]
@#nc -zv 127.0.0.1 23306
Connection to 127.0.0.1 23306 port [tcp/*] succeeded!
[14:52:49root@dc07-prod-db-product-580137 /root]
@#mysql -h127.0.0.1 -P 23306 -utt -ptt
mysql: [Warning] Using a password on the command line interface can be insecure.
hung!
**In my previous example, thread pool set was **
thread_handling=pool-of-threads
When i comment this config, it’s default set one-thread-per-connection, this time i’m loggin in succeeded!
I think the problem is here：thread_handling=pool-of-threads
looking forward to your reply!
Hi @Chuang_Pan,
With thread_handling value of pool-of-thread, now I see the behaviour you’re describing. Can you please consider raising the bug report here.
Additional reference:
Thanks,
K
Hi @kedarpercona
I have submitted this bug here
https://perconadev.atlassian.net/browse/PS-9265
Looking forward to your fix,tks!
1 Like