Connection pooling, routing, and queuing with PgBouncer

PgBouncer is primarily thought of as a connection pooler, but it can be much more. As a key component in many high-performance PostgreSQL installations, PgBouncer can help with connection routing, queuing, load management, and database maintenance.

Learn how to use PgBouncer, how to configure it for the right application, how to monitor it appropriately, what errors to look for, and when to consider alternatives.

http://www.pgconf.us/2015/event/65/

Transcript

1. Connection pooling, routing, and queuing with PgBouncer Peter Eisentraut MeetMe, Inc.
2. PgBouncer quick intro DB client PgBouncer DB
3. PgBouncer configuration p g b o u n c e r / e t c / p g b o u n c e r / p g b o u n c e r . i n i [ d a t a b a s e s ] f o o = h o s t = b a r d b n a m e = b a z a b c = h o s t = d e f d b n a m e = g h i u s e r = w w w [ p g b o u n c e r ] l i s t e n _ p o r t = 6 4 3 2
4. Connection pooling
5. client app DB
6. client app PgBouncer DB
7. Performance analysis: connection l i b p q c o n n e c t : connect, check error, disconnect; 10000 times e x p o r t P G H O S T = l o c a l h o s t P G D A T A B A S E = t e s t d b direct to postgres P G P O R T = 5 4 3 2 . / l i b p q c o n n e c t 0 . 8 3 s u s e r 2 . 3 6 s s y s t e m 1 3 % c p u 2 4 . 4 3 1 t o t a l pgbouncer P G P O R T = 6 4 3 2 . / l i b p q c o n n e c t 0 . 7 6 s u s e r 1 . 9 2 s s y s t e m 5 6 % c p u 4 . 7 2 0 t o t a l my proxy P G P O R T = 9 9 9 8 . / l i b p q c o n n e c t 0 . 8 3 s u s e r 2 . 4 0 s s y s t e m 3 0 % c p u 1 0 . 5 7 2 t o t a l
8. Performance analaysis: query l i b p q s e l e c t 1 : connect, select 1, disconnect; 10000 times e x p o r t P G H O S T = l o c a l h o s t P G D A T A B A S E = t e s t d b direct to postgres P G P O R T = 5 4 3 2 . / l i b p q s e l e c t 1 0 . 8 8 s u s e r 2 . 3 6 s s y s t e m 1 1 % c p u 2 7 . 5 8 4 t o t a l pgbouncer P G P O R T = 6 4 3 2 . / l i b p q s e l e c t 1 0 . 7 9 s u s e r 2 . 1 0 s s y s t e m 4 1 % c p u 6 . 9 9 7 t o t a l my proxy P G P O R T = 9 9 9 8 . / l i b p q s e l e c t 1 0 . 9 4 s u s e r 2 . 7 6 s s y s t e m 2 8 % c p u 1 2 . 9 8 8 t o t a l
9. Performance analaysis: persistent connection l i b p q s e l e c t 2 : connect, select 100000 times, disconnect e x p o r t P G H O S T = l o c a l h o s t P G D A T A B A S E = t e s t d b direct to postgres P G P O R T = 5 4 3 2 . / l i b p q s e l e c t 2 0 . 5 0 s u s e r 1 . 6 2 s s y s t e m 3 0 % c p u 6 . 8 7 4 t o t a l pgbouncer P G P O R T = 6 4 3 2 . / l i b p q s e l e c t 2 0 . 4 9 s u s e r 1 . 5 6 s s y s t e m 1 8 % c p u 1 1 . 0 5 6 t o t a l my proxy P G P O R T = 9 9 9 8 . / l i b p q s e l e c t 2 0 . 7 6 s u s e r 3 . 3 2 s s y s t e m 1 9 % c p u 2 1 . 0 4 1 t o t a l pgpool P G P O R T = 9 9 9 9 . / l i b p q s e l e c t 2 0 . 5 9 s u s e r 2 . 2 6 s s y s t e m 2 2 % c p u 1 2 . 7 0 3 t o t a l
10. Connection queuing
11. DB client PgBouncer ∞ DB ?
12. DB client PgBouncer ∞ DB 20
13. d e f a u l t _ p o o l _ s i z e = 2 0 ; r e s e r v e _ p o o l _ s i z e = 5 ; r e s e r v e _ p o o l _ t i m e o u t = 3
14. $ p s q l - p 6 4 3 2 p g b o u n c e r = > S H O W p o o l s ; ┌ ─ [ R E C O R D 1 ] ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐ │ d a t a b a s e │ s o m e _ d b │ │ u s e r │ w w w │ │ c l _ a c t i v e │ 2 2 3 │ │ c l _ w a i t i n g │ 0 │ │ s v _ a c t i v e │ 0 │ │ s v _ i d l e │ 3 │ │ s v _ u s e d │ 1 │ │ s v _ t e s t e d │ 0 │ │ s v _ l o g i n │ 0 │ │ m a x w a i t │ 0 │ ├ ─ [ R E C O R D 2 ] ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┤
15. Connection routing
16. How do you connect to PostgreSQL?
17. $ d b c o n n = p g _ c o n n e c t ( " h o s t = d b 1 . e x a m p l e . c o m d b n a m e = a p p 1 2 " ) ; d b c o n n = p s y c o p g 2 . c o n n e c t ( h o s t = ' d b 2 . e x a m p l e . c o m ' , p o r t = 5 4 3 3 , d b n a m e = ' a p p 5 ' ) p s q l - h d b 3 . e x a m p l e . c o m - U m o n i t o r a p p 2 1
18. client app PgBouncer DB
19. [ d a t a b a s e s ] a p p 5 = h o s t = d b 2 . e x a m p l e . c o m p o r t = 5 4 3 3 a p p 1 2 = h o s t = d b 1 . e x a m p l e . c o m . . . a p p 2 1 _ m o n i t o r = h o s t = d b 3 . e x a m p l e . c o m u s e r = m o n i t o r [ p g b o u n c e r ] l i s t e n _ p o r t = 6 4 3 2
20. $ d b c o n n = p g _ c o n n e c t ( " p o r t = 6 4 3 2 d b n a m e = a p p 1 2 " ) ; d b c o n n = p s y c o p g 2 . c o n n e c t ( p o r t = 6 4 3 2 , d b n a m e = ' a p p 5 ' ) p s q l - p 6 4 3 2 a p p 2 1 _ m o n i t o r
21. client client client app PgBouncer DB DB DB DB DB app PgBouncer app PgBouncer
22. client client client app PgBouncer PgBouncer app PgBouncer app PgBouncer DB DB DB DB DB
23. Connection maintenance
24. p s q l - p 6 4 3 2 p g b o u n c e r
25. = > P A U S E a p p 1 2 ;
26. = > R E S U M E a p p 1 2 ;
27. Online PostgreSQL restart # ! / b i n / s h p s q l - h p g b h o s t - p 6 4 3 2 - c ' P A U S E a p p 1 2 ' s e r v i c e p o s t g r e s q l r e s t a r t p s q l - h p g b h o s t - p 6 4 3 2 - c ' R E S U M E a p p 1 2 '
28. = > K I L L d b ;
29. = > K I L L p r o d u c t i o n ;
30. Online PgBouncer restart p g b o u n c e r - R / e t c / p g b o u n c e r / p g b o u n c e r . i n i
31. Pain points access control no p g _ h b a . c o n f no SSL (stunnel?) managing pool sizes future maintenance of PgBouncer need more features → PgPool?