DB performance issue with table
- Thread starter Shanmugam T
- Start date
Shanmugam T
New Member
I use to see 99% before changing -B value and now it is showing 100%. please suggest me if you find out way to improve performance.
Event Total Per Sec Event Total Per Sec
Commits 17442888 60.5 Undos 55057 0.2
Record Updates 10355100 35.9 Record Reads 40358042921 139915.9
Record Creates 3427494 11.9 Record Deletes 2318737 8.0
DB Writes 1945469 6.7 DB Reads 373862769 1296.1
BI Writes 1082985 3.8 BI Reads 190851 0.7
AI Writes 783880 2.7
Record Locks 141982333 492.2 Record Waits 602 0.00
Checkpoints 701 0.00 Buffs Flushed 8289 0.0
Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 99 % Writes by BIW 52 % Writes by AIW 91 %
Buffer Hits 100 %
DB Size 122 GB BI Size 5468 MB AI Size 9776 K
FR chain 10707 blocks RM chain 895011 blocks
Shared Memory 2079M Segments 1
4 Servers, 369 Users (368 Local, 1 Remote, 3 Batch),5 Apws
Rob Fitzpatrick
ProgressTalk.com Sponsor
It looks like you have several days' statistics here. Samples from shorter periods of time, say five to ten minutes, of heavy application use would provide more useful information.
As far as improving performance is concerned, the most important things you can do have been suggested: upgrade to a newer OpenEdge version and dump and load your data into Type II storage areas.
Have you set the -tablerangesize and -indexrangesize parameters properly yet? Once you do you will be able to see which tables and indexes are most accessed and which users are responsible for the most I/O.
As far as improving performance is concerned, the most important things you can do have been suggested: upgrade to a newer OpenEdge version and dump and load your data into Type II storage areas.
Have you set the -tablerangesize and -indexrangesize parameters properly yet? Once you do you will be able to see which tables and indexes are most accessed and which users are responsible for the most I/O.
Shanmugam T
New Member
Hi Rob,
I uploaded table and index statistics of normal day to day operation(not month end) - 2 files with 15mins time interval. please refer the attachment. also i found below info for tr_hist table.
Table name Create Update Read Delete
tr_hist 16556 23713 957500242 0
tr_hist 18249 25791 983293077 0
Diff 15 mins 1693 2078 25792835 0
Approximately there were 500 users
Event Total Per Sec Event Total Per Sec
Commits 1811076 37.6 Undos 6509 0.1
Record Updates 1262784 26.2 Record Reads 9548439693 198314.4
Record Creates 644879 13.4 Record Deletes 502334 10.4
DB Writes 281251 5.8 DB Reads 59618762 1238.2
BI Writes 187962 3.9 BI Reads 1952 0.0
AI Writes 117233 2.4
Record Locks 13461741 279.6 Record Waits 57 0.0
Checkpoints 104 0.00 Buffs Flushed 0 0.0
Rec Lock Waits 0 % BI Buf Waits 00 % AI Buf Waits 0 %
Writes by APW 100 % Writes by BIW 43 % Writes by AIW 91 %
Buffer Hits 100 %
DB Size 122 GB BI Size 5468 MB AI Size 27 MB
FR chain 11538 blocks RM chain 898566 blocks
Shared Memory 4198M Segments 1
4 Servers, 524 Users (523 Local, 1 Remote, 4 Batch),5 Apws
I uploaded table and index statistics of normal day to day operation(not month end) - 2 files with 15mins time interval. please refer the attachment. also i found below info for tr_hist table.
Table name Create Update Read Delete
tr_hist 16556 23713 957500242 0
tr_hist 18249 25791 983293077 0
Diff 15 mins 1693 2078 25792835 0
Approximately there were 500 users
Event Total Per Sec Event Total Per Sec
Commits 1811076 37.6 Undos 6509 0.1
Record Updates 1262784 26.2 Record Reads 9548439693 198314.4
Record Creates 644879 13.4 Record Deletes 502334 10.4
DB Writes 281251 5.8 DB Reads 59618762 1238.2
BI Writes 187962 3.9 BI Reads 1952 0.0
AI Writes 117233 2.4
Record Locks 13461741 279.6 Record Waits 57 0.0
Checkpoints 104 0.00 Buffs Flushed 0 0.0
Rec Lock Waits 0 % BI Buf Waits 00 % AI Buf Waits 0 %
Writes by APW 100 % Writes by BIW 43 % Writes by AIW 91 %
Buffer Hits 100 %
DB Size 122 GB BI Size 5468 MB AI Size 27 MB
FR chain 11538 blocks RM chain 898566 blocks
Shared Memory 4198M Segments 1
4 Servers, 524 Users (523 Local, 1 Remote, 4 Batch),5 Apws
Shanmugam T
New Member
Most probably, i will migrate the database from Type 1 to Type 2 storage by 4th week of Jan 2015. i will update the status post migration. Thank you all 
