Subject | RE: [firebird-support] Re: Problem with application that creates hundreds of transactions per minute |
---|---|
Author | Alan McDonald |
Post date | 2015-12-20T02:32:01Z |
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 12345
Generation 65205
Page size 4096
ODS version 11.2
Oldest transaction 65091
Oldest active 65092
Oldest snapshot 65092
Next transaction 65193
Bumped transaction 1
Sequence number 0
Next attachment ID 64
Implementation ID 26
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Dec 19, 2015 14:27:42
Attributes force write
Variable header data:
Sweep interval: 0
*END*
The query normally takes less a few seconds and is now stalled (7:18PM). My application's import process has also stalled and the web application is not serving pages. FB CPU consumption is at approx 1-2% and Read and Write I/O is very active, there is plenty of disk activity going on.
I was using FB 2.5.4 and upgraded to 2.5.5 Superserver but the problem is the same. Running on Windows 2012.
The FB Server is definitely doing something, 35 minutes later, here are the stats:
Database header page information:
Flags 0
Checksum 12345
Generation 66143
Page size 4096
ODS version 11.2
Oldest transaction 65091
Oldest active 65092
Oldest snapshot 65092
Next transaction 66131
Bumped transaction 1
Sequence number 0
Next attachment ID 84
Implementation ID 26
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Dec 19, 2015 14:27:42
Attributes force write
Variable header data:
Sweep interval: 0
*END*
Unfortunately, this is going painfully slow and I will shut down FB 2.5.5, backup and restore the database, and try with SuperClassic instead of SuperServer.
Sounds more like a transaction management issue to me.
You need to tell us what component set you are using for connection and the transaction settings they use.
Your insert transactions should be committing, not commit-retaining or whatever your component set describes this as.
You should do the inset in batches and hard commit more frequently.
Alan