#4127 - database max connections

By ironfeather
Added 16th Feb 2020, 2:31 AM
1 view

Identifier	#4127
Issue type	Major issue (breaks an entire feature)
Title	database max connections
Status	Closed (cannot reproduce)
Handling member	Chris Graham
Addon	core
Description	hello, i have two composr sites and recently updated one of them to the latest version. The older site works as expected but the site I updated now is having database problems, sometimes it has the cant connect, max connections errors. I uped my max connections in the mysql conf and this helped a bit but still my site is crashing for a few hours each day. I am not sure what it is but seems like the new update is not closing connections to database? Thank you for your help
Steps to reproduce
Funded?	No

The system will post a comment when this issue is modified (e.g., status changes). To be notified of this, click "Enable comment notifications".

Rating

Unrated

Comments

By Guest posted 16th Feb 2020, 2:38 AM

example open connection

localhost if_sale Query 00:00:59 UPDATE new_temp_block_permissions SET p_time=1581837407 WHERE (p_session_id='73ea523488551' AND p_block_constraints='block=side_rss,defer=1,max_entries=5,param=http\\://www\\.global\\.hokudai\\.ac\\.jp/blog/category/event/feed/,ticker=0,title=Hokkaido University Events') LIMIT 1

By Guest posted 16th Feb 2020, 9:42 AM

Are you able to discuss this with me on IRC?

By Guest posted 16th Feb 2020, 10:41 PM

This shouldn't be the case, but I'll work with you to make sure it is fixed. It's probably not actually possible for Composr to not close connections because PHP auto-closes any remaining connections when it finishes. What may be is a fix in the previous version changing the performance characteristics under load. I can work with you to look more closely if you email me at [email protected].

I'd suggest setting 'connect_timeout' in the MySQL configuration to a lower value like '30' to start with.

By Guest posted 17th Feb 2020, 5:43 PM

Hello, tonight I hope to work on this. I tweaked the mysql settings and still no luck. I can do show database connections open and the old site never has any but new site version has hundreds. All are UPDATE new_temp_block_permissions type. I have many busy blocks on the new site, many rss feeds, many deferred items. Some had been open for more than 3 minutes etc so i set wait timeout to 30 seconds to stop that. but still the mysql is overwhelmed and crashed out. Im using 7.0 i think, not the 7.4 and curious if upgrading to 7.4 would be helpful. i also made a complete backup and go back to 10.28. (i skipped 10.29) . ok will report back more this eve

By Guest posted 18th Feb 2020, 9:29 AM

hi, tweaking mysql and apache settings and turning off other memory hog softwares and going to see how it does. one thing i noticed thanks to the health checkup feature is.... Failed: Directory caches/guest_pages is very large @ 7 GB

By Guest posted 18th Feb 2020, 9:46 AM

hello, for now can you delete or close this ticket.

i plan to migrate the sites to a faster more modern server this weekend and also clean up everything and bet it will work fine.

thank you and will drop a line after the move

cheers

By Guest posted 18th Feb 2020, 10:35 PM

Did you see my replies to your email? Wondering if it went to spam.

By Guest posted 19th Feb 2020, 9:06 PM

Ok I'm going to close this, but if the problem persists I will help you solve it directly.

Here are some notes...

The latest patch release did fix block caching, which was often broken. That may have changed some of the performance characteristics (e.g. bigger but fewer queries). I doubt it though.

You probably have 1 of 3 possibilities:
i) PHP is crashing, leaving connections open when it does (unlikely)
ii) You configured to use persistent connections when installing (now in _config.php) but the pool is too big for the number of connections MySQL allows
iii) You have a lot of hits and it needs that many connections to serve the request load. Often bots can cause a problem. Check raw access logs to see if you have a lot of bots hitting the server. With deferred block loading you're putting on extra stress, as each block is a separate PHP request executed in parallel (works via AJAX).

Note that Composr leaving connections open isn't a possibility, because PHP will always close them regardless of Composr's behaviour (or add them back into the persistent connection pool).

An UPDATE query on temp_block_permissions is one of the last queries to run on a page view, so that's interesting - it makes me wonder if you are using persistent connections, and that you could try disabling them in _config.php (I think this because you might just be seeing the *last* active query on that connection before PHP added it back to its internal connection queue). Also I can confirm this query is used for deferred blocks.

What you could try is
:set_value('whitelisted_blocks', 'side_rss,main_rss');
in Commandr.
You need the leading colon.
Put whatever blocks you are deferring AND DON'T CARE IF HACKERS LOAD WITH DIFFERENT PARAMETERS, as comma-separated. My example is doing it for side_rss and main_rss. This hidden option allows you to bypass Composr having to track permissions on these across AJAX, because it will know that the blocks may always be accessed with any parameters.