Support A2Billing :

provided by Star2Billing S.L.

Support A2Billing :
It is currently Wed Apr 10, 2024 9:05 pm
Hosted Voice Broadcast


All times are UTC




Post new topic Reply to topic  [ 28 posts ]  Go to page Previous  1, 2
Author Message
 Post subject:
PostPosted: Wed Oct 10, 2007 4:21 am 
Offline
User avatar

Joined: Mon Apr 30, 2007 6:43 am
Posts: 1060
Location: Canada
Hey Stravos,

Here is an addition to your post. The reason that the query is returning more than just the longest match is that it is actually being done in PHP a few lines down the code.

Code:
      //1) REMOVE THOSE THAT HAVE A SMALLER DIALPREFIX
      $max_len_prefix = strlen($result[0][7]);
      for ($i=1;$i<count($result);$i++){
         if ( strlen($result[$i][7]) < $max_len_prefix) break;
      }   
      $result = array_slice ($result, 0, $i);


Now, since the result of the query is already sorted like this:

Code:
ORDER BY LENGTH(dialprefix) DESC


We can simply add a limit to the result like that:

Code:
ORDER BY LENGTH(dialprefix) DESC
LIMIT 1


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 10, 2007 8:22 am 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
Ah yes, thanks for your input there. I see it now. Swapping the ORDER BY clause for LIMIT 1 would introduce new bugs I think. I believe it's valid output to provide more than one row, so long as the prefixes are the same length. With my optimised query I think it would be reasonable to drop the ORDER BY clause entirely, but testing shows the penalty is unmeasurable so I didn't bother.

Well, it turns out it's trivial to adapt the rate engine to match prefixes in the manner described. Attached is a patch which should implement it.

I've not tested it at all yet... it may even contain syntax errors.
Caution: it's possible this patch may cause performance to drop significantly on MySQL. I can't tell because the documentation is too vague so feedback would be greatly appreciated.

Added after 3 hours 31 minutes:

Okay I've benchmarked this now (still only on Postgres) using the following:
Code:
$RateEngine = new RateEngine();
$cia_res = $A2B -> callingcard_ivr_authenticate($agi);
$testing_phonenumbers = explode(',','$INSERT LONG LIST OF REAL-WORLD NUMBERS');

foreach ($testing_phonenumbers as $this_number) {
        $RateEngine->Reinit();
        $A2B-> Reinit();
        $resfindrate = $RateEngine->rate_engine_findrates($A2B, $this_number, 5);
        echo "$this_number matched $resfindrate rates\n";
}

I tested it with a list of 908 unique numbers captured from real-world call records. There are over 200,000 rows in cc_ratecard split over around 17 tariffplans.

Before:
Code:
real    11m24.332s
user    0m1.340s
sys     0m0.210s
And after my patch to Class.RateEngine.php:
Code:
real    0m44.350s
user    0m4.976s
sys     0m0.683s


So across the set of 908 calls the original A2B libraries spent 1.3 seconds processing rates, whereas the patched version takes nearly 5 seconds. This is expected; there's more work being done by A2B. There's 3 times as much System overhead too, which again is expected due to the much larger number of queries passed to the SQL server.

However the total time taken including Postgres' share is quite different. Down from over 680 seconds to less than 45 seconds.

Less than 42 seconds now, and I've posted what I hope to be the final version of this patch. I'm already running it in production on branches/1.3.


Attachments:
File comment: it helps so much I couldn't resist porting it back to branches/1.3 too
dbperf.1.3.diff.txt [20.36 KiB]
Downloaded 530 times
File comment: for trunk
dbperf.trunk.diff.txt [23.07 KiB]
Downloaded 728 times
Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 25, 2007 9:30 am 
Offline

Joined: Thu Oct 19, 2006 9:56 am
Posts: 300
Location: Athens, Greece
I just came up with a very cunning (TM) way of implementing Stavros's idea purely in SQL!

Code:
CREATE OR REPLACE FUNCTION dial_exp_prefix(str TEXT) RETURNS TEXT[] AS $$
DECLARE
   slen INTEGER; ret TEXT[]; i INTEGER;
BEGIN
   slen := char_length(str);
   IF (slen > 12) THEN slen := 12; END IF;
   
   FOR i IN 0..slen LOOP
      ret := array_append(ret, substr(str,1,i));
   END LOOP;
   
   RETURN ret;
END; $$ LANGUAGE plpgsql STRICT STABLE;

.. or do that in PHP..
.. which, for each number to search returns an array like ARRAY['','1','12','123' ...]

