Moving from Vicidial_list to vicidial_n tables

Discussions about development of VICIDIAL and astGUIclient

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Moving from Vicidial_list to vicidial_n tables

Postby Adrianyong » Tue Dec 27, 2022 9:26 am

mflorell wrote: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?


Based on my experience with other predictive system such as interactive intelligence aka pureconnect

These system would never put the leads into a single table like vicidial_list. In each batch, a new table is created eg vicidial_list_aol_2022_01_spc

This allows easier managing and purging leads if required every three months or so. Let alone, myisam table locking issues to reporting.

I think a lot can be improved with this small change.
Adrianyong
 
Posts: 7
Joined: Mon Dec 26, 2022 8:43 am

Re: Moving from Vicidial_list to vicidial_n tables

Postby Adrianyong » Tue Dec 27, 2022 9:34 am

One do not need to overwrite all existing logic, just add a qualifer to use this schema, and append alternate php or perl script eg

Vicidial_hopper.pl
*new* vicidial_hopper_udl.pl

Udt for user defined lists
Adrianyong
 
Posts: 7
Joined: Mon Dec 26, 2022 8:43 am

Re: Moving from Vicidial_list to vicidial_n tables

Postby mflorell » Tue Dec 27, 2022 9:56 am

What you describe would affect dozens of scripts in hundreds of places in the code, it would not be a simple thing to change.
mflorell
Site Admin
 
Posts: 18334
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Moving from Vicidial_list to vicidial_n tables

Postby Adrianyong » Tue Dec 27, 2022 11:41 am

Yes, it is not a simple thing. It can be done nonetheless and this change is quite important.
Adrianyong
 
Posts: 7
Joined: Mon Dec 26, 2022 8:43 am

Re: Moving from Vicidial_list to vicidial_n tables

Postby Adrianyong » Tue Dec 27, 2022 9:58 pm

Search "INTO vicidial_list" (93 hits in 42 files of 1980 searched)
Search "vicidial_list set" (196 hits in 85 files of 1980 searched)
Search "from vicidial_list" (782 hits in 142 files of 1980 searched)

Looking at the complexies, I would have done the following

1 ) Create rule to all new leads table to have lst_ prefix eg lst_<batch_name> *note the batch name will be list_id or list_name in vicidial_lists*
2 ) Add column list_id to vicidial_list_alt_phones (with hidden logic changes to retrieval)
3 ) Create a function to retrieve the new leads table and apply to top of all affected codes
4 ) Substitute all the queries with a parameters eg $vicidial_list
eg "INSERT INTO $vicidial_list

this is how i would have gone around it. of course testing would be a challenge
Adrianyong
 
Posts: 7
Joined: Mon Dec 26, 2022 8:43 am

Re: Moving from Vicidial_list to vicidial_n tables

Postby martinch » Wed Mar 29, 2023 11:11 am

This is a pretty ambitious ask but it's a nice challenge. I may have a look into this.

You can also consider archiving leads off...that should keep the table nice and trim. That may be a feature in itself. List Options -> archive leads after X days.

Cheers,
Martin.
Project Lead @ mDial -> https://github.com/TheBlode/mDial
martinch
 
Posts: 273
Joined: Thu Nov 15, 2018 9:14 am
Location: England, UK

Re: Moving from Vicidial_list to vicidial_n tables

Postby Adrianyong » Sat May 27, 2023 12:22 am

Yeah great Martin, on while we are at it ... please convert it to INNODB not MyISAM. I can't tell you how worry every time. There is a power trip in my office or so.
Adrianyong
 
Posts: 7
Joined: Mon Dec 26, 2022 8:43 am

Re: Moving from Vicidial_list to vicidial_n tables

Postby RBecker » Tue May 30, 2023 5:21 pm

Adrianyong wrote:Yeah great Martin, on while we are at it ... please convert it to INNODB not MyISAM. I can't tell you how worry every time. There is a power trip in my office or so.


