Book Image

PostgreSQL 10 High Performance - Third Edition

By : Enrico Pirozzi

Book Image

PostgreSQL 10 High Performance - Third Edition

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

Who this book is for

What this book covers

To get the most out of this book

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

Additional PostgreSQL-related software

PostgreSQL application scaling life cycle

Performance tuning as a practice

Database Hardware

Database Hardware

Balancing hardware spending

Reliable controller and disk setup

Database Hardware Benchmarking

Database Hardware Benchmarking

CPU and memory benchmarking

Physical disk performance

Disk benchmarking tools

Sample disk results

Disk Setup

Maximum filesystem sizes

Filesystem crash recovery

Linux filesystems

Solaris and FreeBSD filesystems

Disk layout for PostgreSQL

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

Server Configuration Tuning

Server Configuration Tuning

Interacting with the live configuration

Routine Maintenance

Routine Maintenance

Transaction visibility with multiversion concurrency control

Detailed data and index page monitoring

Monitoring query logs

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

Database Indexing

Database Indexing

Indexing example walkthrough

Index creation and maintenance

Advanced index use

Query Optimization

Query Optimization

Sample data sets

Query plan node structure

Explain analysis tools

Assembling row sets

Processing nodes

Other query-planning parameters

Executing other statement types

Improving queries

SQL limitations

Database Activity and Statistics

Database Activity and Statistics

Statistics views

Cumulative and live views

Table statistics

Index statistics

Database-wide totals

Connections and activity

Buffer, background writer, and checkpoint activity

Monitoring and Trending

Monitoring and Trending

UNIX monitoring tools

Windows monitoring tools

Trending software

Pooling and Caching

Pooling and Caching

Connection pooling

Scaling with Replication

Scaling with Replication

Replication queue managers

Synchronous replication

Logical replication

Special application requirements

Other interesting replication projects

Replication solution comparison

Partitioning Data

Partitioning Data

Table range partitioning

PostgreSQL 10 – declarative partitioning – the built-in partitioning

Horizontal partitioning with PL/Proxy

Avoiding Common Problems

Avoiding Common Problems

Common performance issues

Foreign data wrapper

The amcheck module

Performance-related features by version

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

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

...