Project

General

Profile

Wiki » Documentation » Installation Configuration and Administration Guide »

Database Tuning

Introduction

This page covers what can be changed outside the application code to make database access faster: directory configuration, connection pooling, caching, temporary database strategy, commit durability, schema physical design and database server parameters. The companion page 4GL Database Access Performance Tips covers the other half, the 4GL constructs themselves, and the two are meant to be read together. A configuration change cannot repair a query that issues one statement per record; equally, a well written application still runs slowly against an undersized pool or a fully durable commit on every transaction.

Tuning proceeds in this order, cheapest and highest yield first.

  1. Commit durability. Every write transaction that waits for a disk flush pays a latency the application cannot amortise. This is one setting, and on PostgreSQL FWD already defaults it favourably. See "Commit and Durability":#commit.
  2. Round trips per logical operation. FWD does not share memory with the database server the way a 4GL client shares memory with an OpenEdge broker, so statement count dominates. Configuration influences this through prefetching and caching; the application influences it far more. See "Prefetching and Paging":#prefetch and "Caches":#caches.
  3. Contention. Private temporary databases, dirty share settings and pool sizing decide how much time sessions spend waiting for each other rather than for the database. See "Temporary Databases":#temp and "Concurrency":#concurrency.
  4. Work performed per statement. Index inventory, collation, computed columns and user defined function language decide whether a predicate is answered from an index. See "Schema and Physical Design":#schema.
  5. Database server resources. Buffer pool, working memory, statistics and checkpoint behaviour. See "Database Server Parameters":#server.

Directory paths on this page are written relative to the server node, so persistence/private-temp-dbs means /server/SERVERID/persistence/private-temp-dbs, and database/DBNAME/orm/c3p0/maxpoolsize means /server/SERVERID/database/DBNAME/orm/c3p0/maxpoolsize. Node syntax is described in Directory Configuration Reference.

Instruments

Change one setting at a time and measure it. Most of the knobs below trade one resource for another, so the only way to know whether a change helped is to have measured before it. The instruments are documented elsewhere; what follows is which one answers which question.

Question Instrument Reference
Which queries run, how often, and for how long p2j-query-logging container, globally under persistence or per database 4GL Database Access Performance Tips
What SQL those queries became sql-logging container, same two locations 4GL Database Access Performance Tips
How many SQL statements were issued, aggregated persistence/query-counting, or per database, exposed as the QueryCounter MBean Monitoring
Where time goes inside the runtime rather than the database JMX timers, listed below Monitoring, Profiling
Which plan the server chose EXPLAIN against the logged SQL, prepared with bind parameters Using EXPLAIN to Analyze SQL Performance
Which indices are unused, and which queries have none Conversion reports Conversion Query Reports

The JMX timers worth watching when tuning configuration rather than code:

Timer Reads as
OrmHydrateRecord Cost of turning result rows into DMOs. Rising with no change in statement count points at wide retrievals or at cache misses.
OrmFqlParse, OrmDynQueryInterpret, OrmDynQueryProcess_* Query preparation cost. High values with a low cache hit rate mean the conversion caches are undersized or the query text varies per execution.
OrmTempTableQuery Temporary database work. Compare against "Temporary Databases":#temp.
TMCommit, TMRollback, TMValidate Transaction boundary cost. A large TMCommit is the signature of a durable commit; see "Asynchronous commit":#async.

Commit and Durability

Asynchronous commit

Leave asynchronous commit enabled unless the deployment has a stated durability requirement that forbids it. A fully durable commit returns only once the transaction's redo record has reached stable storage, which costs one disk flush per commit. An asynchronous commit returns as soon as the record is in the server's buffer, trading a bounded window of the most recent commits on an abrupt server crash for a large gain in write throughput.

On PostgreSQL, FWD sets synchronous_commit to off on every pooled connection as it is acquired, unless the database's startup-config names that parameter. This is deliberate, and it is not a departure from legacy behaviour: a default multi-user OpenEdge database also defers its before-image writes (-Mf 3), so PostgreSQL's own fully durable on default is the stricter of the two. Matching the legacy system is the reason for the FWD default.

What is lost in a crash is the transactions committed within the flush window, not database integrity: the cluster still recovers to a consistent state, and no transaction is partially applied. Three cases justify raising it:

  • a replicated cluster where a committed transaction must be known to have reached a standby, which is local or remote_write rather than on;
  • a regulatory or contractual requirement stating that an acknowledged commit is durable;
  • a deployment where the application itself acknowledges commits to an external system that cannot be replayed.

