Book Image

Troubleshooting PostgreSQL

Book Image

Troubleshooting PostgreSQL

Overview of this book

Table of Contents (17 chapters)
Troubleshooting PostgreSQL
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

Index

A

  • arrays
    • versus normalization / Arrays versus normalizations
  • asynchronous replication
    • about / Making use of asynchronous replication
    • pg_basebackup, working with / Working with pg_basebackup
    • firing up / Firing up replication
    • slaves, promoting to masters / Promoting slaves to masters
    • making safer / Making replication safer

B

  • backend process (BE) / Killing the postmaster
  • binary trees
    • URL / How an index works
  • bit fields
    • versus boolean fields / boolean fields versus bit fields
  • boolean fields
    • versus bit fields / boolean fields versus bit fields

C

  • Cacti
    • URL / Using Nagios plugins
    • about / Using Nagios plugins
  • checksums
    • adding, to database instance / Adding checksums to a database instance
  • cidr
    • versus text / text versus cidr/inet/circle/point
  • circle
    • versus text / text versus cidr/inet/circle/point
  • clock_timestamp() function / Procedures and indexing
  • columns
    • grouping / Grouping columns the right way
  • conflicts
    • checking / Checking for conflicts
  • contention / Avoiding performance bottlenecks
  • cursors
    • about / Making use of cursors, Procedures and cursors
  • custom format dumps
    • creating / Creating custom format dumps
    • multiple CPUs, using / Making use of many CPUs

D

  • data
    • large amounts, reading / Reading large amounts of data
    • resetting / Resetting data
  • database
    • checking / Checking the overall database behavior
    • pg_stat_activity, checking / Checking pg_stat_activity
    • pg_stat_database, checking / Checking database-level information
  • database instance / Installing RPM packages
    • checksums, adding to / Adding checksums to a database instance
  • data type
    • finding / Finding the right type
  • deadlocks / Understanding basic locking and deadlocks
  • Debian packages
    • installing / Installing Debian packages
  • distance operator / Finding good matches

E

  • encoding related issues
    • preventing / Preventing encoding-related issues
  • error message, PostgreSQL 9.2
    • FATAL / Fixing memory issues
    • DETAIL / Fixing memory issues
    • HINT / Fixing memory issues
  • execution plan / Using a simple index

F

  • floating-point unit (FPU) / numeric versus floating point
  • floating point
    • versus numeric / numeric versus floating point
  • foreign keys
    • managing / Managing foreign keys
  • FOR UPDATE mode
    • locking in / Locking in FOR UPDATE mode
  • Full-text search
    • about / Full-text search and sorting
  • full-text search
    • fixing / Fixing full-text search
    • not using / Not using full-text search at all

G

  • geometric data
    • indexing, Gist used / Indexing geometric data using GiST
  • GIN index / Full-text search and sorting
  • Gist
    • used, for indexing geometric data / Indexing geometric data using GiST
  • Gist index / Indexing geometric data using GiST

I

  • I/O bottlenecks
    • detecting / Detecting I/O bottlenecks
  • I/O cache
    • inspecting / Inspecting the I/O cache
  • IEEE 754
    • URL / numeric versus floating point
  • index corruption
    • dealing with / Dealing with index corruption
  • indexes
    • in PostgreSQL / Understanding indexes in PostgreSQL
    • simple / Using a simple index
    • working / How an index works
    • trouble, avoiding / Avoiding trouble with indexes
    • missing indexes, detecting / Detecting missing indexes
    • useless indexes, removing / Removing useless indexes
    • troubleshooting / Solving common problems
    • foreign keys, managing / Managing foreign keys
    • geometric data indexing, Gist used / Indexing geometric data using GiST
    • reviewing / Reviewing indexes
  • inet
    • versus text / text versus cidr/inet/circle/point
  • installation process, PostgreSQL
    • about / Methods of installing PostgreSQL
    • RPM packages, installing / Installing RPM packages
    • Debian packages, installing / Installing Debian packages
  • internal information
    • inspecting / Inspecting internal information
    • table, inspecting / Looking inside a table
    • I/O cache, inspecting / Inspecting the I/O cache
  • issues / Power-out-related issues

