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

Are you able to discuss this with me on IRC?

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.

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

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

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

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

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.