Override it per database through startup-config, described next.

Asynchronous commit is also enabled explicitly, and unconditionally, on the dedicated connection pool used by data import; see "Bulk Data Load":#import.

Per-database startup configuration

Use startup-config for any session scoped database parameter that should apply to every FWD connection. Each entry is a bare name = value configuration field. FWD validates it against a conservative per-dialect allow-list, prefixes it with SET, and executes it once on each physical connection as the pool acquires it. The statements are therefore session scoped: they affect only the acquiring connection and disappear when that connection is dropped.

The node is a string list on the database's p2j container:

<node class="container" name="database">
  <node class="container" name="hotel">
    <node class="container" name="p2j">
      <node class="strings" name="startup-config">
        <node-attribute name="values" value="synchronous_commit = local"/>
        <node-attribute name="values" value="work_mem = 32MB"/>
      </node>
    </node>
  </node>
</node>

The allow-list rejects statement separators, comment markers and unquoted special characters, so a malformed entry cannot chain a second statement. A field that fails validation, or fails at the server, is logged and skipped; the connection is still used. Check the server log after adding entries, because a silently skipped field looks exactly like a field that had no effect.

Fields worth setting this way, on PostgreSQL:

Field Effect
synchronous_commit Overrides the FWD default of off. See "Asynchronous commit":#async.
work_mem Memory for a sort or hash before it spills to disk. Raising it helps queries FWD could not delegate a sort for, at the cost of memory per concurrent operation.
statement_timeout Caps a runaway statement. A blunt instrument, but it converts a hung session into a diagnosable error.
jit Turning it off removes compilation latency from short queries, which is most of a converted application's traffic.
TimeZone Not a performance setting, but the same mechanism, and worth knowing about.

A connection customizer exists for PostgreSQL, H2, MariaDB and SQL Server. On H2 it is installed only when startup-config is non-empty, so configuring nothing costs nothing. On SQL Server the allow-list accepts that dialect's multi-word SET forms.

Durability on other dialects

The asynchronous commit tradeoff exists on every supported dialect, under a different name. FWD applies its own default only on PostgreSQL; elsewhere state the parameter through startup-config or in the server configuration.

Dialect Parameter
PostgreSQL synchronous_commit, defaulted to off by FWD
MariaDB innodb_flush_log_at_trx_commit, where 2 is the closest analogue of asynchronous commit
SQL Server Delayed durability, set at database or transaction scope
H2 Not applicable to an in-memory temporary database, which has no durability to trade

Temporary Databases

Private temporary databases

Keep private temporary databases enabled. FWD can back temp-tables either with one H2 database per session or with a single shared H2 database holding every session's tables. persistence/private-temp-dbs selects between them, and the private form is now the default.

<node class="boolean" name="private-temp-dbs">
  <node-attribute name="value" value="TRUE"/>
</node>

Three things change when the databases are private.

  • Locking is unnecessary. A private database is reached by exactly one session, so FWD opens it with LOCK_MODE=0. In the shared arrangement every temp-table operation contends with every other session's temp-table operations in the same H2 instance.
  • Initialization does not serialize. Preparing a per-session temporary database does not take a global lock, whereas the shared database must be prepared exactly once, safely, before any session proceeds.
  • Table name collisions cannot occur between sessions, which removes a class of correctness problem as well as the bookkeeping that avoided it.

The cost is memory: an H2 instance per session rather than one for the server. For an application that uses temp-tables lightly this is small; for one that materialises large temp-tables in many concurrent sessions it is the dominant term in heap sizing. See "Server and JVM":#jvm.

Setting the node to FALSE is a diagnostic step, not a tuning step. Do it to determine whether a problem is specific to the private arrangement, then set it back.

In memory or file backed

Leave temporary databases in memory. The JDBC URL prefix for temporary databases is jdbc:h2:mem:, overridable with the fwd.h2.mem system property. Pointing it at a file-backed URL puts every temp-table write through the filesystem, which is slower by orders of magnitude and buys nothing, because temp-table contents do not survive the session that created them. The override exists for diagnosis, in particular for inspecting a temporary database after the fact.

UNDO and NO-UNDO

Prefer NO-UNDO on temp-tables that do not need transaction semantics, and declare it in the 4GL rather than forcing it globally. An undoable temp-table records a reversible for every property change, record creation and record deletion, so that a block rollback can restore the state at block entry. A NO-UNDO table records nothing and rolls back by discarding the database transaction, re-executing the changes and committing a new one.

