eBook - ePub
PostgreSQL 9.0 High Performance
Gregory Smith
This is a test
Share book
- 468 pages
- English
- ePUB (mobile friendly)
- Available on iOS & Android
eBook - ePub
PostgreSQL 9.0 High Performance
Gregory Smith
Book details
Book preview
Table of contents
Citations
About This Book
In Detail
PostgreSQL database servers have a common set of problems they encounter as their usage gets heavier and requirements more demanding. You could spend years discovering solutions to them all, step by step as you encounter them. Or you can just look in here.
All successful database applications are destined to eventually run into issues scaling up their performance. Peek into the future of your PostgreSQL database's problems today. Know the warning signs to look for, and how to avoid the most common issues before they even happen.
Surprisingly, most PostgreSQL database applications evolve in the same way: Choose the right hardware. Tune the operating system and server memory use. Optimize queries against the database, with the right indexes. Monitor every layer, from hardware to queries, using some tools that are inside PostgreSQL and others that are external.
Using monitoring insight, continuously rework the design and configuration. On reaching the limits of a single server, break things up; connection pooling, caching, partitioning, and replication can all help handle increasing database workloads.
The path to a high performance database system isn't always easy. But it doesn't have to be mysterious with the right guide.
A clear, step-by-step guide to optimizing and scaling up PostgreSQL database servers
Approach
Improving database performance requires an equal mix of understanding theoretical concepts and working through hands-on examples. You'll find both here. Many of the examples given will be immediately useful for monitoring and improving your PostgreSQL deployments, providing insight into hard-to-obtain information about your database.
Who this book is for
This book is aimed at intermediate to advanced database administrators using or planning to use PostgreSQL. Portions will also interest systems administrators looking to build or monitor a PostgreSQL installation, as well as developers interested in advanced database internals that impact application design.
Frequently asked questions
How do I cancel my subscription?
Can/how do I download books?
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
What is the difference between the pricing plans?
Both plans give you full access to the library and all of Perlegoās features. The only differences are the price and subscription period: With the annual plan youāll save around 30% compared to 12 months on the monthly plan.
What is Perlego?
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, weāve got you covered! Learn more here.
Do you support text-to-speech?
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Is PostgreSQL 9.0 High Performance an online PDF/ePUB?
Yes, you can access PostgreSQL 9.0 High Performance by Gregory Smith in PDF and/or ePUB format, as well as other popular books in Computer Science & Desktop Applications. We have over one million books available in our catalogue for you to explore.
Information
PostgreSQL 9.0 High Performance
PostgreSQL 9.0 High Performance
Copyright Ā© 2010 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: October 2010
Production Reference: 1141010
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-849510-30-1
www.packtpub.com
Cover Image by Asher Wishkerman (
<[email protected]>)Credits
Author
Gregory Smith
Reviewers
Kevin Grittner
Jim Mlodgenski
Scott Marlowe
Acquisition Editor
Sarah Cullington
Development Editors
Hyacintha D'Souza
Mayuri Kokate
Technical Editors
Sakina Kaydawala
Alfred John
Indexer
Hemangini Bari
Tejal Daruwale
Editorial Team Leader
Mithun Sehgal
Project Team Leader
Lata Basantani
Project Coordinator
Srimoyee Ghoshal
Proofreader
Aaron Nash
Production Coordinator
Aparna Bhagat
Cover Work
Aparna Bhagat
About the Author
Gregory Smith is a Principal Consultant for international database professional services firm 2ndQuadrant, and founder of the company's first United States office.
Writing about PostgreSQL represents his second foray into teaching database performance tuning. Greg wrote a small, free e-book titled Progress Performance FAQ in 1995, covering the basics of how to make the Progress 4GL and its associated database run faster. In 2001, he converted exclusively to using PostgreSQL 7.0 for projects, and has been watching the complexity of problems the database is capable of solving increase with every release since.
Greg has contributed feature additions to every PostgreSQL version since 8.3. He's also the creator of a growing set of add-on tools for the database, currently including pgtune, pgbench-tools, peg, and 2warm.
About the Reviewers
Kevin Grittner has worked in the computer industry since 1972. While he has filled many roles during decades of consulting, working with databases has been a major focusāparticularly in terms of optimization and providing frameworks for efficient application development against a database. In the mid 80s, he was the architect and primary author of the PROBER Database and Development Environment, which was never released commercially but enjoyed widespread use in certain vertical markets, such as fire departments, hospitals, and probation and parole agencies.
Jim Mlodgenski is Chief Architect at EnterpriseDB. He is one of EnterpriseDB's first employees, having joined the company in May, 2005. Over several years, Jim has been responsible for key activities such as sales engineering, professional services, strategic technology solutions delivery, and customer education.
Prior to joining EnterpriseDB, Jim was a partner and architect at Fusion Technologies, a technology services company. For nearly a decade, Jim developed early designs and concepts for Fusion's consulting projects and specialized in Oracle application development, web development, and open source information architectures.
Scott Marlowe has over 25 years of experience in software development, system administration, and database development. His first program was a gradebook program for the Air Force and he's been hooked ever since.Scott works for Edline/Schoolfusion as a systems administrator and DBA.
Preface
PostgreSQL has become an increasingly viable database platform to serve as storage for applications, from classic corporate database use to the latest web apps. But getting the best performance from it has not been an easy subject to learn. You need just the right combination of rules of thumb to get started, solid monitoring, and maintenance to keep your system running well, suggestions for troubleshooting, and hints for add-on tools to add the features the core database doesn't try to handle on its own.
What this book covers
Chapter 1, PostgreSQL Versions introduces how PostgreSQL performance has improved in the most recent versions of the databases. It makes a case for using the most recent version feasible, in contrast to the common presumption that newer versions of any software are buggier and slower than their predecessors.
Chapter 2, Database Hardware discusses how the main components in server hardware, including processors, memory, and disks, need to be carefully selected for reliable database storage and a balanced budget. In particular, accidentally using volatile write-back caching in disk controllers and drives can easily introduce database corruption.
Chapter 3, Database Hardware Benchmarking moves on to quantifying the different performance aspects of database hardware. Just how fast is the memory and raw drives in your system? Does performance scale properly as more drives are added?
Chapter 4, Disk Setup looks at popular filesystem choices and suggests the trade-offs of various ways to layout your database on disk. Some common, effective filesystem tuning tweaks are also discussed.
Chapter 5, Memory for Database Caching digs into how the database is stored on disk, in memory, and how the checkpoint process serves to reconcile the two safely. It also suggests how you can actually look at the data being cached by the database, to confirm whether what's being stored in memory matches what you'd expect to be there.
Chapter 6, Server Configuration Tuning covers the most important settings in the
postgresql.conf file, what they mean, and how you should set them. And the settings you can cause trouble by changing are pointed out, too. Chapter 7, Routine Maintenance starts by explaining how PostgreSQL determines what rows are visible to which clients. The way visibility information is stored requires a cleanup process named
VACUUM to reuse leftover space properly. Common issues and general tuning suggestions for it and the always running autovacuum are covered. Finally, there's a look at adjusting the amount of data logged by the database, and using a query log analyzer on the result to help find query bottlenecks. Chapter 8, Database Benchmarking investigates how to get useful benchmark results from the built-in
pgbench testing program included with PostgreSQL. Chapter 9, Database Indexing introduces indexes in terms of how they can reduce the amount of data blocks read to answer a query. That approach allows for thoroughly investigating common questions like why a query ...