Still investigating the issue, using the debug_sql, I notice that the connection is correctly open and release - my script is doing what is expected:

2021-03-29 10:49:34.623831 [ALERT] freeswitch_lua.cpp:371 Reuse Unused Cached DB handle db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface" [Database interface prefix: pgsql]
2021-03-29 10:49:34.623831 [ALERT] freeswitch_lua.cpp:372 DBH handle 0x55aa3c24e110 Connected.
2021-03-29 10:49:34.623831 [ALERT] freeswitch_lua.cpp:401 DBH handle 0x55aa3c24e110 released.


Which explain why the max_db_handle setting is not affecting the number of connections opened in postgres, 
Condition: 
while(runtime.max_db_handles && sql_manager.total_handles >= runtime.max_db_handles && sql_manager.total_used_handles >= sql_manager.total_handles) {

But on release:
sql_manager.total_used_handles--;

So for FS the connection is closed but is not coherent to Postgres. 


During the start of sipp it works as expected I don't see more than 3 open connections to postgresql but during the increase of new stablished calls, it starts to open more connections to postgresl and don’t re-use the open handlers. I can’t find out why... 

Any hint to see what is triggering this behaviour?

Thank you 




The output on postgresql open process:
2021-03-29 11:17:45
root     16611  0.0  0.0   8604   892 pts/1    S+   11:17   0:00  |       \_ grep cmcore
postgres  8970  0.2  0.0 211444 13108 ?        Ss   11:16   0:00  \_ postgres: postgres cmcore [local] idle
postgres  8971  1.7  0.0 211444 13136 ?        Ss   11:16   0:01  \_ postgres: postgres cmcore [local] idle
postgres 11885  4.5  0.0 211444 13756 ?        Rs   11:16   0:02  \_ postgres: postgres cmcore [local] idle
2021-03-29 11:17:46
root     16620  0.0  0.0   8604   884 pts/1    S+   11:17   0:00  |       \_ grep cmcore
postgres  8970  0.2  0.0 211444 13108 ?        Ss   11:16   0:00  \_ postgres: postgres cmcore [local] idle
postgres  8971  1.7  0.0 211444 13136 ?        Ss   11:16   0:01  \_ postgres: postgres cmcore [local] idle
postgres 11885  4.4  0.0 211444 13756 ?        Ss   11:16   0:02  \_ postgres: postgres cmcore [local] idle
2021-03-29 11:17:47
root     16645  0.0  0.0   8604   888 pts/1    S+   11:17   0:00  |       \_ grep cmcore
postgres  8970  0.2  0.0 211444 13108 ?        Ss   11:16   0:00  \_ postgres: postgres cmcore [local] idle
postgres  8971  1.7  0.0 211444 13136 ?        Ss   11:16   0:01  \_ postgres: postgres cmcore [local] idle
postgres 11885  4.4  0.0 211444 13756 ?        Rs   11:16   0:02  \_ postgres: postgres cmcore [local] SELECT
2021-03-29 11:17:48
root     16669  0.0  0.0   8604   828 pts/1    S+   11:17   0:00  |       \_ grep cmcore
postgres  8970  0.2  0.0 211444 13108 ?        Ss   11:16   0:00  \_ postgres: postgres cmcore [local] idle
postgres  8971  1.7  0.0 211444 13136 ?        Ss   11:16   0:01  \_ postgres: postgres cmcore [local] idle
postgres 11885  4.4  0.0 211444 13756 ?        Ss   11:16   0:02  \_ postgres: postgres cmcore [local] idle
postgres 16655  0.0  0.0 211444 13160 ?        Ss   11:17   0:00  \_ postgres: postgres cmcore [local] idle
postgres 16656  0.0  0.0 211444 13160 ?        Ss   11:17   0:00  \_ postgres: postgres cmcore [local] idle
postgres 16658  0.0  0.0 211444 13128 ?        Ss   11:17   0:00  \_ postgres: postgres cmcore [local] idle
postgres 16659  0.0  0.0 211444 13152 ?        Ss   11:17   0:00  \_ postgres: postgres cmcore [local] idle
postgres 16660  0.0  0.0 211444 13132 ?        Ss   11:17   0:00  \_ postgres: postgres cmcore [local] idle
postgres 16661  0.0  0.0 211444 13164 ?        Ss   11:17   0:00  \_ postgres: postgres cmcore [local] idle
postgres 16663  0.0  0.0 211444 13164 ?        Ss   11:17   0:00  \_ postgres: postgres cmcore [local] idle


And freeswitch db_cache status: (for sofia I use a different connection than the used for lua script)

**************
2021-03-29 11:17:45
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 14296
Flags: Unlocked, Detached(0)
Creator: mod_callcenter.c:609
Last User: src/switch_console.c:253
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 13359
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 7333
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 1020
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 42779
Flags: Unlocked, Detached(0)
Creator: mod_callcenter.c:609
Last User: sofia_glue.c:2833
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 13398
Flags: Unlocked, Detached(0)
Creator: sofia_glue.c:2833
Last User: mod_callcenter.c:609
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3469
Total used: 5
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2833
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3471
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3471
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3471
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3471
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3471
Total used: 1
Flags: Locked, Attached(1)
Creator: src/switch_core_sqldb.c:2276
Last User: 
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3471
Total used: 3
Flags: Locked, Attached(1)
Creator: src/switch_core_sqldb.c:3636
Last User: src/switch_core_sqldb.c:2276
13 total. 7 in use.

**************
2021-03-29 11:17:46
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 14725
Flags: Unlocked, Detached(0)
Creator: mod_callcenter.c:609
Last User: src/switch_console.c:253
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 13580
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 7402
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 1
Total used: 1027
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 42823
Flags: Unlocked, Detached(0)
Creator: mod_callcenter.c:609
Last User: sofia_glue.c:2833
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 13408
Flags: Unlocked, Detached(0)
Creator: sofia_glue.c:2833
Last User: mod_callcenter.c:609
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3470
Total used: 5
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2833
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3472
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3472
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3472
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3472
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3472
Total used: 1
Flags: Locked, Attached(1)
Creator: src/switch_core_sqldb.c:2276
Last User: 
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3472
Total used: 3
Flags: Locked, Attached(1)
Creator: src/switch_core_sqldb.c:3636
Last User: src/switch_core_sqldb.c:2276
13 total. 7 in use.

**************
2021-03-29 11:17:47
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 8
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 16
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 18
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 18
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 1
Total used: 13
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 1
Total used: 8
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 0
Total used: 15211
Flags: Unlocked, Detached(0)
Creator: mod_callcenter.c:609
Last User: src/switch_console.c:253
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 1
Total used: 13747
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 1
Total used: 7485
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/postgresql/ user=postgres dbname=cmcore",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 1
Total used: 1035
Flags: Unlocked, Detached(0)
Creator: freeswitch_lua.cpp:371
Last User: freeswitch_lua.cpp:371
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 1
Total used: 42864
Flags: Unlocked, Detached(0)
Creator: mod_callcenter.c:609
Last User: sofia_glue.c:2833
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 1
Total used: 13426
Flags: Unlocked, Detached(0)
Creator: sofia_glue.c:2833
Last User: sofia_glue.c:2833
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3472
Total used: 5
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2833
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3474
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3474
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3474
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3474
Total used: 2
Flags: Locked, Attached(1)
Creator: sofia_glue.c:2602
Last User: src/switch_core_sqldb.c:2276
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3474
Total used: 1
Flags: Locked, Attached(1)
Creator: src/switch_core_sqldb.c:2276
Last User: 
db="host=/var/run/pgsql-fs user=freeswitch password=",type="database_interface"
Type: DATABASE_INTERFACE
Last used: 3474
Total used: 3
Flags: Locked, Attached(1)
Creator: src/switch_core_sqldb.c:3636
Last User: src/switch_core_sqldb.c:2276
19 total. 7 in use.




--
Saludos / Regards / Cumprimentos
António Silva




On 23 Mar 2021, at 15:49, António Silva <asilva@wirelessmundi.com> wrote:

Hi,

After migrate an old server from debian jessie to debian buster, hit a problem of cpu load increasing and block the server at 100% of cpu usage. I notice that was the number of postgresql open process.

Doing a simple sipp of 40cps to stress the server I notice that the problem is in a lua script, the script check if the caller is a table (database postgres) and allows or not the call.
in debian jessie the db handles open (using db_cache status) are  less than 20, but now in debian buster it scales to more that 100, and I start to see error messages that postgress cannot open more connections (that is true I’ve limited to 100 connections).

Anyone experience this problem?

I also try to set the max-db-handles open to 50, in switch.xml, but it ignores this parameter - I think that is only use in internal modules and not for lua scripts… 
Can I limit the number of open connections when using FS dbh?
 

The function I get to fetch the result:
filename = "pgsql://host=/var/run/postgresql/ user=postgres dbname=list"

callback_query_fetch = function (row)
if (single_row == true) then
result = row
return 1 -- break the loop
end
if (result == false) then result = {} end
table.insert(result, row)
return 0
end

function db.fetch_single(filename, sql)
local dbh = freeswitch.Dbh(filename)
result = false
single_row = true
if (dbh:query(sql, callback_query_fetch) == false) then
dbh:release()
return false
end
dbh:release()
return result
end



--
Saludos / Regards / Cumprimentos
António Silva