Saving entries in a multi-site setup takes too long

[jsunsawyer]

Our Setup

We have a multi-site setup with 24 sites. Each site represents a country specific language. We use a variety of translation methods for each of our entries' fields, often utilizing site groups as common languages (17 site groups).

Issue

As we've added more sites (more to come), we've noticed a drastic slowdown when saving entries and when re-saving entries tasks are triggered.

Re-saving an entry in a channel section takes almost 6 seconds.

In comparison, a single entry that is only enabled for our US English site takes about 1/2 a second to save.

This makes it difficult to deploy field changes that trigger re-saving entries tasks, as they take far too long. This also makes the CP feel sluggish for content authors.

Help!

We're afraid that this will only get worse as more sites are added.

Is this expected behavior for multi-sites in Craft?
Is there something we could do to improve performance?

Additional info

• Craft version: 3.1.20.1
• PHP version: 7.1.25
• Database driver & version: MySQL 5.5.5

[brandonkelly]

Re-saving an entry in a channel section takes almost 6 seconds.

We could probably speed up entry saving by offloading propagation to the queue. We’ll just need to ensure that by the time the queue job is executed, the entry hasn’t been re-saved for one of the other sites in the meantime. Will put this on the 3.2 list.

This makes it difficult to deploy field changes that trigger re-saving entries tasks, as they take far too long. This also makes the CP feel sluggish for content authors.

You could fix that by disabling the runQueueAutomatically config setting and creating a cron job that runs the queue/run command every minute, or run queue/listen indefinitely using Supervisor (see the Yii Queue documentation for more details on how to set that up.) Ideally those would happen from a dedicated worker environment, so there’s no chance of slowing down your public site.

