Hi everybody,

We have recently published wfrog, a open source weather logger software (
www.wfrog.org) that uses Firebird to keep a record of meteorological data.
One of the goals of this software is to be able to run on small single board
Linux platforms, usually based on flash storage. Currently I am using an
ALIX board from PC Engines, with 256 Mb. of memory, 500 Mhz processor and a
Compact Flash bay as storage, with a 1Gb CF SLC card. The reason for this
email is that flash memory only accepts a given number of delete/write
cycles and therefore we want to reduce disk writes as much as possible.

The sofware connects via USB or serial port to a weather station and
continously recieves data from the station's sensor array. Every 5 minutes
the current conditions are written to a new record on a Firebird database
containing one table:

CREATE TABLE METEO
(
TIMESTAMP_UTC Timestamp NOT NULL,
TIMESTAMP_LOCAL Timestamp NOT NULL,
TEMP Numeric(3,1),
HUM Numeric(3,1),
WIND Numeric(4,1),
WIND_DIR Smallint,
WIND_GUST Numeric(4,1),
WIND_GUST_DIR Smallint,
DEW_POINT Numeric(3,1),
RAIN Numeric(4,1),
RAIN_RATE Numeric(4,1),
PRESSURE Numeric(5,1),
UV_INDEX Smallint,
CONSTRAINT PK_METEO PRIMARY KEY (TIMESTAMP_UTC));

CREATE DESCENDING INDEX IDX_METEO1 ON METEO (TIMESTAMP_LOCAL);

All other components of the software read this Firebird database to extract
the meteorological data and produce graphs, web pages, etc. Some of the
queries might involve a full year, i.e. > 100000 records.

Focusing on reducing disk writes I thought of the following settings:

* gfix -housekeeping 0 localhost:/lib/firebird/2.1/data/wfrog.db

We don't do updates or deletes, so no garbage collection is going to be
needed. Is it a good idea to set sweep interval to 0? In theory all
transactions will commit soon after starting, so maybe changing this value
is not going to affect much.

* gfix -use full localhost:/lib/firebird/2.1/data/wfrog.db

Since we only insert elements we can use the full page without reserving
space for updates. That reduces the database size and therefore will reduce
the number of block writes to disk.

* Set up the delta file in a tmpfs filesystem and lock the main database
file, releasing it only every hour or so to write down all updates at once:

- ALTER DATABASE ADD DIFFERENCE FILE /tmp/wfrog.delta

- Every five minutes we insert a new record into the database with the
current weather data. Once inserted, if one hour or more has elapsed since
last synchronization of the delta file then unlock and lock again the
database, so that the changes in the delta file are merged to the database:
start trans
ALTER DATABASE END BACKUP
end trans
start trans
ALTER DATABASE BEGIN BACKUP
end trans

Is there anyone knows how Firebird internally works who can confirm that
this strategy is effectively going to reduce the number of disk writes?

Do read only transactions imply writes in the database file? If so this
option will clearly reduce the number of writes to the flash card, since
there can be many clients checking periodically the data on the database.

And yes, I know I can loose the data of the last hour... Can it have other
undesirable side effects I am not aware of?

* gfix -buffers 100 localhost:/lib/firebird/2.1/data/wfrog.db

Reduce the number of cache pages because flash memory is fast compared to
hard disk. Better to use memory for other purposes, like tmpfs.


Any other ideas on how to tune a Firebird database in such a environment?
Any suggestion on page size?

Has flash storage been an issue in Firebird development? (I'm positive some
day it will be...)

Thanks,
Jordi.


[Non-text portions of this message have been removed]