--- In firebird-support@yahoogroups.com, "bcarter351"
<bcarter351@y...> wrote:

>
>
> 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 believe your problem may be your step 3. When you delete the records
and commit, the records are flagged as deleted, but are not removed
from the database until every transaction that may require those
records is committed / rolled back. The next transaction that stumbles
across the data is given the task of garbage collection, which may be
what you are experiencing.

You could try either dropping the table, or doing a select count(*)
from the deleted table which will incur the cost of garbage collection
rather than confuse your results.

I am assuming you are using the Superserver architecture, have you
tried classic?

Adam

>
> 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]
>