Kobo sync fails on larger shelves

[bent210]

What went wrong?

Kobo sync fails for shelves of more than a few hundred books on slower systems. With 2,051 books on the
Kobo shelf the snapshot takes ~48 s to build, the device gives up after ~45–50 s and closes
the connection, and the next attempt starts from scratch. The user sees only
"Sync failed. Please check your Wi-Fi and try again", which points in entirely the wrong
direction.

Edit from @imnotjames: Cleaned up the what went wrong at least, everything after this is LLM

---

Root cause

application.yaml asks for batching:

jdbc:
  batch_size: 100
  order_inserts: true

but the entity defeats it:

// KoboSnapshotBookEntity.java
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

Hibernate cannot batch inserts for IDENTITY-generated ids — it has to execute each statement
immediately to read back the generated key — and it disables batching for such entities
without warning. So batch_size: 100 has no effect here, and
KoboLibrarySnapshotService.create() performs one round trip per book.

Observed behaviour

Shelf with 2,051 books, two consecutive attempts:

20:16:07  snapshot created (2,051 books)
20:16:09  SQL_SLOW: insert into kobo_library_snapshot_book (...)   647 ms
20:16:12  SQL_SLOW: ...                                           1293 ms
20:16:13  SQL_SLOW: ...                                            978 ms
20:16:14  SQL_SLOW: ...                                           1102 ms
20:16:16  SQL_SLOW: ...                                           1469 ms
20:16:17  SQL_SLOW: ...                                            637 ms
20:16:18  SQL_SLOW: ...                                            739 ms
20:16:19  SQL_SLOW: ...                                            909 ms
20:16:20  SQL_SLOW: ...                                            617 ms
20:16:52  (device retries: second snapshot created)
20:16:55  Request was canceled by client: ServletResponse failed to flushBuffer:
          java.io.IOException: Broken pipe
20:17:26  Broken pipe (second attempt)

Both snapshots ended up with all 2,051 rows written and only 161 entitlements marked
synced — i.e. the server finished the work, but far too late for the client.

• 2,051 rows in roughly 48 s ≈ 23 ms per row on average (the lines above are only the
  outliers above the 500 ms log_slow_query threshold).
• The same shelf reduced to 61 books syncs cleanly: 61 of 61 delivered, no errors.
• With batch_size: 100 actually in effect this would be ~21 round trips instead of 2,051.

The database is on a Synology NAS (MariaDB 11.4, --log-bin=binlog --binlog-format=ROW), so
per-statement cost is higher than on a developer machine — but the number of statements is the
part that scales with the shelf, and that is what makes the difference between "works" and
"cannot sync at all".

Suggested fixes

1. Let the inserts batch. Replace GenerationType.IDENTITY with a sequence/table
   generator using a pooled optimizer, so the configured batch_size: 100 applies.
2. Or drop the surrogate key altogether. The table already carries a natural unique key,
   uq_snapshot_book (snapshot_id, book_id). Without a generated id the snapshot could even be
   built with a single INSERT ... SELECT on the server.
3. Or bypass JPA for this one write with a batched JDBC insert.
4. Independently: consider capping or streaming snapshot creation so a large shelf degrades
   into a slow-but-successful paged sync instead of a failure loop. Right now every retry
   rebuilds the whole snapshot and leaves the previous one behind (see below).

KoboDeletedBookProgressEntity is written with saveAll from
KoboLibrarySnapshotService.getRemovedBooks() and its table also uses an AUTO_INCREMENT id, so
it likely has the same limitation on shelves with many removals.

Side effect: orphaned snapshots

KoboLibrarySyncService only deletes the previous snapshot when a sync completes
(prevSnapshot.ifPresent(sp -> koboLibrarySnapshotService.deleteById(sp.getId()))). Every
failed attempt therefore leaves a full snapshot behind. After the two failures above, the
database held 4,163 rows in kobo_library_snapshot_book of which 4,102 belonged to snapshots
no client would ever reference again. Nothing cleans these up.

Environment

• Grimmory v3.3.3, Java 25.0.4, official Docker image
• MariaDB 11.4 on Synology DSM
• Kobo Libra Colour, firmware 4.x
• 35,091 books; Kobo shelf 2,051 (fails) vs 61 (works)

[bent210]

Follow-up: measured the per-book cost, and what batching would unlock

I instrumented the reverse proxy in front of Grimmory ($request_time per request) and worked
the shelf size upwards until the sync succeeded. Numbers from a shelf of 1,251 books, which
syncs cleanly (1,251 of 1,251 delivered):

request	duration
first /v1/library/sync — creates the snapshot	25.7 s
the seven following pages (100 entitlements each)	6.2 – 7.5 s

That is 20.5 ms per book, against 23 ms per book derived from the failing 2,051-book run —
essentially the same rate.

The paging works fine. Once the snapshot exists, each subsequent page is quick, and the
device is happy to make eight round trips. The whole problem is the first request, which has
to build the entire snapshot before it can answer.

Observed device tolerance: 25.7 s succeeds, ~48 s does not. So the ceiling today sits somewhere
around 1,300–1,900 books on the Kobo shelf.

This is round-trip bound, not I/O bound

Worth ruling out the obvious "tune your database" answer: my MariaDB had
innodb_buffer_pool_size at the 128 MB default while the database is 141 MB, so InnoDB was
evicting pages constantly. Raising it to 1 GB (and innodb_io_capacity 200 → 1000) removed the
slow-query outliers completely — there was not a single SQL_SLOW line during the 1,251-book
snapshot build, where the 2,051-book run had logged inserts of 600–1,470 ms.

The per-book rate barely moved: 23 ms → 20.5 ms. Tuning the database does not fix this; only
reducing the number of statements does.

What batching would unlock

Because the entitlement list is lazy, a large shelf is genuinely useful — the device creates
tiles and only downloads what the user opens. I measured this at three shelf sizes:

shelf size	new entitlements	books actually downloaded
61	56	5
552	491	5
1,251	699	5

Constant, regardless of shelf size. Covers are not a blocker either: 509 thumbnail requests
during the 1,251-book sync, all HTTP 200, 86.4 MB total, 174 KB average, roughly two per second
— extrapolating to my full 27,030 EPUBs that is about 4.5 GB spread over a few hours, one-off
and resumable.

So the only thing standing between a user and "my whole library as a browsable, searchable
catalogue on the device, downloading on demand" is the snapshot build:

27,030 books × 20.5 ms ≈ 9 minutes — in a single request that must finish in under a minute

With batch_size: 100 actually in effect that becomes ~270 statements instead of 27,030. This
is not just a fix for an error message; it turns the Kobo integration from "a shelf of a few
hundred hand-picked books" into "the whole library, on the device". That seems worth more than
the bug report suggested on its own.

Happy to re-measure on a build with any of the three suggested changes.