What have you expected - to be faster ??

Regards, Bogdan

> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-
> support@yahoogroups.com] On Behalf Of Adam
> Sent: Thursday, December 15, 2005 11:36 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: Slow concurrent insert performance.
>
> --- 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]
> >
>
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.371 / Virus Database: 267.13.13/200 - Release Date:
> 14.12.2005