Scaling with PostgreSQL
Magic of Tuning and Connection Pooling
Pitfalls where IT Mangers get Trapped
Failure to have a correct plan for scaling with
business
Falling prey to proprietary database vendors
and their costly hardware
Failing to identify the correct Core-Memory
Ratio
Adding Resources will not help all the time
Benchmark Studies: Objective
See how PostgreSQL scales with hardware
See how PostgreSQL scales with tuning
See the effect of proper Connection and Transaction
Management on PostgreSQL performance
Highlight the importance of Tuning despite increasing
system hardware
Benchmark Studies: Test bed Setup
We have used HammerDB for this benchmark
An open source benchmarking tool which provides test cases for TPC-C and
TPC-H benchmarks
We used TPC-C for our tests
All the tests were run on a Virtual Machine (using VM player) on a Laptop
64-bit Intel 8 core CPU
16 GB of physical memory
Host OS- Windows 7 64 bit
Testing Environment
Postgres Plus Advanced Server v9.2
Running on Red Hat Enterprise Linux v6.1 (with kernel version 2.6)
Disclaimer
Performance without tuning and with tuning is shared here
to make a point
The tests were done on a virtual environment running on a
low end host machine
Scalability or raw performance numbers presented here are
not true indicators of capabilities of PostgreSQL/Postgres
Plus Advanced Server
With the real server class machines these numbers would be
much higher than on the test machine
Low Configuration PostgreSQL server scaling with
Connection Pooling
Database Parameters:
No of Processors: 1
Memory: 3GB,
shared_buffer: 32MB,
effective_cache_size: 128MB
work_mem=1MB
Key Learning:
Test 1
- The performance degrades once the load increases beyond a point [sounds familiar?]
Test 2
- Session Level Connection pooling with max 50 connections in Pool
- Connections aquired as needed so not much difference than first test Inconsistent/low and
Test 3 highs can be attributed
- Transaction Level Connection Pooling with 25 connections to resources contention
- Does well as concurrency increases [scaling with growth!!!] during autovacuum
Some Important Parameters for Tuning in
PostgreSQL
shared_buffer random_page_cost
effective_cache_size autovacuum_naptime
work_mem autovacuum_vacuum_threshold
wal_buffer autovacuum_analyze_threshold
bgwriter_delay
autovacuum_vacuum_scale_factor
effective_io_concurrency
autovacuum_analyze_scale_factor
checkpoint_segment
autovacuum_vacuum_cost_delay
checkpoint_timeout
PostgreSQL Performs better after Tuning
Database Parameters:
No of Processors: 1
Memory: 3GB,
shared_buffer: 32MB/256MB,
effective_cache_size: 128MB/750MB
work_mem=1MB
Key Learning:
Test 1: Basic Setup
- The performance degrades once the load increases beyond a point [sounds familiar?]
Test 2: Tuned [Link] for shared_buffer and effective_cache_size Still the performance is
- Up to 19% gain over basic configuration not scalable in any of the
3 cases and degrades
Test 3: Tuned other parameters in [Link]
with concurrency [25-
- Up to 21% gain
30% loss]
- The occasional performance troughs because of vacuum operation is
not there anymore
Combining the Effect of Connection Pooling and
Tuning
Database Parameters:
No of Processors: 1
Memory: 3GB,
shared_buffer: 256MB,
effective_cache_size: 750MB
work_mem=1MB
Key Learning:
Test 1: Basic Setup with tuned shared_buffer and effective_cache_size
- The performance degrades once the load increases beyond a point [sounds familiar?] Finally we get
Test 2: Tuned other parameters in [Link] a consistently
- Performance gain of up to 14% scaling
- The performance continues to degrade [by a margin of 26%] at higher concurrency Database
Test 3: Transaction Level Connection Pooling Setup
- Performance is more consistent
- Degradation/loss in performance at peak concurrency is quite less [14% only]
Tuning and proper Transaction Management
enhances the benefit of CPU addition
Database Parameters:
No of Processors: 2
Memory: 3GB,
shared_buffer: 256MB,
effective_cache_size: 750MB
work_mem=1MB
Key Learning:
Test 1: Basic Setup with tuned shared_buffer and effective_cache_size
- The performance degrades once the load increases beyond a point Even after boosting
computing power,
Test 2: Tuned other parameters in [Link]
to get best of
- Performance gain of 36% at Peak load
- The performance continues to degrade [by a margin of 26%] at higher concurrency resources, tuning
and connection
Test 3: Transaction Level Connection Pooling
pooling plays a
- Performance is more consistent
- Improvement caused by tuning is more prominent with boosted computing major role
[1CPU- 46% | 2CPU- 80%]
Tuning and proper Transaction Management
enhances the benefit of Memory Upgrade
Database Parameters:
No of Processors: 2
Memory: 4.5GB,
shared_buffer: 750MB,
effective_cache_size: 2GB
work_mem=1MB
Key Learning:
Test 1: Basic Setup with tuned shared_buffer[750MB] and effective_cache_size[2GB] To make best use
- The performance degrades once the load increases beyond a point
of added memory
Test 2: Tuned other parameters in [Link] tuning and
- Performance gain of 55% at Peak load connection
- The performance continues to degrade [by a margin of 15%] at higher concurrency
pooling are
Test 3: Transaction Level Connection Pooling important
- Performance is more consistent
- Gain with additional memory is more prominent after tuning and connection pooling
Low Memory Tuned Postgres Vs Un-Tuned
Postgres with Higher Memory
Database Parameters:
No of Processors: 2
Memory: 3GB/4.5GB,
shared_buffer: 256MB/750MB,
effective_cache_size: 750MB/2GB
work_mem=1MB
Key Learning: Most of your
Test 1: Un-tuned PostgreSQL [only shared_buffer and effective_cache_size is tuned] performance
with 4.5 GB RAM issues can be
- The performance continues to degrade [by a margin of 38%] at higher concurrency resolved with
Test 3: Tune PostgreSQL with Transaction Level Connection Pooling proper application
- Performance is more consistent and database
- Despite low memory, performance is comparable at low concurrency tuning
- A Tuned database with proper transaction management does better as load increases
Ashnik Message
You dont always need a high end system in the beginning to
scale in future
There are commercially supported Open Source Solutions
available which scale as good as their proprietary
counterparts
There is no generic core-memory formula- Our consultants
help customers identify what is best for them
A well designed application with a well tuned database can
work better than a un-optimized database on a high end
server
How Ashnik Helps?
Server sizing
Health Check contracts on half yearly and annual terms
On-demand Health Check and Tuning services
Provide in migration services- $$ Saving by migrating from
costly proprietary database solutions
