3.3.12 slow start after cache purge

[oferlaor]

I've seen increasingly slow starts on 3.3.12 as the site grows. The issue is pretty simple, immediately after a cache flush, there are two queries that run:

Code: Select all

SELECT ug.user_id, a.forum_id, r.auth_setting, r.auth_option_id, ao.auth_option
FROM phpbb_acl_groups a, phpbb_user_group ug, phpbb_groups g, phpbb_acl_roles_data r, phpbb_acl_options ao
WHERE a.auth_role_id = r.role_id AND r.auth_option_id = ao.auth_option_id
AND a.group_id = ug.group_id
AND g.group_id = ug.group_id
AND ug.user_pending = 0
AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
AND ao.auth_option = 'a_'

Code: Select all

SELECT ug.user_id, a.forum_id, r.auth_setting, r.auth_option_id, ao.auth_option
FROM phpbb_acl_groups a, phpbb_user_group ug, phpbb_groups g, phpbb_acl_roles_data r, phpbb_acl_options ao 
WHERE a.auth_role_id = r.role_id AND r.auth_option_id = ao.auth_option_id 
AND a.group_id = ug.group_id 
AND g.group_id = ug.group_id 
AND ug.user_pending = 0 
AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1) 
AND ao.auth_option = 'm_'

The queries are heavy because of a huge amount of users (approaching 100K). Until these two queries pass and are cached, they can take upwards of 25s. But since there's not just a single user, there are many people triggering this query, each of them slowing eachother down reaching the peak load that the server can handle and therefor causing the actual time to run to increase dramatically (it can take 4-10 minutes).

I've tried a few things. phpBB has an option to pause server access if the server load is too high. However, that won't help because this still triggers these two queries (found in auth.php).

Another approach might be to let the SQL server cache this, by running the query preemptively when the server loads and before phpBB starts. This is a bad idea, but also is less relevant for MYSQL 8 (with MYSQL 5.6-5.7, it has a built-in memory cache that might be relevant).

I think this query is quite badly written (join on was invented to prevent exactly this type of mistake), there's also no way to explicitly load the query while blocking others while it happens, despite the output of this query rarely being changed. A simple solution to this would be to run it off a physical file that changes when the administrator or moderator lists change using separate dedicated files not using the normal cache infrastructure.

since the problem is the joins, indexes are not necessarily that useful, but I am giving it a try.

One flaw that I see with the current index design is that the indexes are not necessarily in the ideal order or composition. Although the query uses all 3 values (group_leader, group_id and user_pending) it seems that separating the indexes has a bigger chance of success. BTW, this might be further improved by adding more indexes (using group_id and user_pending) but this specific index made the most dramatic change:

Code: Select all

ALTER TABLE `phpbb_user_group` ADD INDEX `auth_idx` (`user_pending`, `group_leader`);

before the change, explain showed that phpbb_user_group (ug) was going to pull nearly 91K records, with the extra stating: Using where; Using join buffer (Block Nested Loop) (this is because it wasn't really using the index)

after the change: 1525 rows are pulled from phpbb_user_group (ug). Since this is a Cartesian multiplication, this should be a substantial performance boost.

Not sure if this helps me or anyone else yet, will check in.

[thecoalman]

You would be better off posting this in area 51 or the bug tracker.

https://area51.phpbb.com/phpBB/
https://tracker.phpbb.com/secure/Dashboard.jspa

I'm not commenting on whether it can be improved or should be improved but during normal operation there is really only 2 reasons I can think of to purge the cache. You have made a template edit or you made CSS/JS edit and want to increase the asset version to force the browser to reload those files, that is not necessarily "normal" operation either.

Just a side note, if you are using OpCache purging phpBB's cache will purge OpCache so there will be short performance hit for that too.

Another approach might be to let the SQL server cache this, by running the query preemptively when the server loads and before phpBB starts. This is a bad idea, but also is less relevant for MYSQL 8 (with MYSQL 5.6-5.7, it has a built-in memory cache that might be relevant)

No idea if it's helpful for this specific issue but memcached can be used if you have it installed. Open config.php and find:

Code: Select all

$acm_type = 'phpbb\\cache\\driver\\file';

Replace with:

Code: Select all

$acm_type = 'phpbb\\cache\\driver\\memcached';

[JoshyPHP]

since the problem is the joins, indexes are not necessarily that useful, but I am giving it a try.

I've only skimmed your post but I have a note somewhere that says an index on phpbb_groups (group_id, group_skip_auth) could improve that query. Caveat emptor, this may be outdated and may not apply anymore.

[oferlaor]

thanks for the notes:
1. I'll update the relevant forums as well, but since I saw people on this forum who have this issue, I started here first.
2. Good point about the template, I see this problem when the server starts and when flushing the cache (indeed because of template/theme changes).
3. the index I outlined is the critical one when the forum has a large number of users. Groups might also help, but it's a much smaller number of rows, so it's impact is minimal.

Memcached is indeed what I use for caching. That still leaves quite a lot as cache files, as there are a few things that are always saved to file. BTW, I don't actually know if this particular query is saved anywhere except the MYSQL cache because just running the query (before the index changes) on phpMyAdmin takes 25s the first time and 0.0001 afterwards...