展开查看详情
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
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.
§ https://www.percona.com/blog/2018/08/31/tuning-postgresql-database-parameters-to-
optimize-performance/
• Tune Linux Kernel Parameters For PostgreSQL Optimisation
§ https://www.percona.com/blog/2018/08/29/tune-linux-kernel-parameters-for-
postgresql-optimization/
19
19
20 .Champions of Unbiased
Open Source Database Solutions
热门城市
