UCP Call history slow to load


(Igor Dobrosavljevic) #1

I’ve got a client that extensively uses the Call History panel in the UCP to listen to past call recordings. It’s worked great for the past two years, however as the call history has gotten bigger and bigger as they use it it’s taken longer and longer for the information to load or to perform a search in it.

I suspect because it performs the function on the entire call history over the lifetime of the phone system.

Is there a way to truncate that call history down to only say the last month of calls to speed up the performance as they don’t really care or need access to the history past that.

Is this maybe something I should setup a support ticket on?


(Dave Burgess) #2

There are a lot of regular forum readers smiling under their masks right now, since this is the opposite of what most people want. Usually, we hear about people not getting enough recordings in their UCP feed and asking to remove the non-existent limit. Perhaps it’s time to implement the limit every one has complained about so bitterly. I’d start with a Feature Request asking for some kind of limit that you can optionally add. UCP is getting mature enough now, though, that people that have had good histories are going to start seeing this more and more as a performance problem.


(Igor Dobrosavljevic) #3

Ha, yea having the life time history loading and having to perform a search on it is starting to make it unsaleable for some of our end users because of the performance issues.

I take it there is no way to limit the scope of the history then?


(Dave Burgess) #4

Not yet. Not until someone that has some interest does one of two things:

  1. Submit a feature request and hope someone gets to it.
  2. Use the source, come up with a patch, and submit it attached to a feature request.

Let us know which way you decide you want to go. :slight_smile:


(Igor Dobrosavljevic) #5

I wish I had the coding knowledge to do it.

I’d be happy to submit a feature request. Is this still a valid way to go about that:

https://wiki.freepbx.org/display/SUP/How+to+open+a+Feature+Request


#6

Lets first confirm your indexes are in place

 mysql asteriskcdrdb -e "show indexes from cdr;"

(Dave Burgess) #7

Yeah - start at the Issues link at the top and work your way down. @dicko does offer a good suggestion - in your MySQL database, make sure you have the indexes set up to speed up the process.


(Igor Dobrosavljevic) #8

Seems like things are good:

±------±-----------±--------------±-------------±--------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±------±-----------±--------------±-------------±--------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| cdr | 1 | calldate | 1 | calldate | A | 471834 | NULL | NULL | | BTREE | | |
| cdr | 1 | dst | 1 | dst | A | 523 | NULL | NULL | | BTREE | | |
| cdr | 1 | accountcode | 1 | accountcode | A | 19 | NULL | NULL | | BTREE | | |
| cdr | 1 | uniqueid | 1 | uniqueid | A | 1887336 | NULL | NULL | | BTREE | | |
| cdr | 1 | did | 1 | did | A | 527 | NULL | NULL | | BTREE | | |
| cdr | 1 | recordingfile | 1 | recordingfile | A | 269619 | 191 | NULL | | BTREE | | |
±------±-----------±--------------±-------------±--------------±----------±------------±---------±-------±-----±-----------±--------±--------------+


(Igor Dobrosavljevic) #9

As a follow up. I paid for support time and opened up a support ticket (Ticket ID: 955595) and was told that there was nothing that can be done for me and to send a feature request to some generic email address.

To be honest I am a bit at a loss here. A feature of the phone system stopped working for me correctly and the answer that I am getting from paid support is that I am SOL?

@lgaetz do you have any advice for me possibly?


(Igor Dobrosavljevic) #10

As a follow up. A support engineer helped by creating a script that we’ll run once a month that simply drops records older then two months, here it is for anyone else that’s running into the same problem and would like to implement the same solution:

#!/bin/bash

mysql -D asteriskcdrdb -e “DELETE FROM cdr WHERE calldate < DATE_SUB(NOW(), INTERVAL 2 MONTH);”
mysql -D asteriskcdrdb -e “DELETE FROM cel WHERE eventtime < DATE_SUB(NOW(), INTERVAL 2 MONTH);”
mysqlcheck --auto-repair --optimize --all-databases

If you are looking at this years from now keep in mind that the structure of the databases may have changed and that the above commands would need to be adapted to that.