Re: Solving table locking issues

[Steve Gurasich]

Hi Rick,

Thanks for the reply, I appreciate the discussion.

For my "main" table, instead of a summary I actually just copied the entire table. So the users are updating the live table, and the reports are coming off the backup table. This at least makes it so that normal users aren't contending with admins who are running reports, and negates any possibly expensive summary work (it only takes a few seconds to truncate the one table and copy the other), but it does leave admins contending with each other.

I also ended up switching to InnoDB on a few of the more heavily-hit tables. I figured that the performance benefits I've been hearing about MyISAM don't even matter if the tables are locked, but I've also seen some (limited) benchmarks that show InnoDB to do better than MyISAM in several situations (unfortunately the benchmarks only used SELECT). Since switching to InnoDB I haven't seen any stalled MySQL processes waiting for another process to finish.

For other situations, I've started using some basic data caching. One query in particular was giving the system problems. The system has user groups, so they're just groups of users. Currently there are 2 tables to list regular users in each group, and another table to list admins for the group. The query to get all of those seems to be one that was stalling quite a bit with MyISAM. Aside from the fact that it's actually 3 queries, since it's selecting based off the PK (id is an int PK), I was thinking it would go quick. I'm not sure how expensive unions are, but I was expecting it to move pretty quick. I've seen this query locking tables quite a bit though:

  SELECT COUNT(*) AS `num` FROM (
  SELECT `username` FROM `users` WHERE `id` IN (SELECT `uid` FROM `autoassigned_users` WHERE `ugid`={$id})
  UNION
  SELECT `username` FROM `users` WHERE `id` IN (SELECT `uid` FROM `usergroup_users` WHERE `ugid`={$id})
  UNION
  SELECT `username` FROM `users` WHERE `id` IN (SELECT `uid` FROM `usergroup_admins` WHERE `ugid`={$id})
  ) AS u

One thing I can immediately see there is to create another table which will join the information from these tables. So instead of 3 tables with a union, there could be another table that lists all users from the other tables, and then that query would only need to look in 1 place. There was another situation where I did something like that. Users get assigned content, and content also can go in groups. A single user could have content directly assigned to them, or an entire content group assigned. A user belonging to a user group could also have content or content groups assigned to their entire user group. So instead of looking at 4 tables to figure out what a user has access to, I created a fifth table that just has user ID and content ID columns, and the system looks there to figure out who gets what. When an admin changes the assignments it makes the changes in the correct table out of the other 4, and then updates the assignment cache table for that user. It sounds like a similar situation for user groups might be the right thing to do.

Other things aren't so clear though. Another query causing problems is ironically another COUNT query, similar to the above. When a user group admin logs in and clicks on Users, they see a list of all users in all of their user groups and user group children (user groups are nested). The query to count how many users there are in order for the interface to show paging controls is another sticking point. That's what I've taken to caching, both in another table and in the user's session, since I'm pretty sure the number of users in a group isn't going to change very often. It's a problem to create a summary table there to list the counts for each user group (or even store those in the user group table) because of all the options. A UG admin can see 3 user types, each either active or inactive, so there would need to be 6 counters just for unfiltered users for each group - i.e. a counter for active students, active instructors, active admins, inactive students, etc. I've seen about a 40% cache hit rate since I've added that, so it's definitely helping some. The main problem with counting how many users are in a certain data set is the nested user groups. I have to use a recursive PHP function to keep sending queries for each descendant group, so that gets a little expensive. For example, here's my function to get all user IDs that a given UG admin covers:

function get_ugadmin_uids($ugadmin)
{
  global $db;
  $ugadmin = intval($ugadmin);
  $retval = array();

  if ($ugadmin == 0)
    return $retval;
    
  $ugids = get_ugadmin_ugids($ugadmin);
  if (count($ugids) == 0)
    return $retval;

  $db->sql('SELECT uid FROM usergroup_users WHERE ugid IN ('.implode(',', $ugids).') UNION SELECT uid FROM autoassigned_users WHERE ugid IN ('.implode(',', $ugids).') UNION SELECT uid FROM usergroup_admins WHERE ugid IN ('.implode(',', $ugids).')');
  $uids = $db->select();

  foreach ($uids as $uid)
    $retval[] = $uid['uid'];

  return $retval;
}

You can see that calls another function to get the list of user group IDs that the admin covers:

function get_ugadmin_ugids($ugadmin)
{
  global $db;
  $ugadmin = intval($ugadmin);
  $retval = array();
  
  if ($ugadmin == 0)
    return $retval;
    
  $db->sql('SELECT ugid FROM usergroup_admins WHERE uid=%d');
  $db->add_param($ugadmin, false);
  $ugids = $db->select();
  foreach ($ugids as $ug)
  {
    $retval[] = $ug['ugid'];
    $retval = array_merge($retval, find_usergroup_children($ug['ugid']));
  }

  return $retval;
}

That uses a helper function to find a user group's descendents, which is also recursive:

function find_usergroup_children($ugid)
{
  global $db;
  
  $retval = array();

  $db->sql('SELECT id FROM usergroups WHERE parent=%d');
  $db->add_param($ugid, false);
  $res = $db->select();
  foreach ($res as $r)
  {
    $retval[] = $r['id'];
    $retval = array_merge($retval, find_usergroup_children($r['id']));
  }

  return $retval;
}

While doing it this way makes perfect sense (to me at least), this isn't as efficient as it could be. A temporary solution might be to implement data caching here as well, but could I replace these functions with a stored procedure or something else to do the work a little more quickly?



Edited 1 time(s). Last edit at 02/17/2009 03:32PM by Steve Gurasich.