Progress Performance FAQ V2.1, 10/02/96 Greg Smith (gsmith@westnet.com) Revision History: 1.0 11/06/95 First complete, official release 1.01 11/09/95 Minor correction to 2.1.4 (Conversion) 1.02 12/02/95 Cosmetic changes to how I create the document for easier updates 1.1 02/06/96 Revisions to all of 4.5 (dump/reload). Added 3.2.1 (schema caching), 1.4 (suggested reading path), 2.1.5 (V8). Notes sprinkled throughout with references to other places to look for information on some topics, mostly to Dan Foreman's book. 1.2 04/07/96 Removed the examples section and renumbered accordingly. Added sections 2.1.7 (Workgroup vs. Enterprise), 2.3.4 (Server vs. Broker), 4.7.1 (How much space in one file/table). Added information on promon in 1.3.1, updated client parameters in 3.2, new stuff on extents in 3.3, keeping users from deleting the database under UNIX in 3.4, corrected description of BI use in 3.9.1, noted use of dbanalys utility to 4.7 2.0 09/02/96 Updated 2.1.4 with caveat about V7 memory use, 2.3.4 updates the server vs. broker distinction, 3.2 has new client parameters, 3.4 mentions buffered AI, 3.8.1 has more APW suggestions, 3.8.3 has a caveat with the AIW, 3.9.1 has BI cluster size recommendations, 3.10 has more on the 63K .r code limit, 5.2 has a variety of new UNIX specific information, added info to 7.1.2 on getting Knowledge Base entries. Added 1.3.4 (VMS monitoring), 1.5 (The Performance Big Picture), 3.3.4 (OS logical volumes usage), 3.9.2 (BI Buffer Caching), 4.5.6 (What's procopy good for?) 4.9 (Controlling misbehaving Progress processes), 7.2.8 (Software). Changed Word document to print nicely. 2.1 10/02/96 Added 3.9.5 (BI Formatting), 5.2.10 (Digital UNIX specifics). Updated 3.2 with some more client parameters, 3.8 discusses writer process precedence. Note that items in section 7 (such as WWW info, books, contributors, etc.) are updated on almost every release without specific comments in the listing above. There are endless minor corrections that I don't bother documenting as well throughout. The master source for this FAQ at the moment is my home page http://www.westnet.com/~gsmith Updates will get put there first, I may distribute copies by other methods as well (http://www.adio.com/progress has been known to mirror it, and some reasonably current versions are usually available at ftp.peg.com). There's an Acrobat PDF version available in the White Papers section of http://www.every.com/streetSMART. Comments, suggestions, additions, or corrections are best e- mailed to gsmith@westnet.com. If you get me to add or change something I'll even add you to my list of influences at the end; what a bonus, right? Feel free to distribute this, put it on your FTP site, include links to my page on yours, or otherwise use and abuse it. The only thing I ask is that you make sure to include my name, e-mail address, and home page address if you snip away a part of the document to give to someone; that way they can get a copy of the full document if they want, and I get my additional couple of seconds of fame. Table of Contents 1. INTRODUCTION 1.1 How do I know I have a problem? 1.2 I don't have time to read everything out there, what are some quick suggestions? 1.3 How can I tell what's going on? 1.3.1 Using promon on shared memory systems 1.3.2 Monitoring UNIX systems 1.3.3 DOS, Windows, NLM 1.3.4 VMS 1.4 Suggested reading path 1.5 The Performance Big Picture 1.5.1 Benchmarking 1.5.2 Record access speed as the bottleneck 1.5.3 Transactions as the bottleneck 2. PROGRESS PERFORMANCE MAJOR COMPONENTS 2.1 Which Progress version should I use? 2.1.1 6.2 2.1.2 6.3 2.1.3 7.X 2.1.4 8.X 2.1.5 Conversion Issues 2.1.6 What's the difference between Workgroup and Enterprise versions of Progress? 2.2 Why are shared memory systems better? 2.3 What's the best connection mode? 2.3.1 Single user 2.3.2 Direct connect (self service) 2.3.3 Client/server (remote service) 2.3.4 What's the difference between a server and a broker? 2.4 What's the difference between character and GUI modes? 2.4.1 What about GUI with Windows? 2.4.2 What about GUI with X-windows? 2.5 Database design 2.5.1 How does database size change things? 2.5.2 What's all this normal form stuff mean? 2.5.3 How do I optimize the database for queries? 2.5.4 How big should my records be? 2.5.5 Core database and connected database architecture 2.5.6 Multiple connected databases 2.6 Speeding up Progress 4GL programs 3. PROGRESS SPECIFIC PARAMETER AND PROCESS TUNING 3.1 What should my server parameters be set to? 3.1.1 How big do I set -B to? 3.1.2 Shared memory servers 3.2 Client parameters 3.2.1 Schema caching 3.3 Why use a multivolume database? 3.3.1 File system considerations 3.3.2 Single process access limitation 3.3.3 Direct (raw) file systems 3.3.4 What should operating system logical volumes be used for? 3.4 Are after image files worth it? 3.5 How much RAM do I need? 3.5.1 RAM on servers 3.5.2 RAM on clients 3.6 Temp files 3.6.1 Temp files on servers 3.6.2 Temp files on clients 3.7 Multiprocessor systems 3.7.1 Using -spin 3.8 What do all these writer processes do? 3.8.1 Tuning APW 3.8.2 Tuning BIW 3.8.3 Tuning AIW 3.9 Why is the before image file important? 3.9.1 Changing BI Cluster and block size 3.9.2 BI Buffer caching 3.9.3 Why you shouldn't use -bibufsize 3.9.4 Delaying transactions with -M 3.9.5 BI Formatting 3.10 Application .r code 4. SPECIALIZED PROGRESS SOLUTIONS 4.1 Read only databases 4.2 Disabled indices 4.3 Running in no integrity mode 4.4 Faster index rebuilds 4.4.1 Index rebuilds on very large databases 4.5 My dump/reload takes forever; what can I do? 4.5.1 Why should I bother doing a dump and reload, anyway? 4.5.2 Will a dump/reload decrease the database size? 4.5.3 What's a parallel dump/reload? 4.5.4 Incremental loading methods 4.5.5 Binary dump and load 4.5.6 What's procopy good for? 4.6 Off-hours batch programs 4.7 Ongoing database maintenance 4.7.1 How do I tell how much space is taken up by one particular file/table? 4.8 Backup and restore operations 4.9 Controlling misbehaving Progress programs 5. PLATFORM-SPECIFIC RECOMMENDATIONS 5.1 What about Novell NLM servers? 5.1.1 Optimizing the server computer 5.1.2 Optimizing the NLM 5.2 UNIX Kernel parameters 5.2.1 Semaphores and shared memory 5.2.2 Process and memory limits 5.2.3 DG/UX Specifics 5.2.4 SCO UNIX Specifics 5.2.5 System V R4 Specifics 5.2.6 AIX Specifics 5.2.7 HP/UX Specifics 5.2.8 Sequent specifics 5.2.9 UnixWare specifics 5.2.10 Digital UNIX specifics 6. HARDWARE SELECTION 6.1 Bus architecture 6.2 Disk subsystem 6.2.1 What about disk controllers? 6.2.2 How many drives do I need? 6.2.3 Should I use SCSI or IDE drives? 6.2.4 What type of SCSI interface do I need? Fast? Wide? 6.2.5 What about caching? 6.3 Why should or shouldn't I use mirroring or RAID drives? 6.3.1 RAID Data integrity 6.3.2 RAID Performance 6.4 I've got a bunch of disk drives; Where should I put things? 6.5 What about the network? 7. CREDITS AND ADDITIONAL RESOURCES 7.1 How did you find out all this? 7.2 Where can I get more information? 7.2.1 Progress Software 7.2.2 PEG e-mail list 7.2.3 Progress WWW sites 7.2.4 Newsgroups (comp.databases.progress) 7.2.5 Progress FTP sites 7.2.6 Books 7.2.7 Magazines 7.2.8 Software 1. Introduction This FAQ was written in an attempt to describe the major factors that influence the performance of a computer running the Progress database system. The best resource is to find those who have worked with systems similar to your own who can give specific recommendations. But there is a set of common solutions that often work and should be investigated and tested before asking for specific help. This FAQ presents the common solutions so you can have the proper background before trying to get help for your particular case. The information here was gathered through a variety of sources (credits are listed at the end), but ultimately any errors are my fault. There are a variety of risky things discussed here; you should never be experimenting with performance improvements on any database without a current, verified backup. A test system just for such purposes is even better, but prohibitive in cost for most sites. I should probably insert a bunch of legalese here to cover my butt, but what it boils down to is the suggestions here are tried at your risk; don't blame me if your database goes up in flames because you tried messing around with the filesystem, your last backup was two years ago, and you can't even read the tape when you try it. Progress is a trademark of Progress Software Corporation. They can be contacted at (617) 280-4000. NetWare belongs to Novell, DG/UX to Data General, HP/UX to Hewlett Packard, VMS to DEC, and AIX to IBM. Who knows the owner of the UNIX trademark nowadays; last time I checked, it was SCO. Windows is a registered trademark of Microsoft, who would probably be quite happy to sue me I didn't mention that. If I missed anybody else's trademark, I'm sure I'll hear about it, hopefully by some other method then a summons. 1.1 How do I know I have a problem? Usually, if there's a performance problem with your systems, you don't have to ask this; the users will be knocking down your door trying to get things to go faster. It's a good idea to do your testing when performance is going well, though; that way, you have a little more slack in case you accidentally slow things down instead of improving them. 1.2 I don't have time to read everything out there, what are some quick suggestions? For most Progress systems, the first things recommended are: 1. Check the -B parameter to make sure you're using the memory you've got 2. Check out the system performance information to make sure what the bottleneck is, tune system parameters to improve it 3. Add more RAM and increase -B to use it 4. Add more disk drives to the system, move files around to balance the load on each drive Beyond that, you're probably going to have to chew on quite a bit of information before you see something that helps you out. Even this "summary" is rather long, but that's because there is no getting around the fact that Progress is a complicated system with many components, as is your operating system. Expecting that magically tweaking a couple of parameters or throwing some money at the system in the form of RAM or disks will solve the problem is not necessarily the case; often, you'll just shift the bottleneck to some other component, possibly with little or no improvement. A whole system look is really what is required for proper tuning. 1.3 How can I tell what's going on? 1.3.1 Using promon on shared memory systems Progress supplies the promon utility for computers with shared memory (see below for a more detailed discussion of shared memory). Running promon will give you a variety of statistics on what your database server is doing. It's a valuable tool, and you should use it regularly, even when the system is performing well. Information gathered during good days is useful in figuring out what's going wrong on bad days. promon is documented extensively in the Progress System Administration manuals. One thing to watch out for is that some of the percentage calculations done in promon are a bit flaky; if you see anything that looks obviously wrong, try and figure out where the % is calculated from and do the math yourself. For example, it's not unusual to see the buffer hit % go nuts, going absurdly low or over 100%--but if you look at the database activity screen and figure out the ratio of logical to physical reads and writes you can compute it yourself as a sanity check if things look suspicious. Note that when you first access statistics on something in promon, you get the information since when the database was started. If you then hit a key to get another screen, you'll only get information on the activity since you last looked. Exiting back to the main menu and entering a section again gets you the whole thing again. There is an R&D option to promon that gives more information; you get to it by typing by typing R&D at the menu after running promon. The R&D options are documented in the V7 System Administration Reference manual (there were undocumented before that) and are highly useful (particularly for checking to make sure that the database parameters are set to what you thought they were when you started the server up). There also a proutil command proutil db -C iostats that will let you get system level performance information for a database, similar to the disk information you'd get from sar but for just the files in that database. 1.3.2 Monitoring UNIX systems UNIX systems let you watch a number of things. The three major categories of items to monitor are CPU utilization, memory utilization, and disk utilization. On AT&T derived systems, the sar utility will give you all these numbers, although they are sometimes deceptive. On some BSD-style systems you might have to run iostat and vmstat to get some pieces of the puzzle. There are other utilities that often are helpful; swap -l for swap memory information and ipcs -m for shared memory information, for example. There are endless UNIX watching utilities to be found on the Internet. Newer systems can have fancy utilities like rtpm built-in for your use. General UNIX performance tuning is a bit beyond the scope of this document. You can check out the manuals that hopefully came with your system and the man pages on the system for more in-depth discussion of the topic. My favorite introduction to most UNIX topics is the Nemeth, Snyder, etc. UNIX System Administration Handbook published by Prentice-Hall; it is particularly useful because it discusses many different versions of UNIX and what the different commands are for each version. Other useful books to read on the topic include System Performance Tuning (which is admittedly a bit dated) and Essential System Administration (this one was recently updated) from O'Rielly and Associates. UNIX System V R4 is covered in good detail in UNIX System V Performance Management. SCO UNIX users can check out SCO Performance Tuning (Miscovich/Simmons, Prentice Hall). There are a variety of places on the Internet where this sort of thing is discussed; you might want to try the newsgroup for your variant of UNIX as a start. 1.3.3 DOS, Windows, NLM These platforms lack shared memory (so they can't run promon) and are missing most of the good tools users of other operating systems have for monitoring performance. You can look at the Novell monitor to get an idea what's going on at the OS level there, but the statistics you get when the NLM server is running are less than totally reliable (in particular, I've seen the CPU usage go way up even when the NLM wasn't doing anything and other such strangeness). Usually you will have to resort to benchmarking operations (running long reports is usually a good test for some things, but it doesn't say anything about record creation or update speed) and seeing before/after performance changes to see if what you altered is effecting run times. Specific hints are for the these operating systems are speckled around this FAQ, you might find something that helps you out further along. None of these are top performing Progress server platforms no matter how well tweaked. The lack of shared memory hurts you in several ways, as does the older code (non-32 bit and such, unlike your average UNIX). 1.3.4 VMS There are a couple of tools to monitor VMS performance at the operating system level. The monitor command has a huge number of command switches to let you check out things like caching, disk efficiency, and CPU usage. The show command will let you examine things like memory and the system processes. 1.4 Suggested reading path There's a specific audience I'm trying to write to: intermediate Progress administrators who know their way around, but haven't earned their guru merit badge yet (that's where I'm at myself). If that's not you, things may be less than totally helpful to you. References to products here are all clarified at the end in the books section. There are two basic paths to follow when picking up Progress information. The first involves learning all about Progress programming details, while the second deals with administrative details. Most people who work with Progress don't have the luxury of being purists in either category but instead work both sides of the fence. Programmers should start with the stuff in the Progress box; the tutorial and programming books are good enough that no one else tries and duplicate the material given there, most of the other things you'll read assume you already understand all of the stuff in there and want to build on it. I'd next recommend picking up White Star Software's Programmer's Progress. This book is excellent, and it's only flaw is that it's a bit dated (1991; to give you an idea how long ago that was, John Campbell the author refers to a 486/33 as being a top of the line machine) and accordingly only covers up to V6. Thankfully, there's an also excellent add-on available called What Do I Do Now That I Have V7? that covers the differences between the V6 and V7 products. These two together give you a pretty up to date coverage that gives you a good picture of the history of the product as well, but you're not going to find much on all the newest development methods in them. For in-depth details of specific language functions, the line of books from TJD does a good job of explaining things, although last I checked they were a bit dated themselves. After you've digested some of this material, if you're looking for more performance oriented information, get Dan Foreman's Progress Performance Tuning Guide. It includes a wide variety of programming techniques that improve performance, a topic I don't even scratch the surface of here. All these books will pay for themselves the first day you find out something that makes some report you've had problems with in the past suddenly run twice as fast as it used to. Programmers who are using some of the automated tools Progress provides (like the UIB or SmartObjects) aren't going to find much to help them out in any of the books mentioned above-- they're all aimed (so far) that those writing code directly in the 4GL. Some books on those topics are trickling out. There is popular one about Progress V8 available from Que, it may even be in your local bookstore. Progress administrators who concentrate more on that than programming should start with the system administration books that come in the box; again, these are good enough that no one tries to duplicate much of that material. After you've absorbed them, the majority of this FAQ should make sense to you. Make sure to check out what's available to you in the Progress knowledgebase as well, you'll find tons of answers to common questions. The books from White Star will also be useful to you (Programmer's Progress doesn't focus too much on sys-admin performance information, but the V7 book is good on this topic). The Progress Performance Tuning Guide has tons of good comments on all sorts of things you can do to reorganize your system. Most of the administration topics there are touched upon briefly here, but Dan gives examples, benchmarks, and details about a number of things that are simply mentioned in passing by myself. There's also a number of sections describing database internals information that you just won't find anywhere else. Again, all of these will pay for themselves the first time they point something out to you that you didn't know and make something run bunches faster than it used to. I wish I had all of them available when I first started working with Progress, there's any number of dead end paths I wouldn't have had to walk down if I could have read them before. 1.5 The Performance Big Picture In an effort to make this FAQ a more useful guide, I've created a diagram (with Visio, an excellent package for this sort of thing) that encapsulates the relationships between most of the things in and related to Progress that impact performance. All the major pieces are labeled with the appropriate section numbers to direct you along. (No diagram here in the text version) The most readable introduction I've found of how many of these pieces fit together is in a slim Progress document titled "Version 6.3 Documentation Bulletin"; it came with that particular version, and anyone who went straight from V6.2 to V7 missed it. Some of this material is so spread out in later documentation that it's not nearly as helpful for getting an overview (although you do need to realize that some of the minor details, like parameter names, have changed a bit in V7 and up). I often find myself referring back to it when trying to clearly fix in my mind exactly what is meant by works like "checkpoint". It's helpful to recognize that there are two major facets to Progress performance problems. The first is having a limit on just how fast records can be accessed. The second is a limit on how fast transactions can be pushed through the system. Each of these deserves its own discussion, along with some information on benchmarking to help you tell which category you fall into. 1.5.1 Benchmarking In order to verify that you have actually improved performance, you need a way to measure that performance. Just about everyone who is concerned with performance sits down and starts to write a simple benchmark that lets them measure how fast their system is. Here's how it usually goes. You write a simple program that iterates over some file in your system; usually a simple for-each loop that does some trivial operation, with some elapsed time computations. When you run it, you'll usually find that what happens is that your CPU usage skyrockets to take over the entire machine, and the performance is totally unaffected by how you've configured the system (altering, say, the -B buffers does nothing). The problem with this approach is whatever data you're looping over ends up getting into the buffer cache on the first run, and after that all you're doing is testing how fast your computer can move that data around in memory. That's why the CPU usage skyrockets; without any disk access to slow it down, Progress can and will sift through those records as fast as possible, using up all the CPU time it can get to do so, just like you should want it to. Obviously, this isn't a good approach. You can get slightly better results if you pick some monstrous file that won't fit in the -B cache, but then what happens is that you constantly blow out the buffers and essentially they become useless. At that point, all you're testing is how fast you can load new buffers off the disk, since the buffer cache always has old data you're not looping over any more. In real life, the buffer cache and its associated processes work well because there are bunches of users, each looking at different data. The common data ends up staying in the cache, while infrequently accessed records get pulled into memory and head back to disk when the processing on them is done. For effective Progress benchmarking, you need to control quite a few factors. First, you obviously need consistency in the parameters used to start all the Progress programs. Second, you need to be consistent in what data is in memory when you start your program. The initial run of any benchmark will result in some information loading into the buffer cache, so later runs will run faster. Besides, the initial run is unrealistic; the vast majority of time programs are run against the database in reality, the buffer cache is already "primed" with some data. Third, you really do need to have multiple processes running at once to realistically assess performance. Consider using the batch facility to start a variety of different types of programs; maybe some that do reports, some that create or delete records, and some that update existing information. If you're thinking this is starting to sound complicated, you're right. You are not going to "throw together" a benchmark that is really of any use at realistically measuring performance in a couple of hours. The last one I did that ended up being useful at all took several days worth of work. Things like controlling transaction scope so that your programs aren't crippled because they overload the record lock table becomes critical. There seems to be some movement in the Progress community on measuring performance more accurately; check out the offerings that are appearing in the software section at the end of this FAQ for information on the products starting to trickle out that address problems like this. What most people end up doing is finding a complicated report or two and running them, just to get an approximate figure on how things are running or how they are improving. That's about as accurate a benchmark as you'll get without putting some serious time into studying the issues involved and measuring things more accurately. 1.5.2 Record access speed as the bottleneck The reason most simple benchmarks are useless is because all they test is how fast you can access records that are already in memory. Sometimes, though, that's a very important thing to know; it's possible that this sort of situation is the performance problem with your system. Usually the only time you'll run into this is if you have very complicated reports to run. If most of the data for your report can fit into the buffer cache on your system (perhaps because it's running late at night when it has the whole system to itself), the only limit on how fast processing can happen is how quickly Progress can shuffle memory around and compute the information that appears on the report. The easiest way to tell if this is your problem is to watch the system statistics when the program you're interested in is running. If the CPU usage is way up, the disk I/O is low, and promon tells you not much is happening to the buffer cache, you're in a situation where record access speed is the only bottleneck on your system. If this is your problem, about the only choice you have is to upgrade to a faster CPU. It's possible that adding more processors to your system might help, but that depends on how well the programs you're running can utilize the additional processors (if it's one big report, having more processors to wait around and watch the one execute the one program won't help). One thing to remember is that, on a client/server system, much of the record processing is occurring on the client systems. Because of that, it's much easier for a record access speed bottleneck to appear in that situation. The only real solution is to upgrade the horsepower of the clients with faster CPUs, more data stored locally, and a higher bandwidth network connection to the server. More on that topic appears in the client sections below. 1.5.3 Transactions as the bottleneck This is the situation almost all sites running self-service clients find themselves (client/server sites usually have their problems in other spots). How fast things flow through the system is limited primarily by how fast transactions can be written to the BI file (more details on that process appear in 3.9) and then synchronized to the database. Just what's involved here? Look at the diagram. The self- service clients do their access to the shared memory buffer cache; this happens at for the most part at memory speeds. The updates that they do are organized into transactions, and they go into the BI buffer cache. From there, the BIW writes them out, and then the database block in memory is marked as modified. Those modified blocks get written out by an APW. When an entire BI cluster worth of changes has occurred, the system does a checkpoint, where it forces outs writes of all the modified database buffers. That process is the major thing that determines how fast a transaction oriented system can go. It's efficiency is the one of the first things you should investigate. Most of the tools you need are sitting in promon. Obviously, how big the buffer cache is factors in. If it isn't large enough, Progress can't keep enough data in memory to make any of the rest of these processes useful. The broker will be forced to "evict" records from memory back to disk prematurely because it needs more space to deal with new data. You can tell if you have enough buffer space by looking at what portion of the database reads are being cached. Checkpoints are a very high overhead process, but they only occur when a BI cluster is full. You can lower the overhead of a checkpoint by increasing the BI cluster size. For larger databases, this is the first thing to do after making sure your buffer cache is big enough. Don't go making them too large. The system freezes as far as the users are concerned when a checkpoint occurs, because no access to the database can happen until the checkpoint processing is finished. As the cluster size increases, the amount of time that checkpoint takes will increase. Make the cluster too big, and the pause will be too lengthy. Next, you need to have a BIW and APW(s) running for each database. If you have a version before V6.3, or you have a new version but have a Workgroup license, these are not options. V6.2 and earlier systems can have serious problems with the checkpoint freezes mentioned above. By writing changed BI and database buffers out constantly, the BIW and APW processes make the checkpoints take a minimum of time. That was the reason these processes were introduced. If you're on a Workgroup system, you are essentially back to V6.2 performance, with its associated problems. If you can start a BIW, do so, and make sure you're specifying an appropriate size for its buffer cache. If you're using AI, start an AIW and make sure it's buffered correctly as well. Start one or two APWs. You monitor the efficiency of all these processes with promon. The BIW and AIW have "buffer wait" statistics that should be at 0 if they are properly configured, and looking at them should show that they wrote a number of buffers out. The "writes by APW" figure should be as close to 100% as possible. Hopefully you have a V7 system that tunes APWs automatically; if not, you'll have to do some unpleasant tuning on the frequency with which they run. After you get all that configured correctly, the limit should be how fast your OS can deal with the disk drives. If you database is large (or if you're using an OS that has a single-process limitation), you may find a significant performance improvement by splitting the database into a multiple volume one to lower file system overhead. Also, having multiple extents for your BI file can help in some circumstances, as it can ease the overhead of BI file maintenance. Next, you'll need to examine the disks on your system to see how much traffic they get. If it's more than they are keeping up with, you'll need to get more of them and intelligently split things up (putting high-activity areas like the BI and temporary files onto another disk, for example). If you already have things split to many disks, examine what things you can split up further; for example, if your temp file disk is overloaded, you can add another one, since the location of temporary file space can be modified on a user to user basis. If you partition things up well and the system still can't deal with the disks properly, consider adding more disk controllers to spread the load out. Even though I/O performance is what has been focused on here, you will need to have sufficient CPU power to keep up. Consider adding more processors. If you have multiple processors, make sure you are tuning the spin parameter to properly utilize them. If you've gone through this checklist, and everything seems tuned fine, you may need to consider redesigning parts of the application you are using if things still aren't performing well. If you haven't done so, a review of the indexes and code by someone familiar with performance problems is certainly in order before you spend too much cash on hardware. 2. Progress Performance Major Components Before you start asking about parameters to alter or other such things that usually give relatively minor improvements, you should first understand the big architecture choices that can really impact performance. 2.1 Which Progress version should I use? Progress is continually improving its product, and usually new releases have better performance than the ones before them. You should also realize that in a client/server environment it is possible to use a different version for the client program then what is running on the server (in fact, it's often mandatory, because Progress releases for clients tend to come out before the ones for servers). It is possible to have a V6 database accessed by clients running V7. Progress supports a one version difference between client and server (you're not supposed to be able to connect to a V6 server with a V8 client). And there's no backward compatibility for the clients--V6 clients can't talk to a V7 database server. 2.1.1 6.2 There are still lots of 6.2 databases out there. This and earlier versions are much improved upon by versions 6.3 and up. If your database is running too slow and you've got something before 6.3, you should probably upgrade. 2.1.2 6.3 6.3 is the easiest version to upgrade to for those running V5 or earlier V6 releases, as the differences between the releases are fairly minor. You'll probably get away with running the proutil conversion program and recompiling your source code (the 6.2 to 6.3 update doesn't require a recompile and is even reversible if you have problems). There are major performance enhancements in 6.3, including the addition of writer processes and support for multiple processors; that is, if you have the Enterprise version (most do, as the Workgroup/Enterprise distinction came long after most 6.3 installations). 2.1.3 7.X The various V7 releases are the current mainstream versions from Progress. The performance improvements can be quite significant; in particular, client-server performance is greatly improved. The indexes used for files are compressed, making their use much more efficient. This happens whether you use the conv67 utility (after compression, empty space is left in the database where the old index blocks used to be) or a dump/reload (database will reduce in size) to upgrade; . Source code is usually ported over to this version without too many problems, but ultimately you will need to rewrite quite a bit of your application to get the full benefit of the new features in V7. You'll get most of the performance improvements automatically, but the new GUI features will require new code. V7 also releases the 63K limit on .r code, in itself a reason for many people to upgrade (section 3.10 has more information on this topic). The maximum number of extents on a database has been increased from 100 to 256. One minor downside is that the all the new stuff in V7 usually results in things compiling slower. The big drawback is that all these new capabilities require more memory, and it's managed differently. These increased memory requirements make many sites that were correctly equipped for V6 run into problems with running out of physical memory after upgrading, resulting in more system paging. 2.1.4 8.X There weren't many major performance improvements introduced in V8 of Progress over what was already available in V7. One component that was revised was the way client/server transactions are controlled. A 4GL construct called field lists lets you control which of the fields of a record are passed to your client. This facility has the potential to greatly reduce the amount of network traffic that is passed between the server and client. I've been told that you can use field lists with V8 clients if you server is running 7.3C or later; supposedly, that's the version support for that feature was added. One other potential performance enhancement is the inclusion of the BI grow utility, used to add and format BI clusters easily. 2.1.5 Conversion Issues If you update your version of Progress, you can usually convert your old database with the proutil utility (check the documentation for proutil to see what the correct -C parameter is for your particular conversion, it will be something like conv67). Don't even think of trying this one out without a backup. Something to watch out for is the changes that occur in parameter names and defaults when switching between versions. For example, you use the -e parameter in V6 for environment size, but it's - mmax in V7 (and, although basically the same function, there are subtle differences between what exactly happens behind the scenes with that buffer between the versions). One of the new performance features of V7 is the reorganized, compressed indexes; if you have many indexes, you may find that your database could take up considerably less space with V7 (a 50% reduction is not unheard of). But if you just use the conv67 utility, you don't get that space back--it becomes free space that will get used eventually as the database expands. You'll have to do a dump and reload on the database to reclaim the space fully (and to reduce the amount of scatter on your data that results from this process). 2.1.6 What's the difference between Workgroup and Enterprise versions of Progress? In an attempt to reorganize and simplify their pricing structure, Progress has created two tiers of Progress database servers. The Enterprise version is the full product with all the performance enhancing features. The Workgroup version is stripped down by not including any of the APW/BIW/AIW processes and not having improved multiple processor support via the -spin parameter (effectively returning performance of the database to the same level it used to be before V6.3). Accordingly, the Workgroup server is only recommended for smaller systems with the database considerably less than 1GB, a single processor, and less than 30 users (there is supposedly a hard-coded limit of 65 users). Users who are currently running V6.3-V7.3 databases can contact Progress in order to upgrade to an equivalent product under the reorganized scheme. 2.2 Why are shared memory systems better? Most of the high-performance platforms that Progress runs on support shared memory (including most UNIX systems, VMS, NT, and OS/2). There are three major advantages to using shared memory. First, you can use utilities like promon to monitor your database in these environments. Second, starting with V6.3, you can run writer processes that improve the handling of updates to your database. Third, you can have clients that directly connect to and do their own access to the database; this will greatly improve performance. 2.3 What's the best connection mode? Usually you don't have a choice of what connection mode you use; it's forced on your by your server and network configuration. 2.3.1 Single user Single user mode is needed to update the schema of the database, the most common use. One big advantage of this mode is that record locking concerns disappear--since there is no one else using the database that can lock records, that whole mechanism is eliminated. Because of this, there are often system utilities distributed by the manufacturer of a database that are designed to only be run in single user mode. Trying to run them in any other mode will overflow the database lock table (you can get around this by greatly increasing the -L parameter in client startup, but this steals lots of memory from that would be better served elsewhere, like -B). Because you lose the efficiencies of the writer processes that group transactions into blocks and lower overhead, single user mode may be slower than running a single client processes against a server if there are many updates to the database involved (that is, if you've got at least V6.3 where the writer processes were introduced). 2.3.2 Direct connect (self service) On shared memory systems, the best multi-user way to access the database is with a self servicing client. This is done by directly connecting to the system the database server is on and running Progress from there. Telnet is the common method for UNIX systems to get into a system for this purpose. Note that if your clients are using the -H or -S parameters, they are not connecting directly even if they are running on the server; they are still connecting as separate clients and suffer much of the performance degradation of a full client/server connection. 2.3.3 Client/server (remote service) Client/server systems are not the best performing choice for most Progress systems. The overhead of sending records to the clients over the network makes performance worse than a self servicing client running on the server. How much worse depends on several factors. Under V6, the performance difference can be as bad as 10x worse with client-server configurations. Improvements in V7 lower this to around 3x as bad. Two major factors are involved: not being able to use shared memory and the overhead of putting the data into packets and waiting for the network card to processes them and send them across the network. The impact can be minimized by proper database and application design, to reduce the amount of data sent over the network. Designing your queries so that the server can select the appropriate records instead of sending them all over the wire for the client to sort through is particularly important. You may want to investigate making as much data as possible available on the local client if you have a poor performing C/S application (see the section on use a core database below, the core database might be a good choice for local data if it doesn't change often). If you have a GUI application, you usually don't have any choice but to use a client-server configuration. In that case, performance tuning gets quite complicated. You have to tune the server, the clients, and the network connecting them; bottlenecks on any of the three can kill your system's performance. Note that if your server supports high performance direct self- service users, you can still use them even if the majority of your users are client/server. Just run Progress without the -H and -S parameters on the command line while on the server, and your clients are self-service, even if the server was started with a networked client in mind. You will need to start multiple server processes in order to support a configuration like this, but the additional ones will require minimal resources. The direct connections can be useful for less interactive programs (reports, batch updates, etc.) even if the application was designed with a GUI. This sort of configuration is an excellent idea for upgrading NetWare NLM systems--you can keep the same clients, and they won't notice any difference between the server on the NLM and a separate (probably UNIX) server, after everything is configured correctly you only need to change a parameter or two on the command line (or .pf file) for the clients. You always have the option of using the high- performance self-service sessions by using Telnet into the new server for more intense programs. There is a -da parameter you can use on client sessions to force direct access, say if the .pf file you're using has -H and -S in it by default. Another factor to consider is that a client/server will not require anywhere near as much power in the server itself as far as the CPU goes; given sufficient network bandwidth and good disk performance, you should be able to handle more clients in a client/server environment than a self-service one. They will use less memory on the server as well. Queries involving multiple related database files/tables in a C/S environment will send all appropriate records from each of the related files to the client and let the client join them together (there is a knowledgebase entry on how C/S joins are performed). This means that joins will send a whole lot of information over the network. The advantage of this technique is that you can do multiple table joins where the tables in different databases correctly; most database systems don't allow joins across multiple databases. 2.3.4 What's the difference between a server and a broker? Usually these two terms are used interchangeably here (with server being the more common), but there is a fine distinction between them on shared memory systems. The database broker is the process that sits between the physical database on the disk and other parts of the system while also coordinating the different users of the database. If you're running self-service clients, the broker is all that sits between them and the database (with shared memory to pass information among the group). Normally, a self-service client reads from and writes directly to the database buffer pool itself. The broker can become involved with the reading itself if locked transactions are present. The broker handles flushing database updates out the disk during a checkpoint, although if an APW is running it will be writing those changed blocks out as well. The broker handles buffered writes to the BI file if you don't have a BIW process running (unless you have the -Mf parameter set to 0 and there is no buffering on those writes). A database server is a process that takes requests from Progress clients that aren't self-service (usually in the form of network packets) and, as the server is a self-service client itself, deals with the database buffers for them. The reason this seems complicated is that both the broker and server jobs are handled by the same Progress service (the one started with proserve or the like); you don't see the seam between them. Clients started with -H and -S go through the server to the broker, self-service clients go right to the broker's shared memory. Also muddling things is that this distinction only became apparent in recent versions of Progress (I believe this was part of the enhancement to C/S performance first introduced with V7). Before that, everything went through the server, anyway (the server itself was actually the process that handled the use of shared memory). 2.4 What's the difference between character and GUI modes? Progress supports two major graphical types of clients, Windows ones and X-Windows ones. Starting with V7, the Progress 4GL has commands to support event-driven applications that fully utilize these platforms for clients. You can still run the same programs in character modes if they were written correctly, however. Much is going on lately with layered applications in Progress that keep the user interface code separate from the main application logic. The GUI code you can write with Progress is often less than totally portable between Windows and X clients. 2.4.1 What about GUI with Windows? This is rapidly becoming the most popular Progress client, and lately is the first platform new releases of Progress appear on. Tuning your Windows client is it's own adventure. You should put as much RAM as possible in it. I've gotten by with as little as 4MB, but 8MB works far better, 16MB is recommended, and 32MB is not unreasonably much. If you've got anything less than 32MB you should consider setting up a well configured swap file. Fast disk drives are a good idea; using a version of Windows (like Windows for Workgroups or Windows 95) that supports 32-bit disk and file access can improve performance greatly at the disk end; as a bonus, you can get a free Microsoft TCP/IP client that is pretty good. Put as many files as possible on the client (temporary files and .r code) to avoid needing to deal with them over the network. If you find the screens don't pop up as quickly as you'd like or programs that display lots of text are slow, you should consider upgrading to a video card with built in Windows acceleration. There's also any number of things that are usually only considered at the server level that become important as you start working with a busy Windows client. You might move the temporary files to a RAM disk. You might add more drives and spread the disk load around. You might switch to SCSI disks to better support the I/O load. Another option you might want to consider is to move to Windows NT clients, which are often far more reliable and better performing with Progress. You will definitely need to get more memory for this option, however; with NT, 16MB is a minimum, 32MB is recommended, and 64MB is not too much. 2.4.2 What about GUI with X-windows? The biggest concern here is that the client computer will not only have to keep up with running the Progress client, but will also have to be running the X-server as well. The overhead on the database server to send information to the clients is said to be minimal by those who are running such a setup. I don't have much information myself on what the issues are for your average X workstation. Some like them because X clients tend to be more stable than Windows clients (like I often say, it doesn't matter how fast it might run at if it doesn't work). Be warned that support for Motif clients is disappearing out of later Progress releases (there isn't a V8 Motif client expected). 2.5 Database design How your database is designed is ultimately what determines how fast your applications will run, regardless of how well tuned everything else is. Often, you don't have any control over this, so it isn't discussed much, but changes made at the design level can greatly improve (or destroy) performance. 2.5.1 How does database size change things? As your database gets bigger, getting things out of it (or creating new ones) gets slower. A growing database will ultimately require more RAM for buffering then it did when smaller. You can make a lot of mistakes in your database and computer implementation that will be unnoticeable if your database is 10MB; let it grow to 100MB and you'll strongly regret them. There are people out there with 1GB (and bigger) Progress databases--but you really need to have your database optimized for accessing this much data, and you better have great (and properly designed/configured) hardware to pull it off. It's impossible to put exact numbers on what database growth will do to your performance, but it's guaranteed that it will get slower; you'll have to compensate by improving things if you want to keep your level of performance constant. The design criteria become even more critical when dealing with large databases. It's trivially easy to totally blow out all the data in the -B buffer cache and beat the drives to death by running an unindexed query on a big database file. If this is a danger, you might want to investigate using the parameters to give users private database buffers--that way, they can keep the data they are working with around even if the main cache clears because of a bad query. If you need to get statistics on a database such as how big each of the files are, you can run the proutil program with a variety of options (like dbanalys) to get them; there are many more of these informational utilities available with V7. 2.5.2 What's all this normal form stuff mean? Normal forms are organizational methods from database theory that tell you how to split your data up into files/tables; a database that follows them is said to be normalized. For example, if you have a list of customers for your company who all place orders, one of the normal form rules would tell you that the address for the customer should be stored in a separate file from the order file. That way, you can update that address in one place and not have to alter every order. Like any guideline, you shouldn't be a slave to normal forms. For the order example, many applications have the customer's address in a customer file, and another address spot in each order as well. The address for the order defaults to the customer's address, but you can change it. This is essential for some businesses that occasionally deliver to places other then the customer's regular address. There are ways to address this problem while staying within normal form rules (for example, let each customer have multiple address that are stored in a customer address file separate from the customer and order), but the point is that there are occasionally business idiosyncrasies that don't quite seem to mesh well with strict normal form design. In the vast majority of cases, however, proper thought put into database normalization is time well spent and quite a good idea, especially in keeping maintenance easier. As far as performance goes, a normalized database may or may not be faster than an unnormalized one. For our customer/order example, running an order report that needs the address on it might run faster on the unnormalized database because you only need to access one file (order); the normalized one needs to access customer and order (while managing the relations between them). Depending on the selection and break criteria used, the normalized one could result in far fewer records accessed than the unnormalized one, and run faster; it's impossible to say in the general case. Normalization is a difficult topic to get concrete answers on. For more information on normal forms and database normalization, check the Progress manuals first (V7 in particular includes an excellent section in the Database Design Guide). White Star's Programmer's Progress has a good section on the topic with Progress examples. Any good book on database theory will discuss them in extensive theoretical detail should you want further information. 2.5.3 How do I optimize the database for queries? The key to getting good query performance out of Progress it to pick your index fields correctly. Adding an index on the field you are searching by or sorting by can radically improve the speed your reports run at, if the program is written correctly so that it can use it (look at the output from the detailed listings that you can get from the compile command to see what indexes your program is using). Understand, however, that each of those indexes needs to be updated whenever a record is inserted or deleted from the database. More indexes help reports, less indexes help record updating. The proper number is a delicate balance of these two. It's also impossible in many cases to create the number of index required to address every possible report users might run. If there are 10 fields users routinely sort on, there are 10! possible index combinations needed to handle every one of them; no way can you have that many, so you have to pick the more important ones to include. You'd need 90 just to get the top two levels, also not practical. If you've got a really general report that can run against any of the fields of the database, you're almost certainly going to end up with a few break by clauses that don't find any appropriate index to use. Version 7 has introduced multi-level indexes that address this concern. Also, the V7 server knows how to use multiple indexes at once to resolve a query. One trick often used is to dump the data to a file and use operating system calls to sort the data external to Progress. Depending on how complicated your criteria are you may get better performance, but at the total loss of portability (UNIX fans who prefer writing shell scripts to Progress code always mention this possibility). Another factor is the data types used for the file relations. Lookups run much faster using integers for the key fields that are shared than characters. Integers that hold recids are even better, but become problematic when doing dump and reloads; it's possible to provide recid pointers that are redundant, so that they can be used for lookups and regenerated using the extra information after a dump/reload. Examples of using this technique are given in the section labeled "recid joins" in Dan Foreman's Progress Performance Tuning Guide. Getting good report speed is a big topic; it is addressed in the Progress manuals in depth (look up "bracketing" for a discussion of the record selection method). There are also literally books written about just this subject from some of the book sources listed later. I'm most fond of White Star's Programmer's Progress/What do I do now that I have V7? and Dan Foreman's Progress Performance Tuning Guide for this information, both discuss index usage extensively. White Star also has a book devoted to speeding up reports. You can tell your report has problems if it takes the same amount of time to run now matter how little you are trying to get it to do (for example, the report takes just as long to process one day worth of data as it does an entire month). These are the cases where the lack of information Progress has about your data results in a run time dominated by how long it takes to sort through your data just trying to find the necessary records. 2.5.4 How big should my records be? It is most efficient if you can keep the size of the individual records of a file/table less than the block size of the system you are on (usually 1024 bytes, 2048 on some platforms like VMS and Sequent). Progress doesn't ensure that records larger than a single block are kept in the same area of the database, it is theoretically possible that a single record could have its first block at the beginning of the database while the remainder was stored at the end; this introduces large waits by the disk drives to seek around the database just to read one record. You can compute the size of your records using information in the Progress manuals (there is a table that shows how big characters, integers, etc. are). Note that there is approximately a 20% overhead per record used by Progress for internal information, limiting the actual size you can use to about 80% of your system block size. 2.5.5 Core database and connected database architecture Because performance slows as databases get bigger, one solution is to make your databases smaller again. A popular method for doing this is to take the more static core files/tables from your database and put them in one database (parameters and customer information are often good choices), while putting the rapidly changing data (like orders, shipping data, or accounting entries that disappear at the end of the month) in another. You an connect to each of these databases either by including parameters to use them all when starting your Progress session or with the connect command in a 4GL program. By doing this, you can tune the parts of your database more specifically. More active parts of the database can get put on faster disks, their own -B cache, their own BI files. The read- only database doesn't use valuable time dealing with BI files or resources to support database updates. The trade-off is that there is far more administration involved, there is more overhead involved with starting everything up, and you may end up using more resources (like files handles and semaphores) because you are starting more processes up to handle the additional databases. Also see the section below on multiple connected databases for some other comments. Dan Foreman gives examples of configurations for this sort of setup in his Progress Performance Tuning Guide, where he also warns that also connecting to a local database violates the usual license for the client networking package, so investigate at your own legal peril. 2.5.6 Multiple connected databases Usually, in any situation where multiple databases are connected, it is recommended to enable two-phase commit to ensure database integrity across databases. You'll also have potential problems backing up the databases, as on-line backups from the databases can have transactions that span two or more databases that get backed up separately (this isn't a problem with strictly a read- only core and one database, but gets hairy if you split the production information in the database up further). Some of the tools you might want to use to connect to your databases might not work with multiple, related databases. For example, at this moment most Progress ODBC drivers don't support it; Report Builder doesn't support it either. 2.6 Speeding up Progress 4GL programs This is much too big of a topic to properly address here. There are some easy things to do. Make sure every variable that is not associated with database transactions gets declared no-undo. Use no-lock on any database access you can. Always group statements into assign blocks whenever possible. Writing status information to the screen or disk as infrequently as possible (many a program has been found to have most of its time spent displaying counts and such to the screen). Avoid recursion and its stack overhead by placing the items in an array, workfile, or temp table. Try not to escape to the operating system, because this results in a big chunk of overhead. For V7, use internal and persistent procedures instead of the older separate ones; also consider using temp tables instead of workfiles. One very large issue to consider is how big the transactions generated in your application are. It's fairly easy to write 4GL programs that strangle themselves with record locking issues (using up entries in the lock table and hampering performance). Also, transactions require more BI file activity, and this is the major bottleneck for most sites. Keeping transactions controlled is definitely a skill worth learning. 3. Progress specific parameter and process tuning 3.1 What should my server parameters be set to? Many of the things in the server configuration set themselves based on how many users you specify for the database with the -n parameter. You'll still have to check a number of other parameters; some are discussed here, also check the subsections below on this topic. Each of the record locks on a database uses an entry in the Progress lock table. Unless you get an error saying the lock table has overflowed, you don't need to worry about this. There is some memory used by each lock table entry. You set lock table size with the -L parameter. A variety of parameters that affected performance in earlier versions (like -Mr -stsh -pwscan -pwsdelay -pwwmax) have become largely automatic on V7. 3.1.1 How big do I set -B to? The increasing -B buffer size parameter on the server is probably the easiest way to improve the performance of your system with minimal changes. The default value for the parameter is ludicrously small for any reasonably large database. The units of -B are the block size for your system; probably 1024 bytes, possibly 2048 (on Sequent systems, for example). The default is a multiple of the number of users specified with -n. These buffers should get the majority of the RAM on your system (if Progress is the main application on the server, that is); it is not unusual at all for -B to get one-third, one-half, or even more of the total server RAM. It is better to give the memory to Progress to manage instead of leaving it for the operating system to use, as Progress's paging algorithms for determining what to swap out of the buffers are tuned better for Progress databases. To see if you've allocated enough buffers, run promon and check the Buffer Hits %; you definitely want it to be over 95% while your application is running. You should increase -B rapidly until you hit this figure, then back off more slowly until the rate drops noticeably. That way you'll get the best performance without wasting memory by setting it too high. The database buffers are stored in a hash table, so as you increase the size of -B, there is a corresponding hashing figure that you may need to increase. The -hash parameter should be set to the first prime number greater than the # of buffers / 4 for most cases; this means that the chains of buffers that need to be searched to find your data average less than 4 entries deep. Some claim that increasing -hash to be the first prime number greater than -B, providing one hash table entry for each buffer, can improve performance further. One thing to be aware of: versions of Progress before 6.3 didn't support increasing the hash tables for the buffers above a very high number. This effectively limited the useful range of -B to an upper limit of 32,000 (32MB on most systems). Increases above this aren't effective in improving performance on these older systems. Progress claims that you don't need to adjust -hash at all with versions after 6.2, that it will take care of itself as long as - B is less than 500,000 (the system has a lookup table of prime numbers that it uses to calculate -hash in these versions, and the maximum entry in that table is 98,407). If you want to see how Progress is allocating hash buffers for the buffer size you specify, you can look at that by using the R&D promon. Also beware that you may have to increase some of the system parameters (the kernel under UNIX) to support big increases in -B (the maximum amount of memory a single process can use in particular is often limited). 3.1.2 Shared memory servers There is a chapter in the Progress System Administration manual that gives specific details on the operation of server when running on shared memory systems, it is very good reading to give a good idea of just how the parameters you set alter operations. 3.2 Client parameters Most of the client parameters you might want to change are discussed in other sections; for example, -q is discussed in the section on .r code and -RO is discussed in its own section. In V6, the -e parameter controls how big the edit buffer size is. This is where all the .r code is loaded into and where the procedure editor programs get put at; as the buffer fills, the information is swapped out to a temporary file. It has to hold all the parts of the client process data. Superseding -e in V7 is the -mmax parameter ("initial amount of memory allocated for r-code segments" as the manual puts it). It controls how much memory Progress can allocate for the edit buffer, workfiles, and some other data structures. This much memory isn't automatically used, it's more of an upper limit on the size of the buffer (which is dynamically managed as programs run). Since .r code is usually larger in V7, you'll have to run -mmax higher than you set -e in V6, especially if you are using lots of workfiles which now go in that space as well. Each of the client sessions get its own local buffer space that is used for things like workfiles in V6. If you don't have enough, Progress with crash with an error message to that effect. You set this size with -l. Note that on many smaller platforms (like DOS) -l is limited to 63K. With V7, the only way to limit that amount of memory a Progress session can use up is to adjust the kernel MAXUPROC parameter-- unlike V6, where the combination of parameters like -e and -l let you figure out an upper limit. Index cursors are used by each FOR EACH or FIND that uses an index. You'll get an error if the -c parameter used to control the number of these allocated is too low. You can set the size of the stack the clients start with -s. Presumably this also generates an error if set too low, but I've never seen it. It is possible to improve performance in some environments by increasing the values for the -TM and -TB parameters. Typically, these parameters are only considered by those maintenance procedures like an index rebuild. You may find a performance improvement using them on client sessions as well, especially if report data you're sorting through is not indexed. With shared memory systems, it's possible to have private database buffers that work like the main buffer cache, but belong to the one process. There are allocated with the -O parameter. For V7, you can control the amount of buffering for temporary tables (in a manner similar to how the database itself is buffered with -B) by using the -Bt parameter. Applications that rely heavily on temp tables (like the UIB) can get big improvement in performance, but you pay for that in additional memory use. Compiled procedure .r code are stored in a directory, where non- active procedures can get discarded if more space is needed. You control the size of this directory with the -D parameter. Increasing it uses more temporary file space to hold the .r files. 3.2.1 Schema caching When Progress .r object code is compiled, information about the database schema (contained in _file, _field, _index, etc.) that the database was compiled against is encoded and placed in the object file. At run time, this schema signature is checked against the schema of the current database to make sure that changes haven't rendered the .r code obsolete (giving the dreaded message that the code needs to be recompiled). Accordingly, the current databases' schema needs to be read and compared against this information. Because the database schema can be substantial, Progress uses a schema caching scheme where that information is kept as part of the client program; this is especially helpful where the network connecting the clients are slow. The exact method used for caching the schema varies from version to version, with it becoming more efficient in later V7 releases. Schema caching often becomes an issue over low speed network links (like a WAN) because it generates quite a bit of traffic if not totally cached. 3.3 Why use a multivolume database? Reading the Progress documentation doesn't go into much detail into why you would use a multivolume database, other then to say that it lets you make databases bigger than the allowed file system size (often 2GB nowadays). There are several other reasons to use this method to split your database up. Even if your database isn't over 2GB, you might want to put in on multiple disks anyway just to even out the accesses to it and improve performance. You can split your database in up to 99 pieces under V6, up to 256 pieces with V7. If you're running UNIX, you'll probably have to up the number of files that a single process can open, as discussed in the UNIX kernel section, if you start splitting the database into many pieces; otherwise, the server process won't be able to open the database. If you really want to get fancy, you can pick the locations of the extents in fancy ways so that disk contention is minimized further (for example, instead of putting extents 1-25 on disk 1 and 26-50 on disk 2, put all the odd numbers ones on the first disk and all the even numbered ones on the second). If you currently have one big database and want a multivolume one, there's a couple of ways you can do it. In any case, you certainly want to have a backup of your database before trying this. 1) Backup the database with probkup, create a structure file, make an empty multivolume database, then restore the database to it. Do an index rebuild if you've got time. 2) Dump the database, delete the database, create a structure file, make an empty multivolume database, reload the data. 3) Create a structure file, make an empty multivolume database, procopy from the old database to the new one. Each of these has its own pros and cons. 1 is very fast, but doesn't clean up things like the slower approach 2 does. 3 is fast and efficient, but needs much more disk space than the others as your original and new database both have to fit at the same time. 3.3.1 File system considerations The way an operating system accesses files is to start by looking in the file directory, then to lookup the index blocks that point to the file you are trying to manipulate. If the file gets big enough, the OS first has to look through index blocks of index blocks; this is usually referred to indirection (if the OS had to look at 3 nested index blocks before finding the correct sector for your file, that would be triple indirection). This greatly slows access to the database, so it is advantageous to keep your database files small enough so that the OS can find them with a minimum of indirection. By splitting your database into several smaller pieces, you can often make each piece small enough to use one less level of indirection than the whole would take. For most UNIX systems, the triple indirection level comes at 64MB, so databases pieces should be kept smaller than that (for a better explanation of this for UNIX systems, you might want to check out the book The Magic Garden Explained [Goodheart/Cox, Prentice Hall], which goes into great detail about indirection levels). For DOS systems using the FAT file system, this level is supposedly reached at 8MB, but I'm not sure this is correct, as it seems to me that the cluster size of the disk should be a factor. Note that Novell NetWare systems don't use the FAT file system and don't have this same limitation; I don't have exact numbers on what level triple indirection is reached at, and I think that again it depends on how you set the disk cluster size when installing NetWare. 3.3.2 Single process access limitation One limitation that you will run into on some operating systems is that only one process running on the server will be able to access an open file at once; in operating system terms, only one process can use that file handle. This means that, if one process have a critical file open for reading or writing, all other processes on the computer are blocked. This is a horrible state of affairs for a Progress database. If one process is using the DB file, all others have to wait on it. The usual way around this problem on systems with this limitation is to make your DB very multivolume--break it into as many extents as possible (the upper limit of 99 on V6 or 256 on V7 is not unreasonable). That way, there is a much smaller portion of the total data locked when a process is accessing it. If you have a system with this limitation, breaking the database into a multivolume setup with many extents is almost mandatory for environments with any reasonable number of users (reasonable being higher than one in most cases). The most common place you'll run into this limitation is on systems based on UNIX SysVR3 systems; HP-UX is the most frequently mentioned offender (I believe this was corrected in HP-UX V10). Anything based on a SysVR4 release will not have this limitation. 3.3.3 Direct (raw) file systems On some operating systems (Sequent and Data General), Progress supports direct file systems, by starting the server with the - directio parameter. There are sections of a disk that have a file system, but Progress bypasses the normal I/O buffering and handles that itself. Direct file systems can have up to 30% greater performance than regular ones, because of the avoided I/O overhead. These direct file systems are different from totally raw disk use, where no file system at all is placed on a disk; this isn't supported by Progress. 3.3.4 What should operating system logical volumes be used for? Some operating systems, most notably HP/UX and AIX, have logical volume managers (LVMs) that let the disks be managed in a more abstract way. Disks can be split up into smaller volumes, and multiple disks can combined into larger logical volumes. Don't confuse this with using Progress multi-volume features. If you're using a LVM to split your database among disks, you're still suffering from the overhead of file system indirection, and you've got LVM overhead to deal with, too. Progress already provides plenty of striping features for you, using a LVM instead is not a good move from a performance standpoint. You're letting yourself be subjected to corrupted databases from bugs in the LVM software as well. As very new pieces of software, these tend to not be as reliable as the old tried and true disk access routines in your OS. In particular, spanning a volume across multiple physical disks has resulted in major problems for some installations of AIX. Another problem with LVMs is that they are very much subject to abuse. Doing fancy volume mapping tricks makes it impossible to clearly see the relationship between the system volumes and the actual disks, and managing disk performance is a factor you don't want to make any more difficult than it already is. There is also the danger that the disk "fantasyland" will hide data integrity concerns; for example, you might not notice that your database and its AI file are on the same disk until that drive fails and you find both sets of data gone. One useful thing you can do with logical volumes is give each of your multiple volume extents its own logical volume. That way, you can move those extents around (say, to another disk to reduce contention) on-line by mirroring that volume with another one. After the data is mirrored to the new volume, break the mirror and leave only the new volume intact, and you've just transferred the extent over to another area. Normally, moving database extents around is a painful process, but this LVM technique makes it far easier. 3.4 Are after image files worth it? After images are only one way to approach data integrity. If you have a well configured AIW process, the overhead for the after image creation is minimized, but it is still significant (I've seen figures that it lower performance by up to 20% even when done correctly). You should consider other methods for data integrity (like mirroring the disks with the DB and BI files, a solution that actually improves performance) on systems that need the absolute maximum speed. The main reason to use AI files even if you are already using mirroring is to prevent problems caused by data corruption that are not hardware failures. If someone physically deleted the database files, introduced integrity problems by opening the database twice in one session (manually deleting the lock file that tries to prevent this), or other software based corruption, mirroring won't help you; both of the disks will get the corruption introduced into them. As long as the AI file didn't get corrupted as well it can still be played back in these circumstances. If you're running on a UNIX system, one way you can guard against accidentally deleting the database is to make a link to the database from another (preferably hidden) directory; that way, the system won't actually get rid of the files until the last link to them is gone. Note that AI files are extremely similar in layout and function to BI files; the discussions below of BI files (i.e. use a separate drive if possible, don't split it across drives, etc.) usually apply to AI files as well. There are usually corresponding parameters as well (for example, there is a - aiblocksize parameter that works just like -biblocksize for improving performance in V7). AI output can be buffered or unbuffered. If it's unbuffered, changes get written directly to the disk and lost transactions are minimized. Buffered AI can significantly reduce AI I/O overhead, but you run the potential to lose up to two minutes worth of transactions with it. 3.5 How much RAM do I need? There's no simple equation for figuring this out. There are several rules of thumb used; for example, on most UNIX systems, you can take the amount the OS needs and add 1.5-2.5MB/user to get an approximation; much of that gets thrown at the -B parameter for the servers. There are at least three major factors that alter the amount of RAM required for proper performance. The first, accounted for in this simple rule, is the number of simultaneous users. The second is how large the database is; as databases get bigger, you need more RAM to devote to caching them to achieve the same hit rates on the data. The third is how complicated the queries are in the code. Simple queries that are adequately addressed by indexes don't use much RAM. Complicated queries, especially ones that have unindexed break by clauses, use considerably more. Don't forget when running numbers like this that networked clients do most of their processing at their end, and don't require nearly as much RAM per user on average. You'll still need a big chunk for use by the -B parameter, but this can be a lower percentage of the memory in the server than it could be with self-service clients 3.5.1 RAM on servers A typical server used for Progress has a variety of things using up the memory in it. The operating system has its own space it uses. Usually there is some portion of memory devoted to caching disks as well. The Progress server processes use (often big) chunks of memory, as do the clients if they are running on the server as well. On most systems, the best way to improve performance is to increase the amount of memory used for the database buffers, as mentioned in the section on -B. Usually -B is the biggest memory using component of the server process, although the specifications for -n, -Mn, -c, and -L (see the server parameter section) also impact things. As far as the Progress sessions go, the figures showed by UNIX systems with ps (using something like ps -ef) are deceptive. Memory used by a Progress session is broken into several pieces: 1) Private memory for the individual client 2) Areas shared with other Progress clients 3) Areas shared with the broker The numbers shown by ps sum these values for each process up, so if you total that number it doesn't take the overlap between processes into account. If you want to know how much memory is really being used by your clients on the server, follow the instructions in the Progress manuals and do the math with the formulas there. Some of this information can be gleaned from looking at the regular and R&D portions of promon. If you're using OS utilities (like sar on UNIX), they may not be taking this sharing into account, making the free memory figures they report much lower than the actual memory available. 3.5.2 RAM on clients The best way to figure out how much RAM each of the client on your computer is using is to use the figures in the Progress manuals. The three biggest things that take up memory are the execution buffer (the size is set by -e in V6 and part of -mmax in V7), the local buffer (-l in V6 and part of -mmax in V7), and the index cursors (-c). 3.6 Temp files Progress uses temporary files (with names starting with srt, lbi, and pge) that hold a variety of information. The largest use is for sorting purposes. Temporary files are created by default in the directory that Progress was run from. To redirect them to another directory (or disk) you use the -T parameter to point them there. This is something you can configure on a user by user basis, as it is a parameter passed on the client startup line. Temporary files are good candidates for memory based file systems (i.e. RAM disks or solid-state drives) instead of actual disks. It's possible with some operating systems to make file systems that start in memory and swap to disk if they get too big (DG/UX is supposed to support this); this sort of arrangement is ideal for temp files, which are usually small but have to potential to get much bigger on occasion. Temp file throughput is most critical when running programs that access unsorted data (where there's no index to use) or under very high user counts. If you anticipate either of these two things you should consider having a dedicated disk for temp file user, or possibly even one of the solid-state solutions mentioned in the previous paragraph. Since there is also temp file usage for holding .r code, you can reduce the amount of space used by using Progress libraries and having your PROPATH point to them. There's more on this topic in the .r code section. If you want to see how big the temporary files get as you do various operations, starting Progress with the -t parameter will keep those files intact after you quit Progress, so you can check how big they are. Seeing something like a large LBI file lets you know that whatever program was running had a significant number of temporary transactions because of the way it was written. 3.6.1 Temp files on servers Progress doesn't use temp files at the server level, only at the clients. Your operating system probably has some mechanism for handling creation of temp files for things like pipelines, but Progress doesn't use it. If you have self-service clients you do need to add up the temp files that they will each create and find a place for them on the server. Since they can get fairly large, they can easily overflow the directories they get put in (especially since the default, with temp files in the directory Progress is run from, often results in Progress temp files going in the user directories, which is often not a high-availability place for disk space). 3.6.2 Temp files on clients Under usual usage, you can figure that temp files will take up approximately 4MB/client. It is not unreasonable to have temp files that peak at 10MB or more, however. As always, these numbers can run higher than this depending on your database size and query complexity; check out the size of the files for users who are running complicated reports to see how much is being used by your system. 3.7 Multiprocessor systems Progress can be very intensive both in CPU use and disk use. Even if you don't need multiple processors when first implementing your system, it is always a good idea to get a multiprocessor capable motherboard so you have the expansion ability later. A general rule of thumb is that a Pentium class computer can support 50 users on an average Progress system; if you've got more than that, you should be looking at multiple processors. And if your database is large or the code running against it is complicated, you should definitely consider multiple processors. 3.7.1 Using -spin The spin parameter determines how many times a process that is trying to use a shared memory resource that is locked will attempt it before waiting for a bit by being pushed into a queue. It can only be used in V6.3 up. When -spin is enabled, it changes the algorithm used for one that is better for multiple processors running Progress. You should always set -spin to a value for multiprocessor systems, you can tune the exact value by using the R&D promon. A higher value of -spin uses more CPU time overall, but it can improve performance anyway because it keeps processes waiting for things less time. Some report setting - spin to 1 can even performance on certain single processor systems. One suggested algorithm for doing to tuning is to start with - spin 10 and gradually increase it from there. If your system routinely has high CPU free time, you could set spin very high (I've seen it set as high as 2000 on some systems, around 500 is more typical). You can tell when it's set too high by looking at the latch timeout information in promon; if it is near or at 0, you can probably reduce -spin and regain some CPU time without dropping efficiency. 3.8 What do all these writer processes do? Progress has three writer processes that improve performance on shared memory systems. All were introduced in V6.3, but none of them are available if you are working under the new Progress licensing scheme and only have a Workgroup server. Note that some writer processes can, under certain circumstances, write multiple types of records out. For example, since AI updates work with the same information that BI update do, an AIW process can write information to the BI file if it needs to. Similarly, an APW can write to either the BI or AI files, if it needs to flush a database update to disk. Database changes can't occur until the record is first written to the BI file. If that hasn't occurred yet and the database block is going to get written, the APW will write the BI transaction to the file itself first. 3.8.1 Tuning APW Asynchronous Process Writers write the dirty buffers from the -B cache to the DB file in an efficient matter. You should always start at least one APW with any database. Start with one or two, and keep adding them until performance (checked with promon, you can add and subtract them on-line while the database is running) starts getting worse instead of better; then back off a bit. You don't want too many, as each is using up shared memory resources. Merrily adding too may APWs can drag down your performance not only for this reason but because of the database resource contention it causes. With the totally self-tuning APWs in 7.3 and later, you'll probably find that any more than two APWs are wasted. You can tune the individual APWs on-line with promon or via startup parameters, but this is automatic starting with V7.3. Tuning under earlier versions involves much voodoo and is definitely something to ask everyone (Progress, the PEG, etc.) about before you try; Dan Foreman has some notes on this topic in his Progress Performance Tuning Guide, and Dan tells me the next update he's releasing to his book will go into great detail about watching and tuning APWs with promon. There is also a Progress knowledge base entry on the subject of "performance instructions for beginning to tune your database" that goes into details about how to set -pwscan, -pwwmax, etc. 3.8.2 Tuning BIW Before Image Writers write the updates to the BI file in blocks to minimize overhead and maximize BI throughput. You can start only one BIW per database, and you definitely should. The only parameter you can alter is the called -bibufs in V6.3 and - biblocksize in V7. You should only increase these tunables if the BI buffer wait % shown in promon is greater than 0. 3.8.3 Tuning AIW If you're using after-images, you should have a After Image Writer running. Just like BIWs, you can only have one per DB, and it's only parameter to adjust is -aibufs, which needs to get increased if the AI buffer wait % in promon is greater than 0. The default on the AI buffers is 1, which often means that they AIW doesn't get to do much work; it needs to get higher to really be effective. 3.9 Why is the before image file important? Every update transaction to the database first goes through the before-image file. No matter how big your database is, spread across any number of disks, you still can't update it faster than the BI file can handle the transactions. BI file throughput is often the first bottleneck larger Progress systems run into. In order to understand how to speed up BI access, you need some background on how the BI files are updated. The BI file (and the AI file as well) is a sequential series of numbered transactions, written in order as they occur. BI files never have seeks done on them to read something that was done before, they only consist of writes that move forward on the disk. There are several things to realize because of this. First, BI files are not candidates for spreading across multiple disks. Since the updates are sequential in order, having the BI file in multiple places would just mean that the activity would move from one section to the next; you never have BI activity in two places at once, so having multiple places for the activity to occur at does you no good. Second, BI files greatly benefit from having their own disk to themselves. Without other files to access, the disk drive heads can stay over the correct place in the BI file and move forward as records are written, without interruption. This is how you get maximum throughput in a Progress system, and because of it, BI files should be the first thing you spawn onto a separate disk as you add them. Third, the operating system overhead of allocating new sectors as a file grows requires lots of seeking around the disk to alter things like index pointers and free space maps. If you pre- allocate the space on the disk by making your BI file multi- volume (still on one disk!) with fixed extents (probably just two, the first fixed; the last one is always variable for unplanned expansion), you avoid this overhead and can let the BI file move forward and write. This is especially important if you should be forced to have more than one BI file on a single drive. Some people recommend always having the one fixed, one variable multivolume architecture for BI files. This issue with avoiding operating system intervention in allocating space is worth mentioning again, because it also comes into play if you truncate your BI file. After that, all the pre- allocated space the BI file had is now gone, and the first many transactions against that database after the truncation are going to be much slower; the space will have to be allocated again. You can even run into this with a multivolume BI file; the OS won't have to allocate the space, but Progress will still have to format it correctly. No matter how you're configured, truncating the BI file will cause performance to drop considerably until a number of transactions have flowed through the system again. 3.9.1 Changing BI Cluster and block size When adding new transactions, those transactions are saved in the BI file until they hit a certain size, called the BI cluster size. At the same time, any database updates resulting from these transactions are applied to database buffer cache (the one allocated by -B that reads are also cached in). When the cluster size is reached and the cluster is full, Progress must allocated a new BI cluster to be able to store new transactions. Because of this, any outstanding database updates stored in the buffer cache are first applied to the database itself, so that if even if a problem should occur and the new cluster can't be allocated the database won't lose any of the existing transactions (running APWs will help keep things continuously flushing to the database file and reduce the length of this pause). Then a new BI cluster needs to get allocated. If you're lucky, the writing of database blocks will have finished all the transactions in one cluster of the BI file and that one will get reused. Otherwise, a new cluster gets allocated and initialized for use (possibly increasing the size of the BI file itself, if no free clusters are available). This entire process of flushing and new cluster formatting is referred to as a checkpoint, and can take a good chunk of time--time during which your database is frozen. Because of this potentially lengthy freeze, correctly managing checkpoints is one of the most important factors in getting good performance. Because of the method used, the easiest way to keep checkpoints from happening as frequently is to increase the size of the BI cluster size. That way the overhead of initializing the new cluster doesn't happen as much. But be warned that, although not as frequent, the new cluster initialization will take longer because there's more data to initialize. The BI file will also take up more space, and crash recovery will take longer since all active but uncompleted transactions in the current BI cluster need to be scanned after the database crashes to back them out (so if the cluster is bigger, that list is correspondingly longer). Figuring out what size is best for your database and application is best done by watching the checkpointing statistics that promon generates when your system is under a heavy transaction load; having the cluster too small or too big each create their own problems (in general, however, the default you get is probably too small). With V6 and V7, you specify the cluster size in KB with a line like this: proutil db -C truncate bi -bi size The size parameter can range from 16 (16KB) up to 262128 (256MB) and must be a multiple of 16. The default varies depending on your Progress version; it was 16 in 6.2, and is up to 128 in V7. You can find out what the value is on your system with promon, or you can use the fact that empty databases start with 4 clusters in them and figure it out that way; that's why an empty database under V6 is 64K and under V7 it is 512K. Progress recommends the V7 default of 128KB should be good for databases up to 100MB. Between 100MB and 500MB, they recommend 512KB. Databases over 500MB should get a cluster size over 1MB, but details on whether you need to go higher than that are very system specific. In V7, there is also a BI block size that determines how much information gets written to the BI file at a time. If promon shows that BI writes have become a performance bottleneck, you can increase the block size to improve things. You use a command like: proutil db -C truncate -biblocksize xxx The amount is measured in KB, with valid values being 0, 1, 2, 4, 8, and 16. You may want to do this as the same time you increase the cluster size. Note that this command needs to get typed differently if you have AI enabled, check the Progress manual for specifics. 3.9.2 BI Buffer caching When using a BIW, in between the database broker and the before- image file is a little recognized chunk of memory called the before-image buffer cache. You alter its size with the a parameter used when the BIW is started (this is discussed in 3.8.2). All BI file output is put into this cache, which the BIW finds material to write from. If this cache isn't big enough, the system will fill it and be forced to have the broker wait for cleared space to become available. This negates the performance improvement of the BIW. If the BI buffer wait % shown in promon is greater than 0, you need to have a bigger cache because this is happening. 3.9.3 Why you shouldn't use -bibufsize This startup parameter was a failed attempt by Progress to improve BI throughput; not only doesn't it work, but it could corrupt your database as well. The correction to the problem led to the BI block size parameter in V7, this earlier attempt should not be used. 3.9.4 Delaying transactions with -M Progress writes to the BI file after every transaction is completed by default. If your system has so many transactions occurring that they are constantly being created, this overhead gets significant. Progress will allow you to change this method so that instead BI updates only occur at fixed time periods, reducing overhead. Starting the server with the -M parameter delays BI writes a number of seconds, as many as 20, before writing. This is turned on automatically on shared memory systems and set to 3 (unless you're running in single-user mode, where the default is 0). Don't use this parameter with multiple databases, and don't use it with two phase commit unless you're on V7. There is a potential for losing more data if you increase this value; setting -M0 is safer because every transaction gets written immediately instead of delaying, but performance is slower. 3.9.5 BI Formatting Even if your BI space is allocated by the OS already with a multi-extent BI file, there still can be a performance problem with BI formatting. This process initializes the BI clusters so that Progress can put transactions into them. One way that you can force your BI file to expand, and therefore format more clusters, is to open one session that begins a transaction but doesn't finish it. Then, do something that does a bunch of BI file activity (either by program or just by having the users on the system). The BI file will be forced to grow because it can't reuse the space in the first cluster until that earlier open transaction is completed. Under V8, you can format BI clusters in advance with proutil db -C bigrow size 3.10 Application .r code The time taken to load .r code into the client memory can be significant in some systems, especially as code is split into smaller procedures. There are two things to do in order to minimize this. First, you can use the -q (quick request) start parameter on clients. This will keep Progress from continually checking to see if a new piece of .r code has been created since the last time that code was referenced in a procedure. This parameter is usually fine to set in the client startup of everyone but the developers, but beware that if you alter a piece of code (to fix some bug you've been informed of, for example) the users using the .r who ran the old version will not get the new one unless they quit Progress and start again. Another method for decreasing .r code overhead is to group your code into procedure libraries. It's really only a good idea to do this with the procedures that really are common, and it's better to use a number of smaller libraries than one monolithic one. One limitation that used to be quite a bit of a problem for many applications was that, with V6, application .r code was limited to 63K. With V7, you might want to investigate taking programs that had been split into pieces and merging them back for performance reasons (making old procedures into internal procedures). The 63K limitation isn't quite gone with V7. Instead, the compiler can now split the complied code into internal segments. You can have multiple segments, but each of them still has the 63K limit attached. 4. Specialized Progress solutions 4.1 Read only databases Progress clients can be started with a -RO parameter, which makes that session read-only. However, every client connecting to that database must be -RO as well--mixing updating clients with read- only ones can cause some seriously strange things to happen; this is not recommended. At present, there's no way to enforce this at the server level. You'll get a .bi file even with -RO clients. If you can meet these guidelines, read-only client access of database can greatly improve performance accessing records in that database. It's highly recommended for databases being opened in order to dump them. 4.2 Disabled indices The updating of indexes as records are created and destroyed takes much of the time spent doing that operation, with the balance going to managing the space in the database. If you are trying to get maximum speed out of a large scale record creation or destruction (like a year-end close out), you may find that it is faster to disable the indexes on the files involved, run the program that manipulates the records, then do an index rebuild to recreate the indices. There is no other way to reactivate an index other then to do an index rebuild, but it is in many cases faster to do that than to deal with extended update time with the indexes in place. Disabling indexes can be done in the data dictionary. One thing to note is that if you disable indexes, programs that rely on them will no longer work (you'll at least have to recompile the programs, and will never work unless rewritten if that index is specifically called for with a use-index). Another thing to watch out for is that, since the uniqueness constraint on records is embedded in the index update code, you can create records with the indexes off that will cause conflicts after the indexes are enabled; this is a concern with bulk record creation more than bulk deletion. One method I have used to make record updating faster but avoid conflicts is to use a program that manipulates the Progress meta- schema to disable only indexes that aren't unique. This doesn't get you the all-out speed of a totally unindexed solution, but it is free from more potential integrity problems after mass loading of data. Here's an index-disabling program that only disables indexes that are not unique and not primary: for each _file, each _index of _file where recid(_index)<>_file._prime-index and (not _index._unique): _index._active=no. 4.3 Running in no integrity mode You can turn off Progress's integrity checking that makes sure that all transactions are synchronized and no partial transactions are written to the disk. This will greatly improve performance, but should your system crash while a database is open in no-integrity mode it will be destroyed with little hope of recovery. No integrity mode is usually only used for mass loading of data; in fact, it's automatic if you use the Progress bulk load utility. Writes to the BI are still buffered and transaction undo still works even without integrity. No integrity is enabled by using a -i on the startup command for the server or single user session. 4.4 Faster index rebuilds Index rebuilds sort through all of the data in your database and improve the structure of the indexes that keeps it straight. This sorting is done in blocks, with multiple phases. You can greatly improve performance by making those blocks as big as possible. Whenever you are doing an index rebuild, set -TB 31 and -TM 32 to make that session run as fast as possible. These two are client parameters, so you could use them for a regular Progress client sessions (this will speed up users who do file access of unindexed components) and select them on a per user basis. Progress suggests that a TB of 24 might be more efficient on some systems, because that makes the buffer a multiple of the block size Another thing to do to improve the speed is to put the temporary file on a different disk than the DB and BI files with the -T parameter. You could probably set -B to a huge number, but tests I've seen show that once it gets much over 20,000 it stops being effective in increasing the performance of the rebuild, as that's all the data that gets processed in one chunk. If you're not using the presorting option, it can be much more significant. 4.4.1 Index rebuilds on very large databases If you've got a very large (over 2GB) database, you may find that you can't do an index rebuild normally because the sort (SRT) file becomes larger than 2GB, which is a Progress limit and often a file system limit. Sort files can routinely get over 50% of the database size, with 75% not being unheard of. There are a number of approaches for dealing with this problem. You can lower the -TB parameter to make smaller SRT files. Or, you can avoid presorting. It will take much longer, and you'll need to have a much larger -B (like the size of your production buffer cache), but the problem is gone. Another approach is to specify some indexes, and split your database up so that the sort file doesn't get as big. This still won't help if you have any one single file bigger than the file system limit. You can write a program to generate a partial listing of indexes utilizing the _file and _index entries in your database, and run proutil with its output piped into it. 4.5 My dump/reload takes forever; what can I do? Several of the techniques outlined above can be used, often together, to improve these times. You dumping database can be opened in read-only mode. Your reloading database can have its indexes disabled and have its mass load session started with integrity off, or you can even better use the Progress bulk load utility. Afterwards, you perform an index rebuild and your database is ready for production use. The major drawback to this technique is that errors at any step of the process will usually require you to start over. One way to minimize this problem is to back up your database after every major step, then you have only lost the time for the last step if problems occur. Also note that you'll always get better performance if you can put the .d files on a separate disk from where the databases are. If the reason you're trying to dump and reload is to migrate from one type of computer system to another, you might want to investigate using probkup and prorest with compatible media between the machines. You might also want to look at the undocumented binary dump and reload commands, as these are portable between operating systems as well. 4.5.1 Why should I bother doing a dump and reload, anyway? The main reason is to avoid database scatter. Scatter is when portions of a file that are normally accessed together are spread across parts of the disk. The disk drives then have to seek multiple places to get that information into memory. The dump/reload process places the related file information in contiguous areas of the disk, so the drive heads don't have to move as much. The reload will also put the index blocks in a contiguous area which really improves performance during record selection processes. You can use the utility mentioned in the database maintenance section below to check on scatter statistics. 4.5.2 Will a dump/reload decrease the database size? Progress is efficient in reusing its data space. If you delete some records, then create new ones, the space that was made free by the deletion will get reused, even though the records were may be of different types. So if you delete a number of records, then don't add any new records of any type, you may find that your database is smaller than it was when you started (although you are going to need a bunch of disk space around temporarily to hold the database dump files). Even if this is the case, you still might not find your database smaller. Progress can automatically add some empty records (15% or so of the space the rest of your file takes up) at the end of the records you reloaded, so that if that file grows larger in the future the data for it won't be scattered. I am not certain myself of the circumstances under which this happens or doesn't happen. In any case, doing a dump/reload for space reduction purposed often ends up not working out like you expect--if your database was full and didn't have any deleted records to purge, it could actually end up getting 15% bigger. A great amount of information about how exactly Progress implements the entire allocation and deallocation of storage in its database can be found in the document by Progress developer Gus Bjorklund titled Disk Space Allocation in the RDBMS; a copy is available from http://www.peg.com. 4.5.3 What's a parallel dump/reload? A technique to investigate for very large dump/reloads is the parallel dump and reload. With this method, you run several Progress sessions against the database, each dumping only its own section of the data. By keeping the processor and disks busier than a single dump can (and allowing you to utilize multiple processors on that type of system), you will get better dump throughput than with a single dump process. With the reload, you can use the same technique, having several processes load part of the data. For an in depth discussion of automating a parallel dump and reload, there were two articles in Progressions magazine about the subject. Information about contacting Progressions is below in the magazine section. The author has also given permission to include this article on my WWW home page, you can find a copy of it there as well . 4.5.4 Incremental loading methods On way to minimize the amount of down time on a dump/reload is simply to break it into time stamped pieces. You can make a pass through dumping the entire database while the database is up, keeping track of the last record dumped in each file. Reload this data into a new database. Shutdown the production database, dump all the new or changed records, reload them into the new database to bring them back in sync, then switch to that new database. As an alternative, you could create a new database, connect to it in addition to the existing one using the 4GL connect statement, and copy the records directly over to that database. You could execute this in several passes, adding new records each time. This lets you get the databases as close to in sync as you want, which would minimize the down time during the switch. There are many obvious problems with these methods, from getting accurate time stamping information on every database file to handling record deletion, but you may be able to adapt this sort of load to your application. 4.5.5 Binary dump and load There is an undocumented facility in Progress for dumping a database in a binary format instead of the usual text dumps. It's undocumented because there is a great potential to easily demolish your database, and Progress will not support your work in this area. These binary dumps are machine independent. Note that they do not defragment your data like a regular dump and load does. The way you do it is to truncate the bi file, then run proutil db -C dbrpr There are a number of options that will show up; all are quite dangerous to use but potentially quite useful. The binary dump and reload involves using the Database Scan menu to dump records to a RM file, then load from an RM file with a new database. Do not even think of deleting the place holders when asked about it during a dump. Do not load these records into a non-empty database. There are some total figures that display at the end of a run of the dbrpr utility. The total records count is the total number of record start entries found. The total continues is the number of record continuation fragments seen during the scan. And the # dumped is the number actually put into the dump file. If you really, really want to know how to do use the binary D/L, I strongly suggest you purchase Dan Foreman's performance book, where he discusses it in detail. Make sure you do lots of experimentation with test databases before trying any of these utilities on a real one. The main reason sites resort to using this utility is that they have very large databases that take too long to deal with using the standard processes. 4.5.6 What's procopy good for? procopy makes a perfect copy of your database. Accordingly, it is of no use for optimizing fragmentation or any of the other things a dump/reload improves. It can be useful for making a copy of your database that you can access in read-only mode for reports etc. The program normally generates a bunch of screen output that can slow the program operation. You can suppress this output with the -s parameter. 4.6 Off-hours batch programs For most applications, you are worried about making your system perform well when the users are using it. If you have intensive reports, updates, or deletions, consider creating a batch program that does them. Then use some facility (be it operating system commands like cron and at or a Progress program that watches the clock) to schedule your large programs to run at off-hours. Output from large reports can be spooled to a file and programs created that let users view it instead of running the report themselves. 4.7 Ongoing database maintenance The best way to make your database work faster is to have less data in it. Any good database application should have facilities for purging older data out of the main database to some history archive, be it another database or some other file. Usually this history migration is followed by a dump and reload to reclaim the space that was used and get the other data defragmented. Starting with V7, the best way to see what's going on inside your database is to use proutil db -C dbanalys This will give you a summary of database record counts (a handy thing to have for other reasons) and all sort of other statistics. Starting with V7.3A, this utility can be used again a running database (before that, you could fool it by temporarily hiding the .lk file, but that's a scary thing to be playing with). 4.7.1 How do I tell how much space is taken up by one particular file/table? It's possible to write a fairly simple Progress program to add up the size of the fields in a file/table, count the records, multiply, and spit out an estimate of how much space that particular data is taking up. The difficult problem is estimating how much space is taken up by the indexing portion of the database; it's not at all unusual for certain parts of the database to use more space in indexing than to hold the data itself. And even if you did write a sufficiently complicated program to add up the indexing size (I've been told that some have done it), it wouldn't work under V7, as the indexes are compressed and the compression ration is nearly impossible to predict theoretically. If you're using V7, by far the best way to count records and figure out file/table size is to use the dbanalys utility as mentioned in the section above. Before V7, one way you could calculate the size would be to do a binary dump of the one table you want to check out (see the section on binary dump/reload for an introduction to that subject). 4.8 Backup and restore operations The main thing to watch out for is for backups and restores is that the buffer you are using is sufficiently large enough that the tape drive keeps streaming constantly. For example, using cpio, setting the -C parameter to a higher value can often improve performance. You also may want to experiment with using other utilities to backup your data. On some platforms, tar may be considerably faster than cpio, or vice-versa. 4.9 Controlling misbehaving Progress programs One problem many people run into is that clients that become disconnected can leave behind processes that do strange things. This can become a performance problem because these wayward processes can start using up large amounts of system resources doing a number of odd things. Often these problems result from having client computers turned off without exiting Progress first. With a UNIX system, you can stop the resulting misbehaved process a number of ways. You can stop the process with proshut, but not only does this take a while it introduces the opportunity to type a wrong character and take down the wrong user or even the entire database. The officially sanctioned way to stop a Progress process under UNIX is with kill -15 process Where the process number comes from the output of ps. Some people reflexively use kill -9 to stop any UNIX process; be warned that this can result in that process not yielding all the shared resources it had, which take down the server when it is noticed or even corrupt data. You should never kill -9 any Progress process because of problems like this. Other kill signals you can try other than 15 (software termination) are 1 (hangup), 2 (interrupt), 3, or even 8 (floating point error). One reasons you can have these signals not work is that some script you are using is trapping them. This is a bad thing, as it can mean you can't stop out of control processes properly. There a number of other UNIX utilities that can clean up after damaged processes. proutil -C dipcs shows what shared memory segments are used by each process. You can look at and remove them with the ipcs and ipcrm commands. 5. Platform-specific recommendations 5.1 What about Novell NLM servers? It is possible to get good performance from an NLM server, but you're handicapped by several things. First, NLM servers only communicate client/server, which gives you that performance hit. Second, the NLM server is not at the top of Progress's list of platforms to support, so you don't get new performance improving releases as frequently as the more popular platforms (and I hear there won't be a V8 NLM server released at all). One problem many people run into is the difficulty of remotely shutting down and restarting a NLM server. Information needed about automating this process is available on my WWW home page. 5.1.1 Optimizing the server computer Most of the things discussed in the hardware section below apply just as well to NetWare servers as they do to UNIX systems--use more disk drives, add more RAM, etc. There is lots of material available on NetWare tuning. One good source is to get an NSEPro volume and search through the history of articles on the subject. You can get one by buying the Novell Press CNE training book; one particularly appropriate article this I can't seem to find anymore from 1993 discusses database performance specifically (there's little there that doesn't appear somewhere in this document, however). Many of the parameters NetWare optimizes tune themselves as the server runs; one things you may find is that your server doesn't perform very well when first started because of this (it takes a while to arrive at the optimal parameters by the self-tuning processes). 5.1.2 Optimizing the NLM The biggest tradeoff you'll have is how big to make the -B buffer; whatever you don't use there gets used by the server for disk caching. Experimentation is the only good way to figure out how this balance should work for your circumstances, because if you take away memory from the caching to use for -B the other operations of your file server can be crippled. The most common Progress NLM version I see is 6.2M, which suffers from the limitation on how big you can make -B--values over 32,000 don't improve performance as much as they should. Make sure you find out how to set the maximum short term alloc parameter correctly on your system to control how the NLM can interact with other server memory. 5.2 UNIX Kernel parameters Any non-trivial Progress system running under UNIX will require that you update the kernel parameters for that system. Every Progress/UNIX system I've ever used gave an error about semaphores when first starting Progress in multi-user mode out of the box. This section gives some general parameter information, there are specifics for the major categories to update below. On BSD derived systems, you can usually find the kernel configuration information in /sys/conf or /usr/sys/conf. With System V machines, look in /etc/conf/cf.d. Nowadays, you rarely have to touch these files directly; most modern Unixes have utilities to let you look at and configure these files (like mtune, stune, or sysdef). NBUF and NHBUF are parameters to control how much buffering of disk data the operating system does. Because Progress uses its own internal buffering methods, setting these values very high (as they typically are in general purpose UNIX systems) can be harmful--it's better to lower them and use the memory for a -B buffer instead. If yours is an untuned UNIX system, you'll probably need to increase these parameters, as the defaults on most installations is very low. You shouldn't have the NBUF buffers taking up any more than 20% of the memory in the computer if Progress is the main application used. If you have lots of open files (like multi-volume databases with lots of extents), you'll probably have to increase the number of files that can be open by a single process (usually called NFILES or NOFILES) Sometimes, when you try to increase kernel parameters, the system will not let you build a new kernel because you've gone over the arbitrary maximum set for the value. You can alter files like /etc/conf/cf.d/mtune and /etc/conf/cf.d/stune to increase these maximums so your kernel rebuild will succeed. If you're using TCP/IP, you may have to update the timeout parameters for it in order to get Progress to function properly. 5.2.1 Semaphores and shared memory Progress uses UNIX semaphores extensively to manage the communication between the self-service clients and the database broker. Because of this, most UNIX systems as they are configured upon initial installation will give various errors when you start Progress up. The default number of semaphores on many systems is 25, which isn't enough to correctly start up a Progress database server with the default parameters (i.e. number of users=20). If you want to monitor the status of the shared memory in use by Progress on a system, use proutil -C dbipcs Basically, each database server requires its own semaphore handle. Each of these handles needs to be able to have a number of processes accessing it equal to how to the number of users that may attach to any database server. Here's one set of guidelines for setting these parameters; for the official Progress view, look in the troubleshooting section of the Progress System Administration guide (this also has a very useful table that gives a list of the different error messages Progress gives and what setting you need to change to fix that problem). Some information on this topic often appears in the installation notes. SEMMSL: This is the maximum number of semaphores that can access one semaphore identifier. Set this each to the maximum number of users on any one database, plus one for the server, plus a few (5 or so) for miscellaneous system use. This is the maximum number of semaphores any one database server might use. A value too low may result in Progress error 1130. SEMMNS: Total number of system semaphores. Multiply SEMMSL by the number of databases, and add a percentage (say 30%) slack for other system resources that might need semaphores. If this is too low, you may get Progress errors 1093, 1131, or 1195. Some V4 systems dynamically handle this parameter. SEMMNU or SEMUME: Semaphore undo structures. You need one of these for every process (broker, BIW, AIW, APW, watchdog, server, monitor, or self-service client); essentially, this should be a bit higher than your database -n parameter is set to. A value too small may result in Progress error 1081. You are certainly safe if you set this value to the same value as SEMMNS. SEMMNI: Maximum number of semaphore memory identifiers for the system. You need at least one per database server. Setting SEMMNI equal to the number of database servers plus a few (5 or so) is usually sufficient. A value too low may result in Progress error 1131. Shared memory is where Progress puts its database buffers. You'll especially need to be concerned with these if you are trying to increase the size of your buffer cache, since it's allocated that way (along with the lock tables, log buffers, and other shared structures). UNIX shared memory consists of a number of segments of limited size. If the total amount of memory needed is greater than the amount that can be put in a single segment, multiple segments are allocated. SHMMAX (SHMSIZE on some systems): Maximum size of a single shared memory segment. This may be measured in bytes, but is more commonly measured in "clicks", which are the system's page size. If you set the value too low, Progress will create lots of small segments and possibly overflow the limits on number of segments described below. For V6.2, the maximum size Progress would use for this parameter was 8,000,000. With V6.3, you should set it to 16,777,216 bytes on most systems (taking in to account whether you measure in clicks or bytes for how you actually set the parameter). For Sequent systems, use 134,217,728 bytes. For SCO systems, check the SCO section below for information on this parameter. This is not a tunable parameter on AIX systems. SHMSEG: Maximum number of shared memory segments per process. Progress requires at least 4, with 8 being more reasonable. Usually the system default for this parameter is higher than that, so you probably won't have to change it. Some version of UNIX don't have this parameter at all. If you have a large shared memory pool, and SHMMAX/SHMSIZE is set to a small size, you'll need more segments here to compensate. SHMMNI, SHMALL: Number of shared memory identifiers. How many of these you need depends on how many segments each of your databases has, and how many databases you have. Setting this to 2 * Number of database servers + a few (5 or so) is a good start. If you have a large number of users on a single database, the server may use more than 2 segments, so change that 2 accordingly in that case. This is the parameter that Progress usually complains about when starting multi-user servers (with error 1137 or 1138), saying that it doesn't have enough identifiers. There's good reason for that--your shared memory could get corrupted if the system runs out of these. 64 is a reasonable start for this parameter for most small systems, and setting it to the maximum allowed is not a bad idea. Note that all these suggestions don't take in to account other software on your system that may very well use semaphores or shared memory as well. You are safest if you add the numbers suggested for these parameters to what was already the default to your system. Also note that semaphores are not used in single user sessions, and only one is used for each remote client server regardless of how many clients connect to it. 5.2.2 Process and memory limits Be aware that, in this section, when a reference to "number of users" is made, this is usually a count of self-service users only. If you have remote clients, they all go through the server, which only counts as one user in this context. There are a number of limits that may keep additional programs from being run. Some of these have had their names (and exact functions) changed when System V R4 was released. Process limits: NPROC: Maximum number of simultaneous processes that the system can run. As you start running bunches of clients on your system, this parameter may need to be increased. MAXPROC or MAXUP: Maximum number of processes one user can run at once. If your system complains that you can't start a process, check this value along with NPROC. Memory limits can keep your processes from starting, especially when sufficiently large parameters are used (large -B values in particular often cause this). DATLIM: Maximum memory available for the data segment for a single process. VLIM: Virtual memory limit (this is for when swapping is involved). MAXUMEM: Maximum amount of memory a single user is allowed to use. This will have to go up as the shared memory usage is increased with a larger -B etc. ULIMIT (V3): Largest file size a process can create. SFSZLIM, HFSZLIM (V4): Soft and hard limits on the amount the largest file size a process can create. MAXUMEM (V3): Maximum amount of memory that can be used to run a process. SVMMLIM, HVMMLIM (V4): Soft and hard limits on the memory that can be used to run a process. MAXPMEM (V4): Maximum amount of physical memory to use (0 means no limit). The file limits usually need to be increased if you break your database into a large number of extents. NFILE: Maximum number of files the system can have open at a time. NOFILES (V3): Maximum number of open files one user can have at a time. SFNOLIM, HFNOLIM (V4): Soft and hard limits on maximum number of open files one user can have at a time. 5.2.3 DG/UX Specifics DG/UX: Set NPROC and MINBOUND to 512. Vary MAXBUFAGE to alter the frequency of the frame cleaner. Here's a sample of a Data General DG/UX 5.4 on a system 9500 with 6 50Mhz cpus, 640MB RAM, 32GB Clariion drive: SEMOPM 32 SEMUME 15114 SEMMNI 1088 SEMMSL 576 PTYCOUNT 128 NPROC 1024 SDESLIM 128 HDESLIM 2048 MAXUP 256 PERCENTSTR 20 NPIPE 512 NQUEUE 8192 MAXSLICE 50 SHMMAX 1667216 PERCENTBUF 45 MINBOUND 1024 MAXBOUND 2048 5.2.4 SCO UNIX Specifics Most SCO machines can be tuned with the sysadmsh command. OS5 includes a GUI interface to this same utility, but read the warning about it below. Here's an example from a SCO UNIX machine running with 32MB of RAM: SEMMAP 35 SEMMNI 35 SEMMNS 218 SEMMSL 108 SHMMNI 109 SHMMAX 540000 You'll get errors when you build a kernel with these parameters, as some are beyond the recommended limits for the parameter. If this bugs you, call SCO up and tell them their recommended limits are must be for customers not doing any real work. If you're running out of data space, check and increase the MAXPMEM and MAXUMEM parameters. For SCO OS5, there are several parameters that can't be altered with the GUI system administration interface. What you need to do is to login as root and cd /etc/conf/cf.d ../bin/idtune -m PARAM VALUE You will need to do this to alter the SEMMNS and SEMMSL parameters. Rebuilt the kernel after you've finished making changes. One thing to watch out for is that there is a bug with the handling of semaphore memory with SCO OpenServer 3.2.X. You'll find you can't usefully increase the value of SHMMAX above 4MB (4194304). What you have to do as a workaround is set it to a lower value, like 4000000, and increase the SHMSEG parameter instead. 5.2.5 System V R4 Specifics You'll have to increase the semaphores, possibly increase the number inode tables, and increase the file descriptor high water mark to get most Progress system to work with this release. Many items appeared in the generic UNIX section above. 5.2.6 AIX Specifics You can tune the AIX kernel with the smit or smitty interface to system configuration. The vmtune command lets you adjust how much of the systems memory is allocated for disk caching; you'll need to drop this down from it's very high default to something much lower if you're going to be running Progress on the system, and use that memory for the buffer cache instead. One problem that has been reported with AIX is that it's upper limit on the number of shared memory segments that can be used is too low for some applications (error #1175 occurs), and you can't increase the segment size. You can reduce the amount of shared memory your processes are using by lowering parameters like -B, - c, or -L. This will decrease performance, but it doesn't matter how fast it might work if it doesn't. 5.2.7 HP/UX Specifics Make sure you check out the section above on single process access limitations, it will impact the setup of HP/UX configurations considerably (supposedly this limitation is gone in HP/UX 10.0). Here are a sample set of formulas for setting the parameters with HP/UX; these are from a tutorial by Bruce McIntrye about configuring Progress for MFG/PRO (you can get this via WWW, details are in that section below). MAXUPRC 50 MAXUSERS 300 NBUF 8000 NFILE 32 * (NPROC + 16 + MAXUSERS) / 10 + 32 + 2 * NETSLOP NSTLBE 0 NTEXT 24 + MAXUSERS + NETSLOP SEMMAP 10 SEMMNS 1500 SEMMNU 1500 SHMMNI 100 SHMSEG 120 Obviously, the semaphore section may need to get adjusted for your site from these fixed values. HP/UX includes a utility called sadp to get information about what your system is doing as far as disk access goes. You also might want to check out purchasing a copy of glance, a utility to better monitor what's happening 5.2.8 Sequent specifics Sequent systems need their shared memory space configured quite differently from others. Make sure to check that section above to see how large to set parameters like SHMMAX. 5.2.9 UnixWare specifics UnixWare is the definitive System V R4.2 release, so most of the comments in that section apply. One nice utility included with UnixWare is rtpm, which is an interactive performance monitor that includes the information sar gives you but in a more readable form. Be warned that you need to pop the sampling interval up (with the + key) to something more on the order of 10 or 20 seconds to get useful data, and that keeps the monitoring overhead from eating all your system resources (be warned that rtpm will hog a big chunk of memory no matter what you do). 5.2.10 Digital UNIX specifics The default configuration on a typical Digital UNIX system is tuned so that the disk buffers can expand to use all of the system memory. With Progress, you'll need that memory for the buffer cache and Progress processes instead. Contact DEC for the specifics for how to detune the disk buffers. You'll want to drop the maximum percentage of memory that the system can use for them, and also increase the amount of memory that gets returned to free space when the system runs out of memory. 6. Hardware selection This is the last thing addressed here, because it often the last resort. If the computer you're using was badly designed, there's not much you can do with it except upgrade, which is never what whoever is signing the checks (be it yourself or someone else) wants to hear. Most of the suggestions in this section are mainly aimed at people who are designing a new Progress server. 6.1 Bus architecture One of the things that drives Progress performance tuning is that it is quite demanding of both the CPU and the I/O bandwidth of the computer. Having one without the other doesn't help you much. Because of this, you can't afford to have items in your computer that occupy too much of their CPU's attention in their I/O usage. Multiple serial port boards (like those used for attaching lots of dumb terminals) are one good example; if you have a badly designed one, the CPU can end up taking far too much time talking with the serial board. Don't just check out throughput for these devices, also make note what percent of the CPU's time is spent servicing the device to get that throughput. The three major devices that Progress sites usually have to worry about are disk controllers, network cards, and (for those still using terminals instead of network connections) multiple serial port cards. As far as Intel based servers go, the only two real choices for buses are EISA and PCI. Both are designed to keep the CPU free if the cards are well designed. PCI, being the latest and greatest, is more successful at this goal than EISA, the former champ. 6.2 Disk subsystem In the vast majority of cases, it's the disk drive subsystem that is the initial bottleneck in a Progress system. You can address this through Progress itself, with things like increasing the database buffers with -B, but there is no substitute for having a good disk system. 6.2.1 What about disk controllers? Disk controllers for Progress should be of a type that minimize the amount of intervention required by the CPU to read and write data. This will let the CPU perform other tasks instead of waiting for a drive to respond to a request. Usually these controllers are labeled as bus-mastering or other such terminology, they usually use DMA techniques for improving performance. Get the highest bandwidth disk channel you can that is reliable for your data. Consider getting controllers that support hardware RAID for future use even if you're not using it now, but make sure you read the comments about RAID below before implementing it. Another thing to watch for is how many devices you have per controller. Just because you can hang 7 devices off of a SCSI bus doesn't mean you should; between transmission delays to get to the end of a longer bus and device contention, it's really not a good idea to have more than 4 disk drives on one SCSI bus. Ideally, for systems with one large database, you should have at least 3 controllers: one for the OS and assorted peripherals, one for the database, and one for the BI file. It is critical in high performance environments to keep the devices attached to a controller from overflowing the controller's available bandwidth. If you have several high intensity drives on a controller, you should definitely investigate adding another controller to split the load up. Usually 3-4 drives are about all you can usefully use on a regular SCSI channel, or 4-5 on a wide SCSI channel. 6.2.2 How many drives do I need? This is totally dependent on the intensity of transactions you have, but in most cases, adding more disk drives is the second best thing you can do improve the performance of a Progress system (with adding more RAM being the best). The section below on where to place files at will give you a better idea how to prioritize moving things to new drives as they are added. I'd probably say at least one for every ten users on the system is a reasonable start. 6.2.3 Should I use SCSI or IDE drives? IDE drives are designed for single user computer systems. Their performance looks pretty good in those circumstances, but one thing you don't notice is that they use quite a bit of CPU time to get that speed. Under a multiple process load of a real operating system, IDE drives end up monopolizing the CPU just to do I/O, which is not a good thing. SCSI drives have other advantages as well; good implementations have features like command queuing that are specifically designed to improve performance in multiprocessing environments. Plus, there are higher quality SCSI drives available than IDE ones. Just recently some high performance IDE implementations, using DMA and/or bus-mastering, have started appearing. These may end up equaling the high performance of current SCSI interfaces, but SCSI usually gets improved at the same rate to keep its edge. 6.2.4 What type of SCSI interface do I need? Fast? Wide? SCSI is an evolving interface. The constant tradeoff you'll run into is that newer technologies for drives tend to be faster, but less reliable when first released. The original SCSI standard transferred data with a bandwidth of 5MB/s, far faster than any of the drives at the time. As drives got faster, and new standard was introduced that supports a 10MB/s SCSI bus; this is referred to as SCSI-2 or Fast SCSI (both mean the same thing). Both of these interfaces transferred data 8 bits at a time between the peripherals and the controller. The newer Wide and Ultra SCSI implementations let you up this to 16 or 32 bits at a time, increasing the bus bandwidth to 20MB/s or 40MB/s. What does this mean, in real terms? Well, a reasonably good SCSI hard drive nowadays might read 5MB/s of data if it got really lucky looking things up on the disk; 3MB/s is more typical. If you've only got one drive, it doesn't matter what type of SCSI interface you use. But as you start adding drives, you'll eventually use all the bandwidth of the SCSI channel you're on; that's why you need to start shopping for more controllers. Also realize that if you're using some older SCSI devices mixed in with the newer, they will drag the bandwidth down with them if they are active often; it doesn't matter if all but one of your drives are the latest Fast/Wide implementations, if you've also got an old SCSI-1 drive that gets used frequently it will use 4x the bandwidth on the SCSI bus compared to the new ones, which can cause the bus to saturate far sooner. The moral here is that the type of SCSI interface gets far more important as you start adding more drives. It's really better if you could add more controllers instead of using a faster SCSI interface, but it's more cost efficient to better utilize the SCSI bandwidth. Since Wide SCSI is fairly new, there are more potential problems with its implementation. The interface lets you push the controller and the drives to their limits; there has been more than one report of Progress systems that had their data corrupted because the Wide SCSI system used pushed some component too far or didn't quite comply with the standard, so some portion of the disk not get written properly. I try to avoid these problems by only purchasing controllers and drives from top quality vendors (after all, the drives are usually far cheaper than the cost of replacing the data they hold). You need to test carefully before committing your data to any disk subsystem. Be aware that cabling termination issues become critical as you push bandwidth up; you'll have to switch to more aggressive active termination (or even passive-active combinations) to keep up with increasing data rates. There are a variety of bleeding edge, very high-performance SCSI implementations running around right now: high speed standard implementations, like Ultra-SCSI, and even serial methods like fiber channel. The downside is that costs are high and standards less than firm at the moment. About the only time I think this would make sense is if you had a really big system that it's impractical to use a lot of controllers with. 6.2.5 What about caching? Caching can often improve performance, but beware of drives or controllers that have write caching. Some of these can save up write requests, sending them out in ordered batches to improve throughput. Such write caching will invalidate the integrity methods Progress uses, and Progress will not support your database in such circumstances. Make sure your write caches are specified as write-through and not write-back to avoid such problems. Read caching doesn't have any such negatives against it (some claim the overhead from double-buffering the reads between the disk cache and the Progress -B adds enough overhead that you should disable the read caching altogether, I don't necessarily agree), but realize that the buffering Progress already does makes most of them far less effective than they might normally be. It is probably a better use of resources to expand the RAM in the computer and increase -B instead of adding memory for disk caching. 6.3 Why should or shouldn't I use mirroring or RAID drives? This is a hotly debated topic, especially on the use of RAID level 5. Check out http://www.ozemail.com.au/~pfred/faq.html for one set of arguments on the subject, but the major points are summarized here. RAID support usually comes in three forms: operating system software (the Veritas on-line data manager is an example here, many operating system vendors also offer products), controller level support (from DPT, Compaq, Mylex, or others), and dedicated RAID devices. Most of the RAID abuse is aimed at the dedicated RAID boxes. One advantage to using software RAID is that you can break the mirror and backup a static copy of your database, remirroring after backup is complete. The disadvantage is that software is subject to competition with the rest of the processes running on the computer and can adversely impact performance, as well as giving more opportunities for interactions with other programs that reveal bugs in the RAID implementation and can corrupt data. 6.3.1 RAID Data integrity With RAID 0 (striping), you get no improvement in integrity, just performance. Since Progress already supports multi-volume databases (and multi-volume BI files don't improve performance because of striping), you are better off using that facility instead of RAID to spread your data across multiple disks. RAID 1 (mirroring) can improve performance on reads, and lets one disk drop dead without any drop in performance writing. It is the only RAID choice that everyone agrees is worthwhile for Progress. RAID 5, with its odd combination of striping and parity style redundancy, does let you get by with a crippled disk at lower overhead than RAID 1. Given how small of a percentage of the average Progress system is spend on disks, however, it is usually better to spend the extra money on RAID 1. If you've got an integrated RAID 5 box with its own controller, be very careful; often they have on-board write caching that makes Progress's write integrity algorithms to be subverted, and can cause database corruption. Make sure that any write caching is backed up by some mechanism, be it a UPS (or two), NVRAM, or the like. BI files in particular are a bad choice for RAID 5 because of the write overhead. DB files are a better choice if you must use RAID 5--the parity type of overhead doesn't require as much additional disk space for the much larger DB files, while still giving protection against single drive failures. RAID that is done in software, with the server's CPU handling write caching, is particularly dangerous to data integrity; unlike dedicated RAID boxes, there is usually no reliable mechanism for recovering write-cached data that wasn't yet written if the server goes down. 6.3.2 RAID Performance The biggest complaint heaved toward RAID 0 and RAID 5 is performance based. Since every disk request requires using every disk, your bandwidth is equal to that of one disk. So, if you had 9 disks with 10MB/s bandwidth each singly in a RAID array (8 bits and a parity bit), the total bandwidth to the drives is 10MB/s; if you simply attached all 9 disks and put the files on them manually, you could have a peak throughput of 90MB/s. Some RAID arrays try to address this by having higher speed connections (possibly multiple parallel connections) than the actual drive speed. Another RAID 5 problem is that any writing to the disk involves updates to the parity information that means heavier activity writing than a simple write to a normal disk (access to the parity drive becomes the bottleneck). RAID 5 performance reading doesn't suffer from this problem. Dedicated RAID 0 boxes have a similar complaint leveled at them; your data is striped to multiple disks that normally could be working simultaneously, but the input channel limits the amount of data, usually to what a single drive along could handle. Even if the input channel is wide enough, you are often left to the RAID boxes' mercy as far as how files are laid out--if you had the disks available separately, you may very well be able to get a much better distribution of data across each drive by knowing how your application works than what you'll get with the RAID system striping files. 6.4 I've got a bunch of disk drives; Where should I put things? Here are the major components of a Progress system: 1) Operating system 2) Application (.r code) 3) Temp files 4) .DB files 5) .BI files (and .AI files) In an ideal system, every one of these items would have its own disk, with the data disks (4,5) being mirrored for maximum performance and integrity. With multiple databases (or large ones), multiple disks are recommended for the DB and BI files (the BI file being the more important of the two; given 3 disks with 2 equally important databases, put both databases on one disk and give each BI file its own). For larger user counts, consider multiple temp file disks, especially if your database design requires access to unindexed sorts routinely. Ultimately, very large system need to start having separate disk for things like the system spooling directories as well. Given less disks than the number given above (the usual case), combining them becomes a bit of an art, where you have to trade- off integrity as well. I would never run a Progress DB or BI in a production setting without mirrored disks for them myself; the data is worth far, far more than the additional disks are in any environment I've worked with. Usually the first thing that gets combined in the list above is the OS and the application. Next is to put the temp files either with the OS or with the DB. The BI files should be the last thing you combine with something else, for reasons mentioned back in the BI file discussion. Also consider having a spare drive or two on the system. You'll often find you need them later. It will let you have a spare copy of the databases for testing, give space for expansion during a dump and reload, or have a formatted drive ready should one crash. If you don't do this, you'll get into trouble if your database takes up over 50% of your drive and you need to dump it for some reason. 6.5 What about the network? Network performance usually becomes a factor in two cases. First, if some portion of the data is located on a remote drive, performance can drop considerably (this is not a factor for Progress databases, you can't put them on a remote system because the data integrity functions can't work that way). Second, client-server configuration are quite heavy on network bandwidth. As always, get the best network backbone you can. It's impossible to get a good estimate on the actual amount of traffic without some experience with a very similar application before for comparison. Given limited bandwidth and network overhead, the best approach is to see what data can be stored locally, avoiding network access altogether. Examples of partitioning data this way are given in the client/server and the database design sections above. If you are using a standard terminal emulation direct host connection to the server (i.e. you Telnet into it or use a multiple serial port terminal server), it is not unreasonable to run Progress over a 9600 baud serial link; everything but a complete screen refresh will be fairly quick. Of course, the overhead of a network connection doesn't mean that you can just multiply that by the number of simultaneous users to determine your network needs (you cannot come anywhere near 100 users over a single section of 10MB/s Ethernet). It's also important to consider the interface between the computer and the network. It doesn't matter if you network can do 100MB/S if you've got a crummy network card that takes up too much of the CPU time. Try to use 16 or 32 bit network cards designed to free your CPU up, and make sure the I/O bus in your computer is capable of keeping up with it (you're never going to get close to even 10MB/S on a PC running an old ISA style network card). I have seen reports that the simple act of packaging the information into packets and feeding them to the network card itself results in up to 75% of the overhead; the actual network bandwidth itself and server response times aren't the only things you need to consider. 7. Credits and additional resources 7.1 How did you find out all this? Most of this information was gathered while I was working at Old Line Plastics (check out http://www.charm.net/~olp for their products) over the course of several years; special thanks go to Chuck Masters there, who continued to pay me every week even though it seemed at times I was just goofing off and playing with the computer (which is, of course, the best way to learn things). Besides reading the manual and fooling around with various systems for a couple of years, the major teacher I used was the traffic on the Progress PEG e-mail list during 1995 and on. Here's a list of the people whose messages I found interesting enough to save, either because they gave me some good information or because they raised questions that started my own investigation. Many of the items in this FAQ are essentially outright theft of their ideas. Just to be fair, the number before each person's name is how many of their messages I swiped material from, so they can get proportional credit for their influence (the order is by #, then by date of first message, in case you care or wander why someone got ahead of your own insightful comments): 27 Tom Bascom (tom@lkw.com) 24 Bruce McIntrye (bam@qad.com) 15 Peter Headland (peter_headland@matrixlk.demon.co.uk) 12 Geoff Crawford (geoff@innov8cs.com) 6 Thomas Hursh (thomas@cintegrity.com) 4 Gus Bjorklund (gus@progress.com) 4 Bernd Felsche (bernie@metapro.metapro.dialix.oz.au) 3 Dwight Taylor (dwight@mckee.com) 3 Maxwell Spangler (maxwell@clark.net) 3 Richard Gray (richard.grey@sophia.attgis.com) 3 Dick Gingras (drg@mactavish.com) 3 Melanie Hubbard (hubbard@bedford.progress.com) 3 Greg Higgins (higgins@happcontrols.com) 3 Dan Foreman (danf@prodb.com) 3 Arthur Fink (arthur@mainelink.net) 2 Scott McClure (scott@imed.omen.org) 2 John Malloy (john_mal@srcditsy.demon.co.uk) 2 Peter Frederick (pfred@ozemail.com.au) 2 Andrew Scott (andrew.scott@dundee.attgis.com) 2 Rick Terrell (rickt@hteinc.com) 1 (gdickson@delphi.com) 1 Tor Dahl (tor@utri.no) 1 John Johnson (jdj@iadfw.net) 1 Stephen Semeniuk (stephen@sentai.com) 1 Marcelo Pacheco (pgs@embratel.net.br) 1 Neil Davies (neil@cogita.co.nz) 1 Chuck Hooke (chuck_hooke@pts.mot.com) 1 John Pierce (jpierce@ix.netcom.com) 1 Tom Kimes (tkimes@nando.net) 1 David La Londe (jungle@lainet.com) 1 Thomas McLean (tkmclean@ingr.com) 1 Kevin Wilson (kevin@pbs.com) 1 Dan Keizer (dan_keizer@fed-ins.ca) 1 Scott Johnson (merritt@usa.net) 1 Paul Amor (p.amor@ci_custima.custima.com) 1 Alan Adams (alan.adams@nz.eds.com) 1 Kar Wong (progress@mnl.sequel.net) 1 Arne Mytting (mytting@oslonett.no) 1 John Suzanne (john_suzanne@acm.org) 1 Yuri Kolesnikov (yuri@sysplus.dspi.com) 1 Alexander Leonenko (a.leonenjo@csbi.spb.su) 1 Ellen White (ewhite@pepsi.com) 1 John Suwantiak (johannes@qed.co.nz) 1 Max Rice (mrice2@sarcom.com) 1 Guy Washburn (rgw@bedford.progress.com) 1 Joe Strain (joe_strain@amf.com) 1 Howard Reagor (howard@mckee.com) 1 Chris Ruprecht (chris@rupeden.Olivetti.za) 1 Neal Rhodes (neal@mnopltd.atl.ga.us) 1 tom@NMSU.edu 1 Dana Huse (huse_dana_g@qad.com) 1 Tim Sargent (@progress.com) 1 Warren Bare (warrenb@gostc.com) 1 dlucy@aclltd.com 1 Ricky (ricky@br.homeshopping.com.br) 1 Michael Troelsen (mictro@post4.tele.dk) 1 Henry Hartig (hhartig@infosel.net.mx) Geoff Crawford and Richard Gray deserve to get singled out of this collection, as they both read my earliest version of this FAQ and provided me with considerable feedback, corrections, and suggestions. Geoff continues to provide a much appreciated scan of each successive release as well for me to catch any errors I've introduced. Thanks also go the people at Progress software who collect the common problem solutions and published them as the Progress Knowledgebase. The current information for some of the sections here, like UNIX kernel tuning, would have been impossible for me to gather myself. 7.2 Where can I get more information? 7.2.1 Progress Software Progress's manuals are often quite useful for discovering performance information, although the information is a bit spread out. There is a whole chapter on performance that serves as most people's introduction to the topic. Progress technical support can also be quite helpful, especially for keeping you from working too hard trying to fix a performance problem that actually stems from a problem or limitation with the particular release of Progress you're using. Progress also maintains what it calls a Knowledgebase, a listing of answers to common questions about a variety of issues. Obtaining KB information is sometimes more difficult than it should be, but if you contact Progress technical support they can refer you to relevant entries. Additional sources for the Knowledge base information include the Que guide to Progress V8 (it's on the CD-ROM) and Progress's home page, where you can access a searchable version if you have a valid serial number. 7.2.2 PEG e-mail list Send a message to peg-request@peg.com with a subject of subscribe to sign up. You can check out the WWW site http://www.peg.com first to get more information, or to check out older messages; this is highly recommended, as you may find your question was already addressed there before. Beware that there are a lot of messages going through the PEG; on any day I usually spend at least 30 minutes just to scan all the messages looking for things that apply to my situations. There are a number of sub-lists to the PEG that have less information and are easier to keep up with (and might be more focused on what you want), but going into details about them is beyond what I want to discuss here; again, check the WWW site for extensive information about what's available. There is also a PEG archive and discussion forum on CompuServe; GO PROGRESS to get to the appropriate section, then look at the PEG section of the library and messages. 7.2.3 Progress WWW sites I have a collection of links to all the Progress sites I have found on my home page, which gets updated more frequently than this section does. Progress itself keeps some information on http://www.progress.com. Two sites I'm particularly fond of are http://www.peg.com (the monographs section there includes a number of very useful documents from the Progress developers themselves) and http://www.ozemail.com.au/~pfred (which has a number of FAQs on several Progress subjects and good tips on getting the latest KnowledgeBase information). streetSMART has put a site at http://www.every.com/streetSMART that contains quite a bit of Progress and general database information (check out their White Papers section for a big listing). There is also a Progress site perennially under construction at http://www.aido.com/progress. Yahoo (http://www.yahoo.com) has an entry under Computers- Software-Databases-Progress with a number of companies selling products or giving information. Companies offering Progress products and information via the WWW include White Star Software (http://www.wss.com), Prostats (http://www.ewt.com/prostats.html), and Professional Computer Consulting (http://emory.com/~emory/progress/progress.html), and United Systems (http://www.usiatl.com). More comprehensive listings appear on my and the PEG's home pages. 7.2.4 Newsgroups (comp.databases.progress) comp.databases.progress is the main newsgroup devoted to Progress, but it's really a secondary source compared with the PEG e-mail list. Depending on your interests, you also might find quite a bit of interesting traffic on the group for your operating system or computer type. There are also some other comp.databases groups devoted to more theoretical, database independent topics. 7.2.5 Progress FTP sites The main FTP archive filled with Progress programs and information is ftp.peg.com. There is a Progress FAQ available at ftp://ftp.netcom.com/pub/rs/rsmith/progress-faq (links to it also appear on some of the WWW pages). 7.2.6 Books The PEG home page has a listing for Progress books in print at http://www.peg.com/wprog/index.html that gives detailed information about the books that are mentioned in this section. If this document is the sort of information you're looking for but you want more, you need to buy a copy of Dan Foreman's Progress Performance Tuning Guide; it's over 300 pages on this topic with periodic updates available as well. You can contract Dan to purchase a copy either via e-mail at danf@prodb.com or by sending a FAX with an order to (770) 449-9003 (there are no longer any other distributors for the book). Current information about pricing and ordering is available on the United Systems home page at http://www.usiatl.com. Dan speaks frequently at Progress gatherings like conventions and user group meetings, and it's definitely worth your time to attend such presentations. A popular series of general and specific interest Progress books are available from White Star Software (e-mail info@wss.com, call (415)857-0686, fax (415)857-0779, http://www.wss.com). TJD is another publisher of a wide line of Progress books, usually highly focused ones on specific topics (call (703)318- 8502, fax (703)318-0875). 7.2.7 Magazines Progressions is a 6 issue per year technical journal aimed at Progress programmers published by White Star (see Books or WWW section for contact info). A one year subscription is $60. 7.2.8 Software A few companies offer software designed to help automate performance management. PROstats is a C/S tool for monitoring performance (http://www.ewt.com/prostats.html). United Systems (fax (770) 449-9003, http://www.usiatl.com) has a Promon automation utility called ProMonitor. John D. Johnson offers a utility to help automate data management like dump/reload processing. (call (972) 733-1422, e-mail jdj@jdjohnson.com). He sells a SmartObject book as well.