[firebird-support] Re: Problem with application that creates hundreds of transactions per minute

[Caroline Beltran caroline.d.belt...@gmail.com [firebird-support]]

Here are some more details.  I used GBAK to backup and then restored the
database.  Here are the gstat details:

Database header page information:
Flags   0
Checksum12345
Generation  30
Page size   4096
ODS version 11.2
Oldest transaction  1
Oldest active   2
Oldest snapshot 2
Next transaction22
Bumped transaction  1
Sequence number 0
Next attachment ID  2
Implementation ID   26
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date   Dec 19, 2015 14:27:42
Attributes  force write

Variable header data:
Sweep interval: 0
*END*

I ran the import process again for several minutes and here are the revised
stats.  It had stalled again and I let it continue doing whatever it does.
I am not sure if I took these stats during or after the stall completed:

Database header page information:
Flags   0
Checksum12345
Generation  16527
Page size   4096
ODS version 11.2
Oldest transaction  16387
Oldest active   16388
Oldest snapshot 16388
Next transaction16515
Bumped transaction  1
Sequence number 0
Next attachment ID  37
Implementation ID   26
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date   Dec 19, 2015 14:27:42
Attributes  force write

Variable header data:
Sweep interval: 0
*END*

Is there something obviously wrong with the transactions?  Thank you.


On Sat, Dec 19, 2015 at 4:43 PM, Caroline Beltran <
caroline.d.belt...@gmail.com> wrote:

> I have happily used Firebird 2.5 for a web application that run 24x7 for
> at least 2 years.  The sweep interval is set to the default 20,000 and it
> works very well.  There is at least 10 inserts per minute but not much more
> than that.  I would guess that there are at least 20 reads per second and
> I've been completely happy with this trouble free application.
>
> Now for the other problematic application...
>
> This is also an online web application which runs wonderfully except
> during our data import process.  This process will INSERT on average about
> 200 records per minute.  But approximately 10% of those INSERTS require a
> large number of SELECTs and several UPDATEs to other records.  This is not
> a simple case of INSERTing new records.
>
> Everything will run very well for a short period of time but then
> everything just stalls for several minutes.  CPU consumption is always low,
> even when the stall happens.  But the I/O Read and Write is quite high,
> even when stalled.  This tells me that FB is busy doing something.
>
> For this particular database, I decided to set the Sweep Interval to 0 but
> this stalling still happens.
>
> Just as soon as I notice that FB stalls, I stop the data import process
> and let FB do whatever it needs to do until it finishes.  I can then resume
> data importation as normal until it stalls again in a few hours.
>
> Is it possible that sweeping is still being triggered even though I have
> set the interval to 0?
>
> Thank you
>

Re: [firebird-support] Re: Problem with application that creates hundreds of transactions per minute

[Caroline Beltran caroline.d.belt...@gmail.com [firebird-support]]

I read that CONSISTENCY imposes table locking even for read operations
which means that I will continue using CONCURRENCY mode.  Additionally, I
will not run both update processes at the same time to avoid the problem I
have been having.

On Sat, Dec 19, 2015 at 11:01 PM, Caroline Beltran <
caroline.d.belt...@gmail.com> wrote:

> Alan, I think you are right.  I backed up then restored my database.  Next
> I installed SuperServer and everything was going very well.  Here are my
> stats:
>
> Database header page information:
> Flags   0
> Checksum12345
> Generation  22516
> Page size   4096
> ODS version 11.2
> Oldest transaction  22474
> Oldest active   22475
> Oldest snapshot 22475
> Next transaction22476
> Bumped transaction  1
> Sequence number 0
> Next attachment ID  34
> Implementation ID   26
> Shadow count0
> Page buffers0
> Next header page0
> Database dialect3
> Creation date   Dec 19, 2015 20:00:46
> Attributes  force write
>
> Variable header data:
> Sweep interval: 0
> *END*
>
> Then I started a secondary process that also updates the database and
> within a few minutes, my stats did not look as they did before:
>
> Database header page information:
> Flags   0
> Checksum12345
> Generation  28442
> Page size   4096
> ODS version 11.2
> Oldest transaction  25127
> Oldest active   25128
> Oldest snapshot 25128
> Next transaction28396
> Bumped transaction  1
> Sequence number 0
> Next attachment ID  40
> Implementation ID   26
> Shadow count0
> Page buffers0
> Next header page0
> Database dialect3
> Creation date   Dec 19, 2015 20:00:46
> Attributes  force write
>
> Variable header data:
> Sweep interval: 0
> *END*
>
> This is when I noticed that things locked up.  I stopped all of the update
> processes and waited a minute or two and things went back to normal and
> everything was accessible.
>
> Database header page information:
> Flags   0
> Checksum12345
> Generation  29520
> Page size   4096
> ODS version 11.2
> Oldest transaction  29446
> Oldest active   29447
> Oldest snapshot 29447
> Next transaction29448
> Bumped transaction  1
> Sequence number 0
> Next attachment ID  66
> Implementation ID   26
> Shadow count0
> Page buffers0
> Next header page0
> Database dialect3
> Creation date   Dec 19, 2015 20:00:46
> Attributes  force write
>
> Variable header data:
> Sweep interval: 0
> *END*
>
>
> All of my non-mutable operations (e.g. SELECTs) are read access
> transactions with isolation mode readcommitted.
>
> All of my mutable operations (INSERTs and UPDATEs) are write access
> transactions with isolation mode concurrency with wait lock resolution.
>
>
> The weird thing about this is that I can run the primary process by itself
> or the secondary process by itself and there are no issues whatsoever.  But
> if I run both concurrently, that is when this issue happens.
>
> Process 1 SELECTs from table1 and table2 to determine the INSERTs and
> UPDATEs to table1, table2, and table3.
>
> Process 2 SELECTs from table1 and UPDATES table1
>
> I am now thinking that when both processes run concurrently transactions
> may be overlapping one another.  For example, a transaction to edit record1
> may begin while another transaction begins to edit other fields belonging
> to the same record.
>
> I may try changing my mutable operations from CONCURRENCY to CONSISTENCY
> to see if this helps.
>
> On Sat, Dec 19, 2015 at 8:32 PM, 'Alan McDonald' a...@meta.com.au
> [firebird-support]  wrote:
>
>>
>>
>> I can reproduce this 'stalling' behavior.  At the moment, the application
>> is importing records without delay.  CPU consumption remains very low.
>>
>> To stall FB, I will now run a query that will scan several thousands of
>> records that have just been INSERTed and UPDATEd.  Before I run this query,
>> here are my stats:
>>
>> Database header page information:
>> Flags   0
>> Checksum   

Re: [firebird-support] Re: Problem with application that creates hundreds of transactions per minute

[Caroline Beltran caroline.d.belt...@gmail.com [firebird-support]]

Alan, I think you are right.  I backed up then restored my database.  Next
I installed SuperServer and everything was going very well.  Here are my
stats:

Database header page information:
Flags   0
Checksum12345
Generation  22516
Page size   4096
ODS version 11.2
Oldest transaction  22474
Oldest active   22475
Oldest snapshot 22475
Next transaction22476
Bumped transaction  1
Sequence number 0
Next attachment ID  34
Implementation ID   26
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date   Dec 19, 2015 20:00:46
Attributes  force write

Variable header data:
Sweep interval: 0
*END*

Then I started a secondary process that also updates the database and
within a few minutes, my stats did not look as they did before:

Database header page information:
Flags   0
Checksum12345
Generation  28442
Page size   4096
ODS version 11.2
Oldest transaction  25127
Oldest active   25128
Oldest snapshot 25128
Next transaction28396
Bumped transaction  1
Sequence number 0
Next attachment ID  40
Implementation ID   26
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date   Dec 19, 2015 20:00:46
Attributes  force write

Variable header data:
Sweep interval: 0
*END*

This is when I noticed that things locked up.  I stopped all of the update
processes and waited a minute or two and things went back to normal and
everything was accessible.

Database header page information:
Flags   0
Checksum12345
Generation  29520
Page size   4096
ODS version 11.2
Oldest transaction  29446
Oldest active   29447
Oldest snapshot 29447
Next transaction29448
Bumped transaction  1
Sequence number 0
Next attachment ID  66
Implementation ID   26
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date   Dec 19, 2015 20:00:46
Attributes  force write

Variable header data:
Sweep interval: 0
*END*


All of my non-mutable operations (e.g. SELECTs) are read access
transactions with isolation mode readcommitted.

All of my mutable operations (INSERTs and UPDATEs) are write access
transactions with isolation mode concurrency with wait lock resolution.


The weird thing about this is that I can run the primary process by itself
or the secondary process by itself and there are no issues whatsoever.  But
if I run both concurrently, that is when this issue happens.

Process 1 SELECTs from table1 and table2 to determine the INSERTs and
UPDATEs to table1, table2, and table3.

Process 2 SELECTs from table1 and UPDATES table1

I am now thinking that when both processes run concurrently transactions
may be overlapping one another.  For example, a transaction to edit record1
may begin while another transaction begins to edit other fields belonging
to the same record.

I may try changing my mutable operations from CONCURRENCY to CONSISTENCY to
see if this helps.

On Sat, Dec 19, 2015 at 8:32 PM, 'Alan McDonald' a...@meta.com.au
[firebird-support]  wrote:

>
>
> I can reproduce this 'stalling' behavior.  At the moment, the application
> is importing records without delay.  CPU consumption remains very low.
>
> To stall FB, I will now run a query that will scan several thousands of
> records that have just been INSERTed and UPDATEd.  Before I run this query,
> here are my stats:
>
> Database header page information:
> Flags   0
> Checksum12345
> Generation  65205
> Page size   4096
> ODS version 11.2
> Oldest transaction  65091
> Oldest active   65092
> Oldest snapshot 65092
> Next transaction65193
> Bumped transaction  1
> Sequence number 0
> Next attachment ID  64
> Implementation ID   26
> Shadow count0
> Page buffers0
> Next header page0
>