and then, put in the sql clause
Code:
... WHERE dialprefix = ANY (dial_exp_prefix(dialednum))


Now, the query optimizer knows it has to make multiple indexed searches!


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 25, 2007 3:35 pm 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
Excellent idea there, xrg! Very cunning indeed! Blackadder himself would have been proud. I was keen to see if it would provide even greater performance gains due to decimating the number of SQL queries issued.

I'm having trouble making it fly though:
Code:
explain analyze select dialprefix from cc_ratecard where dialprefix = ANY(dial_exp_prefix('01392XXXXXX'));
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on cc_ratecard  (cost=0.00..11783.67 rows=81209 width=9) (actual time=2032.716..2032.716 rows=0 loops=1)
   Filter: (dialprefix = ANY (dial_exp_prefix('01392XXXXXX'::text)))
Total runtime: 2032.748 ms
Meh, it takes nearly 10 times longer than Areski's original query whilst not returning any results. It's still using a sequential scan, rather than an index scan. WTF is going on here?

Part of the problem is I can't get the function to return anything but an empty string:
Code:
select dial_exp_prefix('01392XXXXXX'::text);
dial_exp_prefix
-----------------

(1 row)
OK, no problem we'll prove the concept manually and worry about that later:
Code:
explain analyze select dialprefix from cc_ratecard where dialprefix = ANY(ARRAY['01392XXXXXX','01392XXXXX','01392XXXX','01392XXX',
'01392XX','01392X','01392','0139','013','01','0']) order by length(dialprefix) desc;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=19316.76..19519.78 rows=81209 width=9) (actual time=209.257..209.335 rows=49 loops=1)
   Sort Key: length(dialprefix)
   ->  Seq Scan on cc_ratecard  (cost=0.00..11580.65 rows=81209 width=9) (actual time=26.019..209.106 rows=49 loops=1)
         Filter: (dialprefix = ANY ('{01392XXXXXX,01392XXXXX,01392XXXX,01392XXX,01392XX,01392X,01392,
0139,013,01,0}'::text[]))
Total runtime: 209.461 ms
That's better. It's now returning the correct set of results (rows=49), and it's around 10% quicker than Areski's query. It's still using a sequential scan though.

The only way I could successfully force it to use an index scan was like:
Code:
explain analyze select dialprefix from cc_ratecard where (dialprefix = '01392XXXXXX' OR dialprefix = '01392XXXXX'
   OR dialprefix = '01392XXXX' OR dialprefix = '01392XXX' OR dialprefix = '01392XX' OR dialprefix = '01392X'
   OR dialprefix = '01392' OR dialprefix = '0139' OR dialprefix = '013' OR dialprefix = '01'
   OR dialprefix = '0') order by length(dialprefix) desc;
                                                                                                                                                                                                                QUERY PLAN                                                                                                                                                                                                               
----------------------------------------------------------------------------------
Sort  (cost=692.75..693.15 rows=163 width=9) (actual time=0.645..0.728 rows=49 loops=1)
   Sort Key: length(dialprefix)
   ->  Index Scan using ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix,
    ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix,
    ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix on cc_ratecard
    (cost=0.00..686.76 rows=163 width=9) (actual time=0.060..0.525 rows=49 loops=1)
         Index Cond: ((dialprefix = '01392XXXXXX'::text) OR (dialprefix = '01392XXXXX'::text)
         OR (dialprefix = '01392XXXX'::text) OR (dialprefix = '01392XXX'::text) OR (dialprefix = '01392XX'::text)
         OR (dialprefix = '01392X'::text) OR (dialprefix = '01392'::text) OR (dialprefix = '0139'::text)
         OR (dialprefix = '013'::text) OR (dialprefix = '01'::text) OR (dialprefix = '0'::text))
Total runtime: 0.887 ms
Excellent, now we've got the correct set of 49 results and due to the index scan it's the quickest query by far! Assuming MySQL sees similar improvements I think implenting this solution in PHP is the way to go, as it's more portable and doesn't add plpgsql to A2B's list of dependencies.

PS thanks again to Xrg for causing me to revisit this.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 25, 2007 4:03 pm 
Offline

Joined: Thu Oct 19, 2006 9:56 am
Posts: 300
Location: Athens, Greece
What version of Postgres do you have? Is plpgsql installed there?

Perhaps I have mistyped the stored procedure..

I have 8.2.4 and I get:
Code:
a2billing=> EXPLAIN ANALYZE SELECT id from cc_ratecard where
dialprefix = ANY( dial_exp_prefix('1392XXXXXX'));

Bitmap Heap Scan on cc_ratecard  (cost=42.75..146.47 rows=29 width=4) (actual time=0.158..0.165 rows=9 loops=1)
   Recheck Cond: (dialprefix = ANY (dial_exp_prefix('1392XXXXXX'::text)))
   ->  Bitmap Index Scan on ind_cc_ratecard_dialprefix  (cost=0.00..42.75 rows=29 width=0) (actual time=0.151..0.151 rows=9 loops=1)
         Index Cond: (dialprefix = ANY (dial_exp_prefix('1392XXXXXX'::text)))
Total runtime: 0.200 ms
(5 rows)

.. you see, Postgres is smart enough not to fully search the index 10 times but in one loop!


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 25, 2007 4:23 pm 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
Yes, plpgsql is installed otherwise creating the stored procedure bails out with an informative error message.

I was previously testing against Postgres 8.0.13, but I've moved to a different server where I'm running 8.2.4 and now the function returns valid results:
Code:
select dial_exp_prefix('01392');
     dial_exp_prefix
--------------------------
{"",0,01,013,0139,01392}


The planner on this machine still uses a sequential scan, but then it also does for the final query in my previous post. I think this is related to this table being so small.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 15, 2007 1:54 pm 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
:oops: The previous patches I posted did not necessarily return the same set of results as Areski's original query.
Attached is a much simpler patch which achieves the same speed up whilst not introducing a nasty bug. :mrgreen:


Attachments:
File comment: against trunk r420
dbperf.trunk.diff.txt [5.34 KiB]
Downloaded 719 times
File comment: against branches/1.3 r420
dbperf.1.3.diff.txt [5.41 KiB]
Downloaded 674 times
Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 28, 2007 7:20 pm 
Offline

Joined: Sun Mar 12, 2006 2:49 pm
Posts: 954
Location: Barcelona
Hi Steve,

Good patch !!!
I saw ur last commit saying it wasn't working for mysql,
I made a quick fix that should make it for both mysql and postgresql,
http://trac.asterisk2billing.org/cgi-bi ... ngeset/474

btw there is no need in our case to use the "LIKE with %"
as we reduce the length of the prefix


Quote:
WHERE (dialprefix = '47489011115' OR dialprefix = '4748901111' OR dialprefix = '474890111' OR dialprefix = '47489011' OR dialprefix = '4748901' OR dialprefix = '474890' OR dialprefix = '47489' OR dialprefix = '4748' OR dialprefix = '474' OR dialprefix = '47' OR dialprefix = '4' OR dialprefix='defaultprefix')
AND startingdate<= CURRENT_TIMESTAMP AND (expirationdate > CURRENT_TIMESTAMP OR expirationdate IS NULL OR LENGTH(expirationdate)<5)
AND startdate<= CURRENT_TIMESTAMP AND (stopdate > CURRENT_TIMESTAMP OR stopdate IS NULL OR LENGTH(stopdate)<5)
AND (starttime <= 6970 AND endtime >=6970)
AND idtariffgroup='3'
AND ( dnidprefix=SUBSTRING('474890111115',1,length(dnidprefix)) OR (dnidprefix='all' AND 0 = 0))
AND ( calleridprefix=SUBSTRING('6198613164',1,length(calleridprefix)) OR (calleridprefix='all' AND 0 = 0))
ORDER BY LENGTH(dialprefix) DESC


Let me know if this works well for you guys!

Cheers,
/Areski


Top
 Profile  
 
 Post subject: Re: DB optimisation for dialprefix
PostPosted: Mon Mar 03, 2008 1:09 pm 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
xrg wrote:
In a twist, a custom operator could even use Asterisk-style patterns to support prefixes like 12[3-6]XX5.
If anyone read this and hoped it would some day come to fruition, today is your day. 8)


