Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

instamattic

macrumors newbie
Original poster
Oct 26, 2018
1
0
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.

  • 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
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.