CH10.html

A great deal of detail has been presented in this book about how a large system is scaled and what knowledge is required in order to build and operate such a system. In order to demonstrate some of these concepts, this chapter comprises a case study of a large Oracle/UNIX system that I have recently been involved in building.

First of all, let's pose a question: how large is "large"? The answer is "It depends on when you ask." The system presented here is considered to be pretty large at the time of this writing, but it is likely that in two to three years, this system will not seem very large or very challenging to implement. The reason for this, of course, is that things move on, and advances in hardware and software constantly "raise the bar."

Luckily for the builders of large systems, customers' requirements have an amazing ability to track these advances in system capability almost precisely. This is often a result of compressed timescales for the development and implementation of a new system, rather than an actual increase in processing requirements. The net effect is that there will always be a requirement for very large systems.

In addition to providing good response time, this system must also be highly available. This required further considerations to be made when determining the system's configuration.

The client has a combination of smaller offices and larger centers, situated in various locations around the world. The majority of these are in North America and the United Kingdom, with a lower number at other locations across Europe. The system described in this chapter refers only to the North American region of the business, because at the time of this writing, the UK region is not yet using the new system.

In total, there are more than 3,000 potential users of the system, with up to 2,400 connected at any one time. Of these connected users, most are active at any one time, normal think times excluded. The smallest collection of users in any one physical location is one, ranging up to several hundred in the sales centers.

The previous system used by our client was based on several IBM mainframes and 3270-type terminals, and was deemed to be too inflexible for business needs. The major reasons for this were the age of the application and the complexity of making changes in it. The maintenance costs of multiple mainframes and the impending Year 2000 problems in the application were also factors. Therefore, our client sought a partner for the development of a new application to support its business.

Perot Systems Corporation is a leading information technology services and business solutions company, serving clients in various industries, including financial services, health care, energy, and travel and transportation.

The client needed a fully customized application to support their business function. The application was not developed completely from scratch, however, and a little history is required to understand its current incarnation. For clarity, I will refer to the new application as "Silverstone" and to the previous application as "Monaco."

The application began as a project for a previous client of Perot Systems. This previous project was very large and was of vital importance to the business of the client. The client's company was formed as the result of many mergers and acquisitions, resulting in as many as 30 disparate systems in nine separate countries all loosely connected using software bridges. The tracking of data and product in this "system" was nearly impossible, and financial management was incredibly complex and inflexible. The company contracted Perot Systems in 1992 to resolve these problems, resulting in the development of Monaco.

The goal of the Monaco project was to consolidate all the systems and business processes into a single image of the company. The development and implementation of the system had to be complete in two years, and this was the driving force behind the development tools and project planning.

The development process involved the production of a single application, consisting of 5,300 modules, from scratch. The total development time was more than 300 person-years, with 330 developers working during the peak.

Because of the tight schedule, the analysis and development were attempted using CASE Designer and CASE Generator to automate much of the programming effort in the generation phase. This approach not only saved in programming effort, it drastically improved the consistency of the code that was produced. If something was wrong, it was wrong everywhere, and if it was correct, it was correct everywhere. This led to an online application that existed purely as an SQL*Forms 3.0 application, with handwritten batch programs to complement it. The entire application was centralized and two-tier in nature, with the Forms binaries running on fat UNIX "clients" and the database running on separate hardware.

One of the overriding design goals of the Monaco application was to make it compatible with Oracle Parallel Server. To be precise, it was designed to be a two-node OPS application, with sales and back office functions executed on one node, and the point-of-sale agent functions executed on the other. This was a conscious decision from day one, because we knew that even the largest available Oracle database server platform was not large enough as a single machine.

As the complete application approached reality and the sales agents were using their part of the application in production, it was clear that even two nodes of the chosen platform were not going to suffice. In fact, because this was prior to any application tuning effort, the best estimates were showing a requirement for ten nodes. Even if the application tuning effort halved the resource requirement of the application, it would still require more than twice the estimated hardware.

The hardware sizing estimates had been made on the basis of an extensive benchmark of the platform against a mock-up of the actual application, in parallel with the development of the real application. While a great deal of effort was expended in making this as accurate as possible, this example shows the difficulty of attempting to size a system accurately before the application is written.

The hardware requirements of the application meant that a new hardware platform was required in order to proceed with the rollout. At this stage, a benchmark suite was constructed using the application, in order to simulate the workload as it actually appeared. The new platform was proven and was implemented during a six-hour maintenance window.

The application was running on a two-node cluster at this stage, using Oracle7 release 7.0.15. As the rollout progressed, other problems became apparent. These problems were not gradual, but sudden, brick-wall problems, and there was rarely any warning. Many of them were bugs in the parallel server code and the vendor lock manager, whereas others were fundamental scaling difficulties in the base Oracle product at that time.

Most notable among these difficulties was the poor scalability of the library cache in this release. The certification process for the replacement platform had omitted a crucial workload attribute in its testing-parse calls. Because the simulation was implicitly "too efficient" by virtue of the fact that it did not close cursors but simply reexecuted the same cursors, it did not stress this portion of the Oracle product adequately.

This omission was, of course, understandable, because it is not within the scope of customer benchmarks to stress test the entire vendor product set. Or is it? Lessons learned from this exercise led to the desire to test the system in a configuration that mirrored real life. Doing this was the only safeguard against unexpected problems.

The net effect of this testing was a great deal more benchmarking, with a subsequent upgrade of the hardware to a three-node cluster. This marked a departure from the application design, but it was a necessary move in order to provide more bandwidth for the processing of the workload. This kind of change, however, requires extensive application rewrites. These changes were made in conjunction with extensive database reconfiguration in order to reduce the ping rate between the database servers.

The database was still running on release 7.0.15, but now it was known as 7.0.15.Perot owing to the existence of a special branch of the Oracle source tree to support this system. This branch had to be maintained by Oracle until all the patches on the system had been rolled up into a production release. It was only when release 7.3 went into production that all 49 of the major patches, and countless other "minor" patches, were present in a commercial release. This was the first upgrade of the Oracle codeset for this application.

During all this hardware testing and implementation, parallel efforts were proceeding to produce an application with a reduced footprint, and one that cooperated with Oracle7 a little more. Notable in this effort was the ability of Forms 3.0 to generate dynamic SQL by default-filling up the shared pool-and its overzealous closing of cursors that would prove useful later. Extensive application rework was done to work around these problems, resulting in most of the application being at least "retouched by hand" after the generation process. All the major forms were rewritten from scratch, using a significant amount of user exit code to solve the more difficult problems. In addition, the introduction of the third parallel server node meant that a good deal of the code had to be revisited once again to make the appropriate access changes to reduce the ping rate on the database servers.

The Monaco project was a technical triumph, but not without a great deal of pain and hard lessons. Hopefully this book will allow you to skip some of those lessons. As a final word on Monaco, it is worth mentioning that the system recently underwent a further upgrade. Hardware has become more powerful since the system was fully implemented, and now the Monaco system can be housed on a single node, along with a single failover node for availability. This upgrade was performed, but not without significant testing first-a benchmark was run for several months prior to the rollout of the new platform. The result of the upgrade is that Monaco now easily fits onto a single database server, confirming that idea of the constant redefinition of "large."

The new client viewed the Monaco application as an ideal starting point for the functionality and flexibility they required. The two clients were in the same business (in separate markets), so some of the core functionality was already in place. However, the business model followed by the two companies was quite different, and several changes needed to be made to Monaco before it was suitable for the new client. The changes were extensive in some functional areas, although the fundamental design of the application remained sound and provided a solid base to build on.

In addition to the business requirements, Silverstone had technical requirements that were necessary in order to deliver enhanced functionality at higher transaction rates. As a rough estimate, the new system would require approximately four times the throughput of the previous system.

The first of these changes was to start the migration from two-tier to three-tier. This was necessary in order to control the volatile "spikes" in activity observed during the operation of Monaco; these spikes were not controllable in a two-tier configuration. All new major functional areas were written as Tuxedo services, called by Forms using user exits. All major commit blocks were pulled out of the forms and implemented as Tuxedo services. However, all users still connect directly to the database and perform the majority of their queries directly, and the transaction management all occurs in the Oracle server.

The total effort of migrating to Silverstone was huge and far-reaching. For example, Monaco had just over one million lines of Pro*C code in addition to the Forms code. Silverstone also has one million lines of code, but only 276,077 of these are original code, and more than half of these are comments. For the Forms code, line counts are less meaningful owing to the fact that many lines of the text source are simply defaults from Forms. However, the old application had 438 forms, compared with 567 in Silverstone. Of these forms, 30% of the code is brand new-some 3,500,000 lines-performed by a large development team that peaked at 150 people during the main coding effort.

Supporting Silverstone requires a special technical implementation. Bearing in mind that the workload is so much greater than that of the Monaco system, there is clearly some room for things to go wrong if precautions are not taken. Those precautions were taken for the implementation of the new application, including careful sizing, platform selection, availability design, and a well-planned Oracle implementation.

Given the size of the processing requirement, the planning and implementation of the hardware solution had to be flawless. The lessons from Monaco were not to be revisited, and so everything associated with the new implementation was approached with a healthy dose of paranoia. The hardware was a prime example of where a formal process was followed, consisting of

The initial design of the Silverstone hardware solution took the best parts of the implementation for the previous client and created deliberate solutions for the undesirable parts. High priorities included

These elements were all incorporated into the initial design, leaving the finer details of availability until a later date. Initial sizing was performed prior to this stage in order to do a sanity check on the proposed architecture.

Detailed sizing followed the initial design, where only a granular sizing effort was undertaken. During this stage, very detailed sizing information was derived from all the available data points, including

The paper evaluation took the data from the sizing exercise and applied it against a short list of ten potential hardware platforms. For completeness, this list did not include only UNIX servers. Each platform was then given a rating against each of several categories, including capacity and other attributes such as reliability and the support capability of the vendor. The end result of this evaluation was the selection of two platforms for a competitive benchmark. A benchmark was then developed that would represent the future operation of the application.

In order to best represent the image of the client's business, the benchmark required an accurate portrayal of the data in the database, in addition to a simulation of the application. In fact, the buildout of the database took a great deal more effort than the actual simulation of the user sessions.

The simulation of the user sessions was implemented using the Performix/TTY product, heavily customized to provide the level of control desired for the benchmark. Once the customization effort had been completed, the definition of the user simulation was fairly straightforward.

The final piece of preparation for the benchmark was the porting exercise. In addition to porting the application to the target systems, the database also had to be built on each native platform. In order to ensure that all was equal in the database, it was built using the same file layout, the same block size, and the same scripts. The net result was an identical database on each platform, apart from the operating system dependent differences in the files.

During the building of the benchmark suite, a decision had to be made as to which version of Oracle should be used. The testing was to begin in earnest at the beginning of December 1997, just five months after the launch of the initial version of release 8.0. In order to protect the progress of the benchmark from unknown bugs in a very new release, version 7.3 of Oracle was adopted for both platforms.

The benchmark was executed against both successful platforms from the paper evaluation, over a period of 12 weeks. Each vendor was allowed six weeks to complete the series of tests, strictly controlled and subject to a previously documented tuning fairness policy. This policy sets out exactly which changes must or must not be carried out at both sites. This policy ensured that an apples-to-apples comparison could be made without any unfair advantages.

The unsuccessful platform was the first to be tested.¹ The testing went very well, particularly from a reliability standpoint. Several application scaling problems arose during this phase, as the user count approached 3,000. These problems were solved, and the tests were reexecuted.

Once the problems had been resolved, certain limitations of the hardware platform became apparent at high load. Although it is possible that the limit lay just beyond that anticipated workload, this was a definite risk associated with choosing this platform. The problem was in the memory latency of the system and its subsequent ability to maintain cache coherency under heavy latch duress. The limit was near the 200,000 gets/s rate on the cache buffers chains latches, where the system simply could not process any faster. In fact, the processors were not taxed at full loading, and some were taken out to prove this observation. If anything, the latch processing capability improved at this stage. From reading Chapter 2, it is likely that you will be able to determine which hardware architecture this system is built from, given that it has memory latency challenges.

The second system to be tested was very different from the beginning. In fact, it was so different that a full transactional audit was carried out, comparing every operation on both systems, in order to sanity check the performance difference. This audit did in fact show a difference, but not the expected one: The second system had an index built on a ten-block table. Even towing this boat anchor (it doubled the cache buffers chains latch acquisition rate to 500,000 gets/s), the second system processed the workload with a much more even response time and greater transactional throughput. The second system was clearly a good deal faster than the first. On exit from satisfactory fault insertion testing, and with the right commercial deal secured, this vendor was successful in securing the business.

The successful platform was the Hewlett-Packard V2250, a point-to-point SMP system. The system demonstrated itself to be well balanced from a memory latency perspective and capable of very rapid cache coherency operations. Despite having 16 processors, often all trying to spin on a single latch, the system did not exhibit negative scaling tendencies under high load. This made it eminently suitable for the latch-intensive workloads we were presenting to it. Large OLTP systems often exhibit this kind of latch-intensive workload profile as a result of the concurrency requirements of many online users.

From a procedural perspective, the entire process was documented from start to finish. Prior to the benchmark, an approach document was developed and agreed on by all parties. This was a 91-page description of everything associated with the benchmark, including rules, application descriptions, hardware architectures, project planning, operational manuals, and so on. On termination of the benchmark, an additional 61-page document was produced, including all pertinent results and observations from the testing. This was made possible by keeping a daily diary of all events at both sites, in order to recall all events correctly. In addition, the second document included a weighting table that could be applied to all the results (see Table 10.1).

For this particular exercise, this weighting table was appropriate. The only strange thing (for some) was that price was considered the least important weighting factor; in this case, where choosing the wrong platform could result in crippling downtime for the business, price appeared some way down the list.

After the selection process had been completed, the final design phase was started. This stage determined the actual configuration of the production systems, including all availability options and ancillary systems. In the case of the Silverstone systems, this stage dramatically increased the inventory from the clustered database server and three application servers. The final production inventory includes some 15 servers and more than 3TB of physical disk. A good deal of the additional disk (the actual size of the database is 450GB) is used to implement several levels of fault tolerance:

In fact, the only situation in which the production system would need tape backups to be restored would be a full disaster that took out all the production hardware. The logical view of the storage architecture is shown in Figure 10.1.

Each of the disk objects represents a 450GB database "copy," thus totaling 2.7TB of physical disk. Only one of these-the mirror copy-is exactly current with the production database. The rest of the disk regions are at various levels of physical and logical distinction from the online database. First of these are the two BCV (EMC Timefinder snapshot technology, see Chapter 2) copies of the database. These snapshots are taken twice every 24 hours in order to implement the reporting requirement, the backup requirement, and the recovery requirement.

In the early hours of the morning, a snapshot of the database is taken. The procedure is to put the tablespaces into backup mode, copy the physical disk to one set of the BCV volumes, and then take the database out of backup mode. The copy on the BCV volumes now just look like a restored hot backup. These volumes are mounted on the secondary machine of the cluster and rolled forward using the archive logs from the production database. The database is then opened and several reconfiguration steps are taken, including:

This database is now more focused on heavy query work, although the block size cannot be enlarged during this process. When the reconfiguration is complete, the reporting database that exists on the other set of BCV volumes is shut down, and the new one is started up in its place. Therefore, the reporting facility is refreshed every 24 hours, with only a handful of minutes of downtime on each refresh.

The former reporting volumes are then used as the targets for another BCV operation from production. These volumes are copied off to tape during the day from a small, dedicated system. The disk volumes remain for as long as possible during the business day in order to allow rapid recovery from a complete failure of the primary production volumes.

In addition to the backup and reporting volumes, another set of mirrored volumes are used for further protection against lengthy failures. This set is refreshed from the production volumes every few weeks and is kept current with the production database through the use of the Oracle hot standby database functionality. As archived redo logs are produced in production, they are automatically sent over to the standby system for application to the standby database.

In the case of a failure of many disks in production-even an entire disk cabinet-the hot standby system can be brought up in a matter of minutes. This would compare with many hours if a tape restore were required. The protection provided by each portion of the storage architecture can be matched up against the claims made at the beginning of this section, as shown in Table 10.2.

In fact, the design of the storage system remains the only point of potential problems in the event of failure, and this exists in any database system. If the redo logs become corrupted, the contents of the logs will be propagated to all regions of disk, thus spreading the corruption. This does not present a great deal of risk, because

On delivery of the production system, the implementation phase could begin. In many respects, this phase was a good deal like an extended benchmark: The systems were installed and then extensively tested during the configuration phase. This testing was a deliberate and planned event, classified as "extended benchmarking," and is highly recommended for systems that require high availability. Many lessons were learned every day of this process, which provided a great deal of additional confidence as the go-live date drew near; through these lessons, a well-trained group of technicians emerged.

Many problems were also encountered during this time. The difficulty of installing a new, complex system should never be underestimated. Even the configuration tasks that should have been simple were difficult because of the scale of the operation.

During the configuration of the system, time was spent laying down the infrastructure to control change on the system. This involved the installation of the Concurrent Versions System² (CVS), a public-domain source code management system. Source control systems such as CVS are not limited to program source management and can be used for version control of all the configuration files on a system.

Owing to the client/server nature of CVS, the change repository for these files was stored on a development machine on the other side of the country. This also allowed a kind of disaster recovery capability in that all the changed files were available at short notice from a remote site.

The Oracle implementation was tightly coupled with the hardware implementation and was not considered a separate task. A good example of this is the implementation of the backup and reporting function, as detailed above. These processes were so tightly integrated into the hardware configuration that skills in both areas were essential in implementing these parts of the system.

The implementation consisted of taking the lessons learned during the benchmarking process and merging them with operational requirements and current sizing demands. The result was an initial plan for the physical configuration and tuning of the production database instance.

The immediate problem with this system was the sheer size of the database. At 450GB, the OLTP database would have been considered quite a large data warehouse just a few years ago, and managing such a large database in a 24\7 environment presents several challenges. All these challenges revolve around the time taken to scan the data in order to

One table in particular was estimated to reach a size of 80GB after one year in production, so this was clearly a target for partitioning. The table was divided into ten partitions, making the average size of each partition 8GB. Although still large, this made the management of this table much more straightforward.

The table partitioning keys varied, depending on the reason for partitioning. In general, it was not possible for partitioning to be based on date range for this application. It was more practical to partition by other means, such as the station ID for a given user. While this limited the ability to roll old partitions out of the database as a purge mechanism, it allowed maintenance to be carried out on a subset of the data at one time.

As detailed earlier, index partitioning can also be used to alleviate contention for a given block in the buffer cache. This was found to be the case for one of the most heaviliy used tables in the database, and so a partitioned global index was created over the nonpartitioned table in order to spread out the access pattern. This is described later in this chapter, in the description of rates engines.

The tuning exercise retained many of the philosophies of the benchmark tuning exercise. The benchmark was the ideal system for tuning, and much of the derived wisdom was applied directly to the production tuning, specifically

· Large number of hash buckets for the buffer cache in order to minimize hot chains in the cache. Approximately one chain for every buffer in the cache means that it is less likely to have more than one hot block on a single hash chain.

· db_block_max_dirty_target kept at 5% of available buffers. Combined with sufficient database writer processes and deep checkpoint batches, the checkpoints are kept short and sweet.

· Very low log_small_entry_max_size (8) so that copying is always done under redo copy latches rather than under the redo allocation latch. Number of redo copy latches (log_simultaneous_copies) set to one per CPU.

Overall, the tuning is kept as simple as possible. Once in production, a database is very difficult to tune. Tuning presents risks to the operation of the production database, potentially causing unscheduled downtime because of emergency reversal of changes. The database is tuned only when there is a specific problem, which is a very rare event after the initial implementation.

The exclusion of the listener.ora and tnsnames.ora files from the CVS repository may appear strange at first. In fact, they are not required to be version controlled, because they are never edited by a person. Instead, these files are generated by a script (that is checked into CVS), which creates the desired listener.ora and tnsnames.ora configuration files on demand. This eliminates the tedious configuration of these files, which can quickly become a lengthy and error-prone process when many networks are used to carry client/server traffic.

Prior to actually connecting any real users to the system, the application was driven against the production system using a modified version of the benchmark simulator. This immediately uncovered a large number of unintentional full table scans and other problems. With these problems rectified, the user connections could be started.

The user rollout was deliberately staged over several weeks. In this way, bad code and scalability challenges could be determined before they became a problem and could be fixed before the next set of users came online. It is estimated that the footprint of the application was reduced by 50% during this time, and it is certain that a "big bang" approach would have resulted in a system driven way beyond its capacity.

Any large implementation brings with it a variety of challenges. However careful the preparation, a certain amount of tension remains during the first stages of the rollout. Prime among these concerns are

In the case of Silverstone, we came out pretty clean. This does not mean that everything was perfect, but fortunately all the technical problems could be classified as teething problems and understandable parts of a new implementation.

The vast majority of the teething issues can be categorized as "application tuning." When so much code changes during heavy production use, it is inevitable that some of it will not perform as well as it should. In fact, a tuning team was initiated even before the application was put into production, and this was simply stepped up in priority when the application became live.

One part of the system that was to benefit from the tuning exercise was the Tuxedo service known as the "rates engine," which returned pricing information for specific product combinations. This service is called on four times for every sales inquiry and once when the product is actually sold. Therefore, this service is called a great deal during the operation of the system.

The initial code for the rates engine used dynamic SQL to generate specific queries for each request. The effect of this was to deplete the 600MB of memory in the shared pool in 15 minutes flat, along with the enormous amounts of hard parsing that were necessary for all these requests.

The dynamic SQL was the first thing to change in this service, adopting a fixed string of bind variables that were filled in at runtime. This helped the global system a great deal, with the shared pool never filling up during the production day. The hard parsing was well down, and the dictionary cache was allowed to grow about ten times larger than it previously had been. Therefore, the whole system ran a great deal more efficiently than before.

Unfortunately, the performance of the rates engine itself was still not perfect, and it was still using about 40% of the database server CPU. In order to rectify this, the following steps were taken:

The net result of this effort was to bring down the server-side CPU utilization to around 10 to 15 percent, and to halve the response time of the engine itself. In addition, the response times of the queries were now more predictable because of the removal of several contention points.

In addition to the application tuning process, the management of Oracle itself presented a variety of challenges. Foremost among them were the cost based optimizer (CBO) and the difficulties of maintaining stable response times when data volumes and ratios were exploding every day.

When a system first goes live, it is likely that it will be fully populated in reference data but will have very little transactional history. Therefore, as this transactional data grows, the following implications affect the CBO and how it approaches query plans:

As a result, each day will herald new surprises in terms of queries "going bad," even if you stay on top of analyzing all the objects in the database. In fact, a few weeks into the rollout, it was deemed less risky to terminate the regular analysis procedure, and simply analyze when things were known to change. This downside of the CBO is one that will go away starting with release 8.1, in which optimizer plans can be frozen once they are satisfactory. Regardless of this, it is still very good practice for the developer to code all SQL statements with hints, because it is likely that the developer has a good understanding of how the data will look in production.

From a reliability standpoint, Oracle has been solid. Compared with the experiences of 7.0.15.Perot, the implementation and operation of Silverstone have been like a breath of fresh air. Minor problems have been encountered, but none of them could be considered serious and none merited an upgrade. Although this could be attributed to Oracle8 being less of a quantum leap than Oracle7, it is more likely that Oracle learned some hard lessons from the Oracle7 days. Oracle7 could be considered Oracle's transition into the very large-scale database market, and adjustments in process were sure to be required for this kind of step forward.

The implementation of Silverstone has been a huge success. Well-placed paranoia up front ensured that the majority of risk was removed from the project, and good planning ensured that delivery was complete and on time. The side effect of having a complete delivery is that time and attention could be applied to observing the production rollout, thus allowing any problems to be rectified very quickly.

The road from sales proposal to implemented product was long and hard, and was made possible by the very best people. At the end of the day, nothing this large can succeed without the skills of many good people. Rather like this book, the skillsets required to complete such a project range from the workings of the hardware right up to higher-level knowledge of software products and business processes. For that kind of spread, a good team is essential.

The identity of the unsuccessful vendor is not disclosed: I do not want any derived conclusions of superiority of one over the other. All conclusions from this testing apply only to the versions of the hardware and of Oracle (7.3) used in the testing. This requirement is likely to be different from any other, and other cases could suit this platform very well.

rdist is a utility included on most UNIX variants. It can be configured to distribute multiple files to many different hosts, with custom locations and treatments on each. Once set up, rdist is a single command that takes care of all software distribution.