Contacts
IBSurgeon

Firebird OLTP-EMULator test

OLTP-EMUL is the official Firebird test that emulates a realistic OLTP workload on Firebird 2.5 and above. Use it for performance and regression checks under many concurrent ISQL sessions. Read this guide carefully before you run the test.

Clone the repository

Windows

git clone --config core.autocrlf=true https://github.com/FirebirdSQL/oltp-emul

Linux / POSIX

git clone --config core.autocrlf=false https://github.com/FirebirdSQL/oltp-emul

Quick-start based on work by Pavel Zotov (Moscow). Questions about the test itself: [email protected]. IBSurgeon support: [email protected].

1. Prerequisites

On the host that will run the ISQL sessions, install a Firebird client and ensure these binaries are available:

  • isql (on some POSIX packages: isql-fb)
  • fbsvcmgr

2. Choose and copy a config file

In the src folder, pick the default config that matches your Firebird major version and OS, then copy it without the .default suffix.

Firebird OS Config file
2.5Windowsoltp25_config.win.default
2.5Linuxoltp25_config.nix.default
3.0Windowsoltp30_config.win.default
3.0Linuxoltp30_config.nix.default
4.0Windowsoltp40_config.win.default
4.0Linuxoltp40_config.nix.default
 
# Windows
copy .\oltp30_config.win.default .\oltp30_config.win

Linux


cp ./oltp30_config.nix.default ./oltp30_config.nix

3. Important settings

Edit the copied config. Pay special attention to:

  • fbc - path to isql on the machine that launches sessions
  • clu - optional non-standard ISQL binary name (e.g. isql-fb on POSIX packages)
  • dbnm - full path and file name of the database on the server (ASCII only; not an alias if you want the script to create it)
  • host, port, usr, pwd - connection parameters
  • tmpdir - directory for ISQL logs and temporary test files
  • init_docs - documents to create before the measured workload (try 3000-5000 first)
  • warm_time / test_time - warm-up and measured phases, in minutes
  • sleep_min / sleep_max - pause between transactions (set sleep_max to 0 to disable pauses)
  • sleep_ddl - optional UDF for pauses (preferred over shell sleep / cscript); binaries under util/udf64/
  • wait_for_copy - pause after init so you can snapshot the DB before warm-up/measure (useful for repeated runs)

After init, the test runs warm-up for warm_time minutes, then measures for test_time minutes.

4. Windows and firebird.conf tips

Exclude antivirus / OS scanning from tmpdir and from Firebird temp folders (FIREBIRD_TMP / TMP / TEMP, and TempDirectories in firebird.conf). Aligning FIREBIRD_TMP with TempDirectories reduces slowdowns under many ISQL sessions.

Increase at least:

  • DefaultDbCachePages
  • LockHashSlots
  • LockMemSize
  • FileSystemCacheThreshold - must be set explicitly and greater than DefaultDbCachePages (the test rejects a missing/commented value, except some POSIX package layouts)

Target roughly 25% of RAM for the page cache. Do not set DefaultDbCachePages above 2048 for Classic / SuperClassic. Page size is hardcoded to 8192.

Example medium load (~100 sessions):

Parameter SuperServer 3.0+ [Super]Classic 3.0+ [Super]Classic 2.5
DefaultDbCachePages ~0.25 × RAM / 8192 1024 1024
LockHashSlots 8191 16001 16001
LockMemSize 4194304 16777216 16777216

For ready-made configs, see the Configuration Calculator and reference configurations.

5. Run the test

Open a shell, change to src, and run:

1run_oltp_emul <FbMajor> <NumSessions> [nostop]
  • FbMajor - 25, 30, or 40 for Firebird 2.5 / 3.0 / 4.0
  • NumSessions - number of ISQL sessions to launch
  • nostop - optional; skip interactive pauses (useful from a scheduler)

If the database file does not exist and dbnm is a full path (not an alias), the script can create it. Incomplete or empty previous DBs are recreated as needed.

6. Reports and stopping

After warm-up + measure time, sessions stop automatically. The first ISQL session writes the final report under tmpdir. With make_html = 1 on Windows, an HTML report is also produced (slower).

Report naming depends on file_name_with_test_params:

  • Commented (default) - e.g. oltp**.report.txt
  • regular - timestamped names suited to browsing many runs in a folder listing
  • benchmark - names suited to comparing settings/DDL variants

Reports include architecture and settings, overall and per-interval performance (CNT_OK_PER_MINUTE / AVG_TIMES_PER_MINUTE), optional monitoring details, exceptions, DB statistics, version/record ratios, validation for modified tables, and a firebird.log before/after diff.

You can also query:

select * from srv_mon_perf_dynamic;
select * from srv_mon_perf_total;

Stop early

Use 1stoptest.tmp (.bat / .sh) created in tmpdir on each launch. For multi-host launches, you can instead enable ExternalFileAccess in firebird.conf, set use_external_to_stop in the OLTP config, and write a character into that stop file (clear it before each new run).

Repeatable baselines

Set wait_for_copy = 1, let init finish, copy the DB, then restore that copy before each comparative run so every measurement starts from the same point.

What the workload models

The scenario follows car-service style business documents:

  1. Customer (or internal) orders for parts
  2. Orders combined and sent to a supplier
  3. Supplier invoice (full or partial)
  4. Stock receipt; customer-ordered parts reserved
  5. Sales and stock write-offs
  6. Cancellations possible at multiple steps
  7. Partial/full payments and cancellations affecting balances
  8. First session produces performance, exception, stats, and validation reports