Re: Hypothesis Verification - Moqui EntityListIterator & MySQL Streaming

Technical Discussion
#1

Hi everyone,

We conducted detailed empirical tests to verify the hypothesis in hypothesis-moqui-iterator-mysql-streaming.md regarding whether Moqui’s EntityListIterator automatically streams data and keeps memory low on MySQL.

We tested on the NotificationMessage entity (1,409,062 rows, ~1 GB data) using services defined in [ExampleServices.xml]. Here are the test cases and findings:

1. Moqui Default Setup Handles This Out-of-the-Box

Moqui framework authors were already aware of MySQL’s JDBC driver limitation. In Moqui’s default database configuration [MoquiDefaultConf.xml], Moqui explicitly sets useCursorFetch="true" by default for MySQL (mysql and mysql8 definitions).

2. Test Case 1: EntityListIterator vs .list() (Heap Memory)

  • With useCursorFetch=true + EntityListIterator: Iterator initialization heap delta was 0.004 MB (memory stayed flat as data streamed).
  • With Moqui .list(): Heap memory spiked by 2,114.55 MB (~2.1 GB) as all 1.4M rows loaded into memory.
  • With useCursorFetch=false + EntityListIterator: Heap memory spiked immediately to 1,622.14 MB (~1.6 GB) on iterator initialization.

Finding: Without useCursorFetch=true, MySQL JDBC driver buffers all rows into JVM memory at once, even when using EntityListIterator.

3. Test Case 2: Raw JDBC Driver Behavior (Positive vs Negative fetchSize)

Direct raw JDBC queries demonstrated how MySQL Connector/J handles fetchSize:

Case fetchSize Query Execution Time 50k Rows Iteration Time Behavior
Run 1 20000 (Positive) 5,365 ms 9 ms Buffered full 1.4M rows during query execution.
Run 2 Integer.MIN_VALUE 7 ms (Instant) 144 ms True row-by-row streaming over network.
Run 3 0 (Zero/Default) 5,000+ ms 9 ms Buffered full 1.4M rows (same as positive size).

Finding: On standard MySQL connections (useCursorFetch=false), MySQL driver ignores positive fetchSize values. Only Integer.MIN_VALUE forces row-by-row streaming.

4. Test Case 3: Moqui API Coercion Rule

We tested passing Integer.MIN_VALUE into Moqui’s Entity Find API (ec.entity.find().fetchSize(Integer.MIN_VALUE)).

In Moqui’s [EntityFindBuilder.java]:

if (fetchSize != null && fetchSize > 0) { 
    ps.setFetchSize(fetchSize); 
} else { 
    ps.setFetchSize(100); // Coerces negative values to 100
}

Finding: Moqui’s framework automatically resets non-positive values to 100. Therefore, negative fetchSize streaming cannot be triggered through Moqui API; it relies on useCursorFetch="true" in the datasource config.

5. Test Case 4: Impact of fetchSize Value

Even with useCursorFetch=true, choosing a reasonable fetchSize is necessary:

  • Too Small (fetchSize = 10): Requires 20,000 network roundtrips (200k / 10), causing latency and service transaction timeouts.
  • Moderate/Large (fetchSize = 1000 to 20000): Reduces roundtrips significantly, completing execution quickly without transaction timeout.

Summary

The hypothesis that EntityListIterator alone automatically streams data on MySQL is incomplete. Streaming works because Moqui configures useCursorFetch="true" by default in MoquiDefaultConf.xml. If this flag is turned off, EntityListIterator will buffer the full result set into memory.