Scaling Connections in PostgreSQL Postgres Bangalore(PGBLR) Meetup-2 - Mydbops

This presentation, delivered at the Postgres Bangalore (PGBLR) Meetup-2 on June 29th, 2024, dives deep into connection pooling for PostgreSQL databases. Aakash M, a PostgreSQL Tech Lead at Mydbops, explores the challenges of managing numerous connections and explains how connection pooling optimizes performance and resource utilization.

Key Takeaways:
* Understand why connection pooling is essential for high-traffic applications
* Explore various connection poolers available for PostgreSQL, including pgbouncer
* Learn the configuration options and functionalities of pgbouncer
* Discover best practices for monitoring and troubleshooting connection pooling setups
* Gain insights into real-world use cases and considerations for production environments

This presentation is ideal for:
* Database administrators (DBAs)
* Developers working with PostgreSQL
* DevOps engineers
* Anyone interested in optimizing PostgreSQL performance

Contact [email protected] for PostgreSQL Managed, Consulting and Remote DBA Services

1. Scaling Connections in PostgreSQL
Postgres Bangalore(PGBLR) Meetup-2, 29th June 2024
-Aakash M

2. About Me
•PostgreSQL Tech Lead at Mydbops
•ExpertiseindatabaseslikeMySQL,SQLServer
•Performancetuning/CostOptimization
•TechSpeakeronOpenSourceEvents
•ActiveBlogger
•6+yearsofExperience

3. About Us

4. Our Services
Focus on MySQL, MongoDB, PostgreSQL, TiDB,
Cassandra
Consulting
Services
Consulting
Services
Managed
Support
Services
24*7
DBA Team
Targeted
Engagement

5. Agenda
•Connection Pooling
•Why pooling is required ?
•Available Connection Poolers
•pgbouncer
•Benchmark Comparison

6. Connection Pooling

7. Application
What is Connection Pooling
•A pool of database connections
•Reusing unused connections from the pool
•Once purpose completes, return back to the pool
Connections to the database

8. Application
Connection
Pooler
What is Connection Pooling
•A pool of database connections
•Reusing unused connections from the pool
•Once purpose completes, return back to the pool
Connections to the Pool
Connections to the database

9. Advantages of Connection Pooling
•Limiting connections to the database
•Overhead of opening/closing connection is reduced

10. Why pooling is required ?

11. Why pooling is required ?
•Process based connection

12. Statistics Collector
WAL Writer
Background Writer
Checkpointer
Autovacuum
Archiver
Shared Memory
Postgres(Server
Process)
Log Writer
Why pooling is required ?
Application
Postgres
Architecture
PostgreSQL Server

13. Statistics Collector
WAL Writer
Background Writer
Checkpointer
Autovacuum
Archiver
Shared Memory
Postgres(Server
Process)
Log Writer
Why pooling is required ?
Application
Postgres(Backend
Process)
Postgres(Backend
Process)
PostgreSQL Server

14. Disadvantages
•More Resources (CPU & Memory)
•Slower Context Switching

15. Impact of Idle Connections
Reference:https://aws.amazon.com/blogs/database/resources-consumed-by-idle-postgresql-connections/

16. MySQL is handling More Connections ??

17. Receiver
Thread
Connection Creation in MySQL
Application
MySQL
Server
mysqld process
Connection Requests
Thread Cache
User Threads THDS

18. Connection Creation in MySQL
•Resource Efficiency (CPU & Memory)
•Faster Context Switching
•Better Scalability

19. Available Connection Poolers

20. Connection Pooler Available Since Setup Complexity Github Repository
pgbouncer 2007 Simple
https://github.com/pgbo
uncer/pgbouncer
Pgpool-II 2003 Medium to High
https://github.com/pgpo
ol/pgpool2
PgCat 2020 Medium
https://github.com/postg
resml/pgcat
Odyssey 2017 Medium to High
https://github.com/yand
ex/odyssey
pgagroal 2020 Low to Medium
https://github.com/agroa
l/pgagroal
Available Connection Poolers

21. pgbouncer

22. Pgbouncer
•Lightweight Process
•Easy to setup
•Single Threaded
•Same authentication as database

23. Pgbouncer

24. Configuration

25. Pooling Mode
•Session
•Transaction
•Statement

26. Session Pooling Mode
•Once the client disconnections, Connection will be
released back to the pool
•Client and Server Connection is paired

27. Application
Connection
Pooler
Session Pooling Mode
•Once the client disconnections, Connection will be
released back to the pool
•Client and Server Connection is paired
Connection 1 Connection 1
Connection 2
Connection 2

28. Tranaction Pooling Mode
•Server Connection is assigned to Client whenever a new
transaction is started
•Released back to the pool when transaction closes
•Server and client connection is not paired
•No support for session variables and prepared statements

29. Tranaction Pooling Mode
Application
Connection
Pooler
User1
Trx1:
Begin;
statement
;
commit or
rollback
User2
Trx2:
Begin;
statement
;

30. Statement Pooling Mode
•Server connection returns to pool immediately after
statement completes
•Multi statement is not supported
•Autocommit is enabled

31. Statement Pooling Mode
Application
Connection
Pooler
User1
drop table x;
drop table y;
User2
select 1;
select 2;
select 3;
select 1;
select 2;

32. Authentication Modes
•Authentication with Auth_file
•Authentication with Auth_user

33. Auth File
•Text file contains username and password
•Location of Auth_file requires

34. Auth File
•auth_type = md5 or password
•auth_file = /etc/pgbouncer/userlist.txt
cat /etc/pgbouncer/userlist.txt
"appuser" "md5e10adc3949ba59abbe56e057f20f883e" # Password:
123456
"reporting_user" "md5e10adc543ba59abbe56e057f20f8899"
"admin" "md5e10000543ba59abbe56e057f20f8877"

