10 milion Leads on my Database

All installation and configuration problems and questions

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

10 milion Leads on my Database

Postby boiken » Tue Jul 11, 2017 7:58 am

Hello everybody.

I have a cluster installation of Vicibox 6.0.3
VERSION: 2.12-523a
BUILD: 151211-0940

1 DB
1 Web
4 telephony
and 1 Archive for audio

Now i have a question about the call logs and the leads.
My database now has 10 milion leads and the system logout some agents from time to time.
And another problem when i need to perform a mysqlcheck with -r attribute it takes about an hour to repair.(the tables with call log and leads takes most of the time to repair normally )
What can i do to clean my database from old logs and old lead (for example leads older than 1 year)
Thanks
Last edited by boiken on Mon Jul 24, 2017 4:20 am, edited 2 times in total.
CONTACT US
Vicidial Support
https://www.fiverr.com/share/rxDY0x
boiken
 
Posts: 134
Joined: Fri Sep 12, 2014 11:56 am
Location: World

Re: 1 milion Leads on my Database

Postby mflorell » Tue Jul 11, 2017 10:30 am

If you have adequate hardware for your DB server, then 1 million leads shouldn't be a problem at all. What drive structure and RAID card are you using for your database server?
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: 1 milion Leads on my Database

Postby boiken » Wed Jul 12, 2017 12:53 am

Sorry i mistyped. The numer of the leads is 10 million.
Im using a single SSD HDD connected on a lsi raid card
CONTACT US
Vicidial Support
https://www.fiverr.com/share/rxDY0x
boiken
 
Posts: 134
Joined: Fri Sep 12, 2014 11:56 am
Location: World

Re: 1 milion Leads on my Database

Postby mflorell » Wed Jul 12, 2017 7:08 am

Well, both of those are issues.

If you want to maintain that number of leads, you need to be using a RAID 10 with 4 drives on an LSILogic MegaRAID caching Raid card.

I would suggest on a single drive DB keeping your leads database to 2-3 million maximum for optimal performance.

There is a lead archiving process that will allow you to keep older leads searchable, while not within the active dialing list, but keep in mind it is not easy to bring them back from archival to the active lead database:


CREATE TABLE vicidial_list_archive LIKE vicidial_list;
ALTER TABLE vicidial_list_archive MODIFY lead_id INT(9) UNSIGNED NOT NULL;

select count(*) from vicidial_list where modify_date < "2014-01-01 00:00:00";

INSERT INTO vicidial_list_archive SELECT * from vicidial_list where modify_date < "2014-01-01 00:00:00";

select count(*) from vicidial_list_archive where modify_date < "2014-01-01 00:00:00";

DELETE from vicidial_list where modify_date < "2014-01-01 00:00:00";

optimize table vicidial_list;



The above is just an example, you can also use other parameters to select leads for archiving.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: 10 milion Leads on my Database

Postby boiken » Tue Jul 18, 2017 2:17 am

Ok i will create this table and populate it as the example above.
One thing that i wanted to know is.

1.) After moving the leads to the new table,does the system check this new table too for duplicates when i load new leads ?

This is the size of my largest tables :

vicidial_report_log 7.3 GB
call_log 5.7 GB
vicidial_dial_log 3 GB
vicidial_carrier_log 2.6 GB
vicidial_log 2.1 GB
recording_log 1.8 GB
vicidial_log_extended 1.9 GB
vicidial_list 1.9 GB

2.) Is it safe to delete rows older than 1 year on vicidial_report_log,call_log,vicidial_dial_log,vicidial_carrier_log,vicidial_log ?
CONTACT US
Vicidial Support
https://www.fiverr.com/share/rxDY0x
boiken
 
Posts: 134
Joined: Fri Sep 12, 2014 11:56 am
Location: World

Re: 10 milion Leads on my Database

Postby mflorell » Tue Jul 18, 2017 6:45 am

No, duplicate checks do not run against the archive list table.

Yes, you should be able to delete records older than a year from the tables you listed if you like.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: 10 milion Leads on my Database

Postby boiken » Mon Jul 24, 2017 3:29 am

I started thinking of the lists that load the system because im facing problems with the calls length.

Now i clearing the unused lists from the system to leave a total of 2 million leads.

Does the big amount of leads (10 million) affect the calls lenght ?

Most of the call drops at 600 -+ 20 second (10 minutes aprox) and some others 1200 +- 20 seconds (20 minutes aprox)

I have changed the ISP and the Voip provider but still facing this problem.this is a 2 year cluster and i havent had problem like this before.

Can it be because of the high amount of leads ?
CONTACT US
Vicidial Support
https://www.fiverr.com/share/rxDY0x
boiken
 
Posts: 134
Joined: Fri Sep 12, 2014 11:56 am
Location: World

Re: 10 milion Leads on my Database

Postby mflorell » Mon Jul 24, 2017 6:41 am

There can be multiple causes to issues like that, or secondary causes could just make an existing problem worse.

Next, you should look at your network, and run an analysis tool like SmokePing to see if there are any issues with your network staying consistently connected to your servers and carriers. Sometimes it's bad or overloaded switches that cause these issues.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: 10 milion Leads on my Database

Postby rustynail » Thu Sep 28, 2023 5:50 am

Hi There,

I understand this is very old thread but it matches so much with my situation that I would like to re-activate it.
I have 13 million records in our list table and customer want's to add more 13 million leads so making it total 26 million leads.

1. So I was wondering if we change DB server to use RAID-10 with 4 drives on an LSILogic MegaRAID caching Raid card, then is it possible to handle that number of leads?
2. Other option I was thinking to upload multiple lists of 5 million leads each and attach them to campaigns as required

Thanks in advance for your guidance.
rustynail
 
Posts: 70
Joined: Mon Nov 21, 2016 6:21 pm

Re: 10 milion Leads on my Database

Postby mflorell » Thu Sep 28, 2023 6:29 am

Possible: Yes
Recommended: No

Anything over 10 million will result in slower querying of that table. We've had clients get to over 60 million leads in their vicidial_list table on a very high-end database server before, but at some point it will crash if it isn't pruned, and usually the crash will happen in the middle of the day without warning.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: 10 milion Leads on my Database

Postby rustynail » Wed Oct 04, 2023 11:51 am

Thank you so much Matt. Much appreciated.
I will try to grab the required DB server ...

A normal RAID-10 with default install will be fine or we need to keep DB on a separate partition and OS on another partition as recommended in some of posts in this forum?
rustynail
 
Posts: 70
Joined: Mon Nov 21, 2016 6:21 pm

Re: 10 milion Leads on my Database

Postby mflorell » Thu Oct 05, 2023 6:29 am

For large setups it's always recommended to have the MySQL(MariaDB) data partition on separate physical disks from the OS of the database server.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: 10 milion Leads on my Database

Postby rustynail » Fri Oct 06, 2023 12:35 pm

Thank you Matt,

I will check with my DC to provide this setup. So what I conclude here is that.

1. OS can be installed on single drive?
2. Mysql can be configured to use a RAID-10 drive setup with Megaraid card?
rustynail
 
Posts: 70
Joined: Mon Nov 21, 2016 6:21 pm

Re: 10 milion Leads on my Database

Postby mflorell » Fri Oct 06, 2023 8:42 pm

Yes and Yes.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: 10 milion Leads on my Database

Postby rustynail » Mon Oct 09, 2023 4:35 am

mflorell wrote:Yes and Yes.


Thank you so much. I am now working on it with my DC.
rustynail
 
Posts: 70
Joined: Mon Nov 21, 2016 6:21 pm


Return to Support

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 82 guests