IMHO what is going on is that Firebird is using the lock file (for Classic installation) to synchronize between various connections (threads in this case) to allow one by one to write to FDB file.
I also tested this and if you run various threads inserting among different tables there are no performance issues.
The fastest way I found to insert multiple rows in a Firebird database is run a Stored Procedure that contains various inserts in form of a long string. The SP's splits this string in various inserts. With this approach I can insert around 11,000 recods/second! (if there are no Indexes, even PK).

This is the SP's body:

SET TERM ^ ;

create or alter procedure SP_INSERTS (
    TABELA varchar(50),
    CAMPOS varchar(1024),
    VALORES varchar(32750))
as
declare variable WSTART integer;
declare variable WCMD varchar(32750);
declare variable WPOS integer;
declare variable WSTR varchar(32750);
begin
  wStart = 1;
  while (1=1) do
  begin
    wPOS = POSITION(' # ', valores, wStart);
    if (wPOS = 0) then
      break;

    wSTR = substring(valores from wStart for wPOS - wStart);
    wStart = wPOS + 3;

    wCmd = 'insert into ' || tabela || campos || ' values ' || wSTR;
    execute statement wCmd;
  end
end^

SET TERM ; ^

How to use:
execute procedure SP_INSERTS('MY_TABLE', '(id, name, foo)', '(1, 'A', 'AA') # (2, 'B', 'BB') # (3, 'C', 'CC')');

Also, there are some limitations with this technique. The entire command does not exceed 64Kb, so you must trim the string and split between more commands.

I hope I can help you, and sorry about some English mistakes.
Fabiano.

On 21/01/2015 07:47, brucedickinson@wp.pl [firebird-support] wrote:

 

Hello guys,

Few months ago I've asked you (here: https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/125426 ) about your experiences regarding multithreaded operations on the database.

Since then, I've implemented my solution, but after testing I see that the performance for multiple threads is actually MUCH worse compared to one thread. I've ruled out the hardware.
No matter how much threads I use (one, two, eight...) each thread is using only constant amount of hard disk (for example 500kb/s).

Let me describe in pseudocode how each thread works:

while ThereIsDataToInsert do
begin
  Take100DataPackets;
  Transaction.Start;
  for each DataPacket in 100DataPackets do
    InsertDataPacketToTable;
  Transaction.Commit;
end

So bascially, I am inserting 100 rows per one trans action to the same table from multiple threads.

Let me show you my test results for total of 100000 records inserted to a table. In case of multiple threads, record amount is divided proportionally to the thread count.
For example, if 4 threads are inserting, each thread is inserting only 25000 records. Data to insert is generated on the fly and has no impact on the performance. There is no
synchronization between threads, each thread has its own connection and transaction.

I am inserting data to such empty table:

  CREATE TABLE MASTER_DATA
  (
    ID INTEGER NOT NULL,
    ID_OBJECT INTEGER NOT NULL,
    ID_DEVICE INTEGER NOT NULL,
    KIND INTEGER NOT NULL,
    MEASUREMENT_DATE TIMESTAMP NOT NULL,
    ID_SOURCE BIGINT NOT NULL
  );

There are no triggers, no indices, no keys.


Firebird 2.5.3 Classic, local conn ection. Firebird on the same machine as a testing application.

| Threads |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |
|   HDD   |  12 |  28 |  37 |  39 |  42 |  44 |  41 |  46 |
|   SDD   |   7 |   9 |  10 |  10 |  11 |  12 |  12 |  12 |
| RAID 10 |  36 | 118 | 222 | 258 | 280 | 273 | 288 | 291 |  

I've compared three different disk configurations. HDD and SDD were on my dev machine (Intel i5, nothing fancy). RAID 10 (SDD) was used on some writualized server with a lot of cores.
Measurements are givien in seconds. For example: It took 12 seconds for 1 thread working on HDD to insert 100000 records. Other example: It took 10 seconds for 4 threads orking on SDD to insert 100000 records (25000 per each thread).

As you can see, p erformance degrades when using more than one thread. I am guessing this is because of transactions? Perhaps when inserting a new record Firebird must perform some checks?
Could somebody explain to me what is going on the low level of Firebird internals? Perhaps there is a chance to avoid those "locks" performed internally by Firebird?

If somone knows what is going on here, please let me know. I am pretty sure we all could benefit from this knowledge. I think that most of us do not know how to optimize Firebird (wide topic I know).
Just check out Paul Reeves presentation from last conference - a lot of tests had been made but there are very little conclusions.

I am determined to make further tests, I want to fully utilize the speed of my disks.

Best regards.