[Firebird-devel] Firebird 2.5: Performance degradation and increasing file size

[Gary F. <Gar...@sa...>]

Hello Everyone,
Our company's testing department created a stress test that creates a large number of
tables, populates them, performs queries and then deletes the tables and does this
over and over many times.  At the end of each test run the database is empty.  The
results below are from running a copy of B_2_5_Release taken from around
November 9, 2009 on a Windows 32 bit machine.

When I run Firebird 2.5 using this stress test I am seeing performance degradation
for each new run and the size of the database continually grows.  The original empty
database was 802,816 bytes in size.  The amount of real time (clock), amount of CPU
time and the size of the database after each run completes are listed below:

   Run   Real Time    CPU Time   Database Size
          Seconds      Seconds       Bytes
   ---  -----------  -----------  -----------
     1    1:05.50       21.80      3,600,384
     2    1:09.85       24.36      6,180,864
     3    1:14.46       28.11      8,884,224
     4    1:17.77       31.17     12,021,760
     5    1:24.00       36.28     14,413,824
     6    1:23.54       38.82     17,281,024
     7    1:32.99       41.39     19,505,152
     8    1:43.24       45.26     22,016,000
     9    1:41.83       52.56     24,846,336
    10    1:43.97       55.90     28,045,312

I then ran the gfix command: "gfix -sweep tktest.tdb" and submitted 10 more
runs of the test:

    11    1:11:80       21.82     28,045,312
    12    1:23.93       24.62     28,045,312
    13    1:24.41       29.36     28,045,312
    14    1:19.68       32.45     28,045,312
    15    1:27.67       36.20     28,045,312
    16    1:27.00       38.70     28,045,312
    17    1:31.85       42.65     28,045,312
    18    1:41.79       47.48     28,045,312
    19    1:51.13       52.54     28,045,312
    20    1:48.86       55.93     29,794,304

I then ran the gbak command: "gbak -b tktest.tdb tktest.tbk;
gbak -r tktest.tbk tktest.tdb"  and submitted 10 more runs of the test:

    21    1:15.10       20.03      3,600,384
    22    1:27.63       24.53      6,180,864
    23    1:25.88       27.15      8,884,224
    24    1:25.26       29.95     12,021,760
    25    1:22.40       33.90     14,413,824
    26    1:25.07       37.87     17,281,024
    27    1:26.90       41.64     19,505,152
    28    1:39.10       46.29     22,016,000
    29    1:41.38       49.86     24,846,336
    30    1:43.11       54.45     28,045,312


Running gfix appears to have reclaimed unused pages and running gbak
reduced the size of the database back down to the size of the original empty
database.  After running gfix and gbak the clock times and CPU times were
reduced close to the timing of the original run.   However, the CPU times get
worse on each successive run.

I would expect the size of the database to grow the first time I run the test (from
802,816 bytes to 3,600,384 bytes) but why does the database continue to grow
each and every run after that?  Shouldn't the unused pages be used instead of
the database continually getting bigger?

Is there any way to run the garbage collection after a specific number
of transactions have taken place, say 10,000, rather than being triggered
off the sweep interval?

Is there a way to automatically reduce the size of the database without
running gbak?

Are there any configuration settings that would help keep the size of the
database consistent (3,000,000 and below) and the timings of the run the
same for each run (approximately 1:15.00 seconds or below)?

Both the database growth and performance degradation were not expected.
Can anything be done to improve these to make them more consistent?  The
performance degradation appears to be a serious bug to me, perhaps in the
garbage collection code.

After discussing this a little bit with Alex Peshkoff I captured the values of
the oldest transaction, oldest active, oldest shapshot, next transaction and
generation values from the database after the 30th run of the initial test
and for a brand new empty database after the first 10 runs.


  Run     Oldest     Oldest   Oldest      Next      Generation
       Transaction   Active  Snapshot  Transaction
  ---  -----------  -------  --------  -----------  ----------
   30    155,446     155,447  155,447    155,451      155,860

    0          1          2         2          6            8
    1     15,547     15,548    15,548     15,550       15,592
    2     31,092     31,093    31,093     31,094       31,176
    3     46,636     46,637    46,637     46,638       46,760
    4     62,180     62,181    62,181     62,182       62,344
    5     77,724     77,725    77,725     77,726       77,928
    6     93,268     93,269    93,269     93,270       93,512
    7    108,812    108,813   108,813    108,814      109,096
    8    124,356    124,357   124,357    124,358      124,680
    9    139,900    139,901   139,901    139,902      140,264
   10    155,444    155,445   155,445    155,446      155,848


NOTE:  The stress test is not a standalone test that can easily be included in
an email.  It requires the installation of SAS software.  To recreate the test as a
standalone test will take some effort.  I thought I would start a discussion and
see where it goes.

Thanks in advance for your help,

Gary

___________________________________________________
Gary S. Franklin
SAS Institute Inc.               Phone: (512) 258-5171  Ext-55346
11920 Wilson Parke Ave.    FAX:    (512) 258-3906
Austin, TX  78726               Email:  Gar...@sa...
SAS...  The Power to Know