[Freeswitch-users] Some help with my post-paid billing project

Rupa Schomaker rupa at rupa.com
Wed Oct 14 06:36:07 PDT 2009 

I would still suggest using mod_lcr for this...  If you have any real
volume, use postgresql with the prefix module.

It also supports IN lists, OR lists, optional quoting (since mysql is
retarded), and custom sql so you can interface with whatever stored
proc or deal with whatever database table you may need to support.

It also supports multiple profiles (so you can charge different
amounts based on some other criteria like account code) and...
interstate/intrastate/intralata rating.  probably other stuff I forgot
to mention...

Let me just recommend to reuse what already exists and improve on that.

On Wed, Oct 14, 2009 at 8:22 AM, Muhammad Shahzad
<shaheryarkh at googlemail.com> wrote:
> Oops, you are right, this is what happens when you are doing more then one
> thing at a time, i was writing a db function while replying in this email
> thread and confused LIKE with IN. There is one more mistake in my email if
> any one noticed, that is, string class in STL does not support negative
> length in method substr.
>
> Thanks for correction.
>
>
> On Wed, Oct 14, 2009 at 6:43 PM, Michael Giagnocavo <mgg at giagnocavo.net>
> wrote:
>>
>> Wouldn’t that be SQL “IN” instead of LIKE?
>>
>>
>>
>> First off, see if you can do this in memory. If it’s just a “rate sheet”
>> list of prefixes + billing, it’s probably not that much data. I’d guess
>> maybe 64-128 bytes per record (in Ruby it’s probably more, maybe 256 bytes?)
>> inside a hashtable. Even so, we’re only talking about 1.5 to 3GB of RAM.
>> Stick it in a nice performing dictionary of some sort and you’re set.
>>
>>
>>
>> Just as a quick test, on one core of a Core2, it takes about 150ms to do
>> 100K lookups against a 1M item hashtable (F#) – that’s just a 1 line loop I
>> tried out with no optimization, doing about 650K/sec. For comparison on the
>> same machine, looping on a simple “SELECT @@VERSION” command only achieved
>> 5000 executions/second on a single thread – that’s just the SQL overhead (to
>> a local SQL instance!).
>>
>>
>>
>> If the memory usage is too excessive, consider caching only subsets of the
>> prefixes. Keep track of which leading prefix (1 or 2 digits) are the most
>> used, and keep those in memory. Have a reasonably fast DB to fall back to.
>>
>>
>>
>> If you’re sure the storage requirements or the change frequency truly
>> eliminates keeping it in your own memory, here are some suggestions for
>> working on DB performance:
>>
>>
>>
>> A little perf gain is to pre-calculate possible lengths, so you’re
>> performing less lookups. Just have a table based on the first 2 or 3 digits,
>> giving you the valid prefix lengths. That can easily cut down the number of
>> lookups from 10-12 to, say, 3-4. Compare the perf of temp tables, CTEs, and
>> dynamic SQL “IN”.
>>
>>
>>
>> But I would be hesitant to do this from a higher level ORM or via dynamic
>> SQL queries:
>>
>> -          You’ll have a much harder time doing DB-specific
>> optimizations/hints
>>
>> -          Dynamic SQL needs to be parsed each time (possibly minor issue)
>>
>> -          If the ad hoc queries are not properly parameterized, query
>> plans might not get reused very well
>>
>> -          You can easily send many times more data to the server (your
>> entire SQL, versus just the sproc name + parameters)
>>
>>
>>
>> Really look into doing programmability on the server. There’s all sorts of
>> things you can do to minimize what you need to send to the database, and let
>> the DB engine figure out the most efficient way. You might want to use a
>> product that’s a bit more advanced than MySQL.
>>
>>
>>
>> And finally, test, test, test. You should have a nice benchmark with
>> several million rows and be able to execute, say, 10-50K lookups (on
>> multiple threads) after each change. Modify your query, execute it, _inspect
>> the execution plan_. Check your indexes – make sure they are covering the
>> query in the right way. (For example, if the table is (ID, Prefix, Price),
>> you want an index of (Prefix, Price).) Review possible hints (for example,
>> SQL Server usually needs a hint to choose a indexed view). Make sure your
>> transaction level is set appropriately (can you do the query without
>> locking?). Can you batch up several numbers in one command, saving on the
>> per-command overhead?
>>
>>
>>
>> Also consider it in light of the rest of your application. If everything
>> is in the same database, then you can probably do the entire rate call/save
>> cdr/update balances in a single command and transaction.
>>
>>
>>
>> For us, our initial draft of routing inside SQL Server performed well
>> under 100 calls/sec – not usable. After a few days of playing with things,
>> it went over 1000 calls/sec, which is comfortable enough for a $500 server.
>> This is matching 100 gateways and dialplans with several thousand entries
>> per dialplan, across 4 million+ routes, doing QBR/LCR in the process.
>>
>>
>>
>> -Michael
>>
>>
>>
>>
>>
>> From: freeswitch-users-bounces at lists.freeswitch.org
>> [mailto:freeswitch-users-bounces at lists.freeswitch.org] On Behalf Of Muhammad
>> Shahzad
>> Sent: Wednesday, October 14, 2009 1:55 AM
>> To: freeswitch-users at lists.freeswitch.org
>> Subject: Re: [Freeswitch-users] Some help with my post-paid billing
>> project
>>
>>
>>
>> I fully agree that direct matching is much faster then pattern matching in
>> SQL.
>>
>> One of my clients had same problem, he had around 12 million number
>> prefixes in a table and during each call an AGI script use to query that
>> table to find longest prefix match, but this use to take like 3-5 seconds
>> even with indexed columns. So after a lots of R & D we come up with
>> following logic,
>>
>> 1. We break the destination number length-wise, e.g. suppose number is
>> 923344224088 then length chunks would be,
>>
>> 923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442,
>> 923344, 92334, 9233, 923, 92, 9
>>
>> 2. Then use SQL LIKE function in WHERE clause (you can also use SQL OR
>> function if your DBMS doesn't support SQL LIKE function), and pass all these
>> chunks to it, e.g.
>>
>> WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224,
>> 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)
>>
>> 3. Lastly we ORDER the result by prefix length, e.g.
>>
>> ORDER BY LENGTH(prefix) DESC LIMIT 1
>>
>> 4. The complete query will be,
>>
>> SELECT * FROM prefixes
>> WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224,
>> 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)
>> ORDER BY LENGTH(prefix) DESC LIMIT 1
>>
>> Now the query takes less then 150 ms to execute. :-)
>>
>> Here is an STL method that can generate this query, i am sure you can
>> convert it to any programming language of your choice easily.
>>
>> =========================================================
>> std::string GetQuery(std::string destination) {
>>         std::string query = "SELECT * FROM prefixes WHERE prefix LIKE ('"
>> + destination;
>>
>>         for(int i=1; i<destination.length(); i++) {
>>                 query += "','" + destination.substr(0, (i * -1));
>>
>>         }
>>
>>         query += "') ORDER BY LENGTH(prefix) DESC LIMIT 1";
>>         return query;
>> }
>> =========================================================
>>
>> I am pretty sure this query is 100% ANSI SQL compatible
>> (http://en.wikipedia.org/wiki/SQL).
>>
>> Thank you.
>>
>> On Wed, Oct 14, 2009 at 10:15 AM, Michael Giagnocavo <mgg at giagnocavo.net>
>> wrote:
>>
>> In our testing with SQL Server, we found that executing several queries
>> for direct matches yielded far better performance than one query trying to
>> check prefixes. (The column was also part of the clustered index, but AFAIK
>> MySQL doesn’t support defining your own clustered indexes; you get the PK
>> always.)
>>
>>
>>
>> -Michael
>>
>>
>>
>> From: freeswitch-users-bounces at lists.freeswitch.org
>> [mailto:freeswitch-users-bounces at lists.freeswitch.org] On Behalf Of Diego
>> Viola
>> Sent: Tuesday, October 13, 2009 7:54 PM
>> To: freeswitch-users at lists.freeswitch.org
>> Subject: Re: [Freeswitch-users] Some help with my post-paid billing
>> project
>>
>>
>>
>> Wrong question.
>>
>> Is there a way to compare numbers with prefixes without using the prefix
>> module?
>>
>> Diego
>>
>> On Wed, Oct 14, 2009 at 1:36 AM, Diego Viola <diego.viola at gmail.com>
>> wrote:
>>
>> I'm using MySQL now but I will try PostgreSQL with the prefix module, is
>> there a way to do that without the prefix module and with regular SQL?
>>
>> Any examples?
>>
>> Diego
>>
>>
>>
>> On Tue, Oct 13, 2009 at 10:45 PM, Even André Fiskvik <grevenx at me.com>
>> wrote:
>>
>> What database are you using?
>> You could do this with regular SQL, but it would by a costly operation,
>> for PostgreSQL we're using the prefix module:
>> http://pgfoundry.org/projects/prefix/
>>
>> You can then match the closest prefix by using something like
>> "WHERE myprefix_col @> caller_destination_number ORDER BY LENGTH
>> (myprefix_col::text) LIMIT 1;"
>>
>>
>> Best regards,
>> Even André
>>
>> On 13. okt. 2009, at 23.53, Diego Viola wrote:
>>
>> > Hello,
>> >
>> > I'm trying to write a post-paid billing script, I have the CDR on my
>> > database and also a "rates" table, the CDR contains fields like
>> > caller_destination_number, variable_duration, etc. and the rates
>> > table contains: destination, prefix, rate (cost).
>> >
>> > The problem is that I can't just strip the destination number to
>> > take the prefix from it because I have to deal with destination
>> > numbers from different countries and they all have different prefix
>> > lengths... so I need to find another way to take the prefix from the
>> > destination number.
>> >
>> > Any ideas how to do this?
>> >
>> > Thanks,
>> >
>> > Diego
>> >
>>
>> > _______________________________________________
>> > FreeSWITCH-users mailing list
>> > FreeSWITCH-users at lists.freeswitch.org
>> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-
>> > users
>> > http://www.freeswitch.org
>>
>>
>> _______________________________________________
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>>
>>
>>
>>
>> _______________________________________________
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>>
>> --
>> ________________________________________________________
>> |
>>                        |
>> | FATAL ERROR
>> --- O X |
>> |_______________________________________________________|
>> |                        You have moved the mouse.
>>    |
>> | Windows must be restarted for the changes to take effect.   |
>> |                                        <OK>
>>                  |
>> ####################################/
>>
>>
>> Muhammad Shahzad
>> -----------------------------------
>> CISCO Rich Media Communication Specialist (CRMCS)
>> CISCO Certified Network Associate (CCNA)
>> Cell: +92 334 422 40 88
>> MSN: shari_786pk at hotmail.com
>> Email: shaheryarkh at googlemail.com
>>
>> _______________________________________________
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>
>
>
> --
> ________________________________________________________
> |
>                      |
> | FATAL ERROR                                                           ---
> O X |
> |_______________________________________________________|
> |                        You have moved the mouse.
>  |
> | Windows must be restarted for the changes to take effect.   |
> |                                        <OK>
>                |
> ####################################/
>
>
> Muhammad Shahzad
> -----------------------------------
> CISCO Rich Media Communication Specialist (CRMCS)
> CISCO Certified Network Associate (CCNA)
> Cell: +92 334 422 40 88
> MSN: shari_786pk at hotmail.com
> Email: shaheryarkh at googlemail.com
>
> _______________________________________________
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>
>



-- 
-Rupa

More information about the FreeSWITCH-users mailing list