Craft 3.1.15 also added resave/entries console command if you want to offload entry saving to the terminal, however it’s not yet possible to prevent Craft from reserving entries automatically after updating a section/entry type’s settings (as requested in #3482 – follow that issue to be notified once that’s implemented).

[brandonkelly]

The ability to disable auto entry-saving on section & entry type updating (#3482) has been added for the next Craft 3.1 and 3.2 Alpha releases, and I just implemented element propagation via background jobs for the next 3.2 alpha release.

To get these changes now, edit your composer.json file and change the craftcms/cms requirement to:

"require": {
  "craftcms/cms": "3.2.x-dev as 3.2.0-alpha.1",
  "...": "..."
}

Then run composer update.

[jsunsawyer]

@brandonkelly Thanks, but I'm not sure this solves my issue.

I still need to run these tasks, and these tasks still take far too long.

[brandonkelly]

The time it takes to run background jobs shouldn’t matter as long as they’re not impacting the web server, so if you can move the queue to a separate worker environment and run them over the terminal (as explained in #4064 (comment)) you should definitely see an improvement. And 9e7d6c5 will enable even more work to be offloaded to background jobs, further improving responsiveness.

[jsunsawyer]

I'm unfamiliar with worker environments. Is this something that can be used to speed up my local environment as well?

[brandonkelly]

“Worker” meaning an environment within your hosting infrastructure that can be dedicated to processing background jobs, as to not impact web server performance.

Is this something that can be used to speed up my local environment as well?

No, just a production thing. But even without a dedicated environment, you will speed up your web server by moving the queue to a Supervisor’d process that runs craft queue/listen. (Again, see my original comment.)

[jcherniak]

We are currently running into this issue ourselves. We support about 20 languages and have about 10000 entries. We are hitting save times over 5 minutes. I was able to cherry-pick this patch into 3.1 and it resolved our issues almost completely.

@brandonkelly Is it possible you can backport this to the 3.1 branch? I can submit a PR if you like, but just git cherry-pick 9e7d6c5, fix the one obvious merge conflict and it works wonders!

[brandonkelly]

@jcherniak I actually had to revert 9e7d6c5 as it was introducing other issues, so I’ll reopen this until we come up with a better solution.

[jcherniak]

Can I ask what issues? I was just about to deploy to our prod site with this hotfix and want to make sure I don't corrupt anything.

[brandonkelly]

@jcherniak It introduced order of operations issues, especially with other changes in Craft 3.2. Can’t remember if they would have impacted Craft 3.1 though. I wouldn’t recommend deploying it.

[jcherniak]

Do you have any other suggestions on how to proceed here? We have about 750 entries over 20 languages. Running 3.1 out of the box (3.1.24, but I don't think that matters), we are seeing the time to save a new entry (duplicating a current one to be precise) running over 10 minutes (that's when the server times out).

With this patch applied, I'm still seeing a save time of 8 minutes.

During this time, our whole site is not responsive. Obviously this is a problem. Is this expected behavior (that while saving an entry the site inaccessible)?

I'm going to follow up via email with a copy of our code and DB for some additional one-on-one help.

[brandonkelly]

With this patch applied, I'm still seeing a save time of 8 minutes.

There must be something else going on here. Some plugin or module that is causing delays.

[erktime]

@brandonkelly Any update you can share? I too am worried about the increasing time it takes to create/update entires in multi-site setups. As of version 3.2.9, I see things continue to get bogged down once a few sites are created. For example, I have a setup with 9 sites and saving an entry that only has a title field takes ~4,250 ms and executes 683 db calls. Once I add more fields to the entry, it gets worse and worse.

[brandonkelly]

@erktime We’ve made some significant performance improvements w/r/t saving elements in Craft 3.1.30 and 3.2.0, with more things planned.

4,250ms & 683 DB queries seemed excessive so I just tested that locally – I created a fresh install with 9 sites, a section that is enabled for each one, with just a title field, no custom fields. Versioning is enabled as well.

On my end, saving an entry takes 545ms, and executes 382 database queries. Not great but certainly more tolerable than what you’re seeing. Obviously the actual request time will vary depending on the environment, but the number of database queries should be basically the same. Yours getting almost twice the queries suggests that there’s more going on with your site than mine. Perhaps a plugin that is slowing things down?

[jsunsawyer]

About to board a flight, but I just tested one of our more complex sites and disabling plugins sped up the time it took to resave a simple entry by about 5x (for a very simple entry). I'll try to dig into this a bit more, but so far it seems that every enabled plugin adds a bit more time to the resave process.

[erktime]

@brandonkelly I did a fresh install (0 plugins, etc) with 9 sites, and got 573 queries to save an entry with just a title. Odd that it's different than yours by so much. Despite that, I did some more testing and found our true performance issue is running craft against a PostgreSQL. In our dev environment we run PostgreSQL within a docker container. For some reason the docker PostgreSQL kills us with all these queries. Running the same setup against a dockerized mysql (5.7) offers timings like you get (~516ms or so for an entry save).

I did go on to run my problematic multi-site setup against a non docker postgresql and mysql and found performance to get much better in both setups. I did notice mysql save times to be roughly half the time of postgresql saves!

So obviously i have a docker issue with postgresql, but I'm curious if you have encountered or heard of general craft performance not being as good when running against postgresql? The main reason we're looking at postgresql for a new build is because in past craft 2.x days we had a real pain with mysql and utf8mb4 support for emoji and other multibyte chars. postgresql just does this. I know craft 3 seems to escape things a bit better... but it's still slightly worrisome to me. Character encoding sucks to deal with.

[brandonkelly]

Postgres tends to be a little slower for INSERT/UPDATE requests, and Docker tends to be much slower than Vagrant or running from the host OS directly, regardless of MySQL / Postgres.

Neither would explain why you are getting 200 more DB queries than me though. How are you calculating that? I was getting my number from the Debug Toolbar – after saving I opened the DB panel and loaded the POST request from when the entry was actually saved (not the POST request for running the queue worker, which follows).

[brandonkelly]

Craft 3.4 will support delta element updates, which will drastically reduce the time it takes to update entries with lots of fields, especially if they contain (nested) Matrix/Super Table/Neo fields.

[lenvanessen]

@brandonkelly I don't think we're quite there yet, or it's not working as expected.
We have a set-up with 11 sites and a high-performance database. Page with 11 blocks fires 4000 queries (or 7000 with versioning enabled).

If we really push the timeout limits we can get it to save, but still features like live-preview and so forth are pretty much useless at this point, they trigger to many saves and this will kill the db.

[brandonkelly]

@lenvanessen do those blocks contain nested elements such as Neo or Super Table fields?

[lenvanessen]

@brandonkelly seems to bee more an issue with Neo than Craft itself.

[erktime]

@brandonkelly I'm still struggling with very slow save times when you have multiple sites enabled for a section. The number of DB queries/saves just grows and grows. In my scenario I use sites for multi-language support. We always have content in the default primary site (english), and occasionally we add content to additional sites (languages). The default behavior of having craft propagate entries to all enabled sites makes content edition super slow. This is wasteful if you don't actually need all the sites enabled by default. My idea is to have sites enabled for a section, but not to propagate until a content author actually wants them to exist. I'm having trouble getting craft to do it though. This is what I'm attempting, do you have points on how it can happen?

1. Create a section that has multiple sites but only enable the primary site by default. Set propagation so the save only occurs in the site the entry was created:

2. Create an entry in the primary site (English). This is fast because we're only creating the entry in one site, there's no propagation.

3. When a user wants to support another site (language), we then have custom code which propagates the entry to that site. Now the entry exists for 2 sites, and saves are a little slower, but not nearly as slow as they would be if all sites were enabled.

The problem is I can't figure out how to code this.

I thought the following might work:

Craft::$app->getElements()->propagateElement($entry, $newSiteId);

but I get the error:

Attempting to propagate an element to an unsupported site.

Effectively I want to delay propagating an element to another site until a user actually needs it. Is there a way to do this? You allow site to have a default status of disabled, but that still slows down the entry creation process. Any help would be appreciated.

[brandonkelly]

@erktime Can you request that in a new issue?

[brandonkelly]

@erktime Someone else posted the same request – #5988 – and it has been addressed for Craft 3.5. See #5988 (comment) for details on how it works.