A very big amount of sessions with database

Question
1

Hi,

I work with privacyidea 2.20.1, which connected with oracle database on external server. All is fine, but we noticed a big amount of database sessions with privacyidea user (80 ~ 100). My DBA wants to decrease this amount, 10, for example.
I found some places to do it:

  1. set pool=1 and recycle=60 in user sql resolver settings, because users table in the same db, as privacyidea
  2. set PI_AUDIT_POOL_SIZE=1 and PI_AUDIT_POOL_RECYCLE=60 in pi.cfg file
  3. set SQLALCHEMY_POOL_SIZE=1, SQLALCHEMY_POOL_RECYCLE=60, SQLALCHEMY_MAX_OVERFLOW=3 in pi.cfg file

But this actions not gave any effect! I see the same amount of sessions…

I will be very glad to see every helpful answer

Regards,
Alexander

2

Please provide more information about your setup and especially the usage/configuration of the database.

Do you only use the said database as token database, as audit log or also as the user store?

How does your configuration look like (any caching, user caching)
These are all thinks you need to take into account when looking at this.

3

PI.cfg

PI_HSM: default

PI_NO_RESPONSE_SIGN: True

PI_LOGFILE: /var/log/privacyidea/privacyidea.log

PI_AUDIT_KEY_PUBLIC: /etc/privacyidea/public.pem

PI_PEPPER: some_value

PI_AUDIT_POOL_RECYCLE: 60

PI_ENCFILE: /etc/privacyidea/enckey

PI_EXTERNAL_LINKS: True

SQLALCHEMY_DATABASE_URI = 'oracle+cx_oracle://db_user:db_password@db_server:1521/?service_name=service_name?server=pooled’

PI_AUDIT_POOL_SIZE: 1

PI_AUDIT_MODULE: privacyidea.lib.auditmodules.sqlaudit

PI_LOGLEVEL: 40

PI_AUDIT_KEY_PRIVATE: /etc/privacyidea/private.pem

SUPERUSER_REALM: [realms_list]

This database sheme contains token database, audit log and user store (single db for all, I created user store table near privacyidea tables).

System Base Configuration

recovery.identifier: smtp_conf

sms.identifier: sms_otp

DefaultChallengeValidityTime: 600

totp.timeStep: 30

hotp.hashlib: sha1

UserCacheExpiration: 0

UiLoginDisplayRealmBox: False

AutoResync: False

splitAtSign: True

timestamp: 1523622993

PrependPin: True

sms.Provider: privacyidea.lib.smsprovider.HttpSMSProvider.HttpSMSProvider

IncFailCountOnFalsePin: False

totp.hashlib: sha1

UiLoginDisplayHelpButton: False

ReturnSamlAttributesOnFail: False

failcounter_clear_timeout: 0

email.tls: False

radius.dictfile: /etc/privacyidea/dictionary

email.validtime: 600

email.identifier: smtp_inter

AutoResyncTimeout: 600

remote.verify_ssl_certificate: False

ReturnSamlAttributes: True

As for me, there is a problem with sqlalchemy. Sessions number increase to some amount (~ 100). If you don’t use db, all sessions must be closed, but they hang in inactive status…
As I see, session.close() used in audit and sql resolver, but I don’t see mechanism the internal db work.

4

Any suggestions are still being accepted.

5

Hi folks,

I’m facing the same issue; any suggetions?

Regards,