Top
 Profile  
 
 Post subject:
PostPosted: Sat May 03, 2008 10:10 pm 
Offline

Joined: Thu Feb 08, 2007 12:54 pm
Posts: 14
Hi,
has anyone ever given thought to using memcached or eaccelerator for using optimized rate-table lookup ?

Essentially i was thinking along the following lines.
- Memecached(M) and eaccelerator(E) provide sort of apis to serialize data across a session or on a server global scope for php/php-cli. You can GET/PUT/REMOVE data from a shared mem cache
lookt at
http://bart.eaccelerator.net/doc/phpdoc ... erator_put
http://bart.eaccelerator.net/doc/phpdoc ... erator_get
http://bart.eaccelerator.net/doc/phpdoc ... lerator_rm

- the plan goes like follows.
Whenever there is any CRUD operation on any ratetable, your mark a particular field somewhere in monitor table as 'dirty'. Then you let the operation of CRUD take place on the DB as usual.

- At the beginning of each page (a2billing.php for ENGINE or modules.access.php/defines.php For WEB), a check is made for the value or this monitor table field. It can either be 'fresh' or 'dirty'

If it is 'fresh', fine. If it is 'dirty', you dump the data from table to a M or E global cache variable which is in format of
[key] =>{value1,value2,value3.....}
In our case it can be
[Prefix] =>{Countryname,Sellrate,buyrate,Disconcharge .....}
like
[91] => {India,0.3,0.1,0.0}....

