Update MySQL Global Stats datasource to read config from my.cnf

Hi,

I had an alert yesterday for the Threads_connected datapoint on the MySQL Global Stats datasource. We actually increased the max_connections in the my.cnf config file.
Is it possible to either read my.cnf file (assuming SSH credentials are available), or get the max connections variable to see what it is actually configured as?

This will negate the need to manually check the value in the config and adjust thresholds.

scavern

Posted 6 years ago·Last reply 6 years ago

8 comments

LM User

·6 years ago

Just now, mnagel said:

we have a DS applied to collectors that only has complex groovy datapoints with the value ##property## for each.

Exactly how I would do it.

mnagel

·6 years ago

I don't think so, but perhaps. global status should have current statistics and variables should have static settings. Could be wrong, of course.

As far as the DP versus property issue, yeah, I have run into this before. We wanted to be able to reference a property in widgets and you simply can't, so we had to create a datapoint from the property (this is to be able to display resource commit usage for our clients). So, we have a DS applied to collectors that only has complex groovy datapoints with the value ##property## for each. Rough edges :).

LM User

·6 years ago

Wait, wait, wait. Isn't


<span class="pln">show global status;</span>

contained inside


<span class="pln">show variables;</span>

If that's the case (to be verified), you could just change the query to "show variables;" and it should output all the same stuff along with max_connections. Then it's just a matter of adding the datapoint.

LM User

·6 years ago

Cool. I was trying to figure out how to get the output of two queries within the single DS. I guess you could cobble together some sort of union of the two so that it returns a single recordset?

mnagel

·6 years ago

Either way, but a property has the advantage of being able to be referenced in alert messages, so sure :). If you want to reference it for percentage utilization, I think it still needs to be a DP as well.

LM User

·6 years ago

Even better. To be clear @mnagel, you're suggesting the query in the property source, right?

mnagel

·6 years ago

Or, you could not add SSH overhead and just use a query :).


<span class="pln">MariaDB [(none)]> show variables like '%conn%';
+-----------------------------------------------+-----------------+
| Variable_name                                 | Value           |
+-----------------------------------------------+-----------------+
| character_set_connection                      | utf8            |
| collation_connection                          | utf8_general_ci |
| connect_timeout                               | 10              |
| default_master_connection                     |                 |
| extra_max_connections                         | 1               |
| init_connect                                  |                 |
| max_connect_errors                            | 100             |
| max_connections                               | 151             |
| max_user_connections                          | 0               |
| performance_schema_session_connect_attrs_size | -1              |
+-----------------------------------------------+-----------------+
10 rows in set (0.00 sec)</span>

Confirmed via same user with GRANT USAGE as normally required for "show global status" results.

LM User

·6 years ago

Yes, you could write a property source that would SSH into the server and get the max_connections from the config file and set it as a property on the device. Then you'd need to slightly modify the mysql global stats datasource so that it used that property in a complex datapoint that uses a formula like this:


if(un(##max_connections##), threads_connected, threads_connected / ##max_connections##)

I haven't tested this, but this is the gist of how it would work.

There's an example Expect script here: https://github.com/sweenig/monitoring-recipes/blob/master/DataSources/Groovy/Expect/Groovy_Expect_Example.groovy