vicidial.org

Hello guys,

we have a very old vicidial Version 2.2.1 running for about 6 years.
We did some customizing - thats the reason why we cant upgrade to a newer version.

Problem: sometimes an agent in talking (inbound or outbound) and suddenly a second customer getting in the conference.
The agent could talk with both customers.
Sometimes it happens 1 seond after receiving the first call and sometimes after two minutes.

Perhaps anyone has an idea where the problem could be located?
An agent only gets an call if the status = closer/ready and lead_id < 1

Thanks for any hint.

Regards
Philip

The version you are using is over 7 years old. It has bugs and serious security holes. I would strongly encourage you to upgrade to the latest svn/trunk version.

I know, but we cant upgrade.
The system is absolutely stable and works fine, thats the only problem we have.

We hoped that someone knows the problem and has a good hint for us (-:

I know exactly what the problem was, and it required a rewrite of several sections of the code to fix it. This is not something you can just patch, which is why I strongly recommend your system be upgraded.

OK matt i understood that you cant support us for money.
Upgrade isnt possible at the moment. We need about 3 month to adapt our changes.
The customer terminates the contract if we do not fix the problem soon. so we have to find the problem.

Is there any guy how knows the problem and perhaps can help us for money?

Best regards
Philip

That's just it, there is no single problem. Agents getting more than one call at a time was a symptom of several different problems, and it took years and multiple sections of code rewrites across dozens of scripts to fix it. The diff file for the vicidial.php script alone across those 7+ years is over 19 thousand lines long, and that's just one of the files involved.

We will not resign before we tried it.

So the problem is vicidial.php and not an AGI script?
- agi-VDAD_ALL_inbound.agi
- agi-VDAD_ALL_outbound.agi

If yes that will help us

The problems were in all of those processes, the perl scripts, the AGI scripts and the PHP scripts. The processes had to be rewritten to work differently to prevent the multiple call issues.

I cant believe what you say - it doesnt make sense

First we will find out in which status the user is during he gets the second call in vicidial_live_agents: READY/CLOSER or INCALL.

You do know that I created VICIdial by myself and I wrote most of the code and I am still the maintainer of the codebase, right?

There were multiple causes for that one problem. I believe we've finally fixed them all as of March of 2017. But there were several major code rewrites to fix each of the issues involved over the last several years.

Yes i know. sorry my english is not so good.
I mean that i cant believe that we cannot write a bugfix without updating to the newest version.

As i told via email its OK if we write a dirty bugfix to fix the problem asap.
Next year we will upgrade to the newest version step by step - because this is a huge project for us.

We will try to add a new enum column with 0,1 in vicidial_live_agents called "incall".
After terminating a call the value set to 0
After receiving a call the value set to 1
Before an agent receiving a call we check if the value is 0

This will be our first try. simple easy and only some hours work

Some of the fixes for that one issue involved database schema changes to get everything to work right. Only changing the code won't do it. Only checking if an agent is INCALL won't catch all cases where it happens either.

We have no choice - we have to try it. If we fail we loose our customer and its game over.

If we catch 90% of the cases then perhaps we save some time.
so we can start adapting our changes to the newest vicidial version january.

Secondly we want to optimize database
- more indexes for the some tables (vicidial_live_agents,vicidial_mananger, vicidial_auto_calls,live_channels)
- perhaps deleting values in vicidial_manager after 5 minutes?

putting additional indexes on MEMORY tables like vicidial_live_agents isn't going to help at all.

You can already change the crontab parameters for the vicidial_manager table to clear out more frequently, for example:

### flush queue DB table every hour for entries older than 1 hour (changed to 10 minutes)
1,11,21,31,41,51 * * * * /usr/share/astguiclient/AST_flush_DBqueue.pl --seconds=600 -q

Although, keep in mind that clearing some entries too quickly could actually cause other problems, so I would recommend trying 10 minutes as is shown in the example above.


Also, putting your database on a high-end database server would help. one with more RAM, more CPU cores and separate partitions for data and os/applications as well as a proper LSI Logic MegaRAID caching RAID card.

mflorell wrote:You do know that I created VICIdial by myself and I wrote most of the code and I am still the maintainer of the codebase, right?

There were multiple causes for that one problem. I believe we've finally fixed them all as of March of 2017. But there were several major code rewrites to fix each of the issues involved over the last several years.

hello matt!!!

my version is:

Version: 2.14b0.5
SVN Version: 2722
DB Schema Version: 1498

VERSION: 2.14-605a
BUILD: 170327-1655 (March of 2017)

The problem has been solved in my version????

I believe so, yes. Although we are fixing issues all the time, so we recommend upgrading at least every 6 months if possible so you always have the most up-to-date code.

Thanks we try it - perhaps we can reduce the calls

Server version: 5.1.46-log SUSE MySQL RPM
2 x OPTERON 12-CORE 6168 1.9GHZ
8 x RAID 10 INTEL 320 Serie SSD 120GB SATA II
1 x 96 GB DDR3-RAM (12x KVR1333D3D4R9S/8G)

is it better to change vicidial_live_agents to a memory table?
We use a RAID10 system with 8 SSD disks (very fast) and have 100 GB RAM

Code: Select all

[mysqld]
port            = 3306
socket          = /var/run/mysql/mysql.sock
key_buffer_size = 640M
key_buffer = 512M
max_allowed_packet = 2M
table_open_cache = 8192
table_cache = 8192
sort_buffer_size = 16M
read_buffer_size = 16M
join_buffer_size = 16M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 128
query_cache_size = 0 # 0 = deaktivieren
datadir=/var/lib/mysql
#query_cache_limit = 1M
#query_cache_min_res_unit = 1024 # Use this is you have TONS of small queries
thread_concurrency = 48 # Try number of CPU's*2 for thread_concurrency
log-error=mysqld.error.log
log-slow-queries=mysql-slowquery.log
# ViciDial DB-Only Settings
connect_timeout=60
wait_timeout = 28800
slow_query_log
long_query_time = 0.5
max_connections=1200
open_files_limit=24576
max_heap_table_size = 1G
bulk_insert_buffer_size         = 1G    #recommend same size as tmp_table_size
tmp_table_size                  = 1G    #recommend 1G min
concurrent_insert=2
expire_logs_days=3
#log-queries-not-using-indexes=1
skip-locking
skip-innodb
skip-name-resolve
skip-host-cache

On a system like yours, the vicidial_live_agents should be getting cached by mysql. If you want to change it to MEMORY, then you'll have do alter it to change the TEXT fields to VARCHARs, which will also limit the capacity of the in-groups that agents are allowed to select. We've done this for many clients, but it sometimes can end up actually being slower, so you have to monitor it at first to make sure it's not causing more issues.

ALTER TABLE vicidial_live_agents MODIFY closer_campaigns VARCHAR(200) default '';
ALTER TABLE vicidial_live_agents MODIFY external_ingroups VARCHAR(200) default '';
ALTER TABLE vicidial_live_agents MODIFY agent_territories VARCHAR(45) default '';
ALTER TABLE vicidial_live_agents ENGINE=MEMORY;

You can also change a couple other tables to MEMORY, but you need to monitor the memory usage and not set your hopper too high in your campaigns or you will "fill" the table and cause additional new problems:

ALTER TABLE vicidial_manager ENGINE=MEMORY;
ALTER TABLE vicidial_hopper ENGINE=MEMORY;

Thanks a lot!

We try to change vicidial_manager, vicidial_live_agents and vicidial_hopper to MEMORY table
-> have to use varchar(4000) for closer_campaigns

mysql_slow_query is default ON with 0.5 seconds.
We have got 1 slow query in 10 minutes
- hopper to vicidial_list
- custom query to vicidial_callbacks

# User@Host: cron[cron] @ [192.168.203.22]
# Query_time: 0.581407 Lock_time: 0.000404 Rows_sent: 200 Rows_examined: 84412
SET timestamp=1507717508;
SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user FROM vicidial_list where

# Time: 171011 12:30:07
# User@Host: cron[cron] @ [192.168.203.33]
# Query_time: 0.535268 Lock_time: 0.000097 Rows_sent: 1 Rows_examined: 0
SET timestamp=1507717807;
select count(*) as callbacks, sum(if(status='LIVE',1,0)) as live from vicidial_callbacks where recipient='USERONLY' and user='xxxxxx' and campaign_id='xxxxxx' and status NOT IN('INACTIVE','DEAD');

ALTER TABLE vicidial_live_agents MODIFY closer_campaigns VARCHAR(4000) default '';
ALTER TABLE vicidial_live_agents MODIFY external_ingroups VARCHAR(200) default '';
ALTER TABLE vicidial_live_agents MODIFY agent_territories VARCHAR(45) default '';
ALTER TABLE vicidial_live_agents ENGINE=MEMORY;
ALTER TABLE vicidial_manager ENGINE=MEMORY;
ALTER TABLE vicidial_auto_calls ENGINE=MEMORY;

-> we have a try and give feedback soon

4000 is very big, something to keep in mind is that there is no "VARCHAR" in MEMORY tables, it is handled only as a "CHAR" and it will fill the whole field to the limit you have set for it. On larger systems we have seen setting a field that large in a MEMORY table to actually make the table query slower.

We will do a benchmark tomorrow again and see the difference.
at the moment there are 115 users loggedin.
time is always between 0.73 sec and 0.75 sec
i think this is a good measurement

Code: Select all

mysql> select benchmark(20000000,(select count(*) from vicidial_live_agents where closer_campaigns like '%K%'));
+----------------------------------------------------------------------------------------------------+
| benchmark(20000000,(select count(*) from vicidial_live_agents where closer_campaigns like '%K%')) |
+----------------------------------------------------------------------------------------------------+
|                                                                                                  0 |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.74 sec)

