Mod_lua: dbh open to many connections to database
António Silva
asilva at wirelessmundi.com
Mon Mar 29 09:32:53 UTC 2021
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 at 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/ <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
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20210329/de080543/attachment-0001.html>
More information about the FreeSWITCH-users
mailing list