J

  • JavaScript Object Notation (JSON)
    • about / pganalyze-collector
  • joins
    • about / Fixing disastrous joins
    • demo data, creating / Create demo data for joins
    • outer joins / Understanding outer joins

K

  • kernel issues
    • about / Memory and kernel issues
    • fixing / Fixing other kernel-related limitations
  • kernel parameters
    • adjusting, for Linux / Adjusting kernel parameters for Linux
    • adjusting, for Mac OS X / Adjusting kernel parameters for Mac OS X
  • kernel variables
    • SEMMNI / Adjusting kernel parameters for Linux
    • SEMMNS / Adjusting kernel parameters for Linux
    • SEMMSL / Adjusting kernel parameters for Linux
    • SEMMAP / Adjusting kernel parameters for Linux
    • SEMVMX / Adjusting kernel parameters for Linux
  • KNN
    • about / Finding good matches

L

  • language
    • selecting / Choosing the right language
  • LEAKPROOF
    • versus NOT LEAKPROOF / LEAKPROOF versus NOT LEAKPROOF
  • LIKE queries
    • handling / Handling LIKE queries
    • simple / Simple LIKE queries
    • advanced / More advanced LIKE queries
  • Linux
    • kernel parameters, adjusting for / Adjusting kernel parameters for Linux
  • lock
    • inspecting / Inspecting locks
  • locking / Understanding basic locking and deadlocks
    • URL / Avoiding table locks
  • locks
    • inspecting / Inspecting locks

M

  • Mac OS X
    • kernel parameters, adjusting for / Adjusting kernel parameters for Mac OS X
  • memory consumption, issues
    • about / Understanding memory
    • procedures / Procedures and cursors
    • cursors / Procedures and cursors
    • set-returning functions, handling / Handling set-returning functions
    • memory parameters, assigning to functions / Assigning memory parameters to functions
  • memory issues
    • about / Memory and kernel issues
    • fixing / Fixing memory issues
  • monitoring tools
    • using / Integrating with external tools
    • Nagios plugins, using / Using Nagios plugins
    • Zabbix plugin / Zabbix plugins
    • pganalyze-collector / pganalyze-collector
    • pg_view / pg_view – a simple alternative
  • Multi-Version Concurrency Control (MVCC)
    • about / Looking inside a table
  • Multi Router Traffic Grapher (MRTG)
    • URL / Using Nagios plugins
    • about / Using Nagios plugins

N

  • Nagios plugins
    • using / Using Nagios plugins
    • checks / Using Nagios plugins
  • normalization
    • about / Deciding on data types and structure
    • deciding on / Deciding on normalization
    • 7th normal form / The 7th normal form
    • versus arrays / Arrays versus normalizations
  • NOT LEAKPROOF
    • versus LEAKPROOF / LEAKPROOF versus NOT LEAKPROOF
  • NULL
    • about / Understanding the power of NULL
    • in action / Seeing NULL in action
    • and storage / NULL and storage
  • NULL bitmap / NULL and storage
  • numeric
    • versus floating point / numeric versus floating point

O

  • outer joins
    • about / Understanding outer joins

P

  • pages
    • damaged pages, zeroing out / Zeroing out damaged pages
    • individual pages, dumping / Dumping individual pages
    • header, extracting / Extracting the page header
  • performance
    • about / Attacking low performance
    • indexes, reviewing / Reviewing indexes
    • UPDATE commands, fixing / Fixing UPDATE commands
    • slow queries, detecting / Detecting slow queries
  • pganalyze-collector
    • using / pganalyze-collector
    • URL / pganalyze-collector
  • pg_basebackup
    • working with / Working with pg_basebackup
  • pg_dump
    • using / Using pg_dump
    • textual dumps, creating / Creating textual dumps
  • pg_monz
    • about / Zabbix plugins
    • URL / Zabbix plugins
  • pg_view
    • using / pg_view – a simple alternative
    • URL / pg_view – a simple alternative
  • PITR
    • about / Managing point-in-time recovery
    • working / How PITR works
    • PostgreSQL, preparing for / Preparing PostgreSQL for PITR
    • xlog, replaying / Replaying xlog
  • point
    • versus text / text versus cidr/inet/circle/point
  • PostGIS project
    • URL / Indexing geometric data using GiST
  • PostgreSQL
    • installing / Methods of installing PostgreSQL
    • indexes / Understanding indexes in PostgreSQL
    • URL / Avoiding table locks
    • preparing, for PITR / Preparing PostgreSQL for PITR
    • base backups, taking / Taking base backups
  • PostgreSQL documentation
    • URL / Fixing memory issues
  • PostgreSQL transaction model
    • about / The PostgreSQL transaction model
    • savepoints / Understanding savepoints
    • FOR UPDATE mode, locking in / Locking in FOR UPDATE mode
    • performance bottlenecks, avoiding / Avoiding performance bottlenecks
    • table locks, avoiding / Avoiding table locks
  • postmaster
    • killing / Killing the postmaster
  • prepared queries
    • about / Understanding prepared queries
  • procedural language
    • enabling / Choosing the right language
  • procedural languages
    • URL / Choosing the right language
  • procedures
    • managing / Managing procedures and transactions
    • and transactions / Understanding transactions and procedures
    • and indexing / Procedures and indexing
    • VOLATILE / Procedures and indexing
    • STABLE / Procedures and indexing
    • IMMUTABLE / Procedures and indexing
    • [NOT] LEAKPROOF / Procedures and indexing
    • about / Procedures and cursors

