PostgreSQL Performance Tuning



1.PostgreSQL Performance Tuning Ibrar Ahmed Senior Software Engineer @ Percona LLC PostgreSQL Consultant 1

2.PostgreSQL Why? Support? ● One of the finest open source relational database which has some object-oriented There are many companies providing professional features. support for PostgreSQL. Object-Relational database management system (RDBMS) ● PostgreSQL is free. ● PostgreSQL is Open Source. ● PostgreSQL Conform to the ANSI-SQL:2008. ● PostgreSQL is ACID (Atomicity, Consistency, Isolation and Durability) Complaint. License? Who? PostgreSQL: Released under the PostgreSQL License. (Similar to the BSD or MIT) ● Web technology ● Financial ● No-SQL Workload ● Small & Large Scale Business 2

3.Database Performance • Hardware • Operating System (Linux) • Database (PostgreSQL) Configuration • Workload • Queries • Application 3

4.PostgreSQL Tuning 4

5.PostgreSQL Tuning - Configuration Parameter • shared_buffer • wal_buffers • effective_cache_size • work_mem • maintenance_work_mem • synchronous_commit • checkpoint_timeout • checkpoint_completion_target 5

6.PostgreSQL Tuning / shared_buffer • PostgreSQL uses its own buffer and also uses kernel buffered I/O. • PostgreSQL buffer is called shared_buffer. • Data is written to shared_buffer then kernel buffer then on the disk. postgresql=# SHOW shared_buffers; shared_buffers ---------------- 128MB (1 row) The proper size for the POSTGRESQL shared buffer cache is the largest useful size that does not adversely affect other activity. —Bruce Momjian 6

7.PostgreSQL Tuning / shared_buffer 7

8.PostgreSQL Tuning / wal_buffer • PostgreSQL writes its WAL (write ahead log) record into the buffers and then these buffers are flushed to disk. • Bigger value for wal_buffer in case of lot of concurrent connection gives better performance. 8

9.PostgreSQL Tuning / effective_cache_size • The effective_cache_size provides an estimate of the memory available for disk caching. • It is just a guideline, not the exact allocated memory or cache size. • It should be large enough to hold most accessed tables, but at the same time small enough to avoid swap. 9

10.PostgreSQL Tuning / work_mem • This configuration is used for complex sorting. 10

11.PostgreSQL Tuning / maintenance_work_mem • maintenance_work_mem is a memory setting used for maintenance tasks. • The default value is 64MB. • Setting a large value helps in tasks like VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE. 11 11

12.PostgreSQL Tuning / synchronous_commit • This is used to enforce that commit will wait for WAL to be written on disk before returning a success status to the client. • This is a trade-off between performance and reliability. • Increasing reliability decreases performance and vice versa. 12 12

13.PostgreSQL Tuning / checkpoint_timeout • PostgreSQL writes changes into WAL. The checkpoint process flushes the data into the data files. • More checkpoints have a negative impact on performance. 13 13

14.Linux Tuning - Huge Pages • Linux, by default uses 4K memory pages. • Linux also has Huge Pages, Transparent huge pages. • BSD has Super Pages. • Windows has Large Pages. • Linux default page size is 4K. • Default Huge page size is 2MB. 14 14

15.Linux Tuning / vm.swappiness • This is another kernel parameter that can affect the performance of the database. • Used to control the swappiness (swapping pages to and from swap memory into RAM) behaviour on a Linux system. 15 15

16.Linux Tuning / vm.overcommit_memory and vm.overcommit_ratio • Applications acquire memory and free that memory when it is no longer needed. • But in some cases an application acquires too much memory and does not release it. This can invoke the OOM killer. § Heuristic overcommit, Do it intelligently (default); based kernel heuristics § Allow overcommit anyway § Don’t over commit beyond the overcommit ratio. 16 16

17.Linux Tuning / vm.dirty_background_ratio and vm.dirty_background_bytes • The vm.dirty_background_ratio is the percentage of memory filled with dirty pages that need to be flushed to disk. • Flushing is done in the background. The value of this parameter ranges from 0 to 100; 17 17

18.Linux Tuning / vm.dirty_ratio / vm.dirty_bytes • The vm.dirty_background_ratio is the percentage of memory filled with dirty pages that need to be flushed to disk. • Flushing is done in the foreground. The value of this parameter ranges from 0 to 100; 18 18

19.Blogs • Tuning PostgreSQL Database Parameters to Optimise Performance. § optimize-performance/ • Tune Linux Kernel Parameters For PostgreSQL Optimisation § postgresql-optimization/ 19 19

20.Champions of Unbiased Open Source Database Solutions