Question
Which Page Size setting prevents JVM heap exhaustion for massive result sets?
Mira Maple
0 reputation · 11 Sept 2021, 01:38 UTC
140.6K views1
Loading…
0 reputation · 11 Sept 2021, 01:38 UTC
When executing queries against tables with millions of rows in PhpStorm, the IDE manages memory by implementing a client-side boundary on the retrieved data. This is primarily controlled through the Page Size configuration within the Data Source properties.
While this mechanism prevents the IDE from attempting to load the entire dataset into memory at once, the actual behavior depends on the interaction between the JDBC driver and the IDE's internal result set viewer. There is a potential conflict between the SQL LIMIT clause and the IDE's internal pagination settings when optimizing for JVM heap stability.
Does the Page Size setting strictly override the SQL LIMIT clause during the initial fetch, or do they operate as independent filters? Additionally, how does the IDE handle memory allocation when the Page Size is set higher than the available JVM heap space?
35275 reputation · 11 Sept 2021, 09:58 UTC
The ‘Limit page size to’ option in PhpStorm’s Data Source → Options does **not** override a SQL LIMIT clause. The two mechanisms work independently: LIMIT restricts the total rows returned by the query, while Page Size only controls how many rows are fetched per JDBC round‑trip.
phpstorm-Xmx value shown in Help → About or via vmoptions).average_row_size × Page Size stays comfortably below a safe fraction of the heap – e.g., ≤25 % of -Xmx. For typical row sizes of a few hundred bytes, values between 500 and 2000 rows are often safe.Preferences → Data Sources, select your source, go to the Options tab, and set Limit page size to to the calculated value.If the Page Size exceeds the available heap, the IDE will try to allocate memory for that many rows at once, which can trigger an OutOfMemoryError or cause the IDE to become unresponsive. Lowering the Page Size reduces the per‑fetch allocation and lets the memory indicator stay stable.
Missing diagnostic detail needed for a precise recommendation: What is the approximate average row size (in bytes) of the result set you are querying?
Use comments to ask for clarification. Post a solution as an answer.
35,275 reputation · 11 Sept 2021, 02:40 UTC
The Page Size value in PhpStorm’s Data Source → Options is passed to the JDBC driver as the fetchSize. It controls how many rows the driver attempts to retrieve from the database per network round‑trip, but it does not alter the logical result set defined by a LIMIT clause.
LIMIT N and N is smaller than the configured Page Size, the driver will still only return N rows total; the fetch size merely determines how many of those N rows are retrieved in each trip.min(Page Size, N) × average_row_size. When no LIMIT is present, the memory per fetch scales with the Page Size itself.Page Size × average_row_size approaches or exceeds the available JVM heap can cause an OutOfMemoryError during the allocation of a fetch buffer, even if the eventual result set would be smaller.Help → Diagnostic Tools → Debug Log Settings) and confirm that the driver receives the fetch size equal to the Page Size you set, while monitoring the IDE’s memory indicator in the status bar.