Database locks because of inbound call search in alt_phone

[okli]

Stock Vicibox 6.03
Cluster 1 DB, 6 diallers, 1 Web, 1 Slave.
Vicidial version: 2.10b0.5
SVN Version: 2173

We got database locks for more than 20 seconds, up to a minute on several occasions lately, since we started a new campaign.

After some digging, it turned out that most likely the issue is with inbound calls to a specific DID, where DID setting In-Group Call Handle Method is set to CIDLOOKUPRCALT.
Such calls to this DID, apparently from unknown or alt phone, create records in MasterDB-slow.log like this:

Code: Select all

# Time: 150612 12:33:28
# User@Host: cron[cron] @  [192.168.200.22]
# Thread_id: 37724435  Schema: asterisk  QC_hit: No
# Query_time: 26.609920  Lock_time: 0.000034  Rows_sent: 0  Rows_examined: 7987121
SET timestamp=1434108808;
SELECT lead_id,called_count from vicidial_list where alt_phone='7500XXXXXX'  order by last_local_call_time desc limit 1;

Please note Query_time: 26.609920

In the DID settings, campaign and list are set properly. The campaign has 2 short lists only.

Why the resulting query missed the list clauses?
The way I am reading agi-VDAD_ALL_inbound.agi, the following 2 clauses where list_id(s) are picked, do not apply for xxxxxxALT searches, meaning at least LOOKUPRLALT and LOOKUPRCALT methods would not get any list_ids:

Code: Select all

if ($call_handle_method =~ /LOOKUPRL$/)
         {$listSQL = "and list_id='$list_id'";}
      else
         {
         if ($call_handle_method =~ /LOOKUPRC$/)
            {
            ### Grab all lists associated with Scampaign_id

I've looked at the latest version of agi-VDAD_ALL_inbound.agi and couldn't find any related changes since our version.

As a side question, are there any issues adding another index alt_phone in vicidial_list table?

[mflorell]

Good catch! Please post this to the Issue Tracker so we can get this fixed and track the testing of this problem.

As for a new index on alt_phone, it shouldn't be a problem.

[okli]

Thanks Matt.

Here is the mantis report:
http://www.vicidial.org/VICIDIALmantis/view.php?id=872

As for the alt_phone index, isn't it a good idea to have it by default, in stock install?
We have ~8 mill. records vicidial_list on a decent database server, and search for lead in the web interface with Alt. Phone search: YES does take some time.

[mflorell]

Thanks for posting to the tracker.

As for alt_phone being a default index, we have hesitated to add too many non-essential indexes because every index you add increases the time it takes to optimize or recover your vicidial_list table. It would probably be fine for most systems to have it indexed, but there are some clients that it would noticeably hurt, mostly the ones on slower database hardware. This is the balancing act we always have to do when changing defaults in Vicidial.

[okli]

Got it, thanks.

[williamconley]

I wonder if there's a list of "probably could be indexed IF you use this function a lot" fields?

I seem to recall indexing at least a couple others at one point or another ...

[mflorell]

There are several fields that could be indexed if you need to, but they usually depend on the specific setup and how the system is used. If you indexed everything in vicidial_list it could take three to four times longer to optimize or rebuild the table and that could cause some serious problems on some systems.

[williamconley]

We've already had many clients with large databases experiencing "far too long" optimization time.

I was just wondering if there was a list of fields that could be indexed and the reasons why they might be.

Such as: If you have LOTS of inbound calls and agents searching for clients by field XXX, it's a good idea to index that field because it's not presently indexed.

Sort of a "wish list" for indexing fields presently being avoided to keep Everyone's system fast, but could be implemented in advance of a troubleshooting run for those who actually may need it based on their expected usage of Vicidial.

I'd make the wiki page, if I had more than one item to put in it. LOL (going through our tickets mayor may not result in a hit on the topic, same with email ...).