[Tickets #14368] Re: Improve EAS performance with large History tables

[noreply at bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: https://bugs.horde.org/ticket/14368
------------------------------------------------------------------------------
  Ticket             | 14368
  Updated By         | hannes.brunssen at ewetel.de
  Summary            | Improve EAS performance with large History tables
  Queue              | Synchronization
  Version            | FRAMEWORK_5_2
  Type               | Enhancement
  State              | Feedback
  Priority           | 1. Low
  Milestone          |
  Patch              |
  Owners             |
------------------------------------------------------------------------------


hannes.brunssen at ewetel.de (2016-05-23 08:01) wrote:

This query for example takes over 3 seconds to complete:

SELECT DISTINCT object_uid, history_id FROM horde_histories WHERE  
history_modseq > 956528 AND history_modseq <= 2191866 AND  
history_action = 'add' AND object_uid LIKE  
'kronolith:_J9sL_cb_Xuk0gpnaFfIGA9:%';

Our MySQL server uses the history_action index, but even if I force  
MySQL to use the object_uid index it's still over 1 second. The moqseq  
956528 is from April 2015. I hoped there is a way to prevent checking  
the same history entries every 15 seconds for a year, at least in  
these cases when a user doesn't use a collection.

>
> This confuses me. We filter the history query on the calendar(s)  
> being checked, so the current value of modSeq should be  
> inconsequential since the query will only return entries with higher  
> modSeq values AND matching the calendar. Could you clarify where you  
> see loq modSeq values increasing "the number of history entries that  
> has to be checked"?
>
>
>> These lookups can be reduced if we update the sync state from time to
>> time. This could be done if we override the getChanges method in
>> Horde_ActiveSync_State_Sql:
>>
>> public function getChanges(array $options = array())
>> {
>> 	parent::getChanges($options);
>> 	if ($this->_type == Horde_ActiveSync::REQUEST_TYPE_PING &&
>> 			empty($this->_changes) &&
>> 			$this->_lastSyncStamp < $this->_thisSyncStamp - 10000) {
>>
>> 		$this->_lastSyncStamp = $this->_thisSyncStamp;
>> 		$this->_type = Horde_ActiveSync::REQUEST_TYPE_SYNC;
>> 		$this->save();
>> 		$this->_type = Horde_ActiveSync::REQUEST_TYPE_PING;
>> 	}
>> 	return $this->_changes;
>> }
>>
>> The type change is a bit ugly, but the save method would clear the
>> sync_data otherwise. That could be changed in the save method.
>
> We can't change the request type. This would affect a number of  
> other things, including preventing mirroring client-sourced changes  
> back to the client and the handling of pending changes. It would  
> possible (though admittedly a very small chance)  that this could  
> overwrite any existing pending changes in the state. Basically this  
> changes the sync state without changing the synckey, which we cannot  
> do.
>
>> The update might only be needed on collections that support modseq,
>> but the connector is not available from the state. I don't know if it
>> would cause problems with mail folders, every 10000 seconds would be
>> 8 times per day which would be more than necessary.
>