FWD treats temp-tables as undoable by default, matching the 4GL. persistence/force-no-undo-temp-tables overrides every temp-table in the application to NO-UNDO regardless of its declaration.

<node class="boolean" name="force-no-undo-temp-tables">
  <node-attribute name="value" value="TRUE"/>
</node>

This changes program semantics. An application that relies on a temp-table change being undone by an UNDO or by an error unwinding a block will behave differently, and the difference is silent. Use it when the application is known not to depend on temp-table undo, or as a measurement to find out how much undo tracking is costing before deciding whether to annotate the 4GL definitions individually.

What remains costly

Two temp-table costs are structural and no setting removes them.

A temporary database is a separate database, held in the JVM. A query joining a temp-table to a permanent table therefore cannot be delegated to the database server, and FWD executes the join itself, one statement per outer record against the permanent table. This is a design decision in the application, not a configuration matter; it is covered in 4GL Database Access Performance Tips.

A DataSet with change tracking maintains a before-table image alongside every row. Where the before-image is not read, the tracking is pure overhead.

The prepared statement cache for temporary database connections holds 8192 entries by default and is sized through the cache-size container against com.goldencode.p2j.persist.orm.TempTableDataSourceProvider. An application creating many distinct dynamic temp-tables can exhaust it; see "Caches":#caches.

Prefetching and Paging

The legacy-remote container

Enable prefetching for databases the legacy application reached over the network, and only for those. When a walk retrieves records one at a time, each record is a round trip. Prefetching retrieves a page of records per round trip instead, positioning each page by the sort key of the last record delivered rather than by a row offset.

The configuration deliberately describes the legacy connection rather than FWD's retrieval strategy, and the nodes carry the names an administrator of the legacy system would recognise. The reason is that paging reintroduces something the legacy system had: the client message buffer, a window of records already fetched and therefore opaque to other sessions' commits. A legacy session that reached its database through shared memory read the database's own buffer pool and had no such window. Enabling paging for those sessions invents a staleness window that never existed. Leaving it off where the legacy sessions were networked merely forgoes an optimisation. That asymmetry is why the feature is off until it is stated.

Node Default Effect
enabled FALSE Master switch. States that the legacy sessions performing the long walks reached this database over a network connection.
prefetch-num-recs 16 Records per page, and the size a walk returns to whenever another session commits. This is the granularity at which other sessions' commits become visible to a walk in progress. The default is the multi-user -prefetchNumRecs default through the OpenEdge 11.x line; later releases raised it to 64, and a system migrated from one of those should state 64 explicitly rather than inherit 16.
max-recs-per-message 1024 Ceiling on the records a single round trip may carry. It bounds the JDBC fetch size, so a page crosses the wire in one round trip until it grows past this. It is not the analogue of -Mm, which sizes the buffer in bytes and so cannot be carried across as a record count.
prefetch-growth-factor 8 Multiplier applied to each successive page, so a long walk amortises its round trips. A factor of 1 pins every page at the base size. This is not the legacy -prefetchFactor, which is a percentage.
max-prefetch-growth 4 Number of times a page may grow before the size is pinned. With the other defaults this gives 16, 128, 1024, 8192, 65536.

Each node resolves at two levels independently: a server-wide default under persistence/legacy-remote/, overridden per database under database/DBNAME/legacy-remote/. A deployment whose legacy databases were uniformly networked states it once; a mixed deployment overrides the databases that differ. A tenant's physical database inherits the configuration of its logical database.

<node class="container" name="persistence">
  <node class="container" name="legacy-remote">
    <node class="boolean" name="enabled">
      <node-attribute name="value" value="TRUE"/>
    </node>
    <node class="integer" name="prefetch-num-recs">
      <node-attribute name="value" value="64"/>
    </node>
  </node>
</node>

There is no system property or environment override; the directory is the only source of truth. Server-wide defaults resolve at startup, and a database's own values resolve on first use and are held for the life of the server.

Two properties of the implementation are worth knowing when interpreting a measurement.

  • Growth is surrendered on any foreign commit. From the moment another session's commit is observed, at most one base page of further records is delivered from cursors opened before it, and subsequent pages return to the base size and stay there for as long as other sessions keep committing. A write-heavy workload therefore sees much less benefit than a read-heavy one, and the correct conclusion is usually that the benefit is real but smaller, not that the setting is wrong.
  • The cursor is disposable. Because a page is located by sort key rather than by offset, it can be reopened from the values identifying the last record delivered. A transaction commit, which closes the ORM session underneath an open cursor, costs one reissued query rather than a discarded result set. Records inserted or deleted ahead of the cursor cannot shift the walk, so none is skipped or visited twice.

