[Feature] How to aggregate sql statement using query to ES?

[ranxuxin001]

Search before asking

• I had searched in the issues and found no similar feature requirement.

Description

Hello,
I use skywalking 10.2.0. I need to aggregate sql statement and get average latency, etc. my mysql java agent collects sql statement in span and send to skywalking segment index. But data_binary is base64encode and protobuf. It is performance cost to parse all segment trace's data_binary. I try to use records-all index, but it limit 100 query. How to aggregate sql statement, please?

Best Regards,
Ran Xuxin

Use case

No response

Related issues

No response

Are you willing to submit a pull request to implement this on your own?

• Yes I am willing to submit a pull request on my own!

Code of Conduct

• I agree to follow this project's Code of Conduct

[wu-sheng]

We don't support this kind of aggregation. Can't see the point of statistics of these.
If you want to, aggregation supported in elastic(not in BanyanDB), you could run query on your own. BASE64 is just an encoder, you have public way to decode after query.

[ranxuxin001]

Yes, thank you to reply me. I used elacsticsearch as backend store. If I need to aggregate by sql statement, calculate average latency, I should base64decode every trace in sw_segment-* index to parse each span's tag which saved sql statement in data_binary field. It costs high system load. I want to let my agent mysql plugin to get sql in sw_log-* index. It is easy to aggregate directly. But only the java main application's log can be report to sw_log-* index. Please suggest me how to aggregate sql efficiently.

[wu-sheng]

That is code change on your own. We don't provide that as we don't think this is valuable from monitoring perspective.

[ranxuxin001]

I can aggregate sql statement using sw_records-all index and set access db threshold(slow sql minimum latency) to 0 in application.yml.

Now I want to aggregate sql exception like SQLSYNTAXERROR exception to get the total number the exception occurs. I try to use tags field in sw_segment index. But my skywalking 10.2.0 shows the index template has excluded tags and tags type is keyword. But AI said its type is object and exclude nothing. So which index template is correct? And can I aggregate the tags' key in sw_segment index?

[wu-sheng]

I can aggregate sql statement using sw_records-all index and set access db threshold(slow sql minimum latency) to 0 in application.yml.

I don't think that works. All record in the memory first, this will oom the whole oap.
If you want to do that, you need to create superset record entity by your own, and do insert for all data.

Now I want to aggregate sql exception like SQLSYNTAXERROR exception to get the total number the exception occurs. I try to use tags field in sw_segment index. But my skywalking 10.2.0 shows the index template has excluded tags and tags type is keyword. But AI said its type is object and exclude nothing. So which index template is correct? And can I aggregate the tags' key in sw_segment index?

sw index all is aa separate index, which has no relationship with segment index. I don't care about AI says. AI should be your assistant only, not a thing to discuss with us.

[ranxuxin001]

I found another way to aggregate. first update the sw_segment index template, and remove tags in the excludes array. Add tag_array with nested type. Add tag_hash with object type. request to create ingest pipeline to parse segment index's tags into tag_array and tag_hash . Then write span's tag key in searchableTag argument in core module in application.yml. Then let the agent plugin to report span tag to oap server.
After deleting segment index, new one is created with tag_hash and tag_hash . Now it can be aggregated with span's tag.

[wu-sheng]

As I replied, as this is private code change. You could make a call and change privately.

[ranxuxin001]

Thanks for your communication. I will let my agent plugin to report span tags to oap server. And sw_segment shows object type tags which could be used to aggregate.