[Vtigercrm-developers] Persistant MySQL connections
Rubén A. Estrada Orozco
rulotec1 at gmail.com
Fri Oct 1 18:47:32 GMT 2021
I have struggled myself with similar issues. Not being a DBA and being more
inclined to development activities, it's always a pain to troubleshoot
database performance. I'd really like to have input from a knowledgeable
Vtiger infrastructure expert or someone in the community who could shed
some light on this topic and have general guidelines on database
configuration and performance tuning.
On Thu, Sep 30, 2021 at 3:08 PM Sukhdev Mohan <s.mohan at myti.it> wrote:
> 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
> 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
> *Sukhdev Mohan*
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the vtigercrm-developers