database performance problems

[leo]

Hi all,

we have some performance problems with the database. We have few agents (4-8) on a RATIO auto-dial outbound campaign over a database of 700,000 leads. Often agents get a call with a blank screen, and data about the lead comes after many seconds. This happens when the CPU of the server is busy doing the query to update the hopper, I suppose.

Since the query to fill the hopper is long & heavy while the one to fetch data about the lead is theoretically easy, we thought some tweaking with SQL priorities could fix the issue. We tried changing a SELECT to SELECT HIGH_PRIORITY in AST_VDauto_dial.pl, but with no luck.

astguiclient 2.0.4.1rc2, asterisk 1.4.10, ubuntu server 7.10, mysql 5.0.45, kernel 2.6.22-14-rt #1 SMP PREEMPT RT, calling over an E1 trunk.

Any ideas?

thank you very much for your help!

[i_magic]

What is your hopper level? Maybe bringing it down to 100 would help... just a shot in the dark.

TJ

[leo]

Thanks for your reply.

Hopper level is already 20, as we don't have many agents on the campaign. Too bad!

Leo

[pylinuxian]

700000 lead ... you are approaching the million. I suggest you use shorter lists just to see the effect.

to give you an example :
try to search one number in 10 numbers.
now try to find it in 700000 numbers.

once there, re-read scratch install, specificaly the part that talks about eaccelerator and the part about mysql my.ini file, those are the parts that maybe you have missed.

finaly one rule of thumb, the lighter the database the faster it is.

[mflorell]

First of all, upgrade Asterisk to at least 1.4.18, there are many problems with 1.4.10 that cause load issues and crashing with VICIDIAL.

Is everything running on the same server?

What is the loadavg when things start to slow down?

I would suggest moving yoru database to a separate server if possible.

700,000 records by itself is not a problem if you have a database server that can handle the load. We have done setups where the client has over 12,000,000 leads in the system without issues.

[leo]

Thanks for your reply.

We are still investigating ways to optimize the database performance for our needs. We have 700,000 leads spread over 1800 cities. In our case some 200 cities cannot be called at any time. These not-callable cities change often, so we want to keep them in the database and VICIDIAL-FILTER them out, with the vicidial filter

city not regexp '^(city1|city2|city3 ...)$'

this is unfortunately very slow and difficult to mantain.

We are thinking of creating a new table of non-callable cities, defining the vicidial filter as simply

city not in noncalleble_cities.city

(with noncallable_cities.city as foreign key for vicidial_list.city, and asking mysql to INDEX the 'city' fields).

Well, foreign keys are not available with MyISAM. Does vicidial use MyISAM and not InnoDB for some specific reason?

Any ideas?

Thanks a lot, Leo

[mflorell]

I have heard from VICIDIAL users that are using InnoDB with no issues, the storage engine should not cause incompatibilities since VICIDIAL is designed around MySQL 4.0 standards it does nto use a lot of advanced database functions.

[pylinuxian]

These not-callable cities change often, so we want to keep them in the database and VICIDIAL-FILTER them out, with the vicidial filter
city not regexp '^(city1|city2|city3 ...)$'
this is unfortunately very slow and difficult to mantain.

Create a table 'cities' of two fields : (city_name, statut) where city_name is name of city and statut will take two values 1 or 0 for active or not active.

Code: Select all

echo "select city_name from asterisk.cities where statut = 0;" | mysql -ucron -p1234 | while read i
do
ii=\'$i\'
echo "update asterisk.vicidial_list set list_id = 999999 where city = $ii;" | mysql -ucron -p1234
done


this is ofcourse a very simplified version, the idea is that it hides the cities by giving them a false list_id. you will have to put up another script to get them the right list_id back again ... so add a field in the cities table & call it list_id so that you could roll back to it.

ofcourse this keeps vicidial original code un-touched so smoother upgrades can happen and also maintenance will be easy as you have to deal only with the cities table use some GUI.

[mflorell]

Just put up a posting on issues with using InnoDB with VICIDIAL:
http://www.eflo.net/VICIDIALforum/viewt ... 6376#26376