suggestion on how to lessen the load on the database

[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

[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.

[bobbymc]

interesting, what do you mean when you say "in reduced storage" ?

[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.

[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.

[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.

[williamconley]

try it and see. post your detailed results.

[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?

[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.

[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

[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?

[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?

[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.

[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.

[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.

[bobbymc]

nm just realized that was a stupid idea.. it still create a join which wont change anything

[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?

[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.

[bobbymc]

If I test and implement the this are mysql triggers acceptable by you?

[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.

[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

[williamconley]

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.

[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

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?

[williamconley]

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.

[carpenox]

just curious, but what about using VARCHAR2 now?

[williamconley]

just curious, but what about using VARCHAR2 now?

For what? Memory tables don't do varchar. They have fixed width even for varchar fields.