vicidial.org

I like to attempt to replace the MyISAM dependency of the vicidial queue system by replacing the queue mechanism with something like rabbitMQ or activeMQ. i've been working with the code base for a while now but dont have the in depth knowledge that matt has. Looking at the code i see that only agi_VDAD_ALL_* locks tables and releases them. is there any other part of the code that does locking and is it possible to get a more clear picture of how the queue system works from point a to z?

i know this might be time consuming to explain but here are the upsides:

No more MySQL dependency which = much higher scaleability with less overhead cost.
Faster feature implementation with less time spend on the queue and more time spend on other things.


Hope matt has the time to go over this. i will be branching the latest SVN trunk and work on it until i have a sustem that works in parallel and then publish the work in hopes to be 1 step closer in getting rid of MySQL =)

It's not so much the locking, but maintaining query queue integrity with place in line preserved. There are hundreds of queries and processes in Vicidial that depend on the query place in line being preserved from the moment it is attempted. This is why no matter what you change you will still need some kind of central queue.

So my goal is to replace the queue system with rabbitmq. How can I extract the entire queue process and rebuild it around a queue system such as rabbitmq

I wouldn't even know where to begin. You're talking about thousands of SQL statements spread across hundreds of Perl and PHP scripts needing to be changed.

i figured its a tall order.

how about systematically rewriting all perl scripts first to be more object oriented so its easier to understand the processes and help me and others contribute to the code.

currently the inbound and outbound script is what i had in mind to rewrite. tons of code in there can be minimized and optimized. is this something you would be interested to use and continue using?

IF you rewrote the code to be compatible with the latest SVN and object oriented (external perl modules for often-run sub functions ...), and then tested in a heavy-use environment ...

I think Matt would be receptive to a cleanly rewritten perl codebase. But it must be completely tested OR have an "opt-in" function in system settings (ie: use New Pretty code or use Old Ugly code as an option in system settings). That would allow instant reversion if a bug is found.

in continues research i need to know which tables are part of the queuing/ locking logic vicidial queue system depends on?

if you can list out the tables i can start to study them to code more intelligently

for example is vicidial_list one of them?

It would make more sense to test this. LOL

vicidial_list is always one, many of the log files are involved. the hopper (which is why it is a memory table ...).

but it also depends on your usage (inbound vs outbound, transfers, ...?)

so make a single call of the type in question and record all mysql queries and the time of occurrence.

then also take into account the real time screen which is often running on several systems. and any reporting functions you may want available during production. plus the addition of new leads (things like deduping can pull the system down when you have millions of leads and import during operation ...).

I find the most heavily loaded tables include vicidial_live_agents and vicidial_list tables.

so vicidial_log, vicidial_list are the largest that also have high io, vicdial_live_agents, vicidial_manager, vicidial_hopper, vicidial_auto_calls are the others that have high io but arent so large in data size. (we also got small tables that might have high io such as live_channels or live_sip_channels which by the way i dont get why we have two tabels for the same thing?)

did i miss any?

The heavily used tables depend a lot on the type of installation and how the company uses Vicidial. It can be very different for a company that has 300 mostly inbound agents compared to 300 mostly outbound predictive agents. And a company doing broadcast messaging is different from that as well. vicidial_list is the most common bottleneck, as can be vicidial_live_agents, and there are different options for dealing with both of those depending on how the company uses their system.

@ mflorell

Any suggestion somehow to prevent this bottleneck on vicidial_list table coz somehow im having 19 Million records running 1 year already and i see locked queries most specially during production and if scripts from conjobs do run, its a bit hard coz its 24 hours i cant find better time spot to schedule those script.It seems my vicidial is helpless at the moment with regards to it list.Maybe there are option to somehow correct the issue.coz at the moment we are locked up on ideas same with our vicidial_list tables

How much RAM is on your DB server?

What RAID card are you using?

Do you really need all 19 million records in the vicidial_list table? Are they all active accounts?

@ mflorell

RAM - 16GB RAM on my Database Server solely

RAID - No RAID just plain SAS Drive on it (enough space)

19 Million Records - I need somehow to coordinate on this with Managers (but i see suggestions that they create another table fro list archive- Could this help somehow)

Thanks for reply.

Well there you go, quadruple your RAM and get an LSI Login MegaRAID card and 4 x 15k SAS drives for a RAID10 configuration and your MySQL performance will improve. You will also have some redundancy which you don't have now in case of a drive failure.

I'm also guessing that you may not be using a quad-CPU(or at least a dual-CPU) motherboard, in which case you should switch to one.

@ mflorell

My CPU is Intel XEON Quad Core 2.8 Ghz

for redundancy im using Mysql replication (Master/Salve) it works but somehow unreliable sometimes when get errors and the log bin fall behind, I saw Tungsten Replicator which Automates and more reliable to used based on feedback @ net, have any used this technology somehow.

I would recommend setting your slave to ignore errors on mysql replication.

I've never used Tungsten before, but looking at your hardware it's no surprise you are running into bottlenecks. You should have at least a dual CPU system, more RAM and a faster MegaRAID hardware RAID 10 disk system.