Page 1 of 1

suggestion on how to lessen the load on the database

PostPosted: Sat Oct 13, 2012 10:51 pm
by bobbymc
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

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

PostPosted: Sun Oct 14, 2012 7:17 am
by mflorell
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.

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

PostPosted: Sun Oct 21, 2012 6:26 pm
by bobbymc
interesting, what do you mean when you say "in reduced storage" ?

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

PostPosted: Tue Oct 23, 2012 5:50 am
by mflorell
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.

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

PostPosted: Tue Oct 23, 2012 12:00 pm
by williamconley
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.

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

PostPosted: Sun Nov 11, 2012 3:48 pm
by bobbymc
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.

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

PostPosted: Sun Nov 11, 2012 8:14 pm
by williamconley
try it and see. post your detailed results.

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

PostPosted: Thu Nov 15, 2012 5:22 pm
by mcargile
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?

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

PostPosted: Thu Nov 15, 2012 6:03 pm
by williamconley
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.

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

PostPosted: Thu Dec 27, 2012 6:45 pm
by bobbymc
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

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

PostPosted: Sun Dec 30, 2012 7:20 pm
by mflorell
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?

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

PostPosted: Tue Jan 01, 2013 7:10 pm
by bobbymc
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?

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

PostPosted: Tue Jan 01, 2013 8:00 pm
by mflorell
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.

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

PostPosted: Wed Jan 02, 2013 1:53 am
by bobbymc
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.

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

PostPosted: Wed Jan 02, 2013 1:57 am
by bobbymc
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.

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

PostPosted: Wed Jan 02, 2013 2:11 am
by bobbymc
nm just realized that was a stupid idea.. it still create a join which wont change anything

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

PostPosted: Wed Jan 02, 2013 2:28 am
by bobbymc
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?

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

PostPosted: Wed Jan 02, 2013 7:41 am
by mflorell
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.

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

PostPosted: Wed Jan 02, 2013 1:31 pm
by bobbymc
If I test and implement the this are mysql triggers acceptable by you?

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

PostPosted: Thu Jan 03, 2013 1:07 am
by mflorell
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.

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

PostPosted: Thu Oct 19, 2017 1:23 pm
by Noah
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

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

PostPosted: Thu Oct 19, 2017 1:40 pm
by williamconley
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.

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

PostPosted: Tue Dec 05, 2017 9:40 pm
by Noah
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?

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

PostPosted: Wed Dec 06, 2017 9:08 am
by williamconley
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.

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

PostPosted: Tue Apr 21, 2020 3:00 am
by carpenox
just curious, but what about using VARCHAR2 now?

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

PostPosted: Tue Apr 21, 2020 10:45 am
by williamconley
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.