DATABASE Maintainance

[avi]

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?

[mflorell]

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.

[avi]

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.

[mflorell]

How many days/months/years have you been dialing with this system?

[avi]

For Last three month.and althrough these script putted in crontab.so it should auto run on specified time.

[mflorell]

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;

[Op3r]

I was wondering about this.

How do you generate the reports?

[mflorell]

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.

[SlavaE]

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]

So,I tried it on production server the answer is YES, you can erase vicidial_log_extended with everything still working.

[williamconley]

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.

[bigbozza]

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.