PostgreSQL high performance cookbook mastering query optimization, database monitoring, and performance-tuning for PostgreSQL

Get to know effective ways to improve PostgreSQL’s performance and master query optimization, and database monitoring. About This Book Perform essential database tasks such as benchmarking the database and optimizing the server’s memory usage Learn ways to improve query performance and optimize the...

Descripción completa

Detalles Bibliográficos
Otros Autores: Chauhan, Chitij, author (author), Kumar, Dinesh, author
Formato: Libro electrónico
Idioma:Inglés
Publicado: Birmingham, England ; Mumbai, [India] : Packt 2017.
Edición:1st edition
Materias:
Ver en Biblioteca Universitat Ramon Llull:https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009630083106719
Tabla de Contenidos:
  • Intro
  • PostgreSQL High Performance Cookbook
  • PostgreSQL High Performance Cookbook
  • Credits
  • About the Authors
  • About the Reviewers
  • www.PacktPub.com
  • Why subscribe?
  • Customer Feedback
  • Preface
  • What this book covers
  • What you need for this book
  • Who this book is for
  • Sections
  • Getting ready
  • How to do it...
  • How it works...
  • There's more...
  • See also
  • Conventions
  • Reader feedback
  • Customer support
  • Downloading the example code
  • Errata
  • Piracy
  • Questions
  • 1. Database Benchmarking
  • Introduction
  • CPU benchmarking
  • Getting ready
  • How to do it...
  • Phoronix
  • sysbench
  • How it works...
  • Phoronix
  • sysbench
  • Memory benchmarking
  • Getting ready
  • How to do it...
  • Phoronix
  • tmpfs
  • Write test
  • Read test
  • How it works...
  • Disk benchmarking
  • Getting ready
  • How to do it...
  • Phoronix
  • bonnie++
  • How it works...
  • bonnie++
  • Performing a seek rate test
  • Getting ready
  • How to do it...
  • How it works...
  • Working with the fsync commit rate
  • Getting ready
  • How to do it...
  • How it works...
  • Checking IOPS
  • Getting ready
  • How to do it...
  • Sequential mixed read and write
  • Random mixed read and write
  • How it works...
  • Storage sizing
  • Getting ready
  • How to do it...
  • How it works...
  • Discussing RAID levels
  • Getting ready
  • How to do it...
  • How it works...
  • RAID 0
  • RAID 1
  • RAID 5
  • RAID 6
  • RAID 10
  • Configuring pgbench
  • Getting ready
  • How to do it...
  • How it works...
  • Running read/write pgbench test cases
  • Getting ready
  • How to do it...
  • Read-only
  • Write-only
  • How it works...
  • 2. Server Configuration and Control
  • Introduction
  • Starting the server manually
  • Getting ready
  • How to do it...
  • How it works...
  • Stopping the server quickly
  • Getting ready
  • How to do it...
  • How it works...
  • Stopping the server in an emergency
  • How to do it...
  • How it works...
  • Reloading server configuration
  • Getting ready
  • How to do it...
  • How it works...
  • Restarting the database server quickly
  • How to do it...
  • How it works...
  • Tuning connection-related parameters
  • How to do it...
  • How it works...
  • Tuning query-related parameters
  • How to do it...
  • How it works...
  • Tuning logging-related parameters
  • How to do it...
  • How it works...
  • 3. Device Optimization
  • Introduction
  • Understanding memory units in PostgreSQL
  • Getting ready
  • How to do it...
  • shared_buffers
  • temp_buffers
  • work_mem
  • maintenance_work_mem
  • wal_buffers
  • max_stack_depth
  • effective_cache_size
  • How it works...
  • Handling Linux/Unix memory parameters
  • Getting ready
  • How to do it...
  • kernel.shmmax
  • kernel.shmall
  • kernel.shmmni
  • vm.swappiness
  • vm.overcommit_memory
  • vm.overcommit_ratio
  • vm.dirty_background_ratio
  • vm.dirty_ratio
  • How it works...
  • CPU scheduling parameters
  • Getting ready
  • How to do it...
  • kernel.sched_autogroup_enabled
  • kernel.sched_min_granularity_ns
  • kernel.sched_latency_ns
  • kernel.sched_wakeup_granularity_ns
  • kernel.sched_migration_cost_ns
  • How it works...
  • Disk tuning parameters
  • Getting ready
  • How to do it...
  • CFQ
  • noop
  • Deadline
  • How it works...
  • Identifying checkpoint overhead
  • Getting ready
  • How to do it...
  • How it works...
  • Analyzing buffer cache contents
  • Getting ready
  • How to do it...
  • How it works...
  • 4. Monitoring Server Performance
  • Introduction
  • Monitoring CPU usage
  • Getting ready
  • How to do it...
  • How it works...
  • Monitoring paging and swapping
  • Getting ready
  • How to do it...
  • How it works...
  • Tracking CPU consuming processes
  • Getting ready
  • How to do it...
  • How it works...
  • Monitoring CPU load.
  • How to do it...
  • How it works...
  • Identifying CPU bottlenecks
  • How to do it...
  • How it works...
  • Identifying disk I/O bottlenecks
  • How to do it...
  • How it works...
  • Monitoring system load
  • How to do it...
  • How it works...
  • Tracking historical CPU usage
  • Getting ready
  • How to do it...
  • How it works...
  • There's more...
  • Tracking historical memory usage
  • Getting ready
  • How to do it...
  • How it works...
  • Monitoring disk space
  • How to do it...
  • How it works...
  • Monitoring network status
  • Getting ready
  • How to do it...
  • How it works...
  • 5. Connection Pooling and Database Partitioning
  • Introduction
  • Installing pgpool-II
  • Getting Ready
  • How to do it...
  • How it works...
  • Configuring pgpool and testing the setup
  • Getting ready
  • How to do it...
  • How it works...
  • There's more...
  • Installing PgBouncer
  • Getting ready
  • How to do it...
  • How it works...
  • Connection pooling using PgBouncer
  • Getting ready
  • How to do it...
  • How it works...
  • There's more...
  • Managing PgBouncer
  • Getting ready
  • How to do it...
  • How it works...
  • Implementing partitioning
  • Getting ready
  • How to do it...
  • How it works...
  • Managing partitions
  • Getting ready
  • How to do it...
  • How it works...
  • Installing PL/Proxy
  • How to do it...
  • How it works...
  • Partitioning with PL/Proxy
  • Getting ready
  • How to do it...
  • How it works...
  • 6. High Availability and Replication
  • Introduction
  • Setting up hot streaming replication
  • Getting ready
  • How to do it...
  • How it works...
  • Replication using Slony
  • Getting ready
  • How to do it...
  • How it works...
  • Replication using Londiste
  • Getting ready
  • How to do it...
  • How it works...
  • Replication using Bucardo
  • Getting ready
  • How to do it...
  • How it works...
  • Replication using DRBD
  • Getting ready.
  • How to do it...
  • How it works...
  • Setting up a Postgres-XL cluster
  • Getting ready
  • How to do it...
  • 7. Working with Third-Party Replication Management Utilities
  • Introduction
  • Setting up Barman
  • Getting ready
  • How to do it...
  • How it works...
  • Backup and recovery using Barman
  • Getting ready
  • How to do it...
  • How it works...
  • Setting up OmniPITR
  • Getting ready
  • How to do it...
  • How it works...
  • WAL management with OmniPITR
  • Getting ready
  • How to do it...
  • How it works...
  • Setting up repmgr
  • How to do it...
  • How it works...
  • Using repmgr to create replica
  • How to do it...
  • How it works...
  • Setting up walctl
  • How to do it...
  • How it works...
  • Using walctl to create replica
  • How to do it...
  • How it works...
  • 8. Database Monitoring and Performance
  • Introduction
  • Checking active sessions
  • Getting ready
  • How to do it...
  • How it works...
  • There's more...
  • Finding out what the users are currently running
  • Getting ready
  • How to do it...
  • How it works...
  • Finding blocked sessions
  • Getting ready
  • How to do it...
  • How it works...
  • There's more...
  • Transactional locks
  • Table level locks
  • Prepared transaction locks
  • Usage of SKIP LOCKED
  • Dealing with deadlocks
  • Getting ready
  • How to do it...
  • Using FOR UPDATE
  • Advisory locks
  • Table access statistics
  • Getting ready
  • How to do it...
  • pg_stat_user_tables
  • pg_statio_user_tables
  • How it works...
  • Logging slow statements
  • Getting ready
  • How to do it...
  • How it works...
  • Determining disk usage
  • Getting ready
  • How to do it...
  • Column size
  • Relation size
  • Database size
  • Tablespace size
  • How it works...
  • Preventing page corruption
  • Getting ready
  • How to do it...
  • How it works...
  • There's more...
  • Routine reindexing
  • Getting ready
  • How to do it...
  • How it works...
  • There's more...
  • Generating planner statistics
  • Getting ready
  • How to do it...
  • How it works...
  • null_frac
  • avg_width
  • n_distinct
  • most_common_vals
  • most_common_freqs
  • histogram_bounds
  • correlation
  • most_common_elems
  • most_common_elem_freqs
  • Tuning with background writer statistics
  • Getting ready
  • How to do it...
  • How it works...
  • checkpoints_timed
  • checkpoints_req
  • checkpoint_write_time
  • checkpoint_sync_time
  • buffers_checkpoint
  • buffers_clean
  • max_written_clean
  • buffers_backend
  • buffers_backend_fsync
  • buffers_alloc
  • stats_reset
  • There's more...
  • 9. Vacuum Internals
  • Introduction
  • Dealing with bloating tables and indexes
  • Getting ready
  • How to do it...
  • How it works...
  • Vacuum and autovacuum
  • Getting ready
  • How to do it...
  • How it works...
  • xmin
  • xmax
  • Autovacuum
  • Freezing and transaction ID wraparound
  • Getting ready
  • How to do it...
  • How it works...
  • Fixing transaction ID wraparound
  • Preventing transaction ID wraparound
  • Monitoring vacuum progress
  • Getting ready
  • How to do it...
  • How it works...
  • Control bloat using transaction age
  • Getting ready
  • How to do it...
  • How it works...
  • 10. Data Migration from Other Databases to PostgreSQL and Upgrading the PostgreSQL Cluster
  • Introduction
  • Using pg_dump to upgrade data
  • Getting ready
  • How to do it...
  • How it works...
  • Using the pg_upgrade utility for version upgrade
  • Getting ready
  • How to do it...
  • How it works...
  • Replicating data from other databases to PostgreSQL using Goldengate
  • Getting ready
  • How to do it...
  • How it works...
  • There's more...
  • 11. Query Optimization
  • Introduction
  • Using sample data sets
  • Getting ready
  • How to do it...
  • How it works...
  • Timing overhead
  • Getting ready
  • How to do it...
  • How it works....
  • Studying hot and cold cache behavior.