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, [email protected] [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.
