Report data archiving

[Vince-0]

Hi!
I have a problem whereby any reports before a certain date 2012-11-12 show no data or "no calls for this period for these parameters".

It appears as if the reports are not looking at the archive tables. I usually archive anything before 1 month.

Tested:
All reports before this date: export calls/leads,carrier,agent or any other reports show 0 calls or "no calls for this period".
There are no web server or database errors in the respective logs.
No crashed database tables, repaired and optimized anyway.
If I run the report queries manually they return 0 records.
I can see minimum call_date and start_time data corresponding to data from 2012-11-12 in the log tables and a minimum call_date from 2012-08-06 in the respective archive tables.

Is this normal behaviour to have no reports from archive tables and if so how can I get report output for those periods?

Any advice would be greatly appreciated,
Vincent.

--
Version: 2.6-372a BUILD: 120713-2123 from Vicibox 3.1.15 ISO and upgraded to astguiclient 2.6
Load: 0.03 0.06 0.02
HW Specs: Dual Xeon Hex E5630@ .53GHz 32GB RAM, Dialerx4: Xeon Quad X5355@2.66GHz 8GB RAM, Web: Dual Xeon Quad E5420@2.50GHz 16GB RAM
Codecs: ulaw on softphones / g729 to carrier.
Termination: VoIP IAX
OS: DB: CentOS 6.2, Web+Dialers OpenSuse from Vicibox 3.1.15

[Vince-0]

As an interim solution I have made a temporary database with the archive tables renamed to the original table names. This works because the table schemas are the same in the archive tables so operations can get historical reports out until I can find a permanent fix for this.

So my next question is: what work is involved with rewriting the reports to look at the archives as well as the current tables? I need to see if that is viable.

--
Vincent

[williamconley]

That is indeed viable, but beware the server overload moment from switching to (or including) the archive tables. If you have 1.2M records in your live tables and 10.5M in your archive, a report that ordinarily takes 2.1 second to run can take ... well, a lot more (and hammer your cpu, possibly even causing calls to delay and/or route incorrectly).

That being said: You can solve the issue by creating a reporting server and NOT moving the data from live to archive in that server. Use it for reports only. You can also pay to have an "include archived data" option placed in the report(s) in question and the report can be modified via alteration of the sql commands to join the fresh/archived tables together to generate the reports. Alternately, it could be a 3-item dropdown allowing default "live" and optional "live+archive" and "archive only" to allow for older reports to be run without bothering the live tables at all. After all, locking the archive tables to run a long report should have no impact on the live tables at all.

The first such report would be the most expensive, after that the same technique could be used on all reports. After the first one is done and the code published, we may even get a bit of crowd-coding if we get lucky.

[Vince-0]

I will propose your recommendations, thanks wiliamconley.

A slave database is in place for reporting other than real time. I've identified the slowest table as being vicidial_agent_log with 2 months of data in it and 10 million+ rows so I adjusted archiving to keep 1 month. I am yet to identify the queries running with improper indexes elsewhere, which apparently there are according to the mysql tuning script.

I will forward feedback when I can.

Vin.