VoIP Mailing List Archives

[freeswitch-users at li...]

------ Start of attached email. Subject: Mod_lua: dbh open to many connections to database ------
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






------ End of attached email ------
_________________________________________________________________________

The FreeSWITCH project is sponsored by SignalWire https://signalwire.com
Enhance your FreeSWITCH install with disruptive priced SMS and PSTN services.
Build your next product on our scalable cloud platform.

Join our online community to chat in real time https://signalwire.community

Professional FreeSWITCH Services
sales@freeswitch.com
https://freeswitch.com

Official FreeSWITCH Sites
https://freeswitch.com/oss
https://freeswitch.org/confluence
https://cluecon.com

FreeSWITCH-users mailing list
FreeSWITCH-users@lists.freeswitch.org
http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
https://freeswitch.com

[freeswitch-users at li...]

------ Start of attached email. Subject: Re: Mod_lua: dbh open to many connections to database ------
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