cache flush ultra slow query - index optimization fix

[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.

[Marc]

Thanks for bringing this up and looking into the issue. We'll have a look and will check on whether this can be integrated. As you might know, we typically don't aim at only MySQL and will also have to evaluate the effect on other DBMS types.

[oferlaor]

yes, I doubt this will have an adverse effect on other DBMSs. MySQL is pretty popular (as is MariaDB, which probably will behave the same, I haven't tested it). The additional index doesn't harm anything, it basically fixes a wrong (albeit a very logical) assumption that if you have an index that covers all search columns, it actually improves performance. In this case, an index with less columns is more effective.

The core issue is not the indices, of course. The query itself, which needs to run very seldomly, should be improved or the mechanism should be improved. When there are enough users, the query (one for a_ and one for m_) becomes unwieldy and I can think of many ways that are more efficient to do this:
1. The values there change only when moderators or admins are added/removed. That means that you could save this to a dedicated file or table without having to rely on caching.

2. the query itself should be rewritten, this might mean a temporary table or using JOIN ON.