Trimming Down the Database

Monday Oct 11th 2004 by Drew Robb
Share:

Applimation helps Southwest Gas archive its Oracle database, slashing query times and lifting a huge burden from production servers.

Attend any storage conference and speaker after speaker mentions the storage explosion. Some studies place the figure at 50 percent, while others grow at a rate of 125 percent a year. Yet according to Meta Group (Stamford, CT), up to 80 percent of this data remains inactive in production systems where it cripples performance.

"To compound this problem, many enterprises are in the midst of compliance initiatives that require the retention of more data for longer periods of time, as well as consolidation projects that result in significant data growth," said Charlie Garry, senior program director at Meta Group.

In terms of compliance alone, Sarbanes-Oxley, SEC 17a, HIPAA and a host of other rules have transformed information management into a minefield of potential liability.

Clearly, the legal ramifications of being without a way to archive information from databases can be grim. But there are production reasons, too: slow transactions, system outages; backup failures; and queries timing out as they have to search through millions of records.

stock 
photography
In terms of compliance alone, Sarbanes-Oxley, SEC 17a, HIPAA and a host of other rules have transformed information management into a minefield of potential liability.
At Southwest Gas Corp. in Las Vegas, for example, the Sun Solaris-based Oracle 8i database was growing at a rate of 1 GB a month. Within a couple of years, the database had mushroomed from 30 GB to over 100 GB. Inventory tables had expanded to the point where they contained five million rows, and an HR table comprised 60 million rows.

"The more data you have in production, the slower the database grows," said Luca Cotrone, systems analyst at Southwest Gas. "Users complain of queries taking a long time and you can run out of disk space."

The company has a total of 7 databases instances running: one for production, and others for training, QA, testing, development, special projects and a sandbox for patches, etc. All data from the production database is copied to the others. One Sun server houses the production and training database, while another Sun server hosts the other five instances. That server was particularly affected by the database bloat due to running out of disk space, and having less memory and slower CPU's than in the production system.

To solve these issues, Southwest Gas implemented Applimation Archiver by Applimation Inc. (New York, NY). The Oracle 8i database has stabilized at around the 100 GB mark. Archiving of one General Ledger table, for example, saved 18 GB. Searches are down from several minutes to a few seconds.

Continued on Page 2.

Continued from Page 1.

While the system can be automated, the company prefers to rely on manual archiving for the moment. Every month, a DBA spends 30 minutes selecting files to archive. The decision is based on locating files in the inventory application database, for example, that are older than 30 months. These are moved from the production system to a less expensive Applimation datastore.

HR data, on the other hand, is only archived once per year. Reason: HR depends on a complete year of information. Any reports they run require a whole year, so HR data is retained a little longer and then a year's worth is retired at once.

"Decide how much data you need to keep in production for each application and business unit," said Cotrone. "Also, some areas such as inventory can have archiving done monthly, whereas HR/Payroll may need to have annual archiving as they typically need to access data for the entire year."

As the Applimation system is set up with the same look as the production system, the administrator just selects the rows that need to be moved, does so, and then deletes them from the production system. Cotrone recommends completing a backup of the archive BEFORE deleting anything from the production database, however.

No Performance Hit

According to Cotrone, archived files can be accessed by the user transparently from the original application.

"If you stick to querying archived data alone, there is no noticeable difference in responsiveness," he said. "But if you run a search across the production system and the archive, it will be a little slower."

Cotrone ran into trouble due to differences between Oracle 8i and 9i. His system runs on 8i while the archive database runs Oracle 9i in a Linux server instance within an IBM mainframe. Each successive evolution of Oracle and its associated applications appears to add more complexity that can potentially damage a project. The Oracle 11i E-Business Suite, for example, adds 200 new modules and 17,500 tables to the application infrastructure. The same holds true for other database vendors,

"We couldn't export files from our 8i production database into the 9i archive, as there are certain tables you can't send across," said Cotrone. "Fortunately, our inventory application doesn't have these tables so we were able to archive it while we complete a migration of everything else to 9i."

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved