Slow I/O performance on Linux? Look at the filesystem!

Bryan Cole wrote:

I'm finding that insert performance with firebird is dramatically slower than sqlite to the point where I must be doing something stupid. I'm using superclassic server v2.5 on linux (ext4 fs). The client is running on the same host as the server, using libfbclient (via the python fdb bindings). My test table looks like:

CREATE TABLE MYTABLE
(
  ID integer NOT NULL,
  NAME varchar(50),
  RANK integer,
  "value" float,
  "timestamp" timestamp,
  WAVEFORM blob sub_type 0,
  CONSTRAINT INTEG_8 PRIMARY KEY (ID)
);

The problem is the blob field. Inserting records without a WAVEFORM value goes at an acceptable 1000 inserts / sec. However, when I use a 32KB string for the blob value, the insert rate drops to < 10/sec.

The read-speed for the 32KB blobs is OK at >2000 rows/sec, so the network overhead is clearly not a factor. sqlite can write the same table structure and volume of data at the 1000s rows/sec so the underlying disk bandwidth is also not the limitation. This is the only table in my database. The fdb bindings use prepared statements with parameters.

I'm using firebird in its default configuration out-the-box on fedora-20. The database page size is 4096.

Surely I'm missing something important?

ma_golyo answers:

Firebird do not like ext4 FS. You must chane some settings. See http://www.firebirdnews.org/?p=6421

Carlos H. Cantu answers:

Firebird performance with forced-writes databases stored in linux partitions with Barrier parameter turned enabled is horrible for writings. You may not notice if you have slow volume of sporadic writings, but it becomes a real problem if you have "mass writings", like DML that will affect several records.

Solution is to disable FW or disable Barrier. So far, I really don't know what of this options would be better regarding "safety/stability".

Bryan Cole answers:

Thanks for the tip. Remounting my ext4 partition with barrier=0 increased the write speed from ~8 rows/sec to 450 rows/sec. Yeay.

There are a bewildering array of linux filesystem options I could look at tuning further. Can anyone recommend a good combination (ext3 vs ext4,xfs,btrfs with options writeback-vs-ordered, barriers etc.) for a firebird database. I can afford to lose a few seconds of data in the event of a hard crash, but the filesystem/database as whole needs to remain consistent.

Alexandre Benson Smith answers:

Here is a document with some information provided by Philipe Makowski:

Choosing a File System on Linux for Firebird