Re: Performance degradation question w/transactions
In article <5nkjjn$no$1@hpwhey.ch.apollo.hp.com>, John Cowles
<cowles@apollo.hp.com> writes
>
>While attempting to diagnose some performance problems with our
>application, I decided to run some simple benchmarks just to get
>some idea of the raw performance of Online (5.03.UC1) by itself
>(without my application on top of it).
>
>I encountered some disconcerting results, so disconcerting that
>I'm in search of some help in figuring out what might be going
>on.
>
>
>Here are the tests I ran:
>
> For each test, I began with an empty database named "benchmark".
> Database logging was "ON" but directed to /dev/null.
> Before each test, I created a single table as follows:
>
> create table "test".rows
> (
> f1 varchar(32,4),
> f2 char(10),
> f3 smallint,
> f4 date,
> f5 date,
> primary key (f1) constraint "test".p1rows
> )
> lock mode row
> ;
>
> All tests were run on a very lightly loaded HP 730 running HP-UX 9.05.
> (My tests were the only user processes.) The machine has 64MB RAM.
>
>
>Test 1:
>-------
>
> Insert 100,000 rows into the table.
> Each insert is an individual transaction.
> Here is the excerpted SQL file I executed:
>
> begin work;
> insert into rows values ('key_0', 'anything', '0', 'today', 'today');> commit work;
> begin work;
> insert into rows values ('key_1', 'anything', '0', 'today', 'today');> commit work;
> begin work;
> insert into rows values ('key_2', 'anything', '0', 'today', 'today');> commit work;
> .
SO for each record inserted a begin work record, an insert record and
a commit work record are writtent to the logical log. i.e. 3 records
per row i.e. 300,000 records.
> .
> .
> begin work;
> insert into rows values ('key_99997', 'anything', '0', 'today', 'today');> commit work;
> begin work;
> insert into rows values ('key_99998', 'anything', '0', 'today', 'today');> commit work;
> begin work;
> insert into rows values ('key_99999', 'anything', '0', 'today', 'today');> commit work;
>
>
> I got the following results:
>
> $ time dbaccess benchmark benchmark1 > bench.log 2>&1>
> real 8h45m16.43s
> user 8h31m37.77s
> sys 10m2.26s
>
> Eight hours!!!!
> What's going on?
>
A lot of writes to the logical logs (on disk) which when full are
written to /dev/null. That's right, even with LTAPEDEV set to
/dev/null, the logical logs records are still written to the logical
logs on disk!.
>
>Test 2:
>-------
>
> Insert 100,000 rows into the table, this time without transactions.
> Here is the excerpted SQL file I executed:
>
> insert into rows values ('key_0', 'anything', '0', 'today', 'today');
> insert into rows values ('key_1', 'anything', '0', 'today', 'today');
> insert into rows values ('key_2', 'anything', '0', 'today', 'today');> .
> .
> .
> insert into rows values ('key_99997', 'anything', '0', 'today', 'today');
> insert into rows values ('key_99998', 'anything', '0', 'today', 'today');
> insert into rows values ('key_99999', 'anything', '0', 'today', 'today');>
>
> I got the following results:
>
> $ time dbaccess benchmark benchmark2 > bench.log 2>&1>
> real 11m28.07s
> user 10m05.66s
> sys 1m53.38s
>
>
>Test 3:
>-------
>
> Rerun Test 1 except use Page Locking instead of Row Locking.
> The results were just as bad as in Test 1:
>
Same thing - 300,000 records written to disk. In fact it you use
unbuffered logging (which I suspect you are) then the logical log
buffer in shared memory is being flushed to disk 300,000 times as
it is flushed every time a commit occurs.
> $ time dbaccess benchmark benchmark1 > bench.log 2>&1>
> real 8h45m16.43s
> user 8h31m37.77s
> sys 10m2.26s
>
>
>
>Test 4:
>-------
>
> Insert 100,000 rows into the table using 10 transactions
> of 10,000 inserts each.
>
> The results were:
>
> $ time dbaccess benchmark benchmark4 > build.log 2>&1>
> real 7m43.23s
> user 5m40.99s
> sys 1m56.41s
>
>
>Test 5:
>-------
>
> Insert 100,000 rows into the table using 4000 transactions
> of 25 inserts each.
>
> The results were:
>
> $ time dbaccess benchmark benchmark5 > build.log 2>&1>
> real 11m33.85s
> user 9m9.21s
> sys 2m11.30s
>
>
>
>Can anyone suggest what might be degrading the performance of my
>system so horribly in tests 1 and 3? Are there parameters that I
>can or should tune in my Informix configuration to avoid getting
>bogged down?
Yes, stop commiting so often, this is not a realistic test as I
doubt you will get this many commits in a real world application.
>
>Any insight would be much appreciated!
>Thanks in advance,
>
>John
>--
>------------------------------------------------------------
>John Cowles
>Hewlett-Packard Company
>Product Generation Information Systems
>300 Apollo Drive, M/S CHR-01-SS
>Chelmsford, MA 01824
>(508) 436-4577 FAX (508) 436-5152
>http://www.pgis.hp.com/~cowles
>mailto:cowles@apollo.hp.com
>------------------------------------------------------------
--
David Williams