[Vtigercrm-developers] Persistant MySQL connections

[Sukhdev Mohan]

Hi,

I came across a strange problem: in some php versions you may have persistent mysql connection, needless to say this is a pain in the back, since by default mysql will kill idle/sleepy connections after 28800 seconds (roughly 8 hours), and if unluckily it reaches 131 child process or around 100 process from mysql shell, it’ll freeze giving a blank white page until one of the following:
a. Some of the process are killed automatically because timeout limit is reached or manually
b. Complete reset of mysql service (systemctl restart/relaod mysql)

The fix is in php.ini:
Set mysqli.allow_persistent to Off and mysqli.max_persistent to 0. In some versions you’ll find their values respectively On and -1 which is limitless.

After I set these new values, there were no sleepy process since mysql was killing them as soon as php-fpm ended.

Final considerations:
Why is the library AdoDB not closing the db pool explicitly? Relying on PHP is way too risky: prior to the solution in the best case scenario there were a couple of process brought up in mysql, in the worst case the were ten and refreshing 5 times the dashboard with some widget was enough to paralyse the system.

You can check yourself by logging in to your mysql shell and giving this command:
show processlist;

Or from your shell (linux) give the pstree command and look at mysql and how many process it has spawned. On my system with 131 process it hanged (24gb ram).

More at https://www.php.net/manual/en/mysqli.configuration.php#ini.mysqli.allow-persisten t


Sukhdev Mohan
Developer
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210930/8fe301c8/attachment.html>