Slow CDR Queries With More than 3M Rows

FreePBX Development
1

We thought we had a hardware problem with our system. We ran a call conversion script to transcode past calls from wav to mp3, and the line that updates each row in the CDR from the wav file to the mp3 file would sit for 20 seconds per call recording file. Each day there could be up to 6000 recorded calls on this system.

We then decided to look into creating an index in the asteriskcdrdb database. We used:

CREATE INDEX rec_file_index ON cdr (recordingfile);

Now the queries execute in a split second for each row. What could have taken hours to finish updating each row now finishes in minutes.

Should we put this index into all of our PBXes? Is there some reason we shouldn’t? We want our call conversion scripts to run as quickly as possible, and having to search through millions of rows for that specific query can take a very long time, even on an AWS EC2 t2.large instance.

Comments, thoughts, concerns? Are we doing a bad thing?

P.S. - The call conversion script we run to convert past calls is here:

#!/bin/bash
# A Script to Convert FreePBX call recordings from WAV to MP3
# Also updates the CDR database, for correct downloads through the web UI
# Version 1 - 2015/11/15
#
# Copyright Jaytag Computer Limited 2015 - www.jaytag.co.uk
#
# You may use or modify this script as you wish as long as this copyright
# message remains. Redistribution prohibited.
# Set the Asterisk Recording Directory
recorddir="/var/spool/asterisk/monitor"
# Start the Loop, store the path of each WAV call recording as variable $wavfile
for wavfile in `find $recorddir -name \*.wav`; do
# Make Variables from the WAV file names, stripping the file path with sed
wavfilenopath="$(echo $wavfile | sed 's/.*\///')"
mp3file="$(echo $wavfile | sed s/".wav"/".mp3"/)"
mp3filenopath="$(echo $mp3file | sed 's/.*\///')"
# Convert the WAV files to MP3, exit with an error message if the conversion fails
nice lame -b 16 -m m -q 9-resample "$wavfile" "$mp3file" && rm -frv $wavfile || { echo "$wavfile encoding failed" ; exit 1; }
# Update the CDR Database
mysql -u root -s -N -D asteriskcdrdb<<<"UPDATE cdr SET recordingfile='$mp3filenopath' USE INDEX(rec_file_index) WHERE recordingfile = '$wavfilenopath'"

#SELECT select_list FROM table_name USE INDEX(index_list) WHERE condition;

# On-Screen display of variables for debugging/logging
# echo ""
# echo "File -------------------------------------------------------"
# echo "Wav File : " $wavfile
# echo "Wav No Path : " $wavfilenopath
# echo "MP3 File : " $mp3file
# echo "MP3 No Path : " $mp3filenopath
# echo "End File ---------------------------------------------------"
# echo ""
# End the Loop
done
2

Submit it as a Feature Request. Include what you’re doing, why you’re doing it, and what the fix is. You may want to include the MP3 conversion script (which is best done in batch mode at the end of the day) or just the “CREATE INDEX” request. If it gets picked up, you’re golden. If not, you know how to fix it.

3

What release of FreePBX are you running?

On my machine I show that there is an index on the recordingfile column:

MariaDB [asteriskcdrdb]> SHOW INDEX FROM cdr;
+-------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| cdr   |          1 | dst           |            1 | dst           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| cdr   |          1 | accountcode   |            1 | accountcode   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| cdr   |          1 | uniqueid      |            1 | uniqueid      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| cdr   |          1 | did           |            1 | did           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| cdr   |          1 | recordingfile |            1 | recordingfile | A         |           0 |      191 | NULL   |      | BTREE      |         |               |
+-------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.