[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-25 16:04) wrote:

> What about adding a composite index on history_action, object_uid?  
> Since mysql uses the index that returns the least number of rows,  
> this should improve performance when the modseq range is extremely  
> high.

I tried a few different indexes with different modseq ranges for this  
query on an idle mysql server:

                                                 700k    120k    30k
1 history_modseq                                1.61    0.34    0.08
2 history_action                                2.25    2.16    2.13
3 object_uid                                    0.62    0.65    0.64
4 history_action, object_uid                    0.60    0.43    0.42
5 history_action, history_modseq, object_uid    0.50    0.13    0.02
6 history_modseq, object_uid                    0.39    0.13    0.02

This was order of the preferred indexes on a mysql 5.6 server:
700k: 4, 2
120k: 5, 4, 2
  30k: 5, 1

The history_action index seems to cause more problems than it helps,  
but it might be useful for other queries. The fasted index was never  
selected, so we will probably patch our local Horde installation to  
force the last index for these queries.

It would still be nice to prevent these long modseq ranges. Would it  
be possible to use a specific UPDATE query that only changes the  
modseq if every other field is unchanged and the sync_timestamp is a  
week in the past or could that still lead to data loss?