ACP very slow

[Stephen25]

Some ACP actions (clearing the cache, creating / deleting forums...) are really slow since some time and I can't figure out why. Here is the slow query log from just clearing the cache which took 16 seconds:

Code: Select all

Query_time: 16.156250  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 5090938
SET timestamp=1316023675;
SELECT a.forum_id, ug.user_id, g.group_id FROM (phpbb_acl_options o, phpbb_user_group ug, phpbb_groups g, phpbb_acl_groups a) LEFT JOIN phpbb_acl_roles_data r ON (a.auth_role_id = r.role_id) WHERE (o.auth_option_id = a.auth_option_id OR o.auth_option_id = r.auth_option_id)

Any ideas what could be the cause and how to solve it? Thanks!

[Thoul]

That query is parsing over 5 millions rows from the database. That seems very excessive and is undoubtedly why it is taking so long to run. From the best I can tell, the query is part of the cache_moderators() function in includes/functions_admin.php. It appears to be called at any time moderator permissions might be changed, which would include all the cases you mentioned.

However, the query in a 3.0.9 phpBB file is longer than what you posted. So let's make sure your file has the full query in it, first of all. Look in functions_admin.php and see if you can find this:

Code: Select all

			'WHERE'		=> '(o.auth_option_id = a.auth_option_id OR o.auth_option_id = r.auth_option_id)
				AND ((a.auth_setting = ' . ACL_NEVER . ' AND r.auth_setting IS NULL)
					OR r.auth_setting = ' . ACL_NEVER . ')
				AND a.group_id = ug.group_id
				AND g.group_id = ug.group_id
				AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
				AND ' . $db->sql_in_set('ug.user_id', $ug_id_ary) . "
				AND ug.user_pending = 0
				AND o.auth_option " . $db->sql_like_expression('m_' . $db->any_char),

You should be able to find at least the first line. If the following lines don't match the rest of this, please post them here so we can see what's going on with that.

If the file looks okay, then I would suggest looking at your usergroup, moderator, and permissions set up. Do you have a lot of moderators or groups? Are your moderators' permissions set up on their individual accounts or are they in a usergroup that has moderator permissions?

[Stephen25]

Thanks a lot for your reply. I still use 3.0.8 and the lines in functions_admin.php are:

Code: Select all

			'WHERE'		=> '(o.auth_option_id = a.auth_option_id OR o.auth_option_id = r.auth_option_id)
				AND ((a.auth_setting = ' . ACL_NEVER . ' AND r.auth_setting IS NULL)
					OR r.auth_setting = ' . ACL_NEVER . ')
				AND a.group_id = ug.group_id
				AND g.group_id = ug.group_id
				AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
				AND ' . $db->sql_in_set('ug.user_id', $ug_id_ary) . "
				AND ug.user_pending = 0
				AND o.auth_option " . $db->sql_like_expression('m_' . $db->any_char),
		));
		$result = $db->sql_query($sql);

I have about 8 groups and 10 moderators, they are in a usergroup that has (global) moderator permissions.

[Thoul]

Ok, that code looks correct, so that's good. The moderator set up sounds optimized as well.

At this point the problem getting beyond my ability to diagnose well, but I would suggest double checking the database tables listed in the query to make sure they all have their proper indexes set. The lack of an index on a table can cause queries to be slow by requiring them to read too much data. You can't check this through phpBB; you'll need a database management tool like phpMyAdmin. The tables should have indexes on these columns:

phpbb_acl_options: auth_option_id, auth_option
phpbb_user_group: group_id, user_id, group_leader
phpbb_groups: group_id, group_legend, group_name
phpbb_acl_groups: group_id, auth_option_id, auth_role_id
phpbb_acl_roles_data: auth_option_id, role_id

[Stephen25]

This question may sound dumb... I do have phpMyAdmin & MySQLDumper, but I don't know how / where to check if the proper indexes are set or are missing.

[Thoul]

Just pull up the Structure page for each table in phpMyAdmin. All of the indexes set on a table will be listed near the bottom of that page.