Wiki source code of Missing MySQL CDR Index

Last modified by colinw on 2021/09/10 22:40
Show last authors
1 ====== On this page ======
2
3
4
5 {{toc maxLevel="2"/}}
6
7 == Symptoms ==
8
9 When starting the iSymphony V3 server, any of the following symptoms may present themselves:
10
11 * (% style="line-height: 1.4285715;" %)The mysqld process consumes a large amount of CPU time for long periods of time
12 * (% style="line-height: 1.4285715;" %)The java process for iSymphony consumes a large amount of CPU time for long periods of time
13 * (% style="line-height: 1.4285715;" %)iSymphony appears to not start up completely, or there are missing pieces of the interface (for example, some sections of the settings page in the administration interface may not appear)
14 * The error message "Timeout while waiting for recording executor to finish all threads" appears in the logs
15
16 == Cause ==
17
18 iSymphony relies on CDR (Call Detail Record) entries to query information about call recordings in your system due to the fact that not all metadata about a recording can be placed in the recording file name for iSymphony to parse out. If you have a very large amount of CDR entries in your database these queries can take a very long time and one query needs to be made for each recording in your system.
19
20 == Resolution ==
21
22 In order to speed up these queries you will need to index the uniqueid column in the Asterisk CDR database table. The steps to index the uniqueid column will vary based on the Asterisk configuration.
23
24 Below is an example on how to index the unique id column in a FreePBX based system utilizing a mysql database:
25
26 1. (((
27 Run the following command to get the database username and password. They will be labeled "AMPDBUSER" and "AMPDBPASS".
28
29 {{code language="bash"}}
30 cat /etc/freepbx.conf
31 {{/code}}
32 )))
33 1. (((
34 Next run the following command to log into the mysql database:
35
36 {{code language="bash"}}
37 mysql -u <database username> -p asteriskcdrdb
38 {{/code}}
39 )))
40 1. (((
41 Enter the database password when prompted.
42 )))
43 1. (((
44 Run the following command to create the index:
45
46 {{code language="sql"}}
47 ALTER TABLE `cdr` ADD INDEX `uniqueid` (`uniqueid`);
48 {{/code}}
49 )))
50 1. (((
51 Run the following command to verify the index was created:
52
53 {{code language="sql"}}
54 SHOW INDEX FROM cdr;
55 {{/code}}
56 )))
57 1. (((
58 Type the following command to exit the mysql command line:
59
60 {{code}}
61 \quit
62 {{/code}}
63 )))
64
65 == Related Content ==
66
67 {{expand title="Expand to see related content"}}
68 {{unmigrated-inline-wiki-markup}}
69 {dynamiccontentbylabel:showLabels=false|showSpace=false}
70 {{/unmigrated-inline-wiki-markup}}
71 {{/expand}}

Search

Navigation

iSymphony