Running Custom SQL query extension on ActiveGate group with multiple nodes

[Hillman]

Hi all,

As the original SQL query extension will obsolete on Oct 2024, we are currently working on a custom query extension ourselves.

We have tested the extension and found the resultant data retrieved is 2 times more (purple line) than that retrieved by old extension (yellow line):

The only difference is the old extension running in a dedicated Active Gate node while the custom extension run on an AG group that consist of 2 nodes. It seems like the extension is being run on both nodes simultaneously which leads to the data being sum up.

Is there any config that can be done to change the behavior, say the extension will only run one node at a time? 

The reason I am asking this is, if the extension only run on 1 AG node, it will pose a single point of failure when the node is down or put under maintenance. Also, it can help balancing the load per each AG node to avoid running too many extension at the same moment.

If it is not feasible, we have to resort to the old ways. Creating 2 config that run on 2 separate AG node (group) and switch over when there are maintenance required for that AG node.

[TomásSeroteRoos]

Running the extension on an AG group with two ActiveGates will not make it run twice. At least that's not intended behaviour and I have never seen it happen.

Could it be you have two monitoring configurations pointing to the same database, thus making the extension run twice?

Also, are you sure that your metric selector query is right? Could it be you have the sum aggregation selected and it's summing the values for the different dimensions you are collecting?

Without having a look at your extension.yaml and your monitoring configurations it's difficult to tell what's going on, but from experience I doubt very much that it's related to the number of ActiveGates on your AG group.

[Hillman]

Hi @TomásSeroteRoos ,

I have only chosen "auto" when applying the metrics onto the data explorer. I am thinking about how to share more details without leaking too much information out.

The following is an extract from extension.yaml. Does that surfice?

[TomásSeroteRoos]

Hmm, that looks fine. Are you defining metric metadata in your yaml? How is that defined?

Also, did you double-check your monitoring configurations? Are you sure you are not double monitoring the same database?

Finally, share as well a screenshot of the full metric selector query you have.

One thing I just noticed in your original screenshots: in the first one you are hovering over the purple timeseries which shows a value around 8K. Notice that the purple timeseries on the screenshot below also shows around that value.

On the second screenshot you are hovering over the yellow time series, around 3K, which seems to match the value on the screenshot above.

Are you sure your metrics are actually wrong?

[Hillman]

Yes. I have defined the metrics metadata mainly for the metrics display name.

The monitoring configuration has been assigned to a particular Active Gate group which consist of 2 AG:

And I have made sure the single set of SQLs only got 1 config.

The purple series is the data retrieved from the custom extension while the yellow series the data retrieved from the old framework 1.0 extension which are running exactly the same SQL statement. The only difference is, the custom extension is running on a AG group that consist of 2 AG while the official framework 1.0 one, because of the limitation, is running on 1 AG only.