suggestion on how to lessen the load on the database

Discussions about development of VICIDIAL and astGUIclient

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

suggestion on how to lessen the load on the database

Postby bobbymc » Sat Oct 13, 2012 10:51 pm

The AST_VDautoDial.pl and _FILL.pl grab records from the hopper and then pull additional data from vicidial_list such as phone_number, address3 and etc.. why wont we load that data into the vicidial_hopper table? I ran acorss a issue were we did a broadcast campaign and we had about 14 million leads in the vicidial_list table.. when adding the columns into the hopper table it not only speed up the call process but also lowered the load on the database. IF you approve this change i'll make patches for all areas that pull data from vicidial_list when requesting data from the hopper such as the scritps mentioed above and also FastAGI.pl
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby mflorell » Sun Oct 14, 2012 7:17 am

There is a limit to how big MEMORY tables can get, we had our first client hit this limit a couple of months ago, because of that I an very hesitant to add any more fields to the vicidial_hopper table. We would need to add 7 more fields to that table, some of them quite large(MEMORY tables can't store VARCHARs in reduced storage, they turn them into full CHAR fields) which would slow the table down tremendously and take up a ton of memory.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: suggestion on how to lessen the load on the database

Postby bobbymc » Sun Oct 21, 2012 6:26 pm

interesting, what do you mean when you say "in reduced storage" ?
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby mflorell » Tue Oct 23, 2012 5:50 am

In MySQL, if you have a VARCHAR(100) and store the value "Smith" in it as a MyISAM table, it will only take up 5 characters of space in the data file. VARCHAR means variable-characters. If you do the same thing in a MEMORY table, it has to allocate 100 characters of space to store "Smith" in a CHAR(100) field, because MEMORY tables can't do variable width data fields.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: suggestion on how to lessen the load on the database

Postby williamconley » Tue Oct 23, 2012 12:00 pm

And especially in a broadcast campaign where the hopper itself may have thousands of records ... now you run more than one campaign ... and you could hit the record limit fairly easily. especially if you do not regularly clear the hopper.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: suggestion on how to lessen the load on the database

Postby bobbymc » Sun Nov 11, 2012 3:48 pm

how about only the fields the AST_VDautoDial.pl and _FILL.pl grabs from the vicidial_list table. thats maybe 4 or 5 columns with the phone number.. called since last reset and a few others? this would speed things up much more and lessen the load on that table. i also found it to be very efficient to fork the update to the vicidial_list table to speed up the calls the system needs to make since the updated sometimes slows down due to table locks on vicidial_list.
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby williamconley » Sun Nov 11, 2012 8:14 pm

try it and see. post your detailed results.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: suggestion on how to lessen the load on the database

Postby mcargile » Thu Nov 15, 2012 5:22 pm

Forking adds concurrency issues. What happens if the forked process fails? What happens if the fork does not get executed right away and something else is looking for that updated record?
Michael Cargile | Director of Engineering | ViciDialGroup | http://www.vicidial.com

The official source for VICIDIAL services and support. 1-888-894-VICI (8424)
mcargile
Site Admin
 
Posts: 614
Joined: Tue Jan 16, 2007 9:38 am

Re: suggestion on how to lessen the load on the database

Postby williamconley » Thu Nov 15, 2012 6:03 pm

making a change to a record while it is in the hopper (especially the phone number field ...) is already risky. i'm not sure it will have much more detrimental effect if the information is duplicated. certainly not by comparison to the speedup which could result from one less request to the DB. But certainly worth testing.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: suggestion on how to lessen the load on the database

Postby bobbymc » Thu Dec 27, 2012 6:45 pm

its been in production since my last post and so far it has definetly lessen the load on the DB and i have way less locks.. so 2 thumbs up from me.. hope you guys inherit this change
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby mflorell » Sun Dec 30, 2012 7:20 pm

We have already run into one instance of the vicidial_hopper MEMORY table filling up, which causes all kinds of other problems system-wide. A client had 60 campaigns, and had all of them set to active with all of the hopper levels set to 2000. They were using a DB server with a lot of RAM but it was not enough to handle the 100,000+ records that they were trying to store in the hopper. This illustrates the kind of problem you can run into with MEMORY tables that do not have a very small record count. Adding more data to every record in vicidial_hopper would make the possible record count for that MEMORY table even smaller, and we would run into this problem much more frequently unless we come up with some way to post a warning in the admin web interface or put some kind of limiter on how many records can be in that table. Any ideas on that?
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: suggestion on how to lessen the load on the database

Postby bobbymc » Tue Jan 01, 2013 7:10 pm

Can we use a different db like Mongo to handle the hopper table? I it would be a nice start to move away from mysql. I think we all agree that mysql isn't very scale able without tons of management to build a cluster.. We can also try to use memcache.. What do you think?
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby mflorell » Tue Jan 01, 2013 8:00 pm

I'm not eager to add another software installation and configuration requirement to Vicidial. It would also require adding another layer of DB connectivity to every script that touches the vicidial_hopper table.

Have you performed any tests of MongoDB/Memcached with similar datasets?

Are they able to use variable field length memory storage?

I have read that MongoDB and Memcached are slower than MySQL MEMORY tables, so the benefits of Mongo or Memcached would have to outweigh the limitations of MEMORY tables in MySQL for me to even consider switching.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: suggestion on how to lessen the load on the database

Postby bobbymc » Wed Jan 02, 2013 1:53 am

memcache would be very difficult to implement as we would have to create a fake table, so by that alone i would probably exclude that option. i have not done any test with mongo. any other ideas you have? i dont mind testing this out in our test bed.
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby bobbymc » Wed Jan 02, 2013 1:57 am

i jsut had a amazing idea.. how about we create a view from the two tables and have the hopper script access the view for the other data. this way we dont lock the vicidial_list table and dont have to worry about the data being outdated in the hopper table.
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby bobbymc » Wed Jan 02, 2013 2:11 am

nm just realized that was a stupid idea.. it still create a join which wont change anything
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby bobbymc » Wed Jan 02, 2013 2:28 am

i got another idea.. we take all the extra data needed from vicidial list and place it in a vicidial_hopper_data table.. we make that table innodb, and if you are worried about having to make tons of chnages to keep the data on that table sycned between list and the hopper_data table, simply create a mysql trigger to update anything in the hopper_data table when updates are done to the vicidial_list table.. what do you think?
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby mflorell » Wed Jan 02, 2013 7:41 am

InnoDB isn't good with many updates happening quickly, and then there is the deadlocking, so I would still want to avoid that table engine if possible.

Using another table for extended data to avoid lookups is an interesting idea, but it is something that would need to be tested and measured against current code before I would implement it.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: suggestion on how to lessen the load on the database

Postby bobbymc » Wed Jan 02, 2013 1:31 pm

If I test and implement the this are mysql triggers acceptable by you?
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: suggestion on how to lessen the load on the database

Postby mflorell » Thu Jan 03, 2013 1:07 am

I really prefer not to use triggers in MySQL. It's just one more thing that's harder to track and control and debug when there are issues.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: suggestion on how to lessen the load on the database

Postby Noah » Thu Oct 19, 2017 1:23 pm

How about adding a sleep 5 in front of the hopper script?
Let all the other things that run once a minute for Admin keep alive finish up before executing the hopper

* * * * * sleep 5 && /usr/share/astguiclient/AST_VDhopper.pl -q
MyCallCloud.com - Cool Vici Customizations - Hosted - Configured - Supported
Web: https://mycallcloud.com
P: 888-663-0760
E: sales@mycallcloud.com
Noah
 
Posts: 90
Joined: Tue Feb 08, 2011 7:14 pm

Re: suggestion on how to lessen the load on the database

Postby williamconley » Thu Oct 19, 2017 1:40 pm

Noah wrote:How about adding a sleep 5 in front of the hopper script?
Let all the other things that run once a minute for Admin keep alive finish up before executing the hopper

* * * * * sleep 5 && /usr/share/astguiclient/AST_VDhopper.pl -q


We've done that on several machines and even staggered the hopper among servers and had positive results.

Timing all the DB intense processes to NOT happen "on the minute" has proven useful.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: suggestion on how to lessen the load on the database

Postby Noah » Tue Dec 05, 2017 9:40 pm

williamconley wrote:
Noah wrote:How about adding a sleep 5 in front of the hopper script?
Let all the other things that run once a minute for Admin keep alive finish up before executing the hopper

* * * * * sleep 5 && /usr/share/astguiclient/AST_VDhopper.pl -q


We've done that on several machines and even staggered the hopper among servers and had positive results.

Timing all the DB intense processes to NOT happen "on the minute" has proven useful.


AGREED - perhaps a random timer within the hopper script might be a useful add min sec to max sec random?
MyCallCloud.com - Cool Vici Customizations - Hosted - Configured - Supported
Web: https://mycallcloud.com
P: 888-663-0760
E: sales@mycallcloud.com
Noah
 
Posts: 90
Joined: Tue Feb 08, 2011 7:14 pm

Re: suggestion on how to lessen the load on the database

Postby williamconley » Wed Dec 06, 2017 9:08 am

Noah wrote:AGREED - perhaps a random timer within the hopper script might be a useful add min sec to max sec random?

Random will still overlap in an unpredictable fashion.

Stagger.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: suggestion on how to lessen the load on the database

Postby carpenox » Tue Apr 21, 2020 3:00 am

just curious, but what about using VARCHAR2 now?
Alma Linux 9.3 | Version: 2.14-911a | SVN Version: 3815 | DB Schema Version: 1710 | Asterisk 18.18.1
www.dialer.one -:- 1-833-DIALER-1 -:- https://linktr.ee/CyburDial -:- WhatsApp: +19549477572 -:- Skype: live:carpenox_3
carpenox
 
Posts: 2230
Joined: Wed Apr 08, 2020 2:02 am
Location: Coral Springs, FL

Re: suggestion on how to lessen the load on the database

Postby williamconley » Tue Apr 21, 2020 10:45 am

carpenox wrote:just curious, but what about using VARCHAR2 now?

For what? Memory tables don't do varchar. They have fixed width even for varchar fields.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)


Return to Development

Who is online

Users browsing this forum: No registered users and 29 guests