I am concerned about the performance of concurrent inserts. It appears that for 2 concurrent inserts there is a factor of 2.8 times longer in elapsed time, a factor of 4.3 times longer in elapsed time for 3 concurrent inserts and a factor of 5.8 times longer in elapsed time for 4 concurrent inserts. Can anyone explain why the impact is so significant? What may be causing this impact?

I ran then following test to determine Firebird scalability for concurrent inserts.

1) There are four tables (testb, testc, testd, teste) with the following columns: fld1 varchar(10), fld2 varchar(10), fld3 varchar(10).
2) I used a stored procedure to insert 1million rows into table testb and 3 other stored procedures to insert 1.5 millions rows into tables testc, testd, and teste.
3) The stored procedures insert into an empty table then commit. I then delete the table and commit.
4) I use isql to execute the stored procedure.
5) I am just measuring the performance of the stored procedure that inserts into testb with the isql set stats on. I am using elapsed time of the insert.
6) I first ran the stored procedure to insert into just table testb four times to get a baseline of how long the insert takes.
7) I then ran the stored procedure to insert into testb and testc concurrently 4 times.
8) I then ran the stored procedure to insert into testb, testc and testd concurrently 4 times.
9) I then ran the stored procedure to insert into testb, testc, testd and teste concurrently 4 times.
10) I ran the test using isql on the database server and also using isql on a remote server.
11) The database server is 4 CPU (Xeon 3.6 GHz) with 3.5GB of RAM attached to an EMC SAN. The database is using a RAID 5 file system. CPU affinity was set to 15. CPU utilization during the 4 concurrent user test was about 30% with no CPUs hitting 100% utilization.

Below are the results of the test.





Remote
UserTest







Single 22 User33 User44 User

UserUser% increaseUser% increaseUser% increase

ElapsedElapsedElapsedElapsedElapsedElapsedElapsed

TimeTimeTimeTimeTimeTimeTime















Run 13.7310.59283.91%15.50415.55%23.67634.58%Run 23.6710.53286.92%15.14412.53%20.44556.95%Run 33.7510.67284.53%16.67444.53%21.06561.60%Run 43.6910.95296.75%17.45472.90%21.33578.05%















Avg3.7110.69288.01%16.19436.39%21.63582.88%Min3.6710.53286.92%15.14412.53%20.44556.95%Max3.7510.95292.00%17.45465.33%23.67631.20%



















LocalUserTest







Single 22 User33 User44 User

UserUser% increaseUser% increaseUser% increase

ElapsedElapsedElapsedElapsedElapsedElapsedElapsed

TimeTimeTimeTimeTimeTimeTimeRun 13.7011.01297.57%15.36415.14%20.64557.84%Run 23.8111.06290.29%15.74413.12%20.35534.12%Run 33.7410.74287.17%15.46413.37%20.54549.20%Run 43.8010.84285.26%15.66412.11%19.98525.79%















Avg3.7610.91290.03%15.56413.42%20.38541.59%Min3.7010.74290.27%15.36415.14%19.98540.00%Max3.8111.06290.29%15.74413.12%20.64541.73%
The stored procedure to insert into testb:



DECLARE VARIABLE Cnt NUMERIC(10,0) = 1; BEGIN
WHILE (Cnt <= 1000001) DO BEGIN
insert into testb values ('fld1', 'fld2', 'fld3');
Cnt = Cnt + 1;
END
END

The stored procedure to insert into testc:

DECLARE VARIABLE Cnt NUMERIC(10,0) = 1; BEGIN
WHILE (Cnt <= 1500001) DO BEGIN
insert into testc values ('fld1', 'fld2', 'fld3');
Cnt = Cnt + 1;
END
END

The stored procedure to insert into testd:

DECLARE VARIABLE Cnt NUMERIC(10,0) = 1; BEGIN
WHILE (Cnt <= 1500001) DO BEGIN
insert into teste values ('fld1', 'fld2', 'fld3');
Cnt = Cnt + 1;
END
END

The stored procedure to insert into teste:

DECLARE VARIABLE Cnt NUMERIC(10,0) = 1; BEGIN
WHILE (Cnt <= 1500001) DO BEGIN
insert into teste values ('fld1', 'fld2', 'fld3');
Cnt = Cnt + 1;
END
END

The SQL to run testb stored procedure:

set stat on;
set echo on;
execute procedure ins_testb;
commit;
delete from testb;
commit;

The SQL to run testc stored procedure:

set echo on;
execute procedure ins_testc;
commit;
delete from testc;
commit;

The SQL to run testd stored procedure:

set echo on;
execute procedure ins_testd;
commit;
delete from testd;
commit;

The SQL to run teste stored procedure:

set echo on;
execute procedure ins_teste;
commit;
delete from teste;
commit;






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