Mongodb slow query

[chain312]

SUMMARY

Mongodb slow query

STACKSTORM VERSION

st2 3.7.8, on python 3.8.10

OS, environment, install method

docker deployment

Steps to reproduce the problem

When I input 20,000 pieces of data at a time in a short time, the problem of slow query by mongodb occurs. It takes more than ten seconds to read a piece of data. The cpu of the host where the mongodb container is located is 32G, the memory is 128G, and the utilization rate is not high.
The remaining 80% cpu.

Memory remaining 90%

Query time

Size and number of action _ execution _ scheduling _ queue _ item _ d _ b tables

Expected Results

Mongo read time is reduced so that I can run more action.

Actual Results

I entered 20,000 pieces of data into the playbook.

Thanks!

[chain312]

I changed mongodb to fix this problem

[nzlosh]

Would you mind sharing what you changed to resolve this issue so that other people who encounter the same problem have an hint on how to resolve it?

[chain312]

Would you mind sharing what you changed to resolve this issue so that other people who encounter the same problem have an hint on how to resolve it?

I have optimized the mongo sql index by creating an index in the [sort] field

[arm4b]

@chain312 Good to hear! Is it something you could contribute to StackStorm as a PR?

It would save you from doing the same operation for every new st2 installation and also make the community benefit from the enhancement.

[chain312]

@chain312 Good to hear! Is it something you could contribute to StackStorm as a PR?

It would save you from doing the same operation for every new st2 installation and also make the community benefit from the enhancement.

Hello, mongodb tuning is not written code. Can I write it in https://github.com/StackStorm/st2docs project?

[arm4b]

The index creation in MongoDB is part of StackStorm initialization.
See examples of the past PRs adding DB schema/index changes:

• Change compound index to improve query performance #5568
• Limit database index field names to 65 characters #4690

What was the index you've added?

[chain312]

The index creation in MongoDB is part of StackStorm initialization. See examples of the past PRs adding DB schema/index changes:

• Change compound index to improve query performance #5568
• Limit database index field names to 65 characters #4690

What was the index you've added?

Thank you. I will submit the merge request later

[chain312]

The index creation in MongoDB is part of StackStorm initialization. See examples of the past PRs adding DB schema/index changes:

• Change compound index to improve query performance #5568
• Limit database index field names to 65 characters #4690

What was the index you've added?
I have submitted the code for your review
Add a federated index to resolve mongo slow queries