[Escalated to 2nd Tier] Performance with large database

[Author]

Posts

[HarryT902]

Background issue: https://toolset.com/forums/topic/wp-admin-edit-screen-super-slow/

Hi,

I am using toolset for a WordPress site with a very large database. When I go to Edit a Post Field Group and click on Where to include this Field Group, toolset is timing our and throwing an error.

My error log shows that my hosting provider is killing the query because its excessively large and hogging too much memory.

KILLED QUERY (30816 characters long generated in /wp-content/plugins/types/vendor/toolset/types/includes/classes/class.types.admin.edit.custom.fields.group.php:845): SELECT term_id, meta_key, meta_value FROM wp_termmeta WHERE term_id IN (55750,55662,55836,56051, ect....)

And I am getting a 500 error.

PHP Fatal error:  Allowed memory size of 536870912 bytes exhausted (tried to allocate 20074496 bytes) in /wp-content/plugins/types/vendor/toolset/types/includes/classes/class.types.admin.edit.fields.php on line 1447

My wp_terms table has 45768 rows. The wp_termmeta table has 305984 rows.

I took a look at line 845 in class.types.admin.edit.custom.fields.group.php It's calling the WordPress function 'get_terms'

$terms = apply_filters( 'wpcf_group_form_filter_terms', get_terms( $category_slug, array('hide_empty' => false) ) );

Your function 'form_add_filter_dialog' looks like its building the form of checkboxes for the edit page so a user can select which post-types, taxonomies, and templates will be available to apply the post field group to.

# class.types.admin.edit.custom.fields.group.php
# start line 777
protected function form_add_filter_dialog( $filter, &$form ) {
		global $wpcf;
		switch( $filter ) {
			/**
			 * post types
			 */
			case 'post-types':
				// ...
			/**
			 * taxonomies
			 */
			case 'taxonomies':
				// ...

			/**
			 * templates
			 */
			case 'templates':
				// ...
		}
	}

My question is, why when building the admin edit form does the code need to deeply dive into each term of each taxonomy? In cases like mine, where we have a large number of terms and termmeta, this is causing my memory to run out. Even if I had more memory from my server, its not necessary to call on every single term when assigning post fields to a post type. It seems like here we are dealing with postmeta and post types, but don't need to look at terms of taxonomies. I only have 15 taxonomies registered.

For better performance, I think you all should rethink the case statement for taxonomies, and get by with looking at each taxonomy and not dive into each term.

Thanks,
Harry

PS. I solved my problem I was having with my background issue linked above. When I was visiting the admin edit screen for my custom post type, the edit screen was displaying the meta boxes for the attached taxonomies. The taxonomies were setup as Hierarchical causing a dropdown list to be created. And in displaying the taxonomy dropdown, every singe term of the taxonomy was being listed as a choice. Way too many! I changed my taxonomy settings to be structured as Flat instead of hierarchical and this fixed the problem. On the custom post type admin edit screen, instead of a dropdown being shown, there now is a free-form entry box which makes the page lightning fast since it does not have to query every single term.

[Beda]

While you are right about the performance, the taxonomy terms are needed there, since you can set conditions for each term.

That is very powerful, you can show Custom Fields only on Posts where a certain TERM (not just taxonomy) is used.

But I understand the performance though.

I will pass this ticket to the Developers, as there is not much I can do right now, but I know that we have a similar ticket internally and the idea is to either get the data in chunks, what I could think of, only if you select a certain taxonomy, then call its terms.

But that requires on one end some development work, on the other end it may bring other performance issues, where the site won't time out but you will need more time to find the data (since you need to wait for the chunk to load)

Right now, as said we cannot do much.

Slimming the system is one of the probably best approaches you can take right now, as with such a large database you will run into issues due to WordPress alone, at some point (since all is stored in post table, not each type in a table)
This can produce database bottlenecks later when you query it.

However, I will pass this on.
It is surely required to consider these cases.

Thank you for your report.

[Mohammed]

Hi Harry,

Thanks again for reporting this issue. Can you help us to fix this by providing a copy of your database?

Usually the developer will need to replicate the issue to fix it.

I will set the next reply as a private one so that you can share the database dump securely.
BTW, you will need to upload it to an uploading platform like google drive or any other one and share the download link in the private reply.

Thanks.

[Luo Yang]

Dear Harry,

Thanks for the details, I can duplicate same problem with your database dump file, and escalated this thread to our 2nd tier supporters, our developers will take care of it, I will update this thread if there is anything news.

[HarryT902]

Thanks Luo, Mohammad, and Beda,

Over the lifetime of using toolset (~2 years), the only major problems I have found have been the slow admin edit page, fixed by setting a few taxonomies from hierarchical to flat, and the 500 timeout error mentioned above. Please use this DB dump to look at other edit pages and scenarios when testing, since there is a lot of data. Currently we have about 25K posts in WordPress and are only going to grow that number to 50K or 75K. It really is a testament that toolset can handle a production environment like this.

Thank you!

Beda, you said:

Slimming the system is one of the probably best approaches you can take right now, as with such a large database you will run into issues due to WordPress alone, at some point (since all is stored in post table, not each type in a table)
This can produce database bottlenecks later when you query it.

I have been mindful when building my architecture to make my search queries look at associated taxonomies and not postmeta fields, since taxonomies are in an independent indexed table vs the large wp_posts/wp_postmeta table. This is how I plan to achieve a responsive and searchable dataset while still growing the number of entries in the DB.

At somepoint I may have to migrate a few custom post types to custom WP tables queried with $wpdb, but I will be loosing so much great functionality provided by Toolset. I hope I can count on Toolset to be conscious of size when building new features.

Thanks,
Harry

[HarryT902]

re-opening for 2nd tier

[Luo Yang]

This ticket is escalated to our 2nd tier supporters, our developers will take care of it, I will update this thread if there is anything news. Please keep the google drive disk link unchanged, our developers will need it too.

[Luo Yang]

Here is the update from our 2nd tier supporters:

The cause is the Taxonomy Census Tracts with 11,550 terms.

You can simply confirm this by deactivating the Taxonomy.
Then you can set the condition to display the Custom Field Group Nonprofit – Admin Fields (or any other) just fine, related to any content type.

And we have invited our developers into this problem, I will update this thread if there is anything news.