DATABASE Maintainance

Discussions about development of VICIDIAL and astGUIclient

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

DATABASE Maintainance

Postby avi » Sat Sep 20, 2008 7:00 am

Hi every one.

120 agents dialing in predictive mode and i have 150 channels.problem is that my database is not able to simultaneously update userstate/channels state .due to it my only 35-40 agents are on call and rest waiting for call.then how can i reduce wait time?is there any database maintenance script available which i can use everyday for database maintanance?i dont want to use manually because it may cause other problem.or how can i overcome this problem?
avi
 
Posts: 57
Joined: Tue Sep 16, 2008 4:34 am
Location: india

Postby mflorell » Sat Sep 20, 2008 7:48 am

What is the loadavg on your database server?

what are the hardware specs on this machine?

Other than the ASTDB_optomize.pl and the AST_reset_mysql_vars.pl scripts that should be run at night, we don't really have any pre-made database optimization scripts, but if you are dialing at a high volume, then you may want to roll the call_log and vicidial_log tables into archive tables if they are into the millions of records.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Postby avi » Tue Sep 23, 2008 2:58 am

Thanks Allots for reply.


8GB RAM,140GB HDD(SAS),
Almost time we can use only 3-4 GB RAM,here Cache and swap is also build.60M records in call_log,vicidial_log.
problem with these table that we cant remove any records ,becouse some reports dependent on these table.

if ASTDB_optomize.pl and AST_reset_mysql_vars.pl script is able to update database tables as the way as written the dialer to work .then its worked fine.as i think there is some problem with updation of states like agents,phone etc.which could not sent the right status to the dialer.
avi
 
Posts: 57
Joined: Tue Sep 16, 2008 4:34 am
Location: india

Postby mflorell » Tue Sep 23, 2008 8:13 am

How many days/months/years have you been dialing with this system?
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Postby avi » Mon Sep 29, 2008 7:29 am

For Last three month.and althrough these script putted in crontab.so it should auto run on specified time.
avi
 
Posts: 57
Joined: Tue Sep 16, 2008 4:34 am
Location: india

Postby mflorell » Mon Sep 29, 2008 8:09 am

Here is what I do monthly at several of our larger clients:

Create archive tables without auto-increment in the primary index fields, then run these queries

SELECT count(*) from call_log;
SELECT count(*) from call_log_archive;
INSERT IGNORE INTO call_log_archive SELECT * from call_log;
DELETE FROM call_log WHERE start_time < '2008-03-01 01:00:00';
optimize table call_log;
DELETE from call_log_archive where channel LIKE"Local/9%" and extension not IN(' 8365','8366','8367','8368') and caller_code LIKE "V%" and length_in_sec < 75 and start_time < '2008-03-01 01:00:00';
optimize table call_log_archive;


SELECT count(*) from vicidial_log;
SELECT count(*) from vicidial_log_archive;
INSERT IGNORE INTO vicidial_log_archive SELECT * from vicidial_log;
DELETE FROM vicidial_log WHERE call_date < '2008-03-01 01:00:00';
optimize table vicidial_log;
optimize table vicidial_log_archive;

SELECT count(*) from server_performance;
DELETE FROM server_performance WHERE start_time < '2008-03-01 01:00:00';
optimize table server_performance;

SELECT count(*) from vicidial_agent_log;
SELECT count(*) from vicidial_agent_log_archive;
INSERT IGNORE INTO vicidial_agent_log_archive SELECT * from vicidial_agent_log;
DELETE FROM vicidial_agent_log WHERE event_time < '2008-03-01 01:00:00';
optimize table vicidial_agent_log;
optimize table vicidial_agent_log_archive;
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Postby Op3r » Wed Sep 21, 2011 6:26 am

I was wondering about this.

How do you generate the reports?
Get paid for US outbound Toll Free calls. PM me.
Op3r
 
Posts: 1424
Joined: Wed Jun 07, 2006 7:53 pm
Location: Manila

