Partial fix [performance] Performance slow when adding a nodes with large number of nodes in system

Mike Tougeron

Well-known member
When there are a large number of nodes in the system, in this case > 500, adding a new node to an active system can be very slow and frequently timeout before the node is fully created.

From what I can tell, it will create the record in xf_node but it won't properly create the permissions cache and other necessary data so the system doesn't recognize it as valid.
 
This time I actually got an error so I'm able to give a bit more info...
Code:
Server Error
 
Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
 
Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 320
Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 634
Zend_Db_Adapter_Abstract->update() in XenForo/Model/Permission.php at line 1513
XenForo_Model_Permission->rebuildPermissionCombination() in XenForo/Model/Permission.php at line 1453
XenForo_Model_Permission->rebuildPermissionCache() in XenForo/Model/Permission.php at line 1155
XenForo_Model_Permission->_updatePermissionsForUserCollection() in XenForo/Model/Permission.php at line 1061
XenForo_Model_Permission->updateContentPermissionsForUserCollection() in XenForo/ControllerAdmin/Permission/Node.php at line 257
XenForo_ControllerAdmin_Permission_Node->_setPermissionRevokeStatus() in XenForo/ControllerAdmin/Permission/Node.php at line 76
XenForo_ControllerAdmin_Permission_Node->actionNodeWideRevoke() in XenForo/FrontController.php at line 310
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
XenForo_FrontController->run() in xenforo/admin.php at line 72
 
Long rebuilds are a known design issue when you have lots of nodes / groups.

If you have root access to the server then you can edit the my.cnf file (usually at /etc/my.cnf) to increase the value of innodb_lock_wait_timeout. Increasing that limit should avoid this error. If you don't have root access then you need to ask your host or server person to do this.
 
Long rebuilds are a known design issue when you have lots of nodes / groups.
Yeah, I just didn't see a report for it so I figured I'd make one.

If you have root access to the server then you can edit the my.cnf file (usually at /etc/my.cnf) to increase the value of innodb_lock_wait_timeout. Increasing that limit should avoid this error. If you don't have root access then you need to ask your host or server person to do this.
This is already pretty high for us in production. My work-around for now is to manually update the db via a script. :(
 
Is that because your nodes are mostly "pages"? Maybe another reason why we need better page management and possibly it's own system outside of a "node".
 
The number of "permission combinations" you have (see the table by that name in the DB) has a big effect on this, though the number of nodes obviously applies too.

To some degree, there's only so much we can do about this, as we need to do rebuilding when this happens. Obviously, if I can manage to speed up the process, I will.

But really, the better approach (if possible) will be to try to break up the rebuild process (like with templates, etc) so that we can be sure it succeeds. Have some ideas for 1.2 for this.
 
I was thinking about this on the train ride in this morning... What if it only rebuilt the perm cache only when the perms actually changed? Right now it always does it but, at least for us, it is rarely (if at all) different.
 
I was thinking about this on the train ride in this morning... What if it only rebuilt the perm cache only when the perms actually changed? Right now it always does it but, at least for us, it is rarely (if at all) different.
The permissions do change when you create a node - we cache the final permissions for every node. That said, it is naive as to how much it rebuilds (all of it vs just what is changing).
 
For reference, this is partially fixed in 1.2. There have been some improvements on permission rebuilding in 1.2, though not a fundamental change to the approach. Some superfluous tables/builds have been removed and the full permission build that needs to be done in some situations will automatically be run in parts if needed.

Further changes probably wouldn't happen before 2.0 unfortunately, though I do have various ideas of things to change.
 
For reference, this is partially fixed in 1.2. There have been some improvements on permission rebuilding in 1.2, though not a fundamental change to the approach. Some superfluous tables/builds have been removed and the full permission build that needs to be done in some situations will automatically be run in parts if needed.

Further changes probably wouldn't happen before 2.0 unfortunately, though I do have various ideas of things to change.

2.0... how bad is the current process?
 
Top Bottom