Changing Database Settings - Firebird

A number of gfix command options permit certain configuration settings to be set or changed for a database. To perform these commands, SYSDBA or owner privileges are required and exclusive access is required.

Setting the Default Cache Size

Using gfix is the preferred way to set the default cache size for the database. Some points important to remember are

  • If you increase the page size, the cache size will rise accordingly. You should ensure that you take the amount of physical RAM on the machine into consideration when altering cache size. Once the cache reaches the point where it is too large to be kept in RAM, it will begin swapping out to disk, thereby quite defeating the benefit of having a cache at all.
  • On a Classic server, every client gets its own cache. Even the default cache size of 75 pages will be too large if the database is using a big page_size.

This is the syntax:

gfix -b[uffers] n db_name

where n is the size of the cache (number of page-sized buffers) to be reserved.

For example:

gfix -b 5000 d:\data\accounts.fdb

If the database page_size is 8192, a cache of 5,000 pages will allocate a cache of around 40MB.

Other Cache Options

The switch –c[ache] n is currently unused; it is reserved for future implementation.

Changing the Access Mode

Use the gfix –mo[de] option to switch the access mode, for any connections to the database, between read-only and read-write. A read-only database cannot be written to at all—not even by SYSDBA, its owner, or any server process. This is the syntax pattern:

gfix -mo[de] {read_write | read_only} db_name

To switch a database from read-write to read-only:

./gfix -mo read_only /data/accounts.fdb

To switch from read-only to read-write:

./gfix -mo read_write /data/accounts.fdb

Changing the Database Dialect

With this switch, you can change a dialect 1 database to Firebird’s native dialect 3 format. The database then stops being dialect 1 and obeys the full syntax rules of Firebird SQL and can accept all of Firebird’s data types.

But (aren’t there always some of those?) the database retains any existing data and definitions in accordance with dialect 1. Some traps lie here, especially with respect to fixed numeric types.

In short, changing the dialect with gfix is neither a quick-fix migration tactic nor the most bombproof way to migrate from dialect 1 to dialect 3. A quick change with gfix means a slow route to a fully migrated database. Experienced users recommend, instead, to extract a schema script from your dialect 1 database, modify the definitions to suit your needs, and then to use a datapump tool to move the old data across.

Still, if you want to do it this way anyway, here’s how. This is the syntax:

gfix -s[ql_dialect] n db_name

where n is either 1 or 3.

For example, to change the dialect of the database to 3:

./gfix -s 3 /data/accounts.fdb

or, on Windows, you might do this:

gfix -sql_dialect 3 d:\data\accounts.fdb

Enabling and Disabling “Use All Space”

Firebird fills database pages so that the ratio of data stored per page does not exceed 80 percent. A certain amount of compaction can be achieved by switching the ratio to 100 percent. This may produce a performance benefit during huge bulk inserts, especially if row size is smaller than page size and row size multiples can be stored economically within a one-page space.

It also makes sense to fill pages to full capacity in a database that you plan to distribute as a read-only database, as a catalog or a demonstration, for example.

The command switch is –u[se] and it has two arguments:

gfix -u[se] {reserve | full}

reserve sets the page use to 80 percent, and full sets the page use to 100 percent.

To enable “use all space” use the command

./gfix -use full /demos/catalog.fdb

To disable “use all space” and return to the 80-percent fill ratio, use the command

./gfix -use reserve /demos/catalog.fdb

Enabling and Disabling Forced Writes

Forced Writes is synonymous with synchronous writes. When the behavior is synchronous (“Forced Writes enabled”), new records, new record versions, and deletions are physically written to disk immediately upon posting. Wit asynchronous writes (“Forced Writes disabled”), new and changed data is retained in the system file cache, relying on the flushing behavior of the operating system to make it permanent on disk.

The term “disabling Forced Writes” means switching the write behavior fromsynchronous to asynchronous.

The command syntax pattern is

gfix -w[rite] {sync | async}

To enable Forced Writes:

gfix -w sync d:\data\accounts.fdb

To disable Forced Writes:

gfix -w async d:\data\accounts.fdb

Firebird is installed on Windows NT/2000/XP and Linux with Forced Writes enabled. In a very robust environment with highly reliable UPS support, a DBA may disable Forced Writes to reduce I/O and improve performance. When Forced Writes is disabled in less dependable environments, the database becomes susceptible to data loss and even corruption in the event of an uncontrolled shutdown.

Forced writes are not applicable to Windows 95. On Windows 98 and ME servers, you should never disable forced writes.

Disabling Forced Writes on Windows Servers

Windows is less dependable than other operating systems with regard to cache flushing. It appears that, if applications do not explicitly request the Windows system to flush the cache, it may defer all writes until the database file is closed.

  • Firebird 1.0.x: If Forced Writes is disabled on a 24/7 Windows server, flushing may never occur.
  • Firebird 1.5: New configuration settings were added for flushing the cache buffers on Windows. Refer to the parameters MaxUnflushedWrites and MaxUnflushedWriteTime (firebird.conf) in Chapter Configuration and Special Features.

System Restore on Windows ME and XP

Windows ME, along with the XP Home and Professional editions, has a feature named System Restore, which causes the operating system to update its own filesystem backup of files with certain suffixes each time a file I/O operation occurs. System Restore is not a substitute for forced writes.1

Converted InterBase 6 Databases

Be aware that a Firebird database that started life in InterBase 6.x (commercial or Open Edition) will have been created with Forced Writes disabled by default.

Querying Firebird Server Version

The switch –z (with no parameters) shows the version of gfix and the Firebird engine installed on the server. This is the syntax:

gfix -z

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics