DBA management experience

Recently, a large number of new database management software packages have moved into the market. Oracle's just-released Database 11 GB promises to provide DBAs with new features beyond the DBA's understanding scope. Microsoft's SQL Server 2008 will be released in February, becoming the successor of SQL Server 2005; informix launched a new Cheetah version, which IBM hopes to regain its market share. FileMaker's 9th version of software with the same name, the vendor said the product was ready for Web 2.0 and maintained good usability.

New product features may be quite attractive, but experienced DBAs must understand that new versions usually have more features, but this also means any performance improvement, it may come from expensive hardware upgrades that accompany database upgrades, rather than database upgrades. At the same time, performance improvement may be limited if basic design defects or operational problems still exist.

"Using hardware to solve problems is always wrong," said Amy Stuemky, DBA of Globus, a travel operator ." So, how can we solve common database application problems without spending a lot of money on consultants or new hardware? Some DBAs said that it is wise and a lot of sweat to solve such problems, as well as third-party tools carefully selected.

Fix bad SQL code

For the database and System Administrator DeWayne Treadway of Lone Star Steel, an iron and Steel manufacturing company, the database problem is manifested in the company's two Oracle databases, which are "slow as a whole", but cannot determine the cause.

When Treadway came to the company two years ago, the company's database ran Oracle8i on the ibm aix server, one of which was a 16-way System p570 and the other was an older RS/6000 M80. Treadway felt the problem was related to the SQL code, which was written by the contractor five years ago when the database was migrated from the mainframe to Unix servers.

Lone Star Steel is common. Josh Berkus, one of the developers of the open-source PostgreSQL database, said: "database applications are not always loose. The average life of a 'temporary disposable 'application is up to four years. Currently, code written in 1960s is still running ." Therefore, identifying the problem code is not easy.

After analyzing multiple database performance optimization packages (including Spotlight on Oracle for Oracle Enterprise Manager and Quest Software), Treadway selects Ignite for Oracle from Confio Software. Using the "wait time analysis" feature of this tool, Treadway can quickly determine the Three-segment SQL code that causes half of the database latency. For example, Treadway can rewrite a piece of code identified by Ignite, so that a previously running process that took 4 minutes only needs to run for 10 seconds.

Since using Ignite a year ago, Treadway has improved database performance by 30%. He said he could make further adjustments to improve system performance by 50%, but he did not, because the company has already planned to migrate from Oracle 8i to Oracle 10g R2. However, Treadway estimates that by using Ignite, it can still better test its running status when deploying a new Oracle database.

Correctly construct database support websites

In recent years, supporting dynamic content or e-commerce websites has become one of the most popular database applications. However, the former CIO Neil Day of Walmart.com said that most websites are not built in an efficient way. Day pointed out that in a typical three-tier Web application architecture, the database will carry a lot of load. To prevent database bottlenecks, DBAs must use the cache technology and the smart balance technology of transmission streams to isolate database and application requests.

To this end, Walmart.com has created nodes, each of which consists of four lightweight commercial PC servers. "We didn't use a Cisco, Sun, or EMC device. We used all cheap x86 machines and other cheap hardware," said Day ." Because the entire application stack includes a large Walmart.com database, each node can cache all transmission stream requests that a user may send. This greatly reduces the bottleneck and the resulting latency, and ensures that user requests are always kept locally.

Day said Google's strategy of copying the search index database to tens of thousands of PC servers for speed and redundancy ("sharding" method ), it is a version similar to their method and not very complex. However, "slice" is not suitable for media content websites and e-commerce websites like Walmart.com. Because Walmart.com has a huge product catalog and millions of graphic elements and images, it has a database that is much larger than Google databases.

Day does not want to reveal more details. However, he said, "using the current strategy, even though we are running on $2 million worth of hardware, Walmart.com can still become one of the world's largest e-commerce websites ."

If you are interested in the Walmart.com method but do not want to reconstruct the infrastructure DBA, we recommend that you consider using the open-source Java cluster software developed by Terracotta developed by Ari Zilka, former chief designer of Walmart.com. "Terracota provides real advanced and intelligent caching for data used in applications," he said. It finds out how to make the cache run seamlessly in the code at the application layer so that the cache can work transparently ."

When the upgrade fails

For Amy Stuemky, Globus's senior DBA, database bottlenecks appear on the company's mission-critical website. Stuemky said: "websites are often paralyzed and no one knows why. We don't know whether it is a database or a Web server or an application ."

Globus has 50 Oracle databases from version 8 to version 10 Gb, and 30 Microsoft SQL Server 2000 databases. Stuemky is the only DBA. This makes it difficult for Stuemky to spend more time searching for problems on the website. Travel agents and more consumers are booking travel itineraries through this website.

Stuemky even added two additional CPUs to the SQL Server database.

Globus finally chose Confio Ignite. Stuemky indicates that Ignite performs better than SQL Server's built-in performance monitoring tool. After the Ignite is installed on the client, the database diagnosis result is returned within one day: there is a "huge locking problem" in SQL Server ". With this data, Stuemky can obtain a Hotfix patch from Microsoft support staff.

Modify business processes rather than databases

Simple adjustments to the company's business processes often bring greater returns than technical fixes. According to Craig Shallahamer, a former Oracle-optimized independent database consultant at Orapub, some operations, such as adjusting SQL code, often only move the bottleneck from one place to another. In contrast, reducing database workloads or rebalancing workloads can achieve greater results.

So, why do few people adopt this method? Shallahamer said that this problem is a typical DBA character problem: introverted and preferred to solve the problem. Shallahamer recalled the experience of working with a DBA. The DBA-managed ORACLE System was "overwhelmed" at the end of the month ". The DBA mentioned some SQL statements that are running every second. These SQL statements constitute part of the transfer application. "I asked him if he could reduce the frequency of running these SQL statements to one minute," he said. He said he had to negotiate with the user first. The user said, 'Oh, no problem. 'Therefore, it actually depends on how you raise the question ."

He said that DBAs also "often feel that they do not have enough capabilities to drive process transformation ." Shallahamer said that, as demonstrated in the previous example, this is also a misunderstanding. DBAs can obtain all the information they need to create amazing cases (such as distribution of workload ).

Related Articles]

• What does DBA write on its work list?

• Four survival rules that a qualified DBA should abide

• How to become a real Oracle DBA