CDR search takes a very long time

General questions
1

Hello! I use ASTPP v4, CDR search UI takes a long time and admin interface is search fast, can someone tell me how to fix this iss.

2

Hi @chserhii2,

I have a large installation that uses 4.0.1 community. I don’t find this to be a problem.

What part of the interface are you referring to when performing the search?
What are your search parameters?

Also what are the specs of the server you have ASTPP installed on and OS?

3

@chserhii2
We haven’t face such issue if server properly configured and having sufficient environment.

Please check utilization of services when you perform above action. Such as nginx, mysql, fpm as well as check error logs.

4

Good day! user interface, search takes more than 3 minutes per day. Is this the norm?

vm centos 7.9 core 4 ram 8

5

There are no errors in the logs. The second server has the same problem. This is only a problem with the user interface.

6

Well !! Have you monitor your cpu utilization and above mention services if no then check once there might be some service which take more load and possibly due to that it takes time in your case.

7

I’ve never had a search take more than a few seconds unless my search returns hundreds of thousands to over a million rows.

Specifically describe your steps so I can identify which part specifically you are having trouble with.
Like this:

  1. Go to Reports > Customer CDRS
  2. The search paramaters are: Dates from X to Y, Called Number equal to Z
  3. Press search and then 3 minutes later X rows are returned.
8
  1. Customer interface → Reports → CDRs
  2. Do nothing by default
  3. 4000-4500
    https://youtu.be/ZP3flQLmle8
9

I believe you have a load issue.

Please post more of the log, I suspect you may have a lot of call attempts going through (damn script kids).

Do you have fail2ban configured and working? The default installation doesn’t always work out of the box and sometimes needs tweaked.

10

Moreover connect to mysql and check show full processlist to understand the number of queries system is executing. That might help to optimize.

11

Hi lovely people,
I’m having this very same issue, if I do a CDR report with the user admin it works perfectly and quickly, but if I do the same query from a customer account the report never shows up. These are my findings:

I noticed that this process was failing:

image
image1612×415 133 KB

So I did some “debug” and found out that this query was going pretty fine (0.01 sec):

SELECT `callstart`, `is_recording`, `sip_user`, `callerid`, `call_direction`, `callednum`, `pattern`, `notes`, `billseconds`, `disposition`, `debit`, `cost`, `accountid`, `pricelist_id`, `calltype`, `is_recording`, `trunk_id`, `uniqueid`, `uniqueid` as uid
FROM (`cdrs`) WHERE `callstart` >=  '2022-07-14 11:00:00' AND `callstart` <=  '2022-08-02 10:59:59' AND `accountid` =  '178' ORDER BY `callstart` desc LIMIT 25

But, this other was going awful (+25 sec):

SELECT * FROM (`cdrs`) WHERE `uniqueid` =  'd81b974a-0383-11ed-bbf5-f114a8740aca'

This is very rare, as the first one is bigger and larger than the second one, so I verified the indexes on “cdrs” table and decided to create an index on the column “uniqueid”, like this:

alter table cdrs add index uuid (uniqueid);

And now the report works perfectly, the second query that I spoke of now runs in 0.01 seconds or less, and the report also runs quickly.

Now, here goes my question, indexes impact performance in other areas, what do you think of adding an index to the column “uniqueid”? can this impact the performance of Freeswitch?

Regards, and thanks for your awesome work!

12

Hi @david.bohorquez

Thanks for the detail explaination and suggestion.
Setting index on uniqueid will not impact on performance.