How to limit DB's connection using ODBC in dialplan?

#1

Hello Everyone,

Is there any way to limit the connection every time the dialplan is being used? We are making like 1k to 2k calls every minute thus making thousands of connection on our DB.

Dialplan

; AMD detection based from called_count
exten => 8380,1,Playback(sip-silence)
same => n,AGI(agi://127.0.0.1:4577/call_log)
same => n,NoOp(${DIALEDPEERNUMBER:12})
;same => n,NoOp(${ODBC_MARIADB(${DIALEDPEERNUMBER:12})})
same => n,GotoIf($[${ODBC_MARIADB(${DIALEDPEERNUMBER:12})}>=4]?AMD:NoAMD)
same => n(AMD),AMD(2000,2000,1000,5000,120,50,4,256)
same => n,NoOp(${AMDSTATUS})
same => n,NoOp(${AMDCAUSE})
same => n,AGI(VD_amd.agi,${EXTEN})
same => n,AGI(agi-VDAD_ALL_outbound.agi,NORMAL-----SO)
same => n,Hangup()
same => n(NoAMD),AGI(agi-VDAD_ALL_outbound.agi,NORMAL-----SO)
same => n,Hangup()

Here’s the entry on func_odbc.conf

[MARIADB]
dsn=asterisk
readsql=select called_count from vicidial_auto_calls a inner join vicidial_list b on a.lead_id=b.lead_id where a.phone_number=${ARG1} limit 1

Entry on res_odbc.conf

[asterisk]
enabled=>yes
dsn=>mariaodbc
usernanme=>cron
password=>2fCjO4kJpK4E
pre-connect=>yes
max_connections=>1
#2

What version of Asterisk are you using? Recent versions obey the max_connections options.

#3

Hello Jcolp,

I am using version 13.

#4

That’s not specific enough. There have been over 27 releases of Asterisk 13.

#5

Hello Jcolp,

I am using this verison.

Version: 13.21.0

#6

What does “odbc show” tell you for maximum connections and number of connections in use?

#7

Hello Jcolp,

We are currently not using it.

ODBC DSN Settings
-----------------

  Name:   asterisk
  DSN:    mariaodbc
    Last connection attempt: 1969-12-31 19:00:00
    Number of active connections: 1 (out of 1)

Should I adjust the max_connections on res_odbc?

#8

The default is 1 connection, so from res_odbc there would only ever be a single ODBC connection.

#9

Hello Jcolp,

Should I adjust the max_connection to its max value? Seems like since the max_connection was set to 1 therefore causing to open another connection.

#10

It doesn’t open another connection if exceeded. It sets the maximum there can be at any given time. When you ran the command, there was 1 connection. If you are seeing other connections then it is unlikely they are from res_odbc itself.

#11

Hello Jcolp,

Let’s say we will be calling 1k calls every minute on each dialer. Having max value on the max_connections should help limiting opening new connection on each query?

#12

It will set the maximum number of concurrent connections. It’s up to you what the value should be.

#13

I should also add that connections aren’t closed immediately, they are reused automatically - thus saving a disconnect/connect.

#14

But that does affects the connections on the DB itself? on each query?

#15

It affects anything using res_odbc in Asterisk. If you have external things touching the database, they will not be limited.

#16

Hello Jcolp,

Let’s say the first call went through to extension 8380, so technically it would open one connection since I am using ODBC application to make query on the DB. Does it mean if the second call that would go through to the same extension would still use the same connection? Interval of the first and second attempt would only be less than 1 to 2 seconds.

Regards,

Jessie Kid Fernando

#17

It would end up using the same connection.

#18

Hello Jcolp,

Thanks. So having a max connection would allow me to have multiple calls using simultaneous connections passing on the same extension?

Regards,

Jessie Kid Fernando

#19

The connection is not used simultaneously. One will use it, the others will wait to use it, a new one will use it, and so on. Setting max connections to greater than 1 increases the number of max connections and thus other users won’t have to wait.

1 Like
#20

Hello Jcolp,

What are cons when other calls need to wait for the connection to be used?

Regards,

Jessie Kid Fernando