Translations of this page:

Osmius

Osmius agent for MYSQL
Agent name: osm_ag_MYSQL001 Agent code: MYSQL001
Content: User manual Osmius agent for MYSQL
Date: 20/07/2007 Revision date: 14/08/2007

General Information

This agent can monitor several parameters of a Mysql server. It has been tested in several Mysql Server in Windows and Linux, but we recommend checking its functionality before implanting it in a production environment.

MYSQL agent has been developed using functionalities and enhancements of Osmius framework and ACE libraries, so it is necessary to install the ACE libraries for the proper deployment and operation of Osmius agent for MYSQL. See chapter: installation.

MYSQL agent provides up to 12 basic events with configuration parameters to allow scaling of a very simple way. Events have been selected by the Research and Development Osmius Team as the most interesting for this first development.

All events are remote, so not intrusive, and multiplatform. Osmius Agent for MySQL is ready to reuse connections to the database and try to reconnect only when one event doesn't work.

MYSQL Instance

As a general rule each Osmius agent can monitor one instance type. If you are not familiar with these concepts check out the glossary. Each instance is individually defined in the configuration file (if you want further information go to agents and instances); depending on agent type is the instance type and depending on instance is the connection info.

CONNECTION_INFO

The connection information or connection_info is data that the agent needs to know to connect to the instance. (See more about the connection_info

For Osmius MYSQL agent the connection_info prototype would be:

CONNECTION_INFO= -u USER -p PASSWORD -d BD_NAME -h BD_SERVER -l PORT

Replace the following:

  • USER: User with access to the MYSQL database. Mandatory.
  • PASSWORD: User password. Mandatory.
  • BD_NAME: Name of the MySQL database. Mandatory.
  • BD_SERVER: Target IP (or host name if your DNS is able to resolve it) of the MYSQL database server. Mandatory.
  • PORT: Port number of the MYSQL database. 3306 for default. Optional.

Examples:

CONNECTION_INFO = -u user -p passwd -d osmiusbd -h localhost -l 3307
CONNECTION_INFO = -u user -p passwd -d osmiusbd -h 192.168.12.13

OBSERVATION: We recommend creating a unique user with restricted permissions to be used by the agent.
WARNING: The user and password is saved in the osmius database in plain text.

TYPE

The type defines the instance type to be monitored. Every declared instance must be associated with a type as you can see here

For MYSQL agent:

TYPE= MYSQL001

Event summary table for MYSQL

Here's briefly the capabilities of this agent, further down on this page each event is described in more detail.

EVENT DESCRIPTION c w a tseconds Extra parameters / Remarks
MYOPETBL Opened Tables 0 100 200 3600 If this value is high you must increase table cache .
Use it in capacity plannings.
MYUPTIME Uptime in seconds 1 60 30 300 Silent mode ( -s) recommmended
MYNUMLCK Number of Lock not acquired immediately. 0 90 95 600 Silent mode ( -s) recommmended
MYFULSEL Number of Full Scan Joins. 0 1 5 3600 Silent mode ( -s) recommmended
MYLONOPS Long duration Queries. 0 1 5 3600 Silent mode ( -s) recommmended
MYHRDNXT Next row reads. 0 10 50 3600 Silent mode ( -s) recommmended
MYQRYSEC Questions per second. 0 20 100 600 You can use it to show the load of the system along the time
MYBYTREC Bytes received per second. 0 128 256 600 You can use it to show the load of the system along the time.
Silent mode ( -s) recommmended
MYBYTSND Sent received per second. 0 128 256 600 You can use it to show the load of the system along the time.
Silent mode ( -s) recommmended
MYNUMUSU Number of Opened connections. 0 20 30 3600 You can use it to show the load of the system along the time.
MYRUNTHR Runnig Threads. 0 10 20 600 Silent mode ( -s) recommmended
MYSELCNT Select count on table. 0 NA NA 300 -U “tabla_definida_por_usuario”
Silent mode ( -s) recommmended

Information Events

Info events retieve general data about instance, usually this data doesn't change over time. This kind of events have no severity, simply provides instance details.

EVENT DESCRIPTION tseconds Remarks
MYINFDBN Database Name 86400 (1 día) Database Name
MYINFVER MySQL Version 86400 (1 día) Version of the database
MYINFUSR User 86400 (1 día) Mysql user information
MYINFLIC License 86400 (1 día) MySQL License information
MYINFDDR Data Directory 86400 (1 día) Path of the database files
MYINFBIS Bulk insert buffer size 86400 (1 día) Bulk insert buffer size
MYINFKBS Key Buffer Size 86400 (1 día) Key Buffer Size
MYINFJBS Join Buffer Size 86400 (1 día) Join Buffer Size
MYINFCTO Connection Timeout 86400 (1 día) Timeout
MYINFLFS Long File Support 86400 (1 día) Long File Support
MYINFLQT Long Query Time 86400 (1 día) Max time to consider a query like slow query
MYINFMXC Max Connections 86400 (1 día) Max Connections allowed

MYSQL001 agent events

MYOPETBL

This MYOPETBL event returns global opened tables.

Return values:

VALUE MEANING
-1 Error
X # global opened tables

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s -1 hora: depends on instance importance
Warning threshold Ask to the administrator
Alert threshold Ask to the administrator

Parameter setting example:

MYOPETBL = -t 3600 -c 0 -w 150 -a 200 -T “opened tables”

Remarks: If this value is high you must consider increase tables cache.

MYUPTIME

This MYUPTIME event returns uptime measured in seconds since last MYSQL database restart.

Return values:

VALUE MEANING
-1 Error
X # uptime in seconds

Recommended parameters:

Comparison type Inverse. The lower value the higher severity (-c 1)
Monitoring interval 60 s -1 hora: depends on instance importance
Warning threshold 60
Alert threshold 30

Parameter setting example:

MYUPTIME = -t 300 -c 1 -w 60 -a 30 -T "Uptime" -s

Remarks: We recommend silent mode.

MYNUMLCK

This MYNUMLCK event returns the number of global times that tables have been blocked.

Return values:

VALUE MEANING
-1 Error
X # blocks

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s - 1 día: depends on instance importance
Warning threshold 5
Alert threshold 25

Parameter setting example:

MYNUMLCK = -t 300 -c 0 -w 5 -a 25 -T "Bloqueos"

Remarks: if the value is high and you have performance problems you should first optimize your queries, and then break your table or use replication.

MYFULSEL

This MYFULSEL event returns the total number of joins that have not used indexes.

Return values:

VALUE MEANING
-1 Error
X # joins without indexes

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s -1 día: depends on instance importance
Warning threshold 1
Alert threshold 5 - Ask to the administrator

Parameter setting example:

MYFULSEL = -t 3600 -c 0 -w 1 -a 5 -T "Select full joins" -s

Remarks: If the value is 0 you must check table indexes and the queries of your applications.

MYLONOPS

This MYLONOPS returns the number of queries that have been executes in more time than long_query_time seconds.

Return values:

VALUE MEANING
-1 Error
X # slow queries

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s - 1 hora: depends on instance importance
Warning threshold 1
Alert threshold 5

Parameter setting example:

MYLONOPS = -t 3600 -c 0 -w 1 -a 5 -T "Queries lentas MySQL"

Remarks: The slow queries log contains all SQL queries that have taken more than long_query_time seconds to execute. The time to lock a table is not considered runtime. mysqld writes the slow query in the log after it has been executed and after all locks have been resolved, so log order might be different from the actual order of execution. The minimum and default VALUE of long_query_time are 1 and 10, respectively. You can archive the slow queries mysql activating parameter: log_slow_queries. Read mysql manuals for more information.

MYHRDNXT

This MYHRDNXT event returns the number of requests to read the next row in the data file.

Return values:

VALUE MEANING
-1 Error
X # Requests to read the netx row

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s -1 día: depends on instance importance
Warning threshold 10
Alert threshold 50

Parameter setting example:

MYHRDNXT = -t 3600 -c 0 -w 10 -a 50 -T "Request to read next row because of full scans"

Remarks: This event is high if you do a lot of “table scans”. You should check your tables indexes and/or optimize your queries.

MYQRYSEC

This MYQRYSEC event returns queries per second average.

Return values:

VALUE MEANING
-1 Error
X # queries per second average

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s - 1 hora: depends on instance importance
Warning threshold Ask to the administrator
Alert threshold Ask to the administrator

Parameter setting example:

MYQRYSEC = -t 300 -c 0 -w 20 -a 100 -T "Queries/second"

Remarks: This value is interesting for capacity plannings. If is possible, set the event time pool greater than 60 seconds.

MYBYTREC

This MYBYTREC event returns the number of Bytes received per second throughout all database connections.

Return values:

VALUE MEANING
-1 Error
X # bytes/s recibidos

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s - 1 hora: depends on instance importance
Warning threshold Ask to the administrator
Alert threshold Ask to the administrator

Parameter setting example:

MYBYTREC = -t 300 -c 0 -w 128 -a 256 -T "bytes/s received"

Remarks: This event is the database network loading.

MYBYTSND

This MYBYTSND event returns the number of Bytes sent per second throughout all database connections.

Return values:

VALUE MEANING
-1 Error
X # bytes/s sent

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s - 1 hora: depends on instance importance
Warning threshold Ask to the administrator
Alert threshold Ask to the administrator

Parameter setting example:

MYBYTSND = -t 300 -c 0 -w 128 -a 256 -T "bytes/s received"

Remarks: This event is the database network loading.

MYNUMUSU

This MYNUMUSU event returns the number of database connections.

Return values:

VALUE MEANING
-1 Error
X # current connectiosn

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s - 1 hora: depends on instance importance
Warning threshold Ask to the administrator
Alert threshold Ask to the administrator

Parameter setting example:

MYNUMUSU = -t 600 -c 0 -w 20 -a 30 -T "Connections"

Remarks:

MYRUNTHR

This MYRUNTHR event returns the number of no asleep threads.

Return values:

VALUE MEANING
-1 Error
X # threads in execution

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s - 1 hora: depends on instance importance
Warning threshold Ask to the administrator
Alert threshold Ask to the administrator

Parameter setting example:

MYRUNTHR = -t 600 -c 0 -w 10-a 20 -T "Threads en ejecución"

Remarks: If this VALUE is high try to increase database thread cache parameter.

MYSELCNT

This MYSELCNT event returns the numbers of rows of one table.

Parámetros extra:
This event needs one extra parameter:

PARAMETER MEANING Mandatory
-U -U “user_table”.
Table on wich we count the rows
No.

Return values:

VALUE MEANING
-1 Error
X # table rows

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval 60 s - 1 hora: depends on instance importance
Warning threshold Ask to the administrator
Alert threshold Ask to the administrator

Parameter setting example:

MYSELCNT = -t 600 -c 0 -w 1000 -a 1500 -T "Rown in OSM_PROFITS" -U "OSM_PROFITS"

Remarks: You can use this event to monitor the behavior of specific applications. For example, you can check pending transactions and raise an alarm if the value is high.

MYSQL agent prerequisites

In order to compile, this agent requires a set of prerequisites, which are generic to compile any Osmius agent, you can see these prerequisites.

Make sure you have an updated network map and clear in order to plan adequately monitoring.

MYSQL makefiles and compilation

  • Make Project Creator (MPC) is used by Osmius, so creating Makefiles is a trivial task. If you want to learn more about MPC and Osmius check out the section of Makefiles on Osmius.
  • In the particular case of Osmius MYSQL agent you can easily generate Makefile as follows:

From the agent directory using console or terminal.

Linux:

$ACE_ROOT/bin/mpc.pl -type make osm_ag_mysql.mpc

HP-UX / Solaris:

$ACE_ROOT/bin/mpc.pl -type gnuace osm_ag_mysql.mpc

Windows and Visual C 8:

%ACE_ROOT%\bin\mpc.pl -type vc8 osm_ag_mysql.mpc
  • Now that you have created the Makefile, agent compiling is extremely simple.

Linux:

make -f Makefile.Osm_Ag_Mysql_Osmius

HP-UX:

gmake -f Makefile.Osm_Ag_Mysql_Osmius

Windows:

Double click on Osm_Ag_Mysql_Osmius.vcproj and the project will be opened with Visual C. \\ Select the Rebuild option to compile.

Binaries are automatically installed in the bin directory of OSM_ROOT base directory.

Run MYSQL agent

MYSQL agent have the same running features of the other Osmius agents. You can check it out int he section Start and Stop Agents.

To run MYSQL agent without Osmius web console:

osm_ag_MYSQL001[.exe]1) -c osm_ag_MYSQL001.ini -m MASTERAG -p 1950 -d [>> osm_ag_MYSQL001.log]2)

