search

craftcms / cms

Build bespoke content experiences with Craft.
https://craftcms.com
Other
3.25k stars 631 forks source link

[4.x]: Slow CP entry/user searching 8 seconds+ (AfterPrepare event issue?) #15864

Open anchovy opened 4 days ago

anchovy commented 4 days ago

What happened?

Description

Searching elements in our PROD environment CP is really slow, taking 8.7s NB - there's no slowness navigating around the CP, or in the API endpoints it delivers to the front-end app.

We've got 4+ environments for the site - PROD, UAT, DEV (all aws Ubuntu. uat/dev lower spec than prod) and local (ddev). We constantly pull the PROD database back to UAT/DEV/LOCAL for testing and releases etc.

In all the other environments with the same DB loaded search times are 140ms.

Looking at the request in the debugger I can see there's a 8s jump in the fired events when the first afterPrepare is fired. Image

I've run MySQL CHECK TABLE on all the tables I think might need it:

Any ideas?

Steps to reproduce

Expected behavior

Actual behavior

Craft CMS version

Craft Pro 4.4.16

PHP version

8.1.14

Operating system and version

Linux 5.4.0-1018-aws

Database type and version

MySQL 8.0.31

Image driver and version

No response

Installed plugins and versions

-

angrybrad commented 4 days ago

As a starting point, make sure your searchindex table is using the InnoDB storage engine (and not MyISAM) and run https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html on each of your tables to rebuild the table statistics an indexes and see if that helps.