| Description |
1 online resource (1 volume) : illustrations |
| Contents |
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.... |
| Summary |
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 PostgreSQL server Explore a wide range of high availability and replication mechanisms to build robust, highly available, scalable, and fault-tolerant PostgreSQL databases Who This Book Is For If you are a developer or administrator with limited PostgreSQL knowledge and want to develop your skills with this great open source database, then this book is ideal for you. Learning how to enhance the database performance is always an exciting topic to everyone, and this book will show you enough ways to enhance the database performance. What You Will Learn Build replication strategies for homogeneous and heterogeneous databases Test and build a powerful machine with multiple bench marking techniques Get to know a few SQL injection techniques Find out how to manage the replication using multiple tools Benchmark the database server using multiple strategies Work with the query processing algorithms and their internal behaviors Build a proper plan to upgrade or migrate to PostgreSQL from other databases See the essential database load balancing techniques and the various partitioning approaches PostgreSQL provides Learn memory optimization techniques and database server configurations In Detail PostgreSQL is one of the most powerful and easy to use database management systems. It has strong support from the community and is being actively developed with a new release every year. PostgreSQL supports the most advanced features included in SQL standards. It also provides NoSQL capabilities and very rich data types and extensions. All of this makes PostgreSQL a very attractive solution in software systems. If you run a database, you want it to perform well and you want to be able to secure it. As the world's most advanced open source database, PostgreSQL has unique built-in ways to achieve these goals. This book will show you a multitude of ways to enhance your database's performance and give you insights into measuring and optimizing a PostgreSQL database to achieve better performance. This book is your one-stop guide to elevate your PostgreSQL knowledge to the next level. First, you'll get familiarized with essential developer/administrator concepts such as lo... |
| Subject |
PostgreSQL.
|
|
PostgreSQL |
|
SQL (Computer program language)
|
|
Database management.
|
|
Databases -- Design.
|
|
SQL (Langage de programmation) |
|
Bases de données -- Gestion. |
|
Database management |
|
Databases -- Design |
|
SQL (Computer program language) |
| Added Title |
Postgre Structure Query Language high performance cookbook |
| Other Form: |
Print version: Chauhan, Chitij. PostgreSQL high performance cookbook : mastering query optimization, database monitoring, and performance-tuning for PostgreSQL. Birmingham, England ; Mumbai, [India] : Packt, c2017 xiv, 331 pages 9781785284335 |
| ISBN |
9781785287244 |
|
1785287249 |
|
1785284339 |
|
9781785284335 |
|
More Resources