[asterisk-dev] func_odbc auto commit at answer time

[Dennis Buteyn]

On 2/18/21 12:53 PM, Jaco Kroon wrote:
> Hi All,
>
> So I've been updating some code to use func_odbc vs previous solutions
> and have bumped into a few concerns (1 I'd appreciate feedback on the
> review, 2 is prelude to 3, which is the main issue currently):
<snip>
> 2.  Too many queries resulting in the database server (which is set for
> maximum persistence) being unable to keep up, so enters
> ODBC(transaction,mydb)=meh + ODBC(forcecommit)=1.  This sorts out the
> COMMIT rate since now things are getting grouped into transactions.

<snip>

Nothing is stopping you from using more than one database server. With 
ODBC its pretty easy to spread the load over several machines. But aside 
from throwing more metal at the problem, you may also want to review how 
you are using your database. Perhaps some queries are extra slow, maybe 
some queries can be combined, or perhaps a different storage engine is 
more suitable, etc. Transactions help prevent concurrent queries from 
stepping on each other's toes as well as allowing the database to delay 
disk flushes. Transactions however may also cause delays while waiting 
on row and table locks, potentially deadlocking (especially long-running 
ones).

Without diving deep into transactions, it sounds like your dialplan is 
doing lots of small modifications during the call. Databases like doing 
things in bulk, so perhaps collecting changes in channel variables 
before submitting them with a hangup handler will be more effective?

-- 
Dennis Buteyn
Xorcom Ltd