Q

  • queries
    • checking / Chasing down slow queries
    • overhead / Notes about overhead
    • data, resetting / Resetting data
    • slow queries, fixing / Detecting slow queries

R

  • random() function / Procedures and indexing
  • read committed mode
    • demonstrating / Demonstrating read committed mode
  • repeatable read
    • using / Using repeatable read
  • replication, issues
    • fixing / Fixing common replication issues, Fixing stopped replication
    • failed queries, fixing / Fixing failed queries
  • RPM packages
    • installing / Installing RPM packages
  • RPMs distributions, Yum repository
    • URL / Installing RPM packages

S

  • savepoints / Understanding savepoints
  • scanning
    • synchronized scanning / Synchronized scanning
  • semaphores / Adjusting kernel parameters for Linux
  • sequential scan / Using a simple index
  • silent corruption
    • preventing / Checksums – preventing silent corruption
  • SQL, execution
    • Parser stage / Using a simple index
    • Rewrite system stage / Using a simple index
    • Optimizer stage / Using a simple index
    • Executor / Using a simple index
  • synchronized scanning / Synchronized scanning
  • synchronous replication
    • switching to / Switching to synchronous replication

T

  • 7th normal form
    • about / The 7th normal form
  • table
    • inspecting / Looking inside a table
  • table locks
    • avoiding / Avoiding table locks
  • template pollution
    • avoiding / Avoiding template pollution
  • text
    • versus varchar / varchar versus text
    • versus cidr / text versus cidr/inet/circle/point
    • versus inet / text versus cidr/inet/circle/point
    • versus circle / text versus cidr/inet/circle/point
    • versus point / text versus cidr/inet/circle/point
  • textual dumps
    • creating / Creating textual dumps
    • blobs, handling / Taking care of blobs
    • passwords, handling / Handling passwords
  • timelines
    • handling / Handling timelines
  • transaction isolation
    • about / Transaction isolation
    • read committed mode, demonstrating / Demonstrating read committed mode
    • repeatable read, using / Using repeatable read
    • repeatable read / Beyond repeatable read
  • transaction log
    • resetting / Resetting the transaction log
  • transaction model, PostgreSQL
    • about / The PostgreSQL transaction model
    • savepoints / Understanding savepoints
    • locking / Understanding basic locking and deadlocks
    • deadlocks / Understanding basic locking and deadlocks
  • transactions
    • managing / Managing procedures and transactions
    • using / Using transactions to avoid trouble
    • and procedures / Understanding transactions and procedures
  • Trigrams / More advanced LIKE queries
  • troubleshooting
    • about / Getting an overview of the problem
    • low performance / Attacking low performance
  • trusted language
    • versus untrusted language / Trusted versus untrusted

U

  • untrusted language
    • versus trusted language / Trusted versus untrusted
  • UPDATE commands
    • fixing / Fixing UPDATE commands

V

  • varchar
    • versus text / varchar versus text
  • version number, PostgreSQL
    • deciding on / Deciding on a version number

Z

  • Zabbix plugin
    • using / Zabbix plugins