MySQL storage is useful in the following scenarios:

In smaller environments (up to 10 agents), it is probably overkill to use MySQL storage, because the extra complexity will not be matched by an extra performance advantage.

The QM database storage engine was built with a need to adapt to existing MySQL schemas; therefore the database storage option is very flexible.

It lets you:

The storage system makes no assumptions on the underlying field layout of the table used, therefore you are free to define each field as you best see fit for your scenario.

To obtain these results, the SQL settings are divided into presets and partitions.

A preset is a schema definition to be used, i.e. the names of each field involved in database storage. You can have a number of different presets, e.g. to connect to different tables in the same database. Presets are defined in the WEB-INF/configuration.properties file.

A sample preset can be seen here:

# Preset 1: standard DB access. Edit to suit your DB needs.
sqlPreset.1.table=queue_log
sqlPreset.1.f_time_id=time_id
sqlPreset.1.f_call_id=call_id
sqlPreset.1.f_queue=queue
sqlPreset.1.f_agent=agent
sqlPreset.1.f_verb=verb
sqlPreset.1.f_partition=partition
sqlPreset.1.f_data1=data1
sqlPreset.1.f_data2=data2
sqlPreset.1.f_data3=data3
sqlPreset.1.f_data4=data4
sqlPreset.1.f_incr=unique_row_count

You can have more than one preset, by entering the same data multiple times under sqlPreset.1.., sqlPreset.2.., sqlPreset.3.. and so on.

The values for each field are:

A partition is a key under which separate entries are present in the same queue_log table. You could have separate servers - like test and production - uploading each one to a different partition, and each of them would be completely independent. This is also used for clustering scenarios, where a number of different Asterisk server upload data to the same database.

If you use a partition, your partition/time_id combo should be an access key for the table, as QM will access the table every time under this plan.

If you do not use a partition, just leave this field blank and make sure that time_id is an access key for the table.

There are a number of ways for data to be uploaded into MySQL. If we plan to use the real-time monitoring features, we must upload data to MySQL as events happen, in order to have them seen immediately by QM.

We have developed a very safe script suitable for small to very high volume for high-volume production systems called qloaderd. It can be easily started and stopped from the init.d commands and comes complete with start-stop scripts. Its main advantages are the following:

You can find it under the WEB-INF/mysql-utils/qloader; do not forget to read the installation docs that are in qloader-README file and to use the correct init-script for your system.

In the future, we expect Asterisk to be able to write queue_log data straight to a database via ODBC, so these tricks will not be necessary anymore.

After you configured the table in configuration.properties, using the table is only a matter of inputting

 sql:[partition]|[preset]

as the queue_log file name to analyze. The partition defaults to "" (blank) if absent, while the default preset is 1.

You can do it directly from the "Run custom report" form, or preset the file name in configuration.properties as you best see fit by setting the default.queue_log_file property.

Examples:

sql:P03

Means accessing the partition named "P03" for preset #1.

sql:X23|3

Means accessing the partition named "X23" for preset #3.

sql:|2

Means accessing the present #2 with no partition, and

sql:

Accesses preset #1 with no partition.

If you use agents pages, keep in mind that the value in realtime.max_bytes_agent will not be the portion of the queue_log to be read, but the time interval (in seconds) that will be read for the current agent (i.e. if set to 10000, it will search agent data for the last three hours or so).

When you enter a "sql:" file name, the error "The file sql: does not exist" means that there is a misconfiguration of the table access fields in configuration.properties.

As it is not very immediate to "see" if a partition is being loaded and how much information is available on the database, we provide a "Mysql Storage Information" page (accessible from the main "Edit settings" menu if the user holds the key USR_MYSQL) that provides general database information.

By clicking on the link, a new page will be loaded showing the available partitions; by clicking on the "Details" button, information for the chosen partition is extracted.

The total number of rows in table and the total table space is shown; for each partition, its minimum and maximum data entries and its "heartbeat", that is fake entries that the qloaderd process will add to notify the server that the connection is still alive even if Asterisk is producing no data.

The "number of calls" is a very rough estimate with no logic in it - it may differ a lot from the actual data calculated by reading the log. Only its order of magnitude should match the other reports.

For each partition, all distinct agents and queues are reported, and their first and last appearance on the database. The "Days" is the time difference in days between the first and last reference.

Please not e that accessing this page causes a number of table-scan queries to be performed on the MySQL table - the page might become irresponsive or MySQL can be slowed down if your queue_log table is very large.

If you do a number of deletes followed by inserts on the queue_log table, for example because you manually delete a partition and upload data in another one, the table access plan may become sub-optimal and performance may suffer. The same happens if you upload multiple queue_log instances at once to different partitions, for example if you run a cluster.

In this case, you can manually run the following MySQL query to optimize the table:

ALTER TABLE ‘queue_log‘ ORDER BY partition, time_id, unique_row_count

This might take a while to run and may lock your table until it’s complete. It is not necessary to run this query if you only upload data without ever deleting it for one single partition.

If you run a busy cluster, running it daily at a scheduled, off-peak time might produce the best results.

Since Asterisk 1.6.x and QueueMetrics 1.6.0 is possible to delegate the queue logging to the asterisk realtime subsystem. With this option the QueueMetrics MySQL database log will be replaced by the MySQL database populated by Asterisk. In order to have that working, you need to follow the steps reported below.

First of all you need to enable the Asterisk Realtime QueueLog subsystem, as reported in http://www.voip-info.org/wiki/view/Asterisk+queue_log+on+MySQL.

Then you need to change the default.queue_log_file key, in the configuration.properties file, in order to have something like that:

default.queue_log_file=astr:asterisk|3

This tells to QueueMetrics to instantiate the proper Asterisk realtime analyzer and to read, in this case, the 3rd preset. You need to add it to the configuration.properties file as follow:

sqlPreset.3.table=queue_log
sqlPreset.3.f_time_id=time
sqlPreset.3.f_call_id=callid
sqlPreset.3.f_queue=queuename
sqlPreset.3.f_agent=agent
sqlPreset.3.f_verb=event
sqlPreset.3.f_partition=
sqlPreset.3.f_data1=data
sqlPreset.3.f_data2=
sqlPreset.3.f_data3=
sqlPreset.3.f_data4=
sqlPreset.3.f_incr=id

This defines the table structure (and name) QueueMetrics will expect to find and must match the Asterisk realtime database definition. The database connection is specified in the xml file. For this reason you can’t split the QueueMetrics and the Asterisk Realtime database into different MySQL servers.

Please note that, if the Asterisk Realtime QueueLog subsystem is used, the qloader process is not needed anymore.