JDBC fetch size

Set persistence/jdbc-fetch-size; be aware that the per-database orm/jdbc/fetch_size node does not reach the query path.

persistence/jdbc-fetch-size is a single server-wide value, default 256, applied to every statement that does not carry its own hint. It is how many rows the driver buffers per round trip on a scrolled result, so raising it reduces round trips on large walks and increases the memory each cursor holds.

Paging supplies its own hint per page, bounded by max-recs-per-message, so where prefetching is enabled the paging configuration governs and this value applies to everything else.

Many existing directory files carry a database/DBNAME/orm/jdbc/fetch_size node, often set to 1024. That node is read into the ORM settings but is not consulted when a statement's fetch size is set. Treat it as inert, and do not expect a change there to have an effect.

Retrieval strategy

Configuration cannot change how a query retrieves its records; the 4GL construct does. Whether a query preselects its whole result set, walks a live cursor, or starts optimistic and falls back, is decided by the construct and by the runtime's own analysis. The settings above change how many records cross the wire per round trip, not which strategy is chosen. Retrieval strategy is described in QueryExecution, and the application-side consequences in 4GL Database Access Performance Tips.

Caches

The cache-size container

Size a cache only after establishing that it is missing. Every cache described here has a default that is adequate for most applications, and an oversized cache costs heap that the session record cache or the temporary databases would use better.

Cache sizes are configured under cache-size, one container per cache, each naming the class that owns it:

&lt;node class="container" name="cache-size"&gt;
  &lt;node class="container" name="dynamic-query-lvl1"&gt;
    &lt;node class="string" name="class-name"&gt;
      &lt;node-attribute name="value" value="com.goldencode.p2j.persist.DynamicQueryHelper"/&gt;
    &lt;/node&gt;
    &lt;node class="string" name="discriminator"&gt;
      &lt;node-attribute name="value" value="lvl1"/&gt;
    &lt;/node&gt;
    &lt;node class="integer" name="size"&gt;
      &lt;node-attribute name="value" value="131072"/&gt;
    &lt;/node&gt;
  &lt;/node&gt;
&lt;/node&gt;

The container name is arbitrary; class-name and size are required, and discriminator selects among several caches owned by the same class. A class-level entry with no discriminator applies to every cache of that class, except where the discriminator begins with !, which suppresses that fallback.

The container was formerly at persistence/cache-size. Entries there are still honoured, with a deprecation warning, and entries in the current location win. Move them. Full details are in Cache sizes.

Session record cache

This is the cache that matters most, and the one most often left at its default. Each session holds an LRU cache of the records it has loaded, keyed by record identifier, default 1024 entries per database. A hit avoids both a round trip and a hydration. The cache is configured against com.goldencode.p2j.persist.orm.Session with the database name as the discriminator, so each database can be sized separately.

Two properties shape how to size it. Its capacity policy is lenient, meaning a record still in use by a buffer is not evicted even when the cache is over capacity, so the effective size can exceed the configured one transiently. And the cost of each entry is the width of the record, so a table of a hundred columns makes the same entry count several times more expensive than a narrow one.

Raise it when OrmHydrateRecord is high and the query log shows the same records being read repeatedly within a session. Size the total, across every database and every concurrent session, against the heap; see "Server and JVM":#jvm.

Fast find cache

Leave the fast find cache at its defaults unless a specific find is known to repeat. It caches the record identifier a find resolved to, keyed in three levels: table, then index, then the query's FQL together with its navigation type and substitution parameters. Only the innermost level is bounded, at 10 entries per table for the second level and 100 for the third. The first two levels follow the database structure and are not limited.

The cache is invalidated aggressively, which is why the bounds are small: a record update invalidates the affected index, an insert or delete invalidates every index of the table, and a rollback invalidates every affected table. Enlarging it in a write-heavy workload enlarges what gets thrown away. Configure it against com.goldencode.p2j.persist.FastFindCache with discriminator L2 or L3.

Permanent-table entries are shared across sessions within one database and their access is synchronized; temporary-table entries are context-local.

Conversion and query caches

Raise these only when the application generates many distinct query texts, which in practice means heavy use of dynamic queries. They hold the result of turning query text into an executable form. A hit removes parsing, rule processing and SQL generation from the request; a miss pays all of it, which is what the OrmDynQueryProcess_* and OrmFqlParse timers measure.

