| Subject | Re: [IBO] Transaction management and AutoCommit |
|---|---|
| Author | Helen Borrie |
| Post date | 2014-03-27T03:02:01Z |
At 11:25 a.m. 27/03/2014, Robert Martin wrote:
(a) whether your app is overdoing the commits where it should be committing only on completion of the task (happens if you've chosen to go down the old Borland path and totally ignore database transactions)
and
(b) whether you have checked to ensure none of your transactions is using CommitRetaining (also an old Borland ploy to make Paradox and dBase programmers OK with ignoring the fact that they've moved from a desktop, file-driven data storage system to a multi-generational, server-based data management system)
(c) whether you are using TIBOTables (aaaarrrgh!!)
On the other hand, that might be one old transaction that you started on Day 5 (such as a query on the monitoring tables) that you can easily remedy.
Flags 0
Checksum 12345
Generation 1554500
Page size 8192
ODS version 11.2
Oldest transaction 632200
Oldest active 632201
Oldest snapshot 632201
Next transaction 1489694
Bumped transaction 1
Sequence number 0
Next attachment ID 64798
Implementation ID 26
Shadow count 0
Page buffers 20000
Next header page 0
Database dialect 3
Creation date Mar 13, 2014 9:23:30
Attributes force write
So, a million and a half transactions started since you restored 13 days ago, but maybe most of them today. On the averages, that OAT goes right back to Day 5 or 6. And you've had at least 5000 logins a day in that time. Not all production users, presumably.
The cache size you have set here is absurd - this is Superclassic, right? SC does not have a global cache like Superserver. You have an 8KB page size so 20000 * 8 = 160,000 KB (about 156 MB) of server RAM being reserved for *each* of those attachments. For an 8KB page size with reasonable RAM resources on the host server, think in the low HUNDREDS of pages for the caches, e.g., 128, not TWENTY THOUSAND! That's not an IBO issue - unless everyone is logging in as SYSDBA and setting page buffers from the application - ouch! Check that this isn't so.
So, yeah, this looks a bit like an iceberg. Suspect CommitRetaining and/or long-running read/write transactions, wherein a close examination of your component properties could be game-changing.
That's it from me.
Helen
>Hi HelenLooking closer at your gstat -h numbers below and the problem you were looking at, I should insert here the comment that you possibly have a shipload of old but active transactions sitting about, neglected. More comments anon....
>
>Thanks for your great response :)
>
>On 27/03/2014 10:50 a.m., Helen Borrie wrote:
>> Not really. Next Transaction is not interesting from the POV of transaction management in your application. It hasn't happened yet!
>> Your gstat figures don't suggest a problem with transaction management, at least in terms of a stuck OIT.However.... read on....
>I have a large application that used to be based on DBase. DuringAutocommit is often the lazy way out for conversions from old desktop database applications. Its actual usefulness is where you have a repeating task of multiple steps, where just parameters change from one (database) transaction to another. Efficient implementation wraps the steps of the task in one transaction. In theory, Autocommit should be a good choice for a POS system task since the tasks are intended to complete quickly. At 115,000 (database) transactions a day I'd wonder
>conversion to FB we used the AutoCommit functionality to avoid having to
>re code large parts of the application.
(a) whether your app is overdoing the commits where it should be committing only on completion of the task (happens if you've chosen to go down the old Borland path and totally ignore database transactions)
and
(b) whether you have checked to ensure none of your transactions is using CommitRetaining (also an old Borland ploy to make Paradox and dBase programmers OK with ignoring the fact that they've moved from a desktop, file-driven data storage system to a multi-generational, server-based data management system)
(c) whether you are using TIBOTables (aaaarrrgh!!)
On the other hand, that might be one old transaction that you started on Day 5 (such as a query on the monitoring tables) that you can easily remedy.
>I was doing some stabilityOK, not out of field, since you are observing slow-downs,although this type of application involves a lot of inserting and probably not a lot of updating or deleting, comparatively.
>tests by simply processing a POS (Point of Sale) process repeatedly
>(1million times). I noticed that the time to process each POS
>transaction slowly increased. I did a GStat and noticed the big gap
>between 'Next Transaction' and the Oldest transaction entries and
>thought this could be the culprit!
>I have just checked on the machine and POS transactions have slowed toDatabase header page information:
>taking 42s each !!!!! after about 500,000. GStat gives me the
>following....
Flags 0
Checksum 12345
Generation 1554500
Page size 8192
ODS version 11.2
Oldest transaction 632200
Oldest active 632201
Oldest snapshot 632201
Next transaction 1489694
Bumped transaction 1
Sequence number 0
Next attachment ID 64798
Implementation ID 26
Shadow count 0
Page buffers 20000
Next header page 0
Database dialect 3
Creation date Mar 13, 2014 9:23:30
Attributes force write
So, a million and a half transactions started since you restored 13 days ago, but maybe most of them today. On the averages, that OAT goes right back to Day 5 or 6. And you've had at least 5000 logins a day in that time. Not all production users, presumably.
The cache size you have set here is absurd - this is Superclassic, right? SC does not have a global cache like Superserver. You have an 8KB page size so 20000 * 8 = 160,000 KB (about 156 MB) of server RAM being reserved for *each* of those attachments. For an 8KB page size with reasonable RAM resources on the host server, think in the low HUNDREDS of pages for the caches, e.g., 128, not TWENTY THOUSAND! That's not an IBO issue - unless everyone is logging in as SYSDBA and setting page buffers from the application - ouch! Check that this isn't so.
So, yeah, this looks a bit like an iceberg. Suspect CommitRetaining and/or long-running read/write transactions, wherein a close examination of your component properties could be game-changing.
That's it from me.
Helen