 |
The Progress OpenEdge Database Administration Experts |
 |
Tuning -mmax
From PROGRESSIONS #29 Fall 1997
Performance Analysis & Tuning
Many people know that temporary file operations can cause a considerable amount of IO in a Progress based application. This IO can have a serious impact on an application's performance.
The breakdown of IO operations on a system which I recently worked with was as follows:
| Filesystem | Logical IO | Physical IO | %Logical IO | %Physical IO |
|---|---|---|---|---|
| $DLC | 205.0 | 0.1 | 19.58 | 0.07 |
| Application | 236.0 | 6.0 | 22.58 | 4.47 |
| App Spool | 8.1 | 2.4 | 0.77 | 1.79 |
| Database | 31.0 | 27.0 | 2.97 | 20.40 |
| User Temp | 89.0 | 87.0 | 8.47 | 66.34 |
| After Image | 0.7 | 0.7 | 0.07 | 0.52 |
| Before Image | 1.2 | 1.2 | 0.11 | 0.89 |
This table shows the number of logical and physical IO requests (from UNIX's point of view) per second to the indicated filesystems followed by the percentage of IO requests (systemwide) that this represents. The system is an HP T520 with 14 120MHZ processors, 3.75GB of RAM and an EMC 3700 disk array (2GB RAM cache, 32 disks) attached to it. 400 self service users were on the system at that time.
Temporary file physical IO is three times greater than database IO activity. Different systems will have different IO profiles, of course, but it isn't uncommon for temp file IO to be a significant, and even dominant, portion of the IO picture.
An interesting side observation is that the UNIX buffer cache is largely ineffective in dealing with both the db and temp file IO even though it was quite large (400MB) at the time that these measurements were taken. Presumably this is because Progress has already cached these IOs quite effectively in the -B and -mmax buffers virtually guaranteeing that any IO request of this sort won't be found in the UNIX buffer cache. The ai and bi filesystem activity consists almost entirely of writing new data and thus, obviously, can't be effectively cached. The UNIX buffer cache is, however, quite effective at dealing with more traditional filesystem IO such as that presented by reading PROMSGS (the likely source of the $DLC activity), loading r-code the first time and the application's manipulation of temporary OS files (primarily printer output).
Progress temporary files fall into several categories. Every user has one of each of the listed file types associated with their process and may have others depending on the use of various Progress features. Production systems do not typically use the other categories of temporary files so they are not discussed here:
| File Type | Usage |
|---|---|
| DBI | Temporary table storage. |
| LBI | Subtransaction undo logs. |
| SRT | Temporary sort space and r-code swapping. |
These files are store unlinked in the UNIX filesystem which means that they have no directory entry and will be simply deleted in the event of a system outage. It is possible to circumvent this and allow the file to be visible. This is important since it allows us to identify users who are outside of normal behaviors and to focus tuning efforts on those users. Therefore the -t (lower case "t") startup option should be added to the client startup. This requires that the -T (upper case "T") area be swept clean after a crash but that can easily and safely be made an automated part of the standard broker startup script.
The following UNIX command line shows the number of users whose Progress temp-tables have overflowed and used disk space. (The DBI temp file is initialized to 2048 bytes. The spaces in the grep command are important.)
DBI activity was virtually non-existent; fewer than 1% of this applications users sessions ever use disk for temp tables - indicating that the -Bt parameter can be reduced and that memory returned to the system for other uses.
LBI activity is caused by subtransaction undo logs and undo variable definitions. The code uses no-undo variable definitions and subtransactions are (or should be) quite rare given the architecture of the application. So any LBI activity indicates that a user has run a program that needs to be examined. Most users (80%) have some, but not much, LBI activity (25% have none at all). In any given time period the number of LBI files modified is a small fraction of the total number of users. So things aren't perfect but this isn't the main source of -T IO. That leaves SRT file activity as the most likely suspect.
SRT file activity is driven in two ways. A program may request data from the database manager without specifying an index which completely resolves the query. In that case the database manager will return the smallest set of records that are needed for the client program to sort and resolve the query itself. These records will be stored and sorted using the users SRT file. Does a -TB * -TM buffer get used first?
The other driver of SRT file activity is r-code swapping. The Progress 4gl code is compiled into r-code which is then interpreted at runtime. This r-code is read from the disk into a buffer in memory. The buffer's size is regulated by the -mmax Progress startup parameter. This parameter applies to each user's session individually. It is possible (but unusual) for each user to have a different value. If the user executes enough r-code procedures to fill the -mmax buffer (the next segment won't fit into the remaining memory) then Progress will select the least recently used segment of r-code and write it out to the SRT file (unless it was read from a Progress library file). Progress will read it back in when and if it is needed again.
There are circumstances where Progress must expand the -mmax buffer beyond the value specified at startup. Progress cannot swap out "active" segments. Action code and (current language) text segments for persistent procedures and those procedures on the procedure call stack plus the e-code segment for an expression being evaluated as well as the frame and initialization segments (during r-code initialization) are active segments. If no inactive segment can be swapped to the SRT file then Progress will expand the -mmax buffer (unless -hardlimit was also specified).
The -y client startup parameter causes a file to be written out at the end of a users session which provides critical data regarding r-code swapping. Use the CLIENTMON environment variable to direct these files to a different location for each user (see figure a for a suggested method for doing so). There are several lines in this file of interest:
Reads from and Writes to the temp file indicate r-code swapping and each incurs at least one IO operation (SRT file activity is in -TB sized chunks). If there are any reads/writes to the temp file then the -mmax buffer has been filled and r-code swapping is taking place. The Max Used column of the R-Code Execution Buffer line is usually close to but not always equal to the Limit shown in the memory usage summary if swapping is taking place. Client monitoring data that I collected over a two month period (30,000 sessions) clearly showed that at least 60% of this applications users were in fact filling their -mmax buffers and writing to the SRT file. The 60% of users exceeding the buffer made an average of 3,100 requests per day each for this purpose. (See figure b for example scripts to obtain this data.)
These numbers work out to an average of at least 40 IO requests per second to the -T filesystem. Or at least half of all the requests to the filesystem responsible for two thirds of the applications IO rate. It is, however, quite likely that the distribution of these requests is far from even and that the rate increases substantially throughout the day as the -mmax buffer fills (only end of session totals are available and most sessions end near 5pm local time).
A potential solution to this problem is to use Progress r-code libraries. These libraries bundle a set of procedures into a single operating system file eliminating the need for the swap out portion of the process. Slightly more than half of the observed SRT file activity is segments being written. All of this write activity could be eliminated by using r-code libraries. Eliminating the write activity is especially valuable since it would reduce the number of dirty blocks in the UNIX buffer pool when a sync() call is made (every 30 seconds) to flush dirty blocks from memory to disk saving still more system time and resources. R-code libraries do, however, take effort to setup and maintain and only address part of the problem.
A more complete solution is to increase -mmax. Increasing the -mmax buffer means that potentially more RAM may be consumed as users allocate space for the -mmax buffer. It is important to remember that the -mmax buffer is not statically allocated at session startup. Users who do not use or need the full -mmax buffer do not consume additional memory. As users work within the application they will execute r-code and the buffer will expand to hold that r-code until the -mmax limit is reached.
A larger buffer would permit a greater number of r-code segments to stay resident and will reduce the IO rate to the -T filesystems. The reduction gained will be reflected in the client monitoring data being collected for each user (-y). Specifically the number of reads and writes to the SRT file will be reduced and the percentage of users exceeding their -mmax buffer will be reduced. This will, in turn, reduce the number of IO operations being requested of the usertemp filesystem reducing the amount of system time spent servicing those requests and the load on the associated cache and drives. The actual magnitude of the impact is, however, difficult to gauge in advance.
When -mmax was changed from 512 to 1536 we observed a reduction in IO operations on the -T filesystem from 89/sec to 70/sec, a reduction in users swapping from 60% to 47% and an increase in memory utilization of only a few hundred megabytes. Obviously it helped but there was plenty of room for improvement. Our next change was to increase -mmax from 1536 to 3072. We expected this to leave us with about 10% of the users still doing some r-code swapping and we hoped to get the IO rate on the -T filesystems down to around 25/sec. Instead we completely eliminated SRT file activity and got -T activity down to less than 1 IO operation per second (primarily LBI fileactivity).
Setting -mmax to an appropriate value can be quite expensive. In our case we now have 500+ users who could potentially consume 3MB of RAM apiece or 1.5GB of RAM. All of the r-code in the application could be held in a single shared 130MB cache rather than in individual caches. This would significantly reduce Progress' footprint on larger systems.
Several alternative methods for implementing a shared cache have been thought about in Bedford (I have no idea if they'll be in any particular release although I would, quite naturally, like to see a solution ASAP). One, relatively simple solution, would be to simply make the -mmax buffer shared memory. A broker would probably be required to create and maintain that memory but very little would need to be done to the current r-code management routines to implement this. Another, very interesting, possibility would be to store the r-code in a database and allow the db cache mechanisms to manage the r-code just as they currently manage database blocks. Either solution would be in line with PSC's traditional strengths and would enhance Progress as an easy to manage yet extermely powerful application solution.
Figure a:
Figure b:
