One minute it's .812 second, the next it's 203 seconds. It's about a 50-50 split. I have 1GB reserved for MySQL in my.cnf, otherwise it's stock MySQL 5.7.
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_'
During the duration of the query when it takes 100+ seconds, mysqlId maxes 100% on a single core. mysqlId memory is ~260MB during the long query moments, as measured by Activity Monitor.
The other 30+ queries on pages don't stall. It's only this query, and about half the time. The other half it's returned in under a second. There is no rhyme or reason to the occurence. It happens in the phpBB app or in phpMyAdmin running as a naked query. Thanks for any thoughts.
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_'
During the duration of the query when it takes 100+ seconds, mysqlId maxes 100% on a single core. mysqlId memory is ~260MB during the long query moments, as measured by Activity Monitor.
The other 30+ queries on pages don't stall. It's only this query, and about half the time. The other half it's returned in under a second. There is no rhyme or reason to the occurence. It happens in the phpBB app or in phpMyAdmin running as a naked query. Thanks for any thoughts.
- OS: Mac OS High Sierra
- Hardware: 2017 MBP 16BG ram
- MySQL version: 5.7.23
- Install: MAMP Pro 5.1
- App platform: phpBB 3.2.3