daveprice74
New Member
(The 8.3A nightmare continues...)
So we finally get this 8K database (matching our 8k OS blocksize) loaded, everything seems great (i.e. much faster than 1K)... but now and then we're getting a very nasty pause. Database just hangs, not responding, acts just like an AI/BI stall except there's no message in the log and eventually it just starts going again after 1-20 minutes of pausing.
Does this sound like -B? We were going 45000 on the main, which at 8K obviously is more than the 225000 we had on for 1K earlier, but maybe not enough? Do we need more APWs? More bibufs/aibufs? We moved -B to 65000 (edging closer the 1.75G limit for all 32-bit apps combined on 11.11 64-bit), we bumped aibufs and bibufs from 500 to 2000, and doubled APWs from 4 to 8. Fingers crossed right now, seems better atm.
It's almost like this new 8K database is too fast for... something, and it has to wait while something else catches up. But I don't know what.
We can roll back to our 1K database if we have to (it's still out there), but that's going to be moderately painful too.
Thanks in advance for any help from the always-knowledgable ProgressTalk gurus. (And sorry for hogging all the advice today! I think this is in the running for the worst day of my professional life.)
Some promon performance screens (this is AFTER the adjustments, so maybe not relevant, but fwiw...):
09/03/12 Activity: Performance Indicators
23:18:03 09/03/12 22:18 to 09/03/12 23:18 (59 min 53 sec)
Total Per Min Per Sec Per Tx
Commits 629293 10508 175.14 1.00
Undos 0 0 0.00 0.00
Index operations 169712 2834 47.23 0.26
Record operations 339268228 644929 10748.82 61.37
Total o/s i/o 1278546 21350 355.84 2.03
Total o/s reads 1165602 19464 324.40 1.85
Total o/s writes 112944 1886 31.43 0.17
Background o/s writes 112752 1883 31.38 0.17
Partial log writes 33080 552 9.20 0.05
Database extends 0 0 0.00 0.00
Total waits 36037 601 10.02 0.05
Lock waits 0 0 0.00 0.00
Resource waits 36037 601 10.02 0.05
Latch timeouts 122715 2049 34.15 0.19
Buffer pool hit rate: 2 %
09/03/12 Activity: Summary
23:19:11 09/03/12 22:18 to 09/03/12 23:18 (59 min 53 sec)
Event Total Per Sec |Event Total Per Sec
Commits 629293 175.1 |DB Reads 1163272 323.7
Undos 0 0.0 |DB Writes 71537 19.9
Record Reads 338754135 94281.6 |BI Reads 928 0.2
Record Updates 351694 97.8 |BI Writes 24705 6.8
Record Creates 81661 22.7 |AI Writes 16674 4.6
Record Deletes 80738 22.4 |Checkpoints 33 0.0
Record Locks 14844164 4131.4 |Flushed at chkpt 74 0.0
Record Waits 0 0.0
Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 99 % Writes by BIW 32 % Writes by AIW 99 %
DB Size: 67107840 K BI Size: 1572848 K AI Size: 132200 K
Empty blocks:3521508 Free blocks: 0 RM chain: 2
Buffer Hits 2 % Active trans: 4
0 Servers, 15 Clients (15 Local, 0 Remote, 4 Batch), 8 Apws
UPDATE: Parms:
# Startup Parameters for mfgpro85e database
-aibufs 2000
-aistall
-bibufs 2000
-bithold 999
-bistall
-B 65000
-Bt 1000
-c 250
-cpcoll basic
-cpinternal iso8859-1
-cprcodein iso8859-1
-cpstream ibm850
-D 500
-H hpqadprd
-inp 5000
-L 720000
-Ma 4
-Mi 3
-mmax 3500
-Mn 42
-Mf 30
-n 300
-nb 255
-N TCP
-s 180
-TB 31
-TM 31
-yy 1930
other info:
DB Blocks: 65,535.00 MB
DB HWM: 38,023.78 MB
Free DB Blocks Below DB HWM: 0.00 MB
Total Free Blocks: 27,511.22 MB
Total DB Size Used: 38,023.78 MB
Percent Used: 58.02057%
Total RM Blocks w/Free Space: 0.02 MB
BI HWM: 24.00 MB Total AI File Size: 358.25 MB
AI HWM: 263.30 MB Total DB File Size: 67,430.27 MB
We also have a side database which I don't think is the problem -- the issues seem to be with the main.
So we finally get this 8K database (matching our 8k OS blocksize) loaded, everything seems great (i.e. much faster than 1K)... but now and then we're getting a very nasty pause. Database just hangs, not responding, acts just like an AI/BI stall except there's no message in the log and eventually it just starts going again after 1-20 minutes of pausing.
Does this sound like -B? We were going 45000 on the main, which at 8K obviously is more than the 225000 we had on for 1K earlier, but maybe not enough? Do we need more APWs? More bibufs/aibufs? We moved -B to 65000 (edging closer the 1.75G limit for all 32-bit apps combined on 11.11 64-bit), we bumped aibufs and bibufs from 500 to 2000, and doubled APWs from 4 to 8. Fingers crossed right now, seems better atm.
It's almost like this new 8K database is too fast for... something, and it has to wait while something else catches up. But I don't know what.
We can roll back to our 1K database if we have to (it's still out there), but that's going to be moderately painful too.
Thanks in advance for any help from the always-knowledgable ProgressTalk gurus. (And sorry for hogging all the advice today! I think this is in the running for the worst day of my professional life.)
Some promon performance screens (this is AFTER the adjustments, so maybe not relevant, but fwiw...):
09/03/12 Activity: Performance Indicators
23:18:03 09/03/12 22:18 to 09/03/12 23:18 (59 min 53 sec)
Total Per Min Per Sec Per Tx
Commits 629293 10508 175.14 1.00
Undos 0 0 0.00 0.00
Index operations 169712 2834 47.23 0.26
Record operations 339268228 644929 10748.82 61.37
Total o/s i/o 1278546 21350 355.84 2.03
Total o/s reads 1165602 19464 324.40 1.85
Total o/s writes 112944 1886 31.43 0.17
Background o/s writes 112752 1883 31.38 0.17
Partial log writes 33080 552 9.20 0.05
Database extends 0 0 0.00 0.00
Total waits 36037 601 10.02 0.05
Lock waits 0 0 0.00 0.00
Resource waits 36037 601 10.02 0.05
Latch timeouts 122715 2049 34.15 0.19
Buffer pool hit rate: 2 %
09/03/12 Activity: Summary
23:19:11 09/03/12 22:18 to 09/03/12 23:18 (59 min 53 sec)
Event Total Per Sec |Event Total Per Sec
Commits 629293 175.1 |DB Reads 1163272 323.7
Undos 0 0.0 |DB Writes 71537 19.9
Record Reads 338754135 94281.6 |BI Reads 928 0.2
Record Updates 351694 97.8 |BI Writes 24705 6.8
Record Creates 81661 22.7 |AI Writes 16674 4.6
Record Deletes 80738 22.4 |Checkpoints 33 0.0
Record Locks 14844164 4131.4 |Flushed at chkpt 74 0.0
Record Waits 0 0.0
Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 99 % Writes by BIW 32 % Writes by AIW 99 %
DB Size: 67107840 K BI Size: 1572848 K AI Size: 132200 K
Empty blocks:3521508 Free blocks: 0 RM chain: 2
Buffer Hits 2 % Active trans: 4
0 Servers, 15 Clients (15 Local, 0 Remote, 4 Batch), 8 Apws
UPDATE: Parms:
# Startup Parameters for mfgpro85e database
-aibufs 2000
-aistall
-bibufs 2000
-bithold 999
-bistall
-B 65000
-Bt 1000
-c 250
-cpcoll basic
-cpinternal iso8859-1
-cprcodein iso8859-1
-cpstream ibm850
-D 500
-H hpqadprd
-inp 5000
-L 720000
-Ma 4
-Mi 3
-mmax 3500
-Mn 42
-Mf 30
-n 300
-nb 255
-N TCP
-s 180
-TB 31
-TM 31
-yy 1930
other info:
DB Blocks: 65,535.00 MB
DB HWM: 38,023.78 MB
Free DB Blocks Below DB HWM: 0.00 MB
Total Free Blocks: 27,511.22 MB
Total DB Size Used: 38,023.78 MB
Percent Used: 58.02057%
Total RM Blocks w/Free Space: 0.02 MB
BI HWM: 24.00 MB Total AI File Size: 358.25 MB
AI HWM: 263.30 MB Total DB File Size: 67,430.27 MB
We also have a side database which I don't think is the problem -- the issues seem to be with the main.