Improve database accesses

[hanoii]

I am setting up a VPS where multiple instances of strapi will be run. These instances connects to a DigitalOcean postgre managed database.

I started about the the lowest tier for the managed database (1GB of RAM) which gives me small amount of concurrent connections (22) something I just learnt. While this still in the PoC phase and the servers will be bigger I found it odd that with a handful of no traffic strapi instances I was quickly getting errors out of postgre when running config-sync import.

I wondered by and while looking at database activity I saw that one run of config-sync import was creating several databases connections at the same time.

I'd assume this is not needed and whatever this is doing it can be done using 1 (or as fewer as possible) connections or at least to have the option to limit it if this is because things are done in parallel.

Any advice/comment is appreciated.

[boazpoolman]

Hi @hanoii,

Thanks for raising this issue.

As of now, preforming the import action will do the following:
For each config type:

• Query all the entries with findMany in sets of 100
• Query all the corresponding JSON files (No DB requests)
• Calculate the difference between the database entries and the JSON files. (No DB requests)
• For each entry that has been changed run the import.

And in that last step is probably where most DB requests happen. As for each single config that is imported we do the following:

• Query the entry from the DB.
• Compare to the corresponding file to determine wheter it should be created, updated or deleted.
• Once determined, we will run the create, update or delete query for the entry.
• Also we will query all the relations of the entry.
• Compare then to the corresponding file to determine wheter it should be created, updated or deleted.
• And once determined we will loop over them and run a single update, create or delete query for each individual relation.

It goes without saying that there is much room for improvement here. Some things more obvious then others.
As I'm currently not in a position where I have the time to contribute these performance updates I won't be listing all the possible improvements.

If you, or anyone else looking at this, is looking to contribute to these improvements please reach out and we can discuss the best way to go about this. Where we'll probably start by writing tests to make sure the changes we make don't break any functionality.

[Achder]

I was running into the same issue today.
We have lots of content types and the config-sync plugin was maxing out our 100 available connections.

[Achder]

strapi-plugin-config-sync/server/controllers/config.js

Line 56 in f738ca5

await Promise.all(ctx.request.body.config.map(async (configName) => {

strapi-plugin-config-sync/server/services/main.js

Line 193 in f738ca5

await Promise.all(Object.keys(diff).map(async (file) => {

Here the plugin is running all imports at the same time.
I would recommend using some like https://github.com/sindresorhus/p-queue to queue the jobs.
Maybe make the max parallel jobs configurable.

[boazpoolman]

Queueing would be nice. But setting up a proper queue system would be beyond the scope of this plugin.
The code of config-sync can be optimized by just shrinking the amount of requests that need to be made for an import.
I've laid out some of the pain points in a comment above. This would be step one in my opinion. That would probably solve the issue for most people.

That being said, I would love to see a queue plugin for Strapi, implementing some open-source job runner server side.
Then that plugin could be a dependency of config-sync, allowing us to implement it's API's.

EDIT:

Another solution we can explore is just wrapping the entire import method in a DB transaction.

[Achder]

I'm not quite sure if we are talking about the same kind of queue here. I'm not talking about a separate service for background jobs.

My idea is basically this:

const queue = new PQueue({concurrency: 10});

for(const configName of ctx.request.body.config) {
  const promise = strapi.plugin('config-sync').service('main').importSingleConfig(configName, null, ctx.request.body.force);
  queue.add(promise)
}

await queue.onIdle()

The queue is just for chunking the work.
If this is also what you are talking about, could you explain what you mean by But setting up a proper queue system would be beyond the scope of this plugin.?