MySQL Connections not closed / freed (NO mysql_pconnect used here)
|Priority:||Should have||Due date:|
|Assigned To:||-||% Done:||
|Category:||Database API||Spent time:||-|
|PHP Version:||5.2||Is Regression:|
Debian Etch 4.0 Server
mod-fcgid with php5.2
mysql-server-5.0 (latest stable)
Typo3 seems to "forget" to mysql_close() (or similar) when a Page was rendered and sent to the Client, which keeps the connections open for interactive_timeout seconds (mysql).
For the Info pasted below, a new system was set up and some T3 sites where migrated to it, just to make sure that no system configuration errors are involved (default mysql and apache config, php memory limit raised). On this server, the problem is minor, but on my other servers, where much more traffic is going on, this became a major performance problem, where the mysql connections sometimes run out, because of not freeing the idle connections.
On the live servers, the interactive_timeout is set to 60 secs, but still problematic in busy hours.
As we know from mod_php, mysql Connections are getting closed implicitly, as soon as the running script is finished.
With php-fcgid, this seems NOT to be the case, so mysql_close() should be called explicitly in the DB API (or whatever).
This is a snapshot of "mtop", a nice little helper in watching whats going on in mysql, because "show processlist" can become quite badly readable to the human eye via ssh console apps:
load average: 0.00, 0.00, 0.00 mysqld 5.0.51a-0.dotdeb.1-log up 22 day(s), 3:52 hrs
23 threads: 1 running, 3 cached. Queries/slow: 2.8M/0 Cache Hit: 99.27%
Opened tables: 0 RRN: 1.2K TLW: 4 SFJ: 0 SMP: 0 QPS: 0
ID USER HOST DB TIME COMMAND STATE INFO
5637 site2 localhost site2_typo3 13329 Sleep
5704 site1 localhost site1_typo3 7117 Sleep
5705 site1 localhost site1_typo3 7095 Sleep
5706 site1 localhost site1_typo3 7054 Sleep
5719 site1 localhost site1_typo3 4522 Sleep
5694 site3 localhost site3_typo3 2780 Sleep
5697 site3 localhost site3_typo3 2779 Sleep
5715 site3 localhost site3_typo3 2778 Sleep
5683 site3 localhost site3_typo3 2777 Sleep
5717 site3 localhost site3_typo3 2776 Sleep
5714 site3 localhost site3_typo3 2775 Sleep
5729 site3 localhost site3_typo3 999 Sleep
5712 site4 localhost site4_typo3 978 Sleep
5682 site4 localhost site4_typo3 723 Sleep
5725 site4 localhost site4_typo3 357 Sleep
5710 site4 localhost site4_typo3 338 Sleep
5730 site4 localhost site4_typo3 308 Sleep
5726 site4 localhost site4_typo3 302 Sleep
5727 site4 localhost site4_typo3 251 Sleep
5649 site4 localhost site4_typo3 225 Sleep
5676 site4 localhost site4_typo3 159 Sleep
5709 site4 localhost site4_typo3 155 Sleep
The value in the TIME column is the number of seconds that the particular thread holds the connection open.
How to reproduce:
Open a ssh sessions (or console), for monitoring the mysql connections, and a browser window. Now open the typo3 site in the browser on Windows, wait for the page to load and press and hold F5 (Refreshes the page on Windows Clients). You will see the number of mysql connections explode until all connections are busy and the number of apache client slowly reach the MaxClients Limit.
When the Limit is reached, finally, the server starts to crawl. Depending on the amount of memory and configuration, the server will start to swap too, which makes logging in an restarting apache a real pain (which resolves the issue after mysql has cleaned up the connections already, otherwise a database restart is needed too).
Anyway, its really bad coding practice to not free your resources explicitly.
And i have coded for decades now...
Please reproduce, fix or give a workaround, but resist from the advice to lower interactive_timeout in my.cnf, because that would be somewhat far from a clean solution. I have interactive clients too, and i can not lower the timeout to under one minute.
(issue imported from #M8885)
#1 Updated by Christian Kuhn over 8 years ago
I can not confirm this.
PHP closes non persistent mysql connections implicitly at end of the scripts execution as stated in mysql_connect manual.
I think your mysql_pconnect is still enabled. Please set mysql.allow_persistent = Off in your /etc/php5/cgi/php.ini if you want do deny persistant connections globally, or set $TYPO3_CONF_VARS['SYS']['no_pconnect'] = '1'; in localconf.php of your TYPO3 instances. Both parameters work on my fcgi enabled debian etch system.
#2 Updated by Makro over 8 years ago
You are perfectly right.
me (admin) and my collegue (typo3 developer) had no clue, that pconnect has to be disabled explicitly in typo3.
Setting $TYPO3_CONF_VARS['SYS']['no_pconnect'] = '1'; in localconf.php resolves the issue completely.
"bug" resolved, thanks.