35. Auth User
•Load user/password directly from the server
•No maintenance overhead

36. Auth User
•auth_user=”low_priv_user”
•auth_query=”select usename, passwd FROM pg_shadow
WHERE usename=$1”
•auth_file = /etc/pgbouncer/userlist.txt
cat /etc/pgbouncer/userlist.txt
"low_priv_user" "md5e10adc3949ba59abbe56e057f20f883e"

37. Parameter Description Default
Max_client_conn
Maximum number of client
connections allowed
100
Default_Pool_Size
How many server
connections to allow
20
min_pool_size
Minimum number of server
connections to maintain
0(Disabled)
Configuration

38. Parameter Description Default
Reserve_pool_size
Additional connections
allowed to the pool
0(Disabled)
Reserve_pool_timeout
If timeout, use additional
connections in the pool
0(Disabled)
Listen_port Port to listen 6432
Configuration

39. Working of Pgbouncer

40. Working of pgbouncer
•Max_client_conn=10
•Default_pool_size=3
•Reserve_pool_size=1
•Reserve_pool_timeout=10
•Listen_port=6542
•Pooling_mode=Session

41. Working of pgbouncer
Application
pgbouncer

42. Working of pgbouncer
Application
pgbouncer
Pool
Area

43. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area

44. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area

45. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
2 connections

46. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
2 connections 2 connections

47. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
2 connections 2 connections

48. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
2 connections 2 connections

49. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area

50. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
4 connections

51. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
4 connections 3
connections

52. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
4 connections 3
connections

53. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
4 connections 2 connections

54. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
4 connections 2 connections

55. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
4 connections 3
connections

56. Application
pgbouncer
Working of pgbouncer
Pool
Area
Reserve
Pool
Area
Waiting Area
4 connections 4 connections

57. Monitoring Pgbouncer

58. CLI
•Admin Console
•psql -h127.0.0.1 -Upostgres pgbouncer
root@ip-172-31-3-53:~/# psql -h172.31.3.53 -Umydbops pgbouncer -p6432
psql (16.3 (Ubuntu 16.3 -1.pgdg20.04+1), server 1.12.0/bouncer)
pgbouncer=#

59. CLI
pgbouncer=# show help;
•NOTICE: Console usage
•DETAIL:
•SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
•SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
•SHOW DNS_HOSTS|DNS_ZONES
•SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
•SET key = arg
•RELOAD
•PAUSE [<db>]
•RESUME [<db>]
•DISABLE <db>
•ENABLE <db>
•RECONNECT [<db>]
•KILL <db>
•SUSPEND
•SHUTDOWN

60. CLI
pgbouncer=# show pools;
-[ RECORD 1 ]-----------
•database | test
•user | mydbops
•cl_active | 50
•cl_waiting | 50
•sv_active | 50
•sv_idle | 0
•sv_used | 0
•sv_tested | 0
•sv_login | 0
•maxwait | 0
•maxwait_us | 19535
•pool_mode | transaction

61. Grafana
•Open Source Exporters and Dashboards
https://github.com/spreaker/prometheus -pgbouncer-exporter
https://blog.searce.com/grafana -dashboard-for-pgbouncer-and-monitor-with-percona-pmm-
3170d3eb4d14

62. Grafana

63. Benchmarking Results

64. Benchmark
•50 pool size
•Hosted on Application Server
•Small Instance Size
•Pgbench for benchmarking
•1, 50, 75, 100, 200, 400, 500 connections

65. Benchmark

66. Benchmark

67. Benchmark
•Direct connection performs good when number of connection is less
•Only for OLTP workload
•Maintain resource usage

68. Considerations

69. Few Considerations
•Single Point of Failure
•Single Threaded
•Multiple Instance for More Connections
•OS should be optimised

70. Production Use Case

71. Production Use Case
•High CPU usage for last 1 week
•AWS RDS db.r5.2xlarge (8 CPU, 64G RAM)
•No new deployment

72. Production Use Case

73. Production Use Case
•No CPU causing queries
•Increased connections
•Killing Idle Connections

74. Production Use Case

75. Production Use Case

76. Production Use Case
Application

77. Application
Production Use Case

78. Production Use Case

79. Pgpool -II

80. pgpool-II
•multi threaded Architecture
•Connection Pooling
•Read Write Routing (Load Balancing)
•Query Caching
•In-Built HA Architecture

81. General Considerations
•Complexity
•Higher Resource Usage
•Latency

82. Pgcat

83. pgcat
•multi threaded Architecture
•Simple to setup
•Sharding
•Read Write Routing (Load Balancing)
•Query Caching
•In-Built HA Architecture

84. pgcat vs pgbouncer
Ref: https://tembo.io/blog/postgres-connection-
poolers

85. pgcat vs pgbouncer
Ref: https://tembo.io/blog/postgres-connection-
poolers
Ref: https://tembo.io/blog/postgres-connection-
poolers

86. Summary
•pgbouncer works well for simpler setups
•pgpool/pgcat has features and complex settings
•Ensure to test load balancing
•OS tuning is mandatory
•Do not forget HA
•Good for OLTP workload

87. Reference:
•https://tembo.io/blog/postgres-connection-poolers
•https://www.mydbops.com/blog/getting-started-with-pgbouncer-in-postgresql/
•https://blog.searce.com/grafana-dashboard-for-pgbouncer-and-monitor-with-
percona-pmm-3170d3eb4d14
•https://postgresml.org/docs/product/pgcat/features
•https://www.pgpool.net/mediawiki/index.php/Main_Page

88. Thank You