why does it takes 40sec for proxysql to pause

[basictech01]

I am testing proxysql and I found that proxysql consistently takes 40seconds to pause can you please explain why

mysql> proxysql pause;
Query OK, 0 rows affected (39.74 sec)

here is the configs we are experimenting with

mysql_variables=
{
	threads=40
	monitor_enabled=false
	max_connections=100000
	default_query_timeout=14400000 # 4 hours
	max_transaction_time = 14400000 # 4 hours
	interfaces="0.0.0.0:6033"
	server_version="8.0.28"
	client_session_track_gtid=false
	connection_warming=true
	have_ssl=true
	long_query_time=10000
	query_cache_size_MB=0
	auditlog_filename="/var/lib/proxysql/audit.log"
	auditlog_filesize=1000000000
	verbose_query_error=true
	mysql-ssl_p2s_key="/var/identity.key"
	mysql-ssl_p2s_cert="/var/identity.cert"

	# proxysql defaults
	default_schema="information_schema"
	stacksize=1048576
	commands_stats=true
	sessions_sort=true
	connect_retries_on_failure=10
}

[renecannao]

Hard to guess , but I would bet on threads misconfigured.
40 threads? It seems excessive , especially if you are experimenting.

[basictech01]

Yes, I have a big host, it has 40 cores and I would be using the entire host as a proxy, can something be done to expediate this,
It looks like its taking 1 sec per thread

[renecannao]

Yes, I have a big host, it has 40 cores and I would be using the entire host as a proxy,

ok!

an something be done to expediate this,
It looks like its taking 1 sec per thread

Having a quick lock at the code, it shouldn't take that long.
What is done mostly is to signal all threads (with a write() on a pipe, I guess this is the only thing that could be blocking) , and then remove the listener.
Can you run pt-pmp or similar during the time that PROXYSQL PAUSE is running? This can give us an idea of what threads are doing

[basictech01]

here

 [ ~ ]$ sudo ./pt-pmp.sh --pid=2097076 --interval=1 --iterations=60
Mon Feb 17 10:55:54 UTC 2025
awk: cmd. line:22: warning: regexp escape sequence `\#' is not a known regexp operator
   2400 poll(target:/lib/libc.so.6),::??,::??,::??,::??,::??,MySQL_Thread::run,::??,::??,::??,::??,??
   1360 epoll_wait(target:/lib/libc.so.6),??
   1040 epoll_wait(target:/lib/libc.so.6),??,::??,::??,::??,??,MySQL_Thread::run,::??,::??,::??,::??,??
    566 poll(target:/lib/libc.so.6),::??,::??,::??,::??,::??,child_mysql,::??,::??,::??,::??,::??,::??,::??,??
    300 poll(target:/lib/libc.so.6),::??,::??,::??,::??,::??,child_mysql,::??,::??,::??,::??,::??,::??,??
     90 clock_nanosleep(target:/lib/libc.so.6),??
     90 clock_nanosleep(target:/lib/libc.so.6),::??,::??,::??,??
     61 __lll_lock_wait(target:/lib/libc.so.6),pthread_mutex_lock(target:/lib/libc.so.6),::??,::??,::??,admin_session_handler,MySQL_Session::handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY___not_mysql,::??,MySQL_Session::get_pkts_from_client,MySQL_Session::handler,::??,::??,::??,::??,::??,::??,::??,::??,::??,::??,::??,::??,::??,::??,::??,child_mysql,::??,::??,::??,::??,::??,::??,::??,??
     60 poll(target:/lib/libc.so.6),::??,::??,::??,::??,::??,admin_main_loop,??
     60 poll(target:/lib/libc.so.6),??
     60 __futex_abstimed_wait_common(target:/lib/libc.so.6),??
     33 MySQL_Threads_Handler::listener_del,::??,::??,::??,::??,::??,??

also I am seeing two proxysql processes,
$ pidof proxysql
2329498 2097076

this is normal right

[renecannao]

Two proxysql process is normal , the parent process monitor the child process.

What proxysql package are you using? It seems all symbols are missing? Please use an official package.
Although symbols are missing, few items got my attention:

• listener_del() is present a lot, probably it is spinning
• the 40 worker threads are on poll() , it probably means they are not working on stopping the listener . This may suggest a timing issue
• there are probably other 15 connections to admin: what are they doing? It is possible that they are the trigger of the slowness. For example if they are trying to extract metrics from the worker threads, the worker threads may have temporarily missed the signal to stop the listener.

[basictech01]

I am on Azure Linux, I have to build proxysql from scratch , I don't think you guys build the official package for that,
I am using the modified rehl6 makefile, I am not sure at what place it is setting the symbols

yes, we are making alot of admin connections to the proxysql, we have developed our own monitoring system for proxysql , I think we are only checking how many servers are connected that too every 1 minute

I tried stopping our monitoring and then checking if anything changes, but its still taking 40 seconds