Proxysql: PROXYSQL PAUSE and PROXYSQL RESUME

[renecannao]

ProxySQL has several commands to shutdown:

• PROXYSQL KILL : it kills itself
• PROXYSQL SHUTDOWN : gracefully shutdown all the modules

ProxySQL has already 3 commands useful to stop and start without restarting the process:

• PROXYSQL STOP
• PROXYSQL START
• PROXYSQL RESTART
  These commands are less disruptive than a KILL or SHUTDOWN , as PROXYSQL STOP brings down all ProxySQL with the exception of the Admin module.

Although, even PROXYSQL STOP is not really graceful in shutting down client's connections, as these are terminated immediately. This because a problem when multiple proxysql instances are chained and one of the instances need to be shutdown gracefully.

PROXYSQL PAUSE tries to solve this problem as follow:

• the listeners are immediately shut down : no new connections are allowed ;
• mysql-wait_timeout is set to 0 : any idle connection (only idle!) not in a transaction is immediately closed;
• connections with an active transactions are still processed until they commit/rollback or mysql-max_transaction_time is reached

PROXYSQL RESUME is the command that performs the opposite of PROXYSQL PAUSE:

• resume the previous value of mysql-wait_timeout
• start the listeners

[renecannao]

This needs extensive documentation

[ealter]

Do you think that you could make proxysql pause idempotent? Right now it errors with ProxySQL MySQL module is already paused, impossible to pause if proxysql is already paused.

[dopessoa]

Hello @renecannao, I wasn't able to find nothing about these commands on the wiki.

I was looking for a way to do connection draining in ProxySQL, searched for Graceful Shutdown and could only find this.

Would it be OK to add them somewhere in the wiki? If it's a simple explanation I can do it myself. A simple mention of proxysql pause and proxysql resume along with the other control commands would do.

A good addition would be the ability to see the current status of ProxySQL trough a variable in global_variables or something like that, if you think that's feasible and useful I can open an enhancement issue.

Thank you and congratulations for the amazing project.

[renecannao]

Hi @dopessoa .
Thank you for the feedback.
Please feel free to add something in the wiki.
About the global variable, it is in our roadmap but no estimate date for it.

Thanks