RFC: Email sync performance — heartbeat loop state reloads, SyncCache writes, export batching

[TDannhauer]

Summary

An analysis of the email sync path (Horde_ActiveSync + Horde_Core_ActiveSync_Driver) identified several structural performance bottlenecks. This issue documents the findings and a proposed, robustness-first optimization plan before implementation starts. Review would be appreciated, especially of the database-related changes — pinging @ralflang.

Observations (production deployment, Dovecot backend, device with ~80 pinged mail collections)

During every PING/hanging-SYNC poll iteration (default 15 s wait interval), per device:

1. Collections::pollForChanges() calls initCollectionState() → State_Sql::loadState() for every collection, each doing: collection lock (tables() + SELECT ... FOR UPDATE), _gc() (including device-wide SELECT DISTINCT sync_key over the map/mailmap tables), and unserialize of the full folder UID blob from sync_data.
2. Imap_Adapter::ping() issues one STATUS with STATUS_FORCE_REFRESH per folder — ~80 IMAP round trips per iteration.
3. The SyncCache blob (all collections + folder map) is fully unserialized 2–3x and fully rewritten per save; State_Sql::saveSyncCache() ignores its $dirty parameter (the Mongo backend honors it) and logs the entire serialized blob at meta level.
4. During actual message export, Exporter_Sync fetches one message per Driver::getMessage() call — a structure/envelope FETCH plus a separate body FETCH per message, so a 100-message window costs ~200 sequential IMAP round trips.

With two concurrent heartbeat requests (PING + hanging SYNC) this doubles. Net effect: the server spends most of its time re-proving "no changes".

Proposed changes (execution order = ascending risk)

1. Batched folder status: once per poll iteration, prefetch fresh status for all pinged mail folders via a single Horde_Imap_Client_Base::status(array, ...) call, which uses one LIST-STATUS (RFC 5819) round trip where advertised; ping() consumes the prefetched entry. STATUS_FORCE_REFRESH semantics are kept — data is fresh from the server each iteration, so changes from other clients are still detected. Non-LIST-STATUS servers keep current per-mailbox behavior.
2. SyncCache dirty writes (DB review focus): implement the $dirty contract in State_Sql::saveSyncCache() (field-level merge, skip write when clean), mirroring the Mongo backend. Replace the SELECT count(*) + UPDATE/INSERT with the portable UPDATE-first pattern (INSERT only when 0 rows affected), since Horde_Db (MySQL/PostgreSQL/SQLite/Oracle) has no portable upsert. Stop logging the full blob.
3. Poll-loop state reuse (DB review focus): skip initCollectionState() when the refreshed cache shows an unchanged synckey; add an explicit read-only ping-path state load without collection lock, without FOR UPDATE, and without _gc() (locks/GC remain on the mutating SYNC path). Safety invariant: any anomaly (cache validation failure, stale request, load error) forces a full reload — worst case degrades to current behavior.
4. Export batching: prefetch bounded message batches (10–25 UIDs) via the existing multi-UID Imap_Adapter::getMessages() instead of one fetch per change, with per-message error isolation and single-UID fallback. WBXML streaming, windowing, MOREAVAILABLE, and heartbeat behavior unchanged.

Deferred (higher structural risk, smaller returns)

• Watermark-only PING saves / shrinking the sync_data UID blob (serialization format change).
• Composite indexes for horde_activesync_state, horde_activesync_mailmap, horde_activesync_cache (schema migration).
• Structure+body FETCH merge, dirty-only device saves, maillog Message-ID search batching.

Design principles

• Never trade change detection for speed; every optimization degrades to current behavior on any doubt.
• Explicit fallbacks per item; capability-gated paths keep current code as fallback.
• One item per commit with tests, verified against the package PHPUnit suite and live device logs before the next lands.

Review questions (mainly DB)

1. Is the UPDATE-first (INSERT on 0 affected rows) pattern acceptable for saveSyncCache() across the supported Horde_Db backends, or is there a preferred Horde idiom?
2. Any objection to a lock-free/GC-free read-only state load for the PING poll path? The current lock acquisition on a read-only peek serializes against parallel SYNCs; dropping it should only ever delay detection by one iteration.
3. Was the device-wide (not folder-scoped) SELECT DISTINCT sync_key in _gc() intentional, or would folder-scoping it be safe?
4. Would field-level $dirty handling in the SQL SyncCache backend be accepted upstream, given Mongo already implements it?

[TDannhauer]

please review @ralflang

[TDannhauer]

Implementation is up for review: #91 (library) and horde/Core#212 (driver hooks). Design and invariants are documented in doc/sync-performance.md in the library PR. @ralflang — the SQL query changes (State_Sql::saveSyncCache() merge and the read-only _loadState() path) are in #91.

[SebastianArcus]

The sync is faster now - but still quite slow to download large mail folders in the Gmail app. Would forcing truncation from the server end be an option - or not really a feasible solution?

[TDannhauer]

We can investigate later.
Purpose of the speed optimization change you tested is to eliminate unnecessary client server round trips and queries. It reduces server load a lot and speeds up moderately as well.

[TDannhauer]

Btw what is your definition of large? Is it a folder with trillions of mails in it, or is large defined by the message sizes being extraordinary big?

[SebastianArcus]

The inbox on the account I am testing against contains 75k emails and 92GB in total. It's a pretty good size I would say. But I am only downloading to the phone either the last week or last month worth of emails (depending on the test I run). Gmail does download the messages faster than before - but still much slower overall compared to Mail in iOS. As far as I can tell, this is mainly down to the fact that Gmail requests 400x more data than iOS Mail. Gmail requests 200 000 bytes per message, while iOS Mail only 500 bytes. That is, if my reading of the logs is correct. This also takes up a lot more email cache space on the mobile device.

[TDannhauer]

@SebastianArcus Thanks — your reading of the logs is correct. Those numbers are BodyPreference / TruncationSize (bytes of body per message on Sync), not WindowSize (message count). Truncation is a normal EAS feature: the client chooses how much body to pull during Sync; iOS asking for ~500 bytes vs Gmail asking for ~200000 is expected client policy, not a Horde bug.

Forcing a smaller size from the server is possible as an optional admin ceiling (same idea as maximumwindowsize: 0 = leave the client alone). Drafts are up here:

• Library: feat(activesync): force Sync TruncationSize override #96
• Config UI: feat(horde): add ActiveSync maximumtruncationsize config base#143

Please finish testing the draft / \Deleted work first (#95 for #94) so we can merge that. After it lands we will rebase the truncation PRs, and you can switch to that branch next to try a Sync-only cap (ItemOperations Fetch / opening a message stays uncapped so full bodies are still available on demand).

[SebastianArcus]

Absolutely - I realise that this is about using Horde to compensate for design limitations in the Gmail app's ActiveSync implementation. I didn't mean to suggest this is a Horde problem - sorry if it sounded like that. Come to think of it, much of the recent work and fixes on ActiveSync has been about getting around either the Gmail app weaknesses or what looks like outright bugs. And it is all very much appreciated.

It seems #94 and #95 are fixed now - so I can proceed with testing whatever is needed next.

[TDannhauer]

yes it is, but thats ok Horde does it on other spots as well. If you put the user experience in the front row that is a fitting outcome. the end user does not care where the "culprit" is, its about getting it solved. and in contrast to gmail I can change the code in horde ;)

I am waiting for the review and merge of the two open topics into framework_6_0, then i can rebase the truncation PR and you have a clean start. i'll give you a hint.