Mysql and Postgres modules should allow addition of custom queries

[ajaybhatnagar]

Features Request:

Database monitoring modules of Mysql/Postgres should allow adding additional sql commands / queries to collect additional db performance data to customize dashboards in addition to outputs from standard commands.

Would like to see Cassandra module included .

Thanks
Ajay

[ruflin]

Can you add some examples for the metrics / queries you would run?

[ajaybhatnagar]

For example current mysql module collects data only from show status command. If one wants to use show global status, then there are additional parameters included. Similarly if performance stats are enabled, there are many more data available for monitoring and plotting. There one can enable selective data by specifying queries of interest in the metricbeat mysql module configuration, it can become a powerful tool for monitoring and analyzing db performance.

In general, in my view, for any monitoring tool, it should be possible to define a set of commands to be used to collect data from remote node, map the output data fields to corresponding json document for ES storage. That way one need not build new beats but use modular approach to enhance / modify data collection from the remote source, and setup it's field mapping for storing the document. Thus a generic beat module can be reused and reconfigured for multiple uses for different types of monitoring data collection.

[ruflin]

I would split up this enhancement request in three parts:

• Provide all metrics which were retrieved from a stats API instead of just a filtered and revised subset. An idea for this see Add raw mysql fields to the mysql event #3001
• Allow free form of queries for fetching stats
• Allow execution of external scripts to fetch stats

For the free form of queries, httpbeat comes to my mind. Also something that applies here is probably httpbeat which allows to query any http endpoint. It does not necessarely apply to all module types. An other one is mysqlbeat. Is that the direction you were thinking?

Number 3 is the most powerful but also most complex one as it goes into external script execution. We decided to not touch this topic for at least the first version of metricbeat :-)

[ajaybhatnagar]

Agree that subset of stats is not sufficient and full set of metrics be exported for any application in general. Since in case of Mysql (or Mariadb) sources of monitoring data comes from different tables (like performance tables, sys tables etc), data needed for having a meaningful monitoring or analyzing issues, would have to be in one place. That is where I think these beats can be very useful . This is where free form of queries would come handy to build custom monitoring on top of standard beats. This should not required "Recompile of code" but added / subtracted thru config files.

3rd option is, in my opinion, a way of using simple generic plugin, for any data source . This , I agree , is complex but makes the tool adoptable to any application. What one needs for building such a solution is :
a. Source data query commands.
b. Output format of each of the commands, that can be a single value (number /string), a line with multiple fields, multiple lines or variable number of lines with variable number of fields and field seperator, an array or a hash.
c. Expected standard output fields format ( enumerate all fields, but some may not be present).
d. Parse the output for JSON document to be stored in ES.

As these are the general steps ( most likely common steps) for any data to be stored for future retrieval and analysis, therefore I think, a generic module would be able to cater to multiple application needs at the same time in addition to ready to use modules.

[madhavajay]

+1 like https://github.com/adibendahan/mysqlbeat

[ruflin]

@madhavajay I think we should have here a conversation with @adibendahan if this could potentially fit somehow into metricbeat. He knows already all the complexity and challenges around it.

[madhavajay]

Yeah sounds good, mysqlbeat is working great for me currently.

[adibendahan]

@ruflin, @madhavajay there's nothing too complicated in the mysqlbeat code, I'll be happy to help with anything you need ;)

[ruflin]

@adibendahan Perhaps we can a quick video chat about it? Can you send me an email (see LinkedIn).

[ruflin]

I had a great chat with @adibendahan and learned more about mysqlbeat. Here a few things I wanted to share:

• There is not only mysqlbeat but also sqlbeat (https://github.com/adibendahan/sqlbeat). It is a very similar code base but can be used with other sql databases like MSSQL, Postgres and others. In the long term this means, we can potentially also support these. In a first step we should add support to mysql and grow it from there.
• Mysqlbeat supports a list of queries to be executed

Some thoughts for the implementation:

• Each query that can be executed, can have a different data structure which could lead to data conflicts. Instead of allowing an array of queries I suggest to only allow 1 query, but a module can be defined multiple times. So if there are different queries, multiple modules must be used. This also allows to set a namespace for each query.
• Mysqlbeat as the config entry querytypes: Should we have one metricset for each query type or have this also as a config option in the metricset?

[simpleuser99]

This issue is closed?
Can I use custom pgsql request for get special database metrics?

[ruflin]

@simpleuser99 This issue is still open as it's not implemented yet. Contributions are welcome ;-)

[spencer-edh]

Im interested in being able to run my own queries using metric beat for mySQL. Does it have this functionality yet?

[jsoriano]

A new generic SQL module has been added in Metricbeat, that in principle can be used to collect events from custom queries using any of the included drivers included in the binary. Adding support for more drivers will be in principle a matter of including more implementations of the sql interface.

You can find more info in #13257 and #15048

[madhavajay]

"The Times They Are a-Changin"