-Now, All table lookups in AGI and ratesimulator need to be modifed to lookup in this cache variable rather than DB.

This approach is surely going to lessen/eliminate the load on mysql as CRUD on rates are done *very* infrequently. Just imagine, no request for ratetable being made to lookup in the table at all!! That constitutes the major case for mysql load.

Though, the logic for stripping down destination to prefix match will still need to be done, since you cant do things like "WHERE dialprefix = substr($dialednum, length(dialprefix))" in M/E.

If anyone has any positive comments on this, i volunteer to start work on this.


Regards,
Ashutosh Kumar
[email protected]


PS: Additionally, we can use M/E framework for caching the Configuration DB settings which areski has integrated into DB, since that would cause pages/agi to peep in the DB/config table every now and then to get values like 'play_audio' ,'is_dnid' etc.. That will be wise since config settings are also too less frequently go through CRUD operations.


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 04, 2008 11:36 am 
Offline

Joined: Sun Mar 12, 2006 2:49 pm
Posts: 954
Location: Barcelona
we got to think well about this, a flag for CRUD operation sounds weird at first but perhaps it can improve performance a lot!
lot of peoples have their voip traffic going in the same destination for hours or days and caching this properly at some point could be very neat indeed in terms of performance!

In addition of what you have say, I will advice you to look at the caching system in AdoDB we use it already in couples of place, like for instance, the API, to display ratecard, uses this to increase time respond and so don't execute the SQL query each time someone watch your rate plan!

About the Conf from the DB, that could be interesting as well even if I think that all the cost is rate engine query!

little note about mysql, try to enable caching on it, you will see a very huge difference ;-)

# New according to http://www.debuntu.org/2006/07/21/75-ho ... ponse-time
skip-name-resolve
query_cache_size = 52428800
query_cache_type = 1


Going to visit Cardiff now :D
seeyou later


Top
 Profile  
 
 Post subject:
PostPosted: Tue May 06, 2008 8:07 am 
Offline

Joined: Thu Feb 08, 2007 12:54 pm
Posts: 14
Hey areski! thats cool!! thanks for having a look into the approach.

Right now, more than the ratetable lookup, it gives me shivers to imagine the load on mysql/pg when you release the db configuration branch. Ratetablelookup is done only in a while for calls or ratesimulator, but db config READ will be done almost *everytime* from the web and ivr (defines.php and Class.A2Billing.php). Unless you have already implemented something tht caches/persists the config items from DB, lifes going to be difficult for us :(
Summarily, it doesnt seem wise that we always lookup a2billing["paypal_email"] in the database if its going to be always the same.

>>ADODB
i am not very sure if adodb persists across sessions. The reason that we have had to stress on lessening lookups to db is that across a cluster of a2b/openser, we noted that the limiting factor was mysql, however powerful macinhes you do have. Despite query cache, shm increase and all permuations of mysql engine types. The thing is how many connections you have got opened to the db from across multiple agi across multiple asterisk nodes. So, this is the variable we look at minimizing.

regards,
ashutosh

PS: A quick trick with db conf: We can use the dirty flag solution as mentioned above. Whenever flag is dirty, dump the db to a2billing.conf, then we proceed as our older ways. This way config writes are done to DB, via GUI, then flag=>dirty. Then you dump from DB to .conf.
This was Just a thought. After all, you are the boss. Salutes to you areski.
8)


Top
 Profile  
 
 Post subject: Re: DB optimisation for dialprefix
PostPosted: Thu Nov 12, 2009 2:36 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
Gentleman,

I believe I have some relevant comments in this thread re: 1.4 - I link it here because I believe we're talking about exactly the same block of code, though I'm referencing a different issue.

Thanks!
Andy

http://forum.asterisk2billing.org/viewtopic.php?f=22&t=6560&start=0


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 28 posts ]  Go to page Previous  1, 2
Auto Dialer Software


All times are UTC


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group