Postby mflorell » Wed Sep 21, 2011 8:42 am

you want to generate reports on 12-month old data?

The whole reason for rolling the logs to to prevent locking of the database. Allowing reports based upon the same number of data records could still lock the database. So the reports can only be run based upon the data that is in the active log tables, not the archive tables.

We have clients that have separate reporting database servers that they use to run long-duration and old reports and do not have their logs rolled, but the reports do not pull from archive tables themselves.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re:

Postby SlavaE » Wed Feb 06, 2013 1:57 pm

mflorell wrote:Here is what I do monthly at several of our larger clients:

Create archive tables without auto-increment in the primary index fields, then run these queries

SELECT count(*) from call_log;
SELECT count(*) from call_log_archive;
INSERT IGNORE INTO call_log_archive SELECT * from call_log;
DELETE FROM call_log WHERE start_time < '2008-03-01 01:00:00';
optimize table call_log;
DELETE from call_log_archive where channel LIKE"Local/9%" and extension not IN(' 8365','8366','8367','8368') and caller_code LIKE "V%" and length_in_sec < 75 and start_time < '2008-03-01 01:00:00';
optimize table call_log_archive;


SELECT count(*) from vicidial_log;
SELECT count(*) from vicidial_log_archive;
INSERT IGNORE INTO vicidial_log_archive SELECT * from vicidial_log;
DELETE FROM vicidial_log WHERE call_date < '2008-03-01 01:00:00';
optimize table vicidial_log;
optimize table vicidial_log_archive;

SELECT count(*) from server_performance;
DELETE FROM server_performance WHERE start_time < '2008-03-01 01:00:00';
optimize table server_performance;

SELECT count(*) from vicidial_agent_log;
SELECT count(*) from vicidial_agent_log_archive;
INSERT IGNORE INTO vicidial_agent_log_archive SELECT * from vicidial_agent_log;
DELETE FROM vicidial_agent_log WHERE event_time < '2008-03-01 01:00:00';
optimize table vicidial_agent_log;
optimize table vicidial_agent_log_archive;

Thank's a lot for this information!

One problem I have is that I wrote my own set of scripts for reporting and would not want to delete any records from vicidial_log.
Can I remove all records from vicidial_log_extended only without any repercussions?

Thank you!
SlavaE
 
Posts: 10
Joined: Wed Feb 06, 2013 11:30 am

Re: DATABASE Maintainance

Postby SlavaE » Thu Feb 07, 2013 5:13 pm

So,I tried it on production server the answer is YES, you can erase vicidial_log_extended with everything still working.
SlavaE
 
Posts: 10
Joined: Wed Feb 06, 2013 11:30 am

Re: DATABASE Maintainance

Postby williamconley » Sun Feb 10, 2013 12:36 pm

Removing all records from log files affects only reporting.

Generally it is recommended that those records be moved to a table with _archive appended instead of deletion (in case you have second thoughts one day and want to retrieve them ...) or pushed to a slave DB for perma-storage. But if you have no care for storage, all "log" tables, including extended, can be emptied at any time. Along with /var/log/asterisk and /var/log/astguiclent.
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: DATABASE Maintainance

Postby bigbozza » Wed Sep 11, 2013 7:28 pm

Hi there, I have a similar problem with one of my clients. I didn't setup the Vicidial server but have been tasked with maintaining it, which has been a learning experience as i've only ever dealt with freebpx and its various flavours in the past.

My problem is that every so often, without the hard drive filling up, the call_log_archive becomes corrupted. It is enormous because they are a very active call centre. It takes ages to repair and of course time is money to these people.

Should this be the case?
Do I need to keep this archival data?
Can I drop stuff older than say, a month?

Any help would be greatly appreciated.
bigbozza
 
Posts: 9
Joined: Wed Sep 11, 2013 7:24 pm


Return to Development

Who is online

Users browsing this forum: No registered users and 48 guests