Class Discriminator Default Holds
DynamicQueryHelper lvl1 65536 First-level dynamic query lookup
DynamicQueryHelper lvl2 16384 Second-level dynamic query lookup
DynamicValidationHelper 65536 Dynamic validation expressions
SortCriterion 65536 Parsed sort criteria
FQLPreprocessor ast 8192 Preprocessed FQL syntax trees
FQLPreprocessor !noArgs, !witArgs 2048 each Preprocessed FQL by argument shape
FQLPreprocessor 2048 Translated FQL
FqlToSqlConverter ast 8192 Generated SQL syntax trees
FQLHelperCache 8192 FQL helper results
AbstractQuery 2048 Query sort metadata
Persistence 1024 Static query definitions
Persister 4096 Generated update statements
DmoMetadataManager dynamicTables 16384 Dynamic table metadata, lenient policy
TemporaryBuffer 256 Fast-copy helpers for table-to-table copies
TempTableDataSourceProvider 8192 Prepared statements on temporary database connections

A dynamic query whose text varies per execution, for instance by embedding a literal value rather than using a substitution parameter, defeats all of these no matter how large they are. That is an application fix, not a configuration one.

Session reclaiming

Leave persistence/session-lifespan at its default unless connection churn is a measured problem. It is the number of uses after which an ORM session is reclaimed and rebuilt, default 1000. Reclaiming bounds the resources a long-lived session accumulates, at the cost of rebuilding it. A negative value disables reclaiming entirely, which is a diagnostic setting: it removes the rebuild cost and lets the accumulation be observed.

Connections

Pool sizing

Size the pool to the number of sessions that are concurrently executing SQL, not to the number of sessions. FWD pools JDBC connections per database with c3p0, configured on the database's orm/c3p0 container. Node names are lower case.

Node Effect
maxpoolsize Upper bound on connections to this database. The single most important value here.
minpoolsize Connections retained when idle. Set it to the steady-state concurrency so that normal traffic never waits for an acquire.
initialpoolsize Connections opened at startup. Usually equal to minpoolsize, which moves the connect cost out of the first requests.
acquireincrement Connections added per growth step. A larger value reduces the number of growth events under a ramp.
maxidletime Seconds before an idle connection above the minimum is dropped.
maxstatementsperconnection Prepared statements cached per connection. See "Statement caching":#stmt.
validate Test connections on checkout. Costs a round trip per acquire; prefer the health check described below. 
&lt;node class="container" name="c3p0"&gt;
  &lt;node class="integer" name="maxstatementsperconnection"&gt;
    &lt;node-attribute name="value" value="100"/&gt;
  &lt;/node&gt;
  &lt;node class="integer" name="initialpoolsize"&gt;
    &lt;node-attribute name="value" value="4"/&gt;
  &lt;/node&gt;
  &lt;node class="integer" name="minpoolsize"&gt;
    &lt;node-attribute name="value" value="4"/&gt;
  &lt;/node&gt;
  &lt;node class="integer" name="maxpoolsize"&gt;
    &lt;node-attribute name="value" value="20"/&gt;
  &lt;/node&gt;
  &lt;node class="integer" name="acquireincrement"&gt;
    &lt;node-attribute name="value" value="2"/&gt;
  &lt;/node&gt;
  &lt;node class="integer" name="maxidletime"&gt;
    &lt;node-attribute name="value" value="900"/&gt;
  &lt;/node&gt;
&lt;/node&gt;

An oversized pool is not free. Beyond the point where the database server saturates its CPUs or its disks, additional concurrent statements lengthen every statement rather than adding throughput, and each connection costs memory at the server. The useful maximum is generally a small multiple of the server's core count, not a function of the session count.

Total the pools before comparing them against the server's connection limit. The count is the sum over every connected database of its maxpoolsize, plus the dirty database of each, plus every tenant database. The dirty database inherits its primary's pool settings rather than falling back on the c3p0 defaults, so raising a primary's maxpoolsize silently raises its dirty database's too. The metadata database is an exception: its connections are handed out directly by the driver rather than by a pool, so pool settings there would be inert.

FWD forces c3p0's break-after-acquire-failure behaviour on, so a pool that cannot obtain a connection fails rather than retrying indefinitely.

Statement caching

Cache prepared statements per connection, and let the driver use server-side prepares. maxstatementsperconnection bounds the statements c3p0 caches on each connection. FWD issues a bounded set of statement texts for a given application, so a value covering that set removes the prepare from almost every execution; 100 is a typical starting point.

