Configuring and Tuning Databases on the Solaris Platform

Product Author Bios

ALLAN PACKER is a Senior Staff Engineer with the Performance and Availability Engineering Group at Sun Microsystems. He has developed workloads for performance and regression testing, led the engineering team working with IBM DB2, acted as technical lead for nine published TPC benchmark results, and developed a training curriculum for the internal training organization focused on configuring, monitoring and tuning database systems. He currently specializes in server systems performance and availability.

• Turbocharge your Oracle, DB2, Sybase, or Informix database environment!
• Covers every aspect of database and Solaris Operating Environment tuning
• Optimize data layout, CPU and memory configuration, and system sizing
• Identify and resolve bottlenecks, step-by-step
• Understand industry-leading benchmarks

Maximize the performance of any enterprise database running on the Solaris Operating Environment!

Using this book, database professionals can optimize the performance and cost-effectiveness of virtually any database application running on the Solaris platform. Database specialist Allan Packer presents start-to-finish guidance for optimizing all four leading Solaris platform databases: Oracle (including Oracle9i), DB2, Sybase, and Informix XPS. Drawing on years of expertise as an engineer at Sun, Packer brings together best-practice guidelines for every aspect of Sun database server tuning.

• Optimizing data layout, CPU and memory configuration, and system sizing
• Identifying and resolving bottlenecks: a systematic, drill-down monitoring approach
• Understanding database optimizers, database buffer cache, and other key subsystems
• Demystifying the industry's leading benchmarks-and recognizing their limitations
• Understanding the impact of Java technology on database application design
• Contains a quick primer on database architecture and concepts

Related Articles

Drill-Down Monitoring of Database Servers

Sun StorEdge Instant Image 3.0 and Oracle8i Database Best Practices

Online Sample Chapter

Tuning Databases on Solaris: Role of the Buffer Cache

Preface

PREFACE

Many books have been written on database tuning—I personally own enough volumes on Oracle tuning to fill a small shelf. Very few, though, approach con-figuring, tuning, monitoring, and troubleshooting from the perspective of the system as a whole, treating the database as one of a number of key components. I have set out to approach database configuration and tuning from this broader context.

To my knowledge, this book is the first attempt at tackling database tuning for multiple databases. Since many aspects of database architecture and tuning are common across the major products, I have separated out generic topics such as database concepts, hardware architectures, the buffer cache and the optimizer. I hope that the opportunity to see the big picture and to compare and contrast the different implementations outweighs the inconvenience of having to follow cross-references at times.

This book is also the first published guide to database configuring and tuning for Solaris users (although most of the principles and much of the detail should be applicable to other UNIX systems). I trust that it will enhance your experience of running databases on the Solaris Operating Environment.

My aim has been to identify the highlights. An overwhelming array of statistics are reported by Solaris and the various databases; rather than attempting to define every statistic and tunable parameter, I focus on those likely to have the most impact on common database workloads.

Finally, no one person could claim a full mastery of all the topics covered in this book, and I certainly make no such assertion on my own behalf. I have tried to distill the knowledge I have acquired during my 12 years with Sun—5 years in the field organization as a Systems Engineer specializing in databases, followed by 7 years in Database Engineering (now Performance and Availability Engineering)—and add to it the research and insights of my colleagues.

I was fortunate to join an engineering group that actively pursued engineering relationships with all four database vendors. This involvement has offered me direct participation in performance projects with the engineering groups at Oracle in Redwood Shores, IBM at the DB2 Development Lab in Toronto, and Sybase at Emeryville. My contact with Informix has been peripheral, and in writing the Informix chapters I have relied heavily on the wisdom and experience of my colleagues.

This book should appeal to the following groups of readers:

• Database administrators looking for practical tuning advice and for a broader understanding of the system as a whole.
• System administrators and other technical staff wanting to expand their understanding of the architecture and management of databases.
• Developers hoping to better understand the context in which their applications are used. The suggestions on benchmarking applications during the entire development cycle should be of particular interest.
• Specialists in one database wanting to learn more about other databases. I know many people who have expressed a desire to better understand the other major databases, but found the task too daunting. I hope this book will simplify that process by providing enough material to answer the important questions without being overwhelming.
• System users who simply want to understand the jargon associated with databases and the servers on which they run.

I have tried to go into enough depth to satisfy those looking for detailed configuration and tuning suggestions, while still making the content accessible to people who are not database or Solaris gurus.

This book is organized in five parts. Parts One, Two, and Five are best viewed as reference material. Parts Three and Four lend themselves to hands-on tuning.

Part One: Databases on Sun Servers — Provides background information on Sun's relationship with database vendors and Sun's Database Engineering organization, and discusses the optimizations made to the Solaris Operating Environment to improve database performance and availability. The section concludes with a review of the major hardware architectures (including those not supported by Sun) and their implications for database deployment.

Part Two: Database Architecture — Addresses database concepts, database workloads, and explores in detail the database optimizer and the role and sizing of the database buffer cache. Separate chapters discuss the architecture of Oracle, Sybase, Informix XPS, and DB2 for Solaris; for these chapters I provide a consistent format to make it easier for those wanting to contrast an unfamiliar database with one that is more familiar.

Part Three: Sizing and Configuring Sun DBMS Servers — Focuses on system sizing and configuring the CPUs, memory, and networks of a database server. The section concludes with a detailed discussion of data layout technologies, strategies, and recommendations.

Part Four: Performance Monitoring and Tuning — Deals with the issues and tools associated with performance monitoring and tuning, and offers a drill-down method for identifying and resolving system bottlenecks. Detailed configuration and tuning advice is provided for each of Oracle, Sybase, Informix XPS, and DB2 for Solaris, and the section concludes with a discussion on how to monitor applications and what metrics to collect.

Part Five: Other Topics — The final section of the book covers benchmarks and the role of Java technology in databases. The first chapter examines the pros and cons of industry-standard benchmarks and investigates the process and potential pitfalls involved in developing your own benchmark. The final chapter explores direct support for Java technology in databases and the Java 2 Enterprise Edition (J2EE) technology that is central to the burgeoning middleware market.

An appendix lists sources for supplementary information.

The scripts and utilities referred to in the book are available on the book website, and I will also use this site to post updates to the material in the book. The website can be found at:

http://www.solarisdatabases.com

I welcome your feedback, suggestions, and general comments. If you like the book, please let me know! If you find flaws, errors, or omissions, I would also be glad to hear of them. In reading Configuring and Tuning Databases for the Solaris Platform, I hope you receive even a small proportion of the benefit I have gained from writing it.

—Allan Packer
Allan.Packer@Sun.COM

Table of Contents

I. DATABASES ON SUN SERVERS.

II. Database Architecture.

III. SIZING AND CONFIGURING SUN DBMS SERVERS.

IV. PERFORMANCE MONITORING AND TUNING.

V. OTHER TOPICS.