developing postgresql performance (simon riggs)

© 2ndQuadrant Limited 2010

DevelopingPostgreSQLPerformance

© 2ndQuadrant Limited 2010

Performance Comparisons

© 2ndQuadrant Limited 2010

PostgreSQL Performance Gain:Single Node Improvements

7.3.x (est) 7.4.x (est) 8.0.21 8.1.17 8.2.13 8.3.7 8.4.1

0.00%

20.00%

40.00%

60.00%

80.00%

100.00%

120.00%

Peak RO TPSPeak RW TPS

© 2ndQuadrant Limited 2010

What this talk is about

• History of performance analysis &performance development

• Lessons learned

• Where next?

© 2ndQuadrant Limited 2010

Who Am I?

• Simon Riggs

• Major Developer on PostgreSQL project

• CTO, 2ndQuadrant

• Database Architect at Abbey National BankTechnical Advisor RDBMS Procurement

© 2ndQuadrant Limited 2010

Why performance matters

• User Experience (Response time)

• Headroom for growth (Selection Risk)

• Total Cost of Ownership (Cost profile)

© 2ndQuadrant Limited 2010

Dramatis Personae

• Simon Riggs

• Mark Wong

• Jonah Harris

• Pavan Deolassee

• Manfred Koizar

• Tom Lane

• Greg Smith

• Heikki Linnakangas

• Greg Stark

• Jan Wieck

© 2ndQuadrant Limited 2010

Performance Analysis

7.3.x (est) 7.4.x (est) 8.0.21 8.1.17 8.2.13 8.3.7 8.4.1

0.00%

20.00%

40.00%

60.00%

80.00%

100.00%

120.00%

Peak RO TPSPeak RW TPS

Laptop

Server-based

No re-analysis!

© 2ndQuadrant Limited 2010

Lessons

• Need proper workload analysis

• Laptop-only analysis is never good enough

• Regular re-analysis is needed for each release

© 2ndQuadrant Limited 2010

Target Workloads

• Various workloads, all different– High Volume Transactions

• Read Only• Read Write

– Data Warehouse• Very Large Query• Data Loading

• Different approaches required

• Different development areas

© 2ndQuadrant Limited 2010

Data Warehouse

• Optimizer Improvements (all)

• Partitioning (8.1, 9.1)

• Sort Improvements (8.2-8.3)

• WAL avoidance (8.3-9.0)

• Hash Join skew avoidance (8.4)

© 2ndQuadrant Limited 2010

Real transactional benchmark!

• Unisys sponsored benchmarks on 32-way

• No way to analyse SQL execution time

• EXPLAIN won't work on parameterised statements

• Bgwriter gave negative performance benefit

• Context switch storms

• Massively untuned!

© 2ndQuadrant Limited 2010

Breakthroughs

• Full stack analysis

• Event targeting

• Scalability Analysis

© 2ndQuadrant Limited 2010

Full stack analysis

• Algorithms

• Libraries/Modules

• Compiler Optimisation

• OS Layer Optimisation

• Physical Layer

• Hardware

© 2ndQuadrant Limited 2010

Event targeting

• Reject the smoothness assumption:performance is not lost by constant friction

• Events– Traffic Jams

– Phase changes

– Black Swans

• Don't look at the totals and averages

• Look at the behaviour

© 2ndQuadrant Limited 2010

Scalability Analysis

• Resource sharing rules

• Fine-grained Locking

• Lock avoidance

• Minimise

• Partition

• Dependency Removal

© 2ndQuadrant Limited 2010

Developments

Buffer Alignment (7.4)

Buffer Management (8.0)

Buffer Manager (8.1)

Lock Manager (8.2)

Cache Line optimisation (8.2)

Snapshot Management (8.3)

Buffer recycling (8.3)

© 2ndQuadrant Limited 2010

Results

© 2ndQuadrant Limited 2010

Conclusions

To conclude the above, I think it's safe to say that if we have known PostgreSQL as a slow beast, it's time to re-think (or measure) that, because it has gained quite much performance and scalability in the last three years.Not to speak about its features.

György Vilmoshttp://suckit.blog.hu/2009/09/29/postgresql_history

© 2ndQuadrant Limited 2010

Where next?

• Transactional Workloads– Smoothness (+10-20%)

– Indexing (+10-20%)

• Data Warehouse– Indexing

– Partitioning

– Many available techniques

© 2ndQuadrant Limited 2010

Performance Future

7.3.x (est) 7.4.x (est) 8.0.21 8.1.17 8.2.13 8.3.7 8.4.1

0.00%

20.00%

40.00%

60.00%

80.00%

100.00%

120.00%

Peak RO TPSPeak RW TPS

© 2ndQuadrant Limited 2010

Radical Steps

• Architecture is Critical

• New options in the DBMS

• Mixed mode

• Plus, changes for multi-node scalability– Otherwise ignored during this talk

© 2ndQuadrant Limited 2010

PostgreSQL Books

• http://www.2ndQuadrant.com/books/