When I use wp_nav_menu I get a huge update_meta_cache query regardless of theme

[andyseaton83]

Hi there,

I've got a wordpress site that's been up and running for a couple of years now. I always keep core+plugins up to date, and although I use a custom theme, I'm pretty happy with how it's working.

Recently however it started to crash regularly - and the server guys notified me that it was mysql that was causing it. So I installed Query Monitor and took a look - there was a massive query which looked like this:

SELECT post_id, meta_key, meta_value
FROM ajs_postmeta
WHERE post_id IN (11178,9677,9060,7417,7354, <lots more ids>)
ORDER BY meta_id ASC

This was returning 20,000 rows each time the page was loading. This obviously overloaded the server during peak times and it needed rebooting.

This is the code that was causing the problem (I know this because I replaced it and the offending query disappeared)

<?php
$header_menu_query = wp_nav_menu(array(
                     'theme_location' => 'header-menu',
                     'container' => 'nav',
                     'container_class' => 'nav-menu-holder nav-menu-landing nav-new',
                     'menu_class' => 'nav-menu', 
                     'walker' => new themeslug_walker_nav_menu));

So I replaced wp_nav_menu with static HTML and the query size reduced as it was no longer calling wp_nav_menu. I thought it might have been something I'd done in my theme - so I removed the custom walker, which didn't have any effect, and played about with some settings. I tried using the recommendation here: ​https://hitchhackerguide.com/2011/11/01/reducing-postmeta-queries-with-update_meta_cache/ but that had no effect.

So the next step was to try a different menu. So I deleted my menu (which had 70/80 items in it) and added 5 new items to a new menu. That then gave the following query:

SELECT post_id, meta_key, meta_value
FROM ajs_postmeta
WHERE post_id IN (11178,9677,9060,7417,7354)
ORDER BY meta_id ASC

which is the same query as above but without the lots of additional ids - as this menu only had 5 items. This returned around 450 rows - so it's not hard to see that a menu with 70/80 items would get up to 20,000.

The last thing I tried was to change to the default theme and disable all plugins (bar Query Monitor) and tried again. I associated my test menu (with the 5 items) with the 2017 theme top menu and checked query monitor - it still showed this query:

SELECT post_id, meta_key, meta_value
FROM ajs_postmeta
WHERE post_id IN (11178,9677,9060,7417,7354)
ORDER BY meta_id ASC

with the same number of rows.

So my question is this - is this something that is a problem in core wordpress, seeing as this update_meta_cache() method seems to be creating massive queries if you have a larger than average menu - even 15/20 items would create a fairly large result set each time.

I've been googling like crazy as I really want to get this sorted - but I can't seem to find a solution, and I'm wondering if it's something in the latest release. There's no difference between browser or OS that I can see - its just something wrong (I think) with the update_meta_cache method as it relates to the wp_nav_menu function.

Anyway, happy for you to take a look at my config etc - let me know what you need. Best email to get me on is andy@… or you can call my UK mobile on 07791-107755

Thanks

Andy

[welcher]

@andyseaton83 thanks for the ticket and welcome to trac!

Looking at the code, I can see that there are many places where caching is implemented to potentially mitigate large SQL queries. Do you have object caching in place such as Memcached? If your queries are retrieving 20k+ records to render a menu, I would highly recommend implementing caching as it will bypass any cached id's and not pass them to the query in question ​See the source here

At any rate, I'm not sure that this is an issue with core itself and this may be better suited as a support question on forums such as ​https://wordpress.org/support/ or ​https://wordpress.stackexchange.com/

[andyseaton83]

Hi there,

Sorry I've not replied to this in so long - the fix I put in place is working fine as the client hasn't asked to add any menu items, etc.

However, I wanted to reopen the discussion as I'm not sure it is an issue that can be fixed with caching - I've implemented transient cache for the nav menu, and that works fine when the cache is present, but I still get the 20,000 rows problem when the cache has been deleted or garbaged.

My main point is that I (as a developer) am not doing anything different to regular wordpress functions to call the menu - yet the inbuilt queries return a huge result set due to this update_meta_cache function. I can't believe that Wordpress needs to return 20,000 rows of a database for a menu that contains 80/90 items - hence why I believe it's a bug.

If you could take another look I'd appreciate it.

Andy

[SergeyBiryukov]

Related: #14254, #52360.