This appendix describes how to configure MyARM for using MySQL database with high transaction measurement numbers. For general information regarding MyARM or MySQL please consult the appropriate user manuals.

Motivation

MyARM is designed to measure and store (unlike other performance monitoring tools) all measured transactions of an ARM instrumented application into a connected database. This can result in high number of measurements which need to be stored into the database. This document describes how to configure MyARM using the MySQL database to achieve an optimal transaction INSERT throughput.

Scenarios

The ARM standard defines a variety number of optional features which influence the throughput of storing transactions into a database we need to define typical classes of scenarios. The following list defines the most common (items 1 to 5) used and worth case (items 6 and 7) ARM transaction measurements:

1. Simple transaction measurement without any additional data (Simple).
2. Simple transaction measurement with a parent correlator (ParentCorr).
3. Transaction measurement with 3 gauge metrics attached (Metrics).
4. Transaction measurement with 1 context value string with a size of 32 Bytes (Context 1).
5. Transaction measurement with 5 context value string with a size of 32 Bytes (Context 5).
6. Transaction measurement with 10 context value string with a size of 32 Bytes (Context 10).
7. Transaction measurement with 20 context value string with a size of 32 Bytes (Context 20).

Some of these scenarios are likely to be combined. For example it is quite usual that a transaction measurement has correlators as well as metrics and context properties. The tested scenarios should help to get an overview how additional data influence the overall database transaction throughput.

Test design

The following test design is used to get the transaction storing rate into a MySQL database. A simple C program is used to generate ARM transactions at a maximum rate. MyARM is configured to buffer all transaction measurements and write the buffered transactions into the MySQL database. When all transactions are written into the MySQL database the application terminates.

The elapsed real time divided by the number of transactions stored in the MySQL database gives an good overview of the maximal transaction throughput into the MySQL database.

To increase the maximal transaction throughput MyARM supports a so-called thread datasink which uses a defined number of threads to write data to a destination, here MySQL, datasink. Each thread open its own connection to the MySQL database. All test scenarios described above are executed with one, four, eight, ten or twelve MySQL datasink connections.

Hardware

All tests were executed on an Intel^®Xeon^®CPU E5-2660 v2 (10 cores with hyper threading) running Debian Linux 7.8 (amd64) with 128GB of memory and a 512GB Samsung SSD 840 PRO hard disk.

MySQL configuration

The MySQL version 5.5.43 (x86_64) is used and databases were created using the INNODB storage engine with the following parameters changes according to standard debian installation:

innodb_file_per_table

is set

max_binlog_size

is disabled

For a detailed description of the modified MySQL variables please consult the MySQL user manual.

MyARM configuration

The standard MyARM 4.0.x.0 configuration is used with the following property changes:

db_mysql.connections

is set to 1, 4, 8, 12 or 16 (maximum allowed value).

basic.armdata.buffer.size

is set to 196608.

basic.armdata.buffer.pool.max

is set to 1024.

agent.transaction.pool.max

is set to 2048.

agent.metric.pool.max

is set to 8192.

These property changes are made to support very high transaction measurement rates of the MyARM ARM 4.0 C agent. For a detailed description of the modified MyARM properties please consult the MyARM user manual.

Results

The Figure "MyARM transaction MySQL throughput" shows the results of all tests as described above. First of all the number of MySQL database connections influence the overall transaction throughput in any scenario. Thus if high transaction rates are expected configure MyARM to use more MySQL database connections (db_mysql.connections).

Within this test setup 12 MySQL database connections performs best. As a rule of thumb the number of MySQL database connections should be around the number of physical CPU cores to get best results. Within our test scenario 10-14 MySQL database connections produced the highest transaction rates per second. Above these numbers the transaction rate per second descreases slightly.

The shown results are the average transaction per second values from the last 10 MyARM builds of the version MyARM 4.0.x.0.

In real world ARM instrumented application a mixture of the outlined scenarios are present. However the following rules should be taken into account for instrumenting applications with ARM:

• Add only information (metrics, context properties) which are of real interest
• Generate only a correlator if its passed to a sub-transaction.
• Prefer context properties instead of metrics if possible.
• Use diagnostic detail for failed or aborted transactions instead of repeating information in any transaction for diagnosing errors.

The following table lists all average transaction throughput rates in detail:

One MySQL database connections result details

Four MySQL database connections result details

Eight MySQL database connections result details

Twelfe MySQL database connections result details

Sixteen MySQL database connections result details