Running in standalone mode

MYSQL agent, like the others Osmius agents, allows the execution in standalone mode. This option may be particularly useful when developing a new agent or to perform specific agent tests.

Basically you have to add a new value, called SNDCMD, to Osmius agent configuration file (osm_ag_MYSQL001.ini) as shown here.

Then you must run the Osmius agent for IP setting Master Agent communications port to zero, for example:

osm_ag_MYSQL001[.exe] -c osm_ag_MYSQL001.ini -m 00000000 -p 0 -d

Tests list

Performed test for Osmius MYSQL agent.

Date: 07/12/2007
Test Results Comment
Creating an instance with all its events in silent mode OK N/A
Creating an instance with all its events with custom text OK N/A
Creating an instance with all its events but no custom text OK N/A
Declaration of 3 instances with all its events to 5 seconds and keep it
running for 48 hours
- N/A
Declare 2 instances, cause a disconnect and then reconnect OK N/A
Declare 1 instance and test each event OK N/A
Elimination of general parameter and check unbootable OK PORTCM erased
Elimination of instance CONN_INFO and check unbootable OK N/A
1) On Windows platforms use .exe extension
2) Optional, to store agent messages in a file
 
en/agentes/mysql001.txt · Last modified: 2012/12/05 18:58 by osmius
 
Recent changes RSS feed Creative Commons License Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki