Speed up / Optimize Admin Edit Post Page on Large Websites (50-150k posts, 50 custom fields per post)

Question

I'm working with a set of large Wordpress installs with 50-150k posts, each with 40-50 custom fields. The installs are very basic, with limited plugins (less than 5), and a default Wordpress theme (i.e. twenty twelve, etc.). The sites are on 1-Core, 2GB RAM, SSD VPS servers.

These are all private installs, with no public front-end or web traffic. Automatic updater, auto-update core, and revisions are all disabled.

I have no featured images or other media on the edit post page; Only text. The sites are on Wordpress 5.6 or later (I understand they could be updated to 5.9.3, but doubt that is the issue).

The admin edit post page hangs for 25-45 seconds when accessed, and then finally loads. Is there any way to fix or speed this up on a large, basic wordpress install? It appears to be a problem in loading a large amount of custom field data, with 50-100k+ posts.

Here's what I've found:

• Most other wp-admin pages such as categories, tags, pages, users, settings, etc. load quickly in a few seconds (3 seconds or less). The "All Posts" page takes a little longer at about 8-10 seconds or so.
• If I don't use the classic editor plugin, and don't enable the custom fields section to load in the new block editor, the edit page will load quickly as well (3 seconds or less).
• Disabling any other (or even all plugins) has no effect.
• I can load the front-end post page, and display all of the custom fields with no problem, under 3 seconds.

Any ideas? Is there a problem with the core Wordpress backend functions at this scale for post pages with many custom fields?

Thanks!

Answer 1

I used the QueryMonitor plugin, as birgire suggested in the comments, and found that a core Wordpress Meta Query is causing the problem.

The issue and core Wordpress Meta Query are discussed in depth at CSS Tricks and in another question on Wordpress StackExchange here.

Basically, there's a query that populates the "Select" box for adding a custom field name on the "Edit Post" page (see below).

It goes through the database, and grabs all of the meta keys (or custom field names) in your posts to create a list of previously used keys/names. This makes it easy to use the Select box to choose a key/name you've already used, rather than typing it over and over.

Apparently, the query is not efficient and causes massive performance issues when scaled to 100K+ posts with lots of custom fields.

Workaround

At this time, there is no absolute/easy fix I could find. CSS Tricks and others have more complicated fixes, but I really wanted something simple. Fortunately, there is an easy workaround StackOverflow user Nic Bug suggested that seems to solve the loading issue for me:

function set_postmeta_choice( $string, $post ) {
$meta_keys = array();
foreach(has_meta( $post->ID ) as $meta){
    $meta_keys[] = $meta["meta_key"];
}
return $meta_keys;
}
add_filter( 'postmeta_form_keys', 'set_postmeta_choice', 10, 3 );

Just place this at the bottom of the functions.php file in your theme folder. This shrinks the query down to only find the custom field keys/names used in the current post, rather than all posts.

So, it's a tradeoff. It restores the speed, performance, and loading time, but makes this Select Box less convenient for adding more custom fields.