Kbase P142262: Application running extremely slow since migrating to 10.1B from 9.1D
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
04/08/2010 |
|
Status: Verified
SYMPTOM(s):
Application running extremely slow since migrating to 10.1B from 9.1D
Starting noticing performance impact immediately after migration.
Response time and results from all queries are slow.
promon gather script (iostat and sar -d) showing some disks are extremely busy.
Increasing -B parameter worsens the situation even though the same number of shared memory segments are being allocated.
FACT(s) (Environment):
Database was migrated to 10 using proutil dbname -C conv910
No errors are occurring.
5 databases in use on this machine.
Databases are Type I storage areas.
Databases being used / accessed by 4GL clients only.
Machine has multiple CPUs.
proutil dbname -C dbanalys shows some database fragmentation
promon gather script from solution P109740, "Script for gathering database information on Unix" shows minimal items to be tuned from the database perspective.
Upgraded from 10.1B to 10.1B03 and no difference is detected with regards to the performance issue.
All tables are in the Schema area.
Redirecting client temporary files to another drive did not increase performance of the client sessions.
Using Raid 5.
Sun Solaris SPARC 64-bit
Solaris 10
OpenEdge 10.1B 64-bit
OpenEdge 10.1B03 64-bit Service Pack
OpenEdge Category: Database
OpenEdge Category: Language (4GL/ABL)
CHANGE:
Converted from 9.1D to 10.1B
CAUSE:
The 4GL clients are bottlenecking on Disk I/O. The disks that are extremely busy are the drives where the 4GL client / user temporary files are being created. Writing to temporary files creates i/O activity that might slow down client performance. The amount of temporary file I/O activity varies dependant upon the application being used. In 10.1B there have been a couple of changes associated with the client temporary files (specifically Temp tables which are stored in the clients .dbi file. These changes have resulted in the files being larger than they were in version 9.
In 10.1X the following changes have occurred:
In 10.1B, the default tmpbsize (Temporary Table Block size) has been changed from 1KB block to a 4KB block. The temp-table database block size dictates the size of each buffer in the temp-table database buffer pool for the current OpenEdge Session.
In 10.1A the default for the -Bt (Number of Buffers for Temporary Tables) parameter has been changed from 10 to 255. This parameter is used to identify the number of buffers in the temporary table database pool.
Temporary files now also make use of a Type II storage areas instead of a Type I storage area (block by block allocation). Type II storage areas are comprised of clusters. A clusters is a contiguous allocation of space for one type of database object. Depending upon the amount of temporary files being used by the application, the DBI file could be much larger.
FIX:
There are several options to reduce the Disk I/O associated with the .dbi (Temporary file). They are:
1. Decrease the temp-table blocksize by using the -tmpbsize parameter on the client session startup. This will decrease the initial memory allocation associated the temporary tables. The more temp tables that are defined, the more memory/DBI space that may be consumed.
-tmpbsize 1
2. Redirect the client temporary file to a faster disk or a RAM Disk using the -T parameter on the startup of the client session.
3. Increase the amount of memory each client allocates for temp-table buffers using the -Bt parameter on the client session startup. The DBI file used when the temp-table buffers have been filled. This will increase the size of the memory footprint for each client. But if memory is not a concern, this may be a good alternative to the disk I/O.