On PostgreSQL the JDBC driver additionally decides when to promote a statement to a server-side prepared statement, controlled by database/DBNAME/orm/connection/preparethreshold. A value of 1 promotes on first execution, which suits a pooled connection that will reuse the statement many times.

Startup, idle and health

Node Effect
database/DBNAME/p2j/load_at_startup Connect and initialize the database at server startup rather than on first use. This moves schema initialization, metadata registration and pool warm-up out of the first request that touches the database.
database/DBNAME/p2j/deactivate_if_not_used_sec Release a database's resources after this many seconds without use. Useful where many databases are defined and few are used in a given period; counterproductive where a database is used intermittently but latency-sensitive, since the next use pays reactivation.
persistence/health-check/enabled Periodically check both a pooled connection and a direct JDBC connection, so a database that has become unreachable is reported rather than discovered by a failing request.
persistence/health-check/interval Check interval. The minimum accepted is five minutes, which keeps the cost negligible.

Prefer the health check to c3p0's per-checkout validate: it detects the same failures at a fixed low cost instead of a round trip on every acquire.

Concurrency

Dirty share

Disable dirty share unless the application depends on reading other sessions' uncommitted changes. The 4GL allows one session to see another's uncommitted records. FWD reproduces this by maintaining a separate dirty database holding uncommitted state, and the bookkeeping is not cheap: recording an uncommitted insert takes an exclusive lock on every index of that table in the dirty database, so a table with many indices serialises inserts across sessions.

Node Default
persistence/dirty-cross-share FALSE
persistence/dirty-intra-share TRUE
persistence/force-dirty-cross-share FALSE
persistence/force-dirty-intra-share TRUE
persistence/dirty-share-global-notifications FALSE

Cross-share covers visibility between sessions; intra-share covers visibility between separate contexts of one session. Turning cross-share off removes the expensive case. The per-table dirty-read hint narrows the mechanism to the tables that need it, which is usually a better answer than a global switch. Semantics and the per-table hint are documented in Database Configuration.

This setting interacts directly with index inventory: every index dropped is one fewer index to lock. See "Indices":#indices.

Transaction scope and locking

A transaction's scope decides how long its locks are held, and lock hold time is what makes a contended workload slow. Transaction boundaries in FWD follow 4GL block scoping, so they are a property of the application rather than of configuration. The relevant observation for tuning is that no setting on this page reduces contention caused by a transaction that spans a user interaction or a long computation; the TMCommit and TMRollback timers together with the database server's own lock waits will show it.

Sub-transaction blocks establish savepoints, and a savepoint is not free. An application with deeply nested transaction blocks pays per block entry.

4GL locking semantics are in Chapter 28 Database Record Locking and transaction scoping in Transactions.

Schema and Physical Design

Indices

Drop what nothing uses, and add what the reports say is missing. Conversion produces unused_database_indices.txt and unindexed_queries.txt by static analysis over every query in the application, including paths testing never reaches. Both are actionable directly, and the first is the rarer opportunity: an unused index costs on every insert, update and delete of its table, and is one more index to lock when dirty share is active.

Index width matters beyond write cost. A FIND NEXT over a multi-component index decomposes into one statement per sort component plus one for the primary key, so a wide index makes sequential navigation more expensive, not less. Indices created only to make a sort deterministic are the usual source of excess width; a BY clause overlapping an existing compact index achieves the same thing. Both effects are worked through in 4GL Database Access Performance Tips.

persistence/max-index-size caps the total index key size FWD will accept, default 1971 bytes, matching the 4GL limit. It is a compatibility limit rather than a tuning knob; raise it only when a legitimately wide legacy index is being rejected.

CONTAINS strategy

Confirm which CONTAINS implementation is active before tuning a word index. On PostgreSQL, FWD can answer CONTAINS from word tables or from a user defined function, and can render the word-table form either as a common table expression or as an IN (SELECT ...) subquery. Three system properties select among them.

Property Default Effect
P2JPostgreSQLDialect.useUdf4Contains false true answers CONTAINS through a UDF instead of word tables
P2JPostgreSQLDialect.useCte4Contains true false renders the word-table lookup as a subquery rather than a CTE
P2JPostgreSQLDialect.useMixedMode4Contains true false uses the pure CTE form rather than the mixed one

