| Subject | Simultaneous inserts to the same table. |
|---|---|
| Author | |
| Post date | 2015-01-21T10:47:08Z |
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 transaction 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 connection. 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, performance 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.
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 transaction 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 connection. 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, performance 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.