Matt F. has said himself that ViciDial uses the locking properties of MyISAM as a semaphore-type system to ensure that all processes execute in the proper order. While I don't agree with it and would much rather prefer to have actual transactionality in the system, I understand that that's a huge undertaking and not likely something the devs are going to accomplish any time soon (if at all).
Managed & Dedicated ViciDial Hosting | VoIP Trunking and Termination | https://www.dial-fusion.com/
Main Cluster: 21 Agent Servers | Dedicated DB and Separate Reports Server | 2 Web Servers | HAProxy Load Balancing | Dedicated Archive Server
RBecker
 
Posts: 38
Joined: Mon Aug 30, 2021 3:05 pm

Re: Moving from Vicidial_list to vicidial_n tables

Postby martinch » Fri Jun 02, 2023 6:26 am

I mean, the scope of work involved here...is as mflorell described, it's quite involved. So many lookups and updates to `vicidial_list`...it's thousands of blocks and yeah.

I was wondering if there would be a shortcut way this could achieved. I'm not a big SQL expert...but let's take this example;

We have 1 million records in `vicidial_list` and we use our lead_id and our list_id to point to our "specific" list table based on those values...all other values within `vicidial_list` would be NULL...would that reduce locking when selecting on the table?

Apologies if that was a newbie question...I'm curious about it though. My first thought was to create logic to refer to `vicidial_list_n` table but would require tonnes of change...we're talking potentially thousands of lines of change and yeah...it wouldn't be an easy task.

Thanks guys.
Project Lead @ mDial -> https://github.com/TheBlode/mDial
martinch
 
Posts: 273
Joined: Thu Nov 15, 2018 9:14 am
Location: England, UK

Re: Moving from Vicidial_list to vicidial_n tables

Postby mflorell » Sat Jun 03, 2023 8:48 am

One of the issues you would have to deal with in doing this is how to handle hopper runs where there are many active lists in a campaign and still allow sorting across all of them. Back in the beginning of VICIdial, it was very common for organizations to load new leads into new lists every day, and then work through all available lead data sorting by number of attempts and/or last call time across hundreds of active lists in a single campaign. This isn't a problem at all when using a single vicidial_list table, but when moving to separate tables for each list, this becomes a massive SQL mess with potentially hundreds of JOINs that would cause the database server to lock up and eventually die. You could of course limit or remove this kind of lead/list usage, but it would have to be addressed if you move forward with this as a project.
mflorell
Site Admin
 
Posts: 18334
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Moving from Vicidial_list to vicidial_n tables

Postby martinch » Fri Jun 16, 2023 3:56 pm

mflorell wrote:One of the issues you would have to deal with in doing this is how to handle hopper runs where there are many active lists in a campaign and still allow sorting across all of them. Back in the beginning of VICIdial, it was very common for organizations to load new leads into new lists every day, and then work through all available lead data sorting by number of attempts and/or last call time across hundreds of active lists in a single campaign. This isn't a problem at all when using a single vicidial_list table, but when moving to separate tables for each list, this becomes a massive SQL mess with potentially hundreds of JOINs that would cause the database server to lock up and eventually die. You could of course limit or remove this kind of lead/list usage, but it would have to be addressed if you move forward with this as a project.


Thanks Matt...well, my engineer tells me JOINs are not ideal and can be quite heavy on the the database engine...I'd probaby opt for straight up lookups where possible for speed...like is the ViCi style. Meat and potatoes kinda SQL. Hopper logic would need to be changed to account for it. I work with a ViCi instance that does exactly as you describe, dozens of lists and new ones added all the time...it can get tricky if we offload to x number of tables. It's an interesting one for sure and I love a good challenge.
Project Lead @ mDial -> https://github.com/TheBlode/mDial
martinch
 
Posts: 273
Joined: Thu Nov 15, 2018 9:14 am
Location: England, UK


Return to Development

Who is online

Users browsing this forum: No registered users and 16 guests