These are system properties on the server JVM, not directory nodes. The word-table form is the default because it is index-backed; the UDF form exists for cases the word tables cannot express. Changing any of them changes the SQL, so re-run EXPLAIN afterwards rather than assuming which is faster.

Physical column design

Decision Effect
Extent fields expanded to one column per element, or held in a separate child table Expanded columns make whole-record retrieval a single row and are usually faster; a separate table avoids widening every row for a large extent
Computed columns for function-based indices A predicate over a function can use an index only if the function's result is materialised and indexed
Case-insensitive indices Rendered with upper() on the column, so the index must match; see Database Collation
Mandatory versus nullable fields A nullable field forces FWD to augment predicates to distinguish unknown from NULL, which can make a predicate unable to use an index
Collation Decides whether a prefix comparison can be answered from an index. Set per database as database/DBNAME/p2j/embedded-collation; see Database Collation

User defined functions

Prefer native user defined functions where the dialect supports them, and prefer an inlinable function body. database/DBNAME/p2j/use_java_udfs selects between functions executed in the JVM and functions executed at the database server. A Java UDF means the server calls back into FWD per row, which forecloses any index use and any server-side join. A native UDF stays at the server. FWD ignores the setting and uses Java UDFs if the dialect does not support native ones, logging a warning.

Among native functions the language matters: a function the server can inline into the enclosing query yields correct row estimates, while an opaque one yields a fixed guess, and a wrong estimate changes the join strategy of the whole query. See Database User Defined Functions Overview and Native UDFs for PostgreSQL.

Foreign keys and key naming

persistence/foreign-keys is FALSE by default. FWD resolves relationships itself and does not need declared foreign keys; enabling them adds referential integrity checks on every insert, update and delete. Enable them for a deployment that requires the constraint at the database level, not for performance.

persistence/primary-key-name overrides the primary key column name. It is a naming setting, of interest here only because it appears alongside the others.

Bulk Data Load

Data import is a separate performance regime, with its own pool and its own settings. It runs against a dedicated connection pool, which is why it can take liberties that a running application cannot.

  • Asynchronous commit is enabled explicitly on the import pool, independently of the setting discussed in "Asynchronous commit":#async. The pool exists only for the import, so the durability window applies only to data that would be reloaded on failure anyway.
  • Bulk copy is used where the dialect supports it. On PostgreSQL the loader streams rows as CSV through COPY, falling back to batch inserts if bulk copy is refused. The fallback re-runs against an empty table, so it cannot produce duplicates.
  • Batch size comes from database/DBNAME/orm/jdbc/batch_size, defaulting to 50 when unset. Unlike the fetch size node beside it, this one is honoured.
  • Secondary index creation is deferred until after the data is loaded, so rows are not indexed one at a time.
  • Word tables are repopulated in a separate asynchronous pass rather than incrementally.

When an import is slow, check the database server's maintenance_work_mem and checkpoint settings before looking at FWD: the deferred index build is a large sort, and the load itself generates write-ahead log at a rate a default configuration does not expect.

Database Server Parameters

Tune the database server for the workload FWD actually presents: many small statements from a modest number of pooled connections, with a read-heavy mix and narrow indexed lookups. The parameters below are ordinary database administration rather than FWD settings, so no values are suggested here; what follows is which class of parameter matters and why it matters for this particular workload. Consult the server's own documentation for values, and change one class at a time.

Parameter class Why it matters here
Buffer pool size A converted application performs many small indexed lookups. If the working set of index and heap pages does not fit, those lookups become disk reads, and this parameter dominates everything else on this page.
Per-operation working memory Applies to sorts and hashes. Relevant specifically to queries whose sort FWD could not delegate, and to the deferred index build during import. It is per operation, so it multiplies by concurrency.
Maintenance working memory Index builds and vacuum. Matters during import and schema change, not during steady state.
Planner cost parameters, in particular random page cost The default assumes rotating media. On SSD or with a fully cached working set the default discourages index scans, which is exactly the wrong bias for this workload.
Effective cache size Advises the planner how much of the database the operating system is caching. Understating it discourages index scans.
Statistics target and analyze frequency Every index decision depends on row estimates. A table that has grown substantially since it was last analyzed will get the wrong plan regardless of its indices.
Autovacuum aggressiveness Tables the application updates heavily accumulate dead rows, which inflates scans and can disable index-only scans by invalidating the visibility map. Hot tables usually need per-table settings rather than the global default.
Write-ahead log sizing and checkpoint spreading Governs whether write bursts stall. Most visible during import and during batch processing.
Maximum connections Must exceed the total of every FWD pool, plus dirty and tenant databases, plus administrative access. See "Pool sizing":#pool.
Just-in-time compilation Compilation latency is charged to the query. A workload of short statements pays it without recovering it.
Large page support Reduces address translation overhead for a large buffer pool. Worth considering only once the buffer pool is large.
Large object storage Governs how CLOB and BLOB columns are stored out of line. Relevant where the schema carries large character or binary fields that most queries do not read.
Storage layout Separating the write-ahead log from the data files helps a write-heavy workload; filesystem and readahead settings matter for sequential scans.

