10 milion Leads on my Database

[boiken]

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

[mflorell]

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?

[boiken]

Sorry i mistyped. The numer of the leads is 10 million.
Im using a single SSD HDD connected on a lsi raid card

[mflorell]

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.

[boiken]

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 ?

[mflorell]

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.

[boiken]

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 ?

[mflorell]

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.

[rustynail]

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.

[mflorell]

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.

[rustynail]

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?

[mflorell]

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.

[rustynail]

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?

[mflorell]

Yes and Yes.

[rustynail]

Yes and Yes.

Thank you so much. I am now working on it with my DC.