[asterisk-dev] RealTime, Large Dialplans, and headaches... got any ideas?

[Luigi Rizzo]

On Tue, Mar 27, 2007 at 02:35:05PM -0600, Steve Murphy wrote:
> OK, I just closed 9037 after a conversation with kpfleming.
> Here is the situation:
> 
> 9037's reporter is complaining that with a large dialplan, in order to
> look up an extension, and do the pattern matching, asterisk pulls every
> extension from the context in the DB, and tries to find the best match.
> When the dialplan gets big, and you have hundreds/thousands (or more) of
> extensions, then this takes a while. The user was reporting 5 or more
> seconds.
> 
> I've been working on a search-tree algorithm that would find an
> extension match a bit faster in a large dialplan. It's speed is a
> function of the length of the patterns, not of how many extensions there
> are. I thought I might apply this to the RT extensions, and keep an
> in-mem copy of the search tree... but it depends on a lot of things,
> like being able to discern an update to the DB of the dialplan, so you
> can reform (or spot edit, which I believe is possible) the in-mem search
> tree.
> 
> Kevin pointed out:
> 
>         This is impossible because you have no way of knowing when the
>         database
>         is modified. Even if you did, you'd have to resort to rebuilding
>         the
>         'search tree' in memory, and if the dialplan is large enough
>         then the
>         other issue comes into the picture as well.
>         
>         This is an artifact of the way the Realtime system is
>         architected, which
>         means that it is broken. It is not fixable in the current
>         architecture.
> 
> If someone in the community has a brilliant flash of inspiration as to
> how this problem could be solved, we'd love to hear about it! Any ideas?

>From the description i would think that the real bottleneck is
the thousands queries to the database, rather (or before) the size
of the dialplan itself.

Googling a bit, it seems that you can get the modification date of
a database table with a "SHOW TABLE STATUS" query, and this would
at least help to know if the cache is in sync.

However, i have no idea if there is any provision in the current code
to load the entire realtime database in memory.
If yes, then you could do a quick 1-query check to verify that the
cache is in sync, and then do the lookup from memory.

	cheers
	luigi