The temporary database is H2 in memory and needs none of this. Its one relevant setting, the lock mode, is handled by FWD; see "Private temporary databases":#private.

Multiple Databases and Tenants

Account for every database the server actually connects to, not just the application's own. A running server holds connections to the application databases, a dirty database per application database where dirty share is active, the metadata database, and one physical database per tenant.

  • Tenant databases inherit the logical database's configuration, including pool settings and the legacy-remote prefetching configuration. A per-tenant override is possible but is rarely the right answer, because the tenants of one logical database normally present the same workload.
  • The dirty database inherits its primary's pool settings. It is created only where dirty share is active, which is another reason to disable what is not needed; see "Dirty share":#dirty.
  • The metadata database is not pooled. Its connections come directly from the driver, so pool settings on it have no effect. Where metadata is configured, note that its presence changes when each database's persistence context is created, which can change startup cost.
  • Cache invalidation across a cluster is handled by a distributed invalidation function for the fast find cache. In a clustered deployment, invalidation traffic is part of the cost of that cache.

Server and JVM

Size the heap from the caches, not from a rule of thumb. The FWD server's database-related heap is dominated by three quantities, and all three scale with concurrent sessions:

  1. the session record cache, which is entries multiplied by record width multiplied by databases multiplied by sessions;
  2. the private temporary databases, one H2 instance per session, sized by what the application materialises in temp-tables;
  3. open result sets, sized by the JDBC fetch size or by the prefetch page size where paging is enabled.

Raising the session cache, enabling prefetching with a large growth factor, and using private temporary databases all move work from the database to the JVM. That is usually the right trade, but it is a trade, and it has to be paid for in heap. A server that begins spending time in garbage collection after such a change has been given the work but not the memory.

Where no configuration change suffices and no 4GL rewrite is available, native SQL is the escape hatch; Running Native SQL sets out what it forfeits.

Checklist

Symptom Setting to examine Expected effect
High TMCommit, write-heavy workload synchronous_commit, via "Asynchronous commit":#async One disk flush per commit removed
Temp-table operations contending across sessions persistence/private-temp-dbs, see "Private temporary databases":#private Temporary database locking becomes unnecessary
High temp-table write cost, no reliance on temp-table undo persistence/force-no-undo-temp-tables, see "UNDO and NO-UNDO":#noundo Reversible tracking removed; verify semantics first
Many round trips on long sequential walks, legacy sessions were networked legacy-remote container, see "The legacy-remote container":#legacy-remote Records per round trip grows geometrically
Round trips on walks where prefetching does not apply persistence/jdbc-fetch-size, see "JDBC fetch size":#fetchsize More rows buffered per fetch
High OrmHydrateRecord, records reread within a session Session cache size, see "Session record cache":#session-cache Repeat reads served from memory
High OrmDynQueryProcess_* or OrmFqlParse Conversion and query cache sizes, see "Conversion and query caches":#query-caches Query preparation served from cache
Sessions waiting to acquire a connection maxpoolsize and minpoolsize, see "Pool sizing":#pool Waiting removed, until the server saturates
A prepare on every execution maxstatementsperconnection and preparethreshold, see "Statement caching":#stmt Statement reused across executions
First request to a database is slow load_at_startup, see "Startup, idle and health":#lifecycle Initialization moved to server startup
Inserts serialising across sessions Dirty share settings and index count, see "Dirty share":#dirty Fewer indices to lock, or no dirty bookkeeping at all
Table scans in the plan Index inventory, collation, UDF language, see "Schema and Physical Design":#schema Predicate answered from an index
Import slow Batch size, deferred index build, server maintenance memory, see "Bulk Data Load":#import Rows loaded in bulk, indices built once
Indexed lookups reading from disk Database server buffer pool, see "Database Server Parameters":#server Working set cached
Garbage collection time rising after a tuning change Heap versus cache totals, see "Server and JVM":#jvm Work moved to the JVM is paid for in memory

Related Pages