Feedback of the customer: its a little bit better than before with MEMORY tables
I think the benchmark isnt useful, time is the same with MEMORY or MYISAM
MYISAM 1 row in set (0.74 sec)
MEMORY 1 row in set (0.74 sec)

The customer said the in 90% the agent gets a second call from internal transfers
If one agent do a transfer to one other inboundgroup .. so perhaps we will find a code error on transfer.

mysql_slow_query is default ON with 0.5 seconds.

that will show you queries over 0.5 seconds, but some decisions in vicidial are made in well under 0.5 seconds.

a query that is delayed for 0.3 seconds can cause an incorrect decision based on the missing information.

if you check the script responsible for making the decision to put that 2nd client into the agent conference, you should be able to determine which query failed. but in the end, I'll bet putting your system back to "stock" is the appropriate resolution. don't stick with it the way it is just because you spent a lot of time making it this way. that way lies madness.

move to MEMORY is reducing the problems.
Perhaps we move vicidial_hopper to MEMORY too (20.000 entries)
Database server is running since over a year.

How can we track if we have enough RAM?
mysql takes up the whole RAM but where can i see if mysql perhaps swaps on the HDD?

you can use the 'top' command in Linux to see if swap is being used.

we deciced to disable SWAP in /etc/fstab because mysql swaps a little bit on HDD
mysql 90GB in RAM
mysql 1 GB in SWAP
-> 96GB RAM -> no need of 4GB SWAP partition

Is it OK to convert vicidial_hopper to a MEMORY table?

We have several clients that set vicidial_hopper as a MEMORY table, but you need to make sure you have any unused campaigns set to inactive and that your active campaign hopper levels aren't set too high.

Thanks!!

We will import a simple mysql query in the morning and in the evening in crontab (cause of backup/reboot of the system)

0 5 * * * mysql: ALTER TABLE vicidial_hopper ENGINE=MEMORY;
0 21 * * * mysql: ALTER TABLE vicidial_hopper ENGINE=MyISAM;

Not sure why that is necessary. Unless your campaign is set to dial 24 hours, the hopper is cleared out every night automatically. Also, you shouldn't have to reboot your database server on a scheduled basis.

its getting better and better.
Im looking for a mysql proffessional to optimize the database configuration (my.cnf)
perhaps we can optimise a little bit more

I see a bunch of recommendations here about changing the vicidial_live_agents table to Memory. and I think it could help since I see this table locking with 500 agents on the system. I am nervous for a potential negative impact though. I know you have mentioned Ingroups being an issue. I see currently the closer_campaigns field has 100 characters per agent in it.

If you want to change vicidial_live_agents to a MEMORY table, here's how we usually recommend doing that:

ALTER TABLE vicidial_live_agents MODIFY closer_campaigns VARCHAR(200) default '';
ALTER TABLE vicidial_live_agents MODIFY external_ingroups VARCHAR(200) default '';
ALTER TABLE vicidial_live_agents MODIFY agent_territories VARCHAR(45) default '';
ALTER TABLE vicidial_live_agents ENGINE=MEMORY;