How to cleanup big log data

[tys-b]

I need to cleanup old call log data, currently we have 16 million calls in call history.
This is a problem because we have a external integration tool accessing the data to get information and things are to slow because database is getting too big.
I know i can use reports > settings > clear call logs
If i try that the systen is getting slow,, web management console will give errors and mail notification will stop workig, probably there is so much data 3cx can not handle this big cleanup.

is there a better way to cleanup al call log data lets say everything older then 2 years.

It would be better to have someway to fully automatic delete old call log data.

 

[fgc92210]

By browsing the database, there is a table called my phone_callhistory_v14
I assume it is a table which has been used since V14, but I also suggest to look at the other call_history tables.Take a backup, and see if deleting calls from this database table solves your issue.

If you are not comfortable with databases, then I do NOT recommend trying this.
Database administration is not something which can be taught in 5 minutes over a discussion forum.

 

[NickD_3CX]

@tys-b
In your Management Console go to "Reports" and here press on "Settings".
Here you will see a section for clearing the Call Logs. In the drop-down select the last option and set the range for which you want the call logs to be deleted.

Be sure you enter the correct date because this is irreversible, the only way to get the call records back is by restoring a backup.

One more thing, avoid accessing the database, even accessing it alone while the system is operational, under circumstances, can cause problems.

 

[fgc92210]

@NickD_3CX I understood from his first post that he tried that, but it locks the server and he gets errors? Hence why I suggested the DB.

@tys-b maybe you should give a little more details about what happens exactly when you try the report call log cleaning?

 

[tys-b]

yes thats the problem it locks up the system, so i'am still looking for a solution, i do not like to directly access the database.

 

[NickD_3CX]

yes thats the problem it locks up the system, so i'am still looking for a solution, i do not like to directly access the database.

The only supported way is through the MC, I am curious though, what specs is the machine running on? DB operations usually use 1 CPU core, so if there are more, the system should be operational to an acceptable level.

Anyhow, the only other thing you could do is break the periods into smaller segments, so for example do 2 months at a time. Generally though, of multi-core machines, this shouldn't happen.

 

[tys-b]

well i did try deleting in smaller segments but that is way too much time consuming and even then it sometimes does not seem to work.

Specs are not a issue,
Intel Xeon E5-2407, 6x300gb 10k rpm sas disk in raid5
hyper-v running 3cx vm with 16gb memory, a firewall vm and a dns/dhcp/ad vm, no heavy stuff

 

[NickD_3CX]

I am afraid you might have to put up with this for a little time while the process finishes, although deleting large number of entries shouldn't take very long.
I do have a question though, how do you stop this process after starting it? Generally if you put the customer dates in the MC and press the "Clear" button, it starts without an abort button.

Also this integration, how exactly does it work and what data does it pull? Do we know how it is doing this? Because if it is accessing somehow the DB, the this could be slowing everything down too.

 

[tys-b]

oke first when starting the cleanup job voip phones keep working without problems but the mail notification stops working and accessing the management console wil give errors.
to resolve this i scheduele a reboot outsite office hours.

The crm tool is a plugin for 3cxphone, on a call it wil access the 3cx database (read only) to query call log history for the calling number.

i know a few other companies are using the same plugin without problems BUT these other companies are small maby 10 to 50 extentions we have currently 400 extentions up and the dashboard shows call history of 15050028 calls

So the general idea is to cleanup old call log data, this perhaps helps to speedup thee used 3cx plugin, we are working with the plugin vendor on this.

The vendor also says that the call history table do not have sql database indexes, and making indexes on this database table would also speedup things.
But i do absolutly not want to touch the 3cx database.

So thats why i'm working on this issue.

 

[NickD_3CX]

I am a bit afraid in case something has happened to the Database, because deleting records when the DB stops could lead to issues, because it is not only one table that is used, its more, so one remaining entry in 1 table could be a problem.
I'd like to ask you for a favor, try deleting just a month worth of recordings, and keep track on how many call records entries are before and after and how long it took.

I am very curious to see the rate that your system is deleting entries. Of course as this may cause you issues, maybe try doing it outside office hours.

 

[tys-b]

Oke did some testing, i still think there is too much data,
and 3cx/database can not handle this amount of data.

reports>settings>cleanup>2018-01-01 to 2018-01-31 (1 month)
Within some minutes error appears management console not responding

Oke I just wait for 45 minutes, maby its still running in the background.


oke now we result on dahboard call history before/after.
start-end=result 15053335-15053791=456
Looks like about 456 call records are deleted, that can't be right.

So i ran a call report for jan 2018.
Wich is extremely annoying because as stated before at this point the email notification does not work anymore.
So for the rest of the day i can not deploy news 3cx activations and people do not
recieve notifications.
Until i reboot this 3cx system.
(Yeah i know should do this after office hours but just don't have the time)
And i can tell you after that the report
will stil show a month of call history data for januari 2018

 

[NickD_3CX]

I agree this is not normal at all, the rate at which the entries are being deleted is way to slow, regardless of system specs and HDD type.
Also I agree that the DB is quite large, but millions of call recordings is not unusual for a 3CX system, postgres can handle way way more.

What I would suggest then, as a first step, enable Verbose logging, then try deleting just a single day.
Then open a ticket to the 3CX Support Team so that they can have a look, because there may be something else going on in this case.

 

[fgc92210]

You have multiple things to take in account here. Make sure that there is no concurrent write access in the database would be one. Did you try to run a VACUUM ANALYZE before the deleting operation?

@NickD_3CX, he might have to increase temporary buffers. As long as most of the database can fit into memory, or possibly all of it, that would be the fastest way to delete millions of records.

Maybe TRUNCATE if there are no foreign keys used? I did not look deep into it to say if it does or not.

 

[NickD_3CX]

@NickD_3CX, he might have to increase temporary buffers. As long as most of the database can fit into memory, or possibly all of it, that would be the fastest way to delete millions of records

I am against touching anything like this, the DB with the settings that it currently has are optimizes more or less for the amount of entries. Beyond that, tweaking the buffer could improve slightly the performance, but in no way is the current deletion rate justified.

TRUNCATE again would imply direct access, and unfortunately the data there is shared among other tables.
I would only allow a member of the 3CX Support Team to resort to that, and only in the event that you would be OK in completely emptying all tables, not just between some dates.

I think at this point, as I suspect something else may be going on, to contact the support team and take it from there.
At least, this is my advice.

 

[SteveITS]

way to fully automatic delete old call log data

In a general self-maintenance sense this would be nice, to set it to self delete logs older than "n" months or years. People could leave that unchecked or set it if they wish.

 

[tys-b]

oke currently a bit busy but in a few weeks i will get back on this because really in need for a solution i contact support but not sure if they help me, i only have a support contract with a reseller, whom is nog very usefull on these kind of tecnical issue's.