19. Storing queue data on MySQL

QueueMetrics lets you store the queue_log data on a MySQL table and is able to produce the very same analyses - including real-time analyses - from data stored on a database.

This scenario is mostly useful for large call centres, where the queue_log data starts to be quite large and the main Asterisk server is quite busy handling its own traffic. In this case, it would be a better solution to have QM run on a separate server, so that even if it has to run a huge analysis the main Asterisk server will not be slowed down.

QM lets you have a deployment scenario like the following one:

./Pictures/image133.png

In this case, we see that we are using two separate servers; one for the database and one for the QueueMetrics server itself. It is possible to use the same server for both the database and QM, or to consolidate the database on an existing database server and QM on an existing servlet container.

It is very important that all the servers share the same system time; this way real-time events will be shown in an exact way [1]

19.1. Who should use MySQL storage?

MySQL storage is useful in the following scenarios:

  • Large call centres with a very busy or mission-critical Asterisk server
  • Large QM reports run very often
  • A large number of agents reloading QM agents pages
  • Clustered call-centres monitored by a single QueueMetrics instance

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.

19.2. Understanding MySQL storage

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:

  • Define the names of each SQL field
  • Define the name of the SQL table used (it must reside in the same database as the other QM tables)
  • Define one or more table partitions

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:

  • Table is the table name
  • Time_id is the first field in the queue_log. This is used for most extractions and should be an access key.
  • Call_id is the second field of the queue_log
  • Queue is the third field of the queue_log
  • Agent is the fourth field of the queue_log
  • Verb is the fifth field of the queue_log
  • Data1..Data4 are the remaining fields of the queue_log. Currently Data4 is not defined in the queue_log; in case just leave it blank.
  • Partition is a logical partition of the table.
  • Incr: as the minimum time detail for Asterisk activity is by the second, it is possible that events that happen on the very same second seem to happen in the wrong, meaningless order when the data is read back from the database. It is possible to define an auto-increment field on the table that is used to make sure that rows are fetched from the database in the same exact order they were inserted into. This table definition is the default for QM since version 1.1

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.

19.3. Uploading data to MySQL

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:

  • Extra safe: will check for duplicate lines in the database
  • Extra safe: will retry loading data on MySQL connection errors
  • Creates a full import log
  • Can be started/stopped as a standard system service

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

19.4. Loading data in QueueMetrics

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.

19.5. Checking MySQL database status

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.

./Pictures/image135.png

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.

19.6. Optimizing the queue_log table

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.

19.7. Using the Asterisk Realtime QueueLog subsystem

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]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:

  • The Asterisk database is kept on the same MySQL server as the QueueMetrics database
  • The user (generally queuemetrics) that Qm uses to connect to the database has read grants on Asterisk’s database

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



[1] The ntpdate command can be used on Linux to synchronize the system clock to an external timing source with a high degree of precision. Usage in a daily cron script is highly recommended