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?
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:
- Please suggest what config to be in place so that pgpool doesnt consume high cpu with SSL.
- How to fix the SSL timeout issue.
