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.

Note

Starting from QueueMetrics 1.7.0b8 the qloaderd provided with QueueMetrics is no more compatible with previous QueueMetrics database versions. In order to have it working with previous QueueMetrics installations a database modification should be applied. This is limited to adding a new field data5 on the queue_log table. The new data5 field should have properties like other dataX fields already present in the table.

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 it 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.

	Caution
	As it is way more likely that a database will be temporarily down versus a simple text file, we generally suggest using the flat-file queue_log logging plus qloaderd, that is optimized to avoid any possible data loss in cases of MySQL unavailability.

As the procedure to follow is different based on the version of Asterisk you are running, please check the Asterisk version before continuing.

In any case, as a first step you need to enable the Asterisk Realtime QueueLog subsystem, as reported in http://www.voip-info.org/wiki/view/Asterisk+queue_log+on+MySQL.

As Asterisk will be logging data to its own database, it is of paramount importance that:

This can usually be obtained by issuing an SQL command like:

GRANT ALL PRIVILEGES
      ON asteriskdb.queue_log
      TO 'queuemetrics'@'%'
      IDENTIFIED BY 'javadude';

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

Also, as the default Asterisk tables have no concept of "partition", a placeholder partition ("-") is used instead.

Realtime on Asterisk 1.6

First 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=sql:-|a16

This tells QueueMetrics to instantiate the proper Asterisk realtime analyzer and to read, in this case, the preset called a16. You need to add it to the configuration.properties file as follow (if it is not present):

sqlPreset.a16.table=asteriskdb.queue_log
sqlPreset.a16.f_time_id=time
sqlPreset.a16.use_timestamp=true
sqlPreset.a16.f_call_id=callid
sqlPreset.a16.f_queue=queuename
sqlPreset.a16.f_agent=agent
sqlPreset.a16.f_verb=event
sqlPreset.a16.f_partition=
sqlPreset.a16.f_data1=data
sqlPreset.a16.f_data2=
sqlPreset.a16.f_data3=
sqlPreset.a16.f_data4=
sqlPreset.a16.f_data5=
sqlPreset.a16.f_incr=id

This defines the table structure (and name) QueueMetrics will expect to find and must match the Asterisk realtime database definition.

	Note
	The "table" entry is made up of the name of the Asterisk database followed by a dot followed by the name of the queue_log table.

Optimizing access performance

Thought the basic table definition will work out-of-the-box, QueueMetrics relies heavily on database access in order to produce any output. It is therefore important to issue the following statements on the Asterisk database so that its table is ready for QueueMetrics:

ALTER TABLE queue_log
  CHANGE COLUMN `time` `time` INT(10) NOT NULL DEFAULT 0;

ALTER TABLE queue_log ADD INDEX  qm_main ( `time`, `queuename` );
ALTER TABLE queue_log ADD INDEX  qm_hotdesk ( `event`, `time` );

The first statement makes sure that the time column be numeric, and the other two add QM’s main access indexes.

And what about the old access format?

Versions of QM before 1.7.2 used to have the format "astr:" to access the ARA database. This is still present but we do not encourage using it anymore.

Realtime on Asterisk 1.8+

First 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=sql:-|a18

This tells QueueMetrics to instantiate the proper Asterisk realtime analyzer and to read, in this case, the preset called a18. You need to add it to the configuration.properties file as follow (if it is not present):

sqlPreset.a18.table=asteriskdb.queue_log
sqlPreset.a18.f_time_id=time
sqlPreset.a18.use_timestamp=false
sqlPreset.a18.f_call_id=callid
sqlPreset.a18.f_queue=queuename
sqlPreset.a18.f_agent=agent
sqlPreset.a18.f_verb=event
sqlPreset.a18.f_partition=
sqlPreset.a18.f_data1=data1
sqlPreset.a18.f_data2=data2
sqlPreset.a18.f_data3=data3
sqlPreset.a18.f_data4=data4
sqlPreset.a18.f_data5=data5
sqlPreset.a18.f_incr=

This defines the table structure (and name) QueueMetrics will expect to find and must match the Asterisk realtime database definition.

	Note
	The "table" entry is made up of the name of the Asterisk database followed by a dot followed by the name of the queue_log table.

	Important
	As this table format does not preserve the insert ordering when reading, QM may produce incorrect results unless you perform all the steps described in the "optimization" section below.

Optimizing access performance

Thought the basic table definition will work out-of-the-box, QueueMetrics relies heavily on database access in order to produce any output. It is therefore important to issue the following statements on the Asterisk database so that its table is ready for QueueMetrics:

ALTER TABLE queue_log
 ADD COLUMN id INT(10) AUTO_INCREMENT NOT NULL FIRST,
 ADD PRIMARY KEY(id);

ALTER TABLE `queue_log`  DROP INDEX `bydate`;
ALTER TABLE `queue_log`  DROP INDEX `qname`;

ALTER TABLE queue_log ADD INDEX  qm_main ( `time`, `queuename` );
ALTER TABLE queue_log ADD INDEX  qm_hotdesk ( `event`, `time` );

The first statement makes sure that there is an order-preserving index on the table, so that lines that have been inserted in the same second still hold the original sequence when read.

The second set of statements drops indexes that are not needed by QueueMetrics; and the third set creates indexes that are needed for efficient data retrieval in QM.

You will also need to change the property:

sqlPreset.a18.f_incr=id

So that the order-preserving index is used by QM.