dibyanduroy · GitHub

During peak load application generate approx: 75000 average transactions per seconds to DB.
Left side, without SSL, PGPool consume only 25% of CPU and postgres consume 6.5% of CPU.
Right side, with SSL enabled at both pgpool and postgres, pgpool consume 75% of CPU and postgres consume 10% of CPU.
So with SSL, postgres cpu consumption increased by 54% and pgpool consumption increased by 200%.
How to setup the pgpool so that the cpu consuptions increase of postgres and pgpool are proportionate?

Image

These are the config of postgres and pgpool2 for without SSL
ssl= off

with SSL
cert are openssl

ssl = on
ssl_key = '/etc/ssl/private/cert.key'
ssl_cert = '/etc/ssl/certs/cert.crt'

addtional pgpool2.conf:

enable_pool_hba = on
pool_passwd = 'pool_passwd'
allow_clear_text_frontend_auth = on
process_management_mode = dynamic
process_management_strategy = gentle
num_init_children = 2500
min_spare_children = 500
max_spare_children = 1000
max_pool = 2
child_life_time = 5min
child_max_connections = 100
connection_life_time = 0
client_idle_limit = 0

pool_hba.conf

local   all         all                               trust
host    all         all         samenet               md5
hostssl all         all         0.0.0.0/0             md5

pg_hba.conf

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             samenet                 md5
hostssl all             all             0.0.0.0/0               md5

our java application hosted in another subnet use sslmode=require to connect to pgpool

pgpool2 version:
4.5.5-1.pgdg22.04+1

Additionally we are getting SSL connection timeout errors at application log, which doesn't generate any log at pgpool or postgres.

There was an error getting a DBCP datasource.
java.lang.RuntimeException: Unable to obtain a connection from the underlying connection pool
Caused by: org.postgresql.util.PSQLException: SSL error: Read timed out

These are the java application connection parameters

<url>jdbc:postgresql://192.168.1.10:9999/db?sslmode=require</url>
<driver-class>org.postgresql.Driver</driver-class>
<username>db_user</username>
<password>db_user_password</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>240</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<pool-max-idle>20</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>60</pool-remove-abandoned-timeout>
<validation-query>select version();</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
<pool-test-on-borrow>false</pool-test-on-borrow>
<pool-test-while-idle>true</pool-test-while-idle>

We dont have this error when ssl is off. SSL is key security requirement we need to comply.

There are two opens:

  1. Please suggest what config to be in place so that pgpool doesnt consume high cpu with SSL.
  2. How to fix the SSL timeout issue.

Read the original on github.com ↗