Details
-
Type:
Bug
-
Status:
Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 1.9.5
-
Fix Version/s: None
-
Component/s: Database SQL/XMLDB, Performance
-
Labels:None
-
Environment:SessionDB = On
-
Database:Any, MySQL
-
Affected Branches:MOODLE_19_STABLE
Description
Our server (100-200 online users) was been block with expiry-update requests in mdl_session2.
When we used MyISAM tables were locked, when we used InoDB requests worket too long.
When session DB switches on, database is overloaded with queries for expiry time update.
Every http request needs 'expiry' update and it needs db indexes update. If there are thousands records in mdl_session2, the indexes update take a long time.
If page includes some pictures etc the one page needs 30-50 http-requests and 30-50 expiry updates every time.
But it isn't necessary - there aren't any sense in every seconds expiry updates.
This is patch for lib/adodb/session/adodb-session2.php (method write()):
$timeNoUpdate = $conn->OffsetDate($lifetime/(24*3600)-60,$sysTimeStamp);
$sql = "UPDATE $table SET expiry = $expiry ,expireref=".$conn->Param('0').", modified = $sysTimeStamp WHERE $binary sesskey = ".$conn->Param('1')." AND expiry >= $sysTimeStamp AND expity < $timeNoUpdate";
It's adding "AND expity < $timeNoUpdate".
I have added not to the new session code in 2.0, thanks for the report