Sign In Start Free Trial

Book Overview & Buying
Table Of Contents
Feedback & Rating

PostgreSQL 10 High Performance

By : Enrico Pirozzi

2.5 (2)

PostgreSQL 10 High Performance

2.5 (2)

By: Enrico Pirozzi

Overview of this book

PostgreSQL database servers have a common set of problems that they encounter as their usage gets heavier and requirements get more demanding. Peek into the future of your PostgreSQL 10 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 and CPUs with the right indexes, and monitor every layer, from hardware to queries, using tools from inside and outside PostgreSQL. Also, using monitoring insight, PostgreSQL database applications continuously rework the design and configuration. On reaching the limits of a single server, they break things up; connection pooling, caching, partitioning, replication, and parallel queries can all help handle increasing database workloads. By the end of this book, you will have all the knowledge you need to design, run, and manage your PostgreSQL solution while ensuring high performance and high availability

Preface

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Free Chapter

PostgreSQL Versions

PostgreSQL Versions

Performance of historical PostgreSQL releases

Choosing a version to deploy

Upgrading to a newer major version

Upgrades to PostgreSQL 8.3+ from earlier ones

PostgreSQL or another database?

PostgreSQL tools

PostgreSQL contrib

Finding contrib modules on your system

Installing a contrib module from source

Using a contrib module

pgFoundry

PGXN

Additional PostgreSQL-related software

PostgreSQL application scaling life cycle

Performance tuning as a practice

Summary

Database Hardware

Database Hardware

Balancing hardware spending

Reliable controller and disk setup

Summary

Database Hardware Benchmarking

Database Hardware Benchmarking

CPU and memory benchmarking

Physical disk performance

Disk benchmarking tools

Sample disk results

Summary

Disk Setup

Disk Setup

Maximum filesystem sizes

Filesystem crash recovery

Linux filesystems

Solaris and FreeBSD filesystems

Disk layout for PostgreSQL

Summary

Memory for Database Caching

Memory for Database Caching

Memory units in postgresql.conf

Increasing Unix shared memory parameters for larger buffer sizes

Crash recovery and the buffer cache

Database buffer cache versus operating system cache

Analyzing buffer cache contents

Summary

Server Configuration Tuning

Server Configuration Tuning

Interacting with the live configuration

Summary

Routine Maintenance

Routine Maintenance

Transaction visibility with multiversion concurrency control

Vacuum

Index bloat

Detailed data and index page monitoring

Monitoring query logs

Summary

Database Benchmarking

Database Benchmarking

pgbench default tests

Graphing results with pgbench-tools

Sample pgbench test results

Sources of bad results and variation

pgbench custom tests

Transaction Processing Performance Council benchmarks

Summary

Database Indexing

Database Indexing

Indexing example walkthrough

Index creation and maintenance

Index types

Advanced index use

Summary

Query Optimization

Query Optimization

Sample data sets

EXPLAIN basics

Query plan node structure

Explain analysis tools

Assembling row sets

Processing nodes

Joins

Statistics

Other query-planning parameters

Executing other statement types

Improving queries

SQL limitations

Summary

Database Activity and Statistics

Database Activity and Statistics

Statistics views

Cumulative and live views

Table statistics

Index statistics

Database-wide totals

Connections and activity

Locks

Disk usage

Buffer, background writer, and checkpoint activity

Summary

Monitoring and Trending

Monitoring and Trending

UNIX monitoring tools

Windows monitoring tools

Trending software

Summary

Pooling and Caching

Pooling and Caching

Connection pooling

Summary

Scaling with Replication

Scaling with Replication

Hot Standby

Replication queue managers

Synchronous replication

Logical replication

Special application requirements

Other interesting replication projects

Replication solution comparison

Summary

Partitioning Data

Partitioning Data

Table range partitioning

PostgreSQL 10 – declarative partitioning – the built-in partitioning

Horizontal partitioning with PL/Proxy

Summary

Avoiding Common Problems

Avoiding Common Problems

Bulk loading

Backup

Common performance issues

Foreign data wrapper

The amcheck module

pgAdmin

Performance-related features by version

Summary

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

2.5 (2)

5 star

0

4 star

0

3 star

50%

2 star

50%

1 star

0

Disk benchmarking tools

Now it's time to see some real disks measured. For the first few examples here, the drive being tested is a 320 GB Seagate Momentus 7200.4 3 Gb/s SATA, model number ST9320423AS. This is one of the fastest 2.5" laptop drives on the market, and its more detailed access time specifications were given in the IOPS section before. The results shown are from an installation on a Lenovo Thinkpad T60 with an Intel Core 2 Duo T7200 running at 2.0 GHz.

We'll start with the HD Tune program running on Windows, because its graphs are extremely nice and it measures almost everything you'd hope for. Its graphs demonstrate several aspects of general disk performance more clearly than the command-line tools for UNIX systems covered later.

HD Tune

...

Search

Your notes and bookmarks