Support

Account

Register Login

Home › Forums › General Issues › Suggestions for scaling ACF

Solving

Suggestions for scaling ACF

  • I searched for info online and on these forums for information about scaling ACF but have not found much. ACF has worked really well for some applications and some parts of our site like with some custom post types and pages… but we are in the process of launching a site that has 100K+ posts and 15-20 acf fields per post. As you can imagine, the wp_postmeta table is really bloated and we feel like this will cause some big performance problems and don’t want to just keep increasing ram as the database grows.

    1. Is ACF not meant to be used for sites that have this many posts? Should we have custom coded a solution that used regular custom fields which would reduce the size of the postmeta rows by half.

    2. Are there any tweaks that can be made so that we can efficiently use ACF with this many posts?

    3. A very large percentage of our posts have some custom fields that have empty values. If we remove those rows from postmeta that have empty meta_value values, will there be any negative repercussions? This post seems to imply that this is possible and fine: https://support.advancedcustomfields.com/forums/topic/database-cleanup-feedback-on-function/

    Thanks!

  • Witt the optimization built into MySQL and the WP tables (all having proper indexes) for performing the queries that are done, there really should be little effect on the speed of a site based on the database size. I’m not sure where you heard that it would. I have some sites with huge databases and as many as 100 fields on some post types using flexible content fields and repeaters with no performance issues on the front end. On one site there is a repeater field and each of these repeaters can have up to 7 WYSIWYG editors and associated fields for a total of about 70 fields per repeater and there is not limit to the repeaters. There are as many as 20 of these repeaters on some posts for a possible total of over 1000 fields. Like I said, I don’t see any performance issues on the front end. If there is any performance loss then it is undetectable.

    On the other hand, a search using the standard WP search that does a %LIKE% query for every word in a search request on 100K+ posts…. that’s going to die, and this has nothing to do with ACF. There is no index on “Content” fields in the database. I would suggest that you look for a different way to do site searches on a site this size.

    If you do a searches and do some research on it you will find that MySQL does not see performance issues on large databases when the tables are indexed properly.

    The back end is a different story entirely, and this really doesn’t have anything to do with ACF, but with the way that WP handles updating the post meta values…. one at a time. ACF simply uses what WP makes available. You would see the same problem whether you use ACF or custom build the custom fields yourself. Because of this, too many fields being submitted on a single admin page will likely cause you issues. That site I mentioned above with the more than 1000 fields on a post, those posts time out when hitting update, why, because WP does more than 3000 queries to update those fields (agian, WP, not ACF), and that does not include the field_key queries. Like I said, nothing to do with ACF. If I had built all these fields myself the result would be the same. That site was built a while ago and since that one I am extremely careful about how many fields I build into a single admin page. When I see that the number will be high I find an alternate way to do so, for example, creating multiple edit pages. I could have, for example, instead of using a repeater field in this case I could have created a hierarchical post type where the “Repeaters” were actually child pages. Although this would have more of an effect on front end performance because it would mean querying for those child pages.

    Hope that helps.

  • John, thanks for such a quick and detailed reply. I’ll share this info with our team and let you know if we have any follow up questions.

    Steve

  • I’m dealing with a site right now that has a lot of fields and I’ve been thinking about this issue. I’m using ACF as a page builder of sorts, and while I’ve found ways to reduce the number of fields in use the slowdown is noticeable on large pages.

    I’m wondering if some sort of alternate saving option might be viable? For example, if top-level repeaters and flexible content fields were saved as a single post meta (encoded in some format), then only decoded and cached when have_rows() is called, that might help with this sort of issue (I assume all large field quantity issues come from repeaters and flex fields). I am not at all sure what other repercussions this might have though, nor if there are better options. If there are tradeoffs, making it an option on the field group level might be sufficient so that we can choose the best save method for our intended use case.

  • I have been wanting, for a long time, to find the time to see if I can bypass then entire WP functionality for update_post_meta() and the other functions in WP that deal with one data item at a time. In my opinion, the entire operations surrounding getting, setting and updating meta values and options is done poorly, one…. at…. a…. time…. That’s the way that WP is build because they never expected that anyone would want to store that much extra data. I don’t think this is going to change any time soon. I’ve seen several discussion about in on trac and nothing ever comes of them.

    It is possible in MySQL to insert multiple rows all with a single query. Even $wpdb has this ability. If you could bypass ACF’s saving of fields you’d simple need to use 2 queries. 1) Deleted everything in the DB related to a post_id and meta_key and 2) Insert all the updated values. Yes, this is a lot of deletes and inserts, but it is done in only 2 queries. The time it takes MySQL to perform all of these deletes and inserts in minimal. The reason it takes WP so long is that each query request takes longer that MySQL would take to do 1000 deletes and 1000 inserts. But first you need to bypass ACF, then you need to assemble the queries, and then somehow stop ACF form doing them anyway. It the 1st and 3rd parts that I’m not sure about and have not had the time to investigate.

  • I looked into this some today, it looks like it might be possible to create a new class to replace acf_input, then reassign acf()->input to use the new class. This should allow you to replace acf_input::save_post().

    This is more invasive and hacky than I’d like, but it might work. It would be better to just have this functionality built into the core, of course. I wonder if Elliot would consider it as a feature request?

  • You might request it, you never know. I do know that Elliot is about using existing WP core functions to do the management rather than work around WP, so I don’t know what he’d think about this. The whole thing is rather hacky to be honest because you must work around WP’s system in the first place, basically throwing away it’s updating of meta values and it’s meta caching system, and then do the same for options, user meta, and term meta once E gets that built in which should arrive in another version or 2. I think this would be the main reason he may not want to do it. It would rebuilding something that’s done by WP.

  • Yeah, we’ve hit big performance issues in the edit screens using flexible content and repeaters for allowing clients to build their own layouts.

    I wonder how easy it would be to add a setting to a field group “save as a single row”, which would save everything in that field group to 1 row. For most of our needs, we don’t need to query against the postmeta fields, so that would work fine and dramatically reduce the time spent in loading / updating pages in the back end

  • I have built a plugin, it does not fix the issue, but it does keep the admin from timing out/crashing and instead shows a “this is taking longer than expected” screen. https://github.com/Hube2/acf-prevent-timeouts

    Recently I’ve been looking at the problem with searches using the standard WP search and the issue that content in custom fields is not searchable. In my case, the standard wp editor is not used at all so after the post is saved I copy all of the important ACF field values to post_content. As you can imagine, this put an even bigger load on saving a post and caused the site to crash. How I worked around this was to set a flag in options to tell me what posts need to be updated and then on the next admin page load an AJAX request is made that causes the copying to be done. The AJAX request uses a similar method to send a reply to the browser and then keep working, much like the method I use in the plugin I mentioned.

    Here’s the problem with saving all of the values to one field in the database, ACF will not be able to find those fields or values later, nor will it update some of the values correctly, for example, adding other choices in radio and checkbox fields, updating taxonomies for a post, correctly formatting map field data, the list goes on. This is the reason that building something to bypass ACF and the individual saving of field values is so difficult. Not only to you need save the values in a faster way but you also need to do all of the field maintenance stuff that ACF does that most people don’t even know is going on. Saving everything to a single field is possible, but then when it comes time for displaying those values you would need to do all the work of getting and formatting the values for the fields values yourself.

    This is definitely something that needs a solution for those of us that create large and complicated sites, but it’s something where the solution is going to be extremely complicated no matter how you decide to deal with it.

  • Anu,

    The solution that we settled upon to speed up our post edit admin loading was to alter the postmeta meta_key index to 191. There are some potential downsides, but in our case it appears to be working for us. If you upgrade WP, check to make sure that the upgrade did not set meta_key back to 255. Here’s the mysql statement:

    ALTER TABLE wp_postmeta MODIFY meta_key varchar(191);

    Here’s an original thread that discusses the issue:

    https://core.trac.wordpress.org/ticket/33885

    Here’s a post that suggests some alternatives:

    https://9seeds.com/wordpress-admin-post-editor-performance/

    We also periodically run a query to delete empty rows both the hidden _ ACF field reference, for example: _some_field_name and the some_field_name field as well. ACF does not appear to mind if those are deleted and if you end up add a value to one of these fields, ACF will re-create the row/s. Here are the queries that I run to do the clean up. These queries work for us because all of the acf fields we want to delete start with the word “article”.

    — delete “_article” postmeta rows that have corresponding empty “article” rows
    DELETE wsp.*
    FROM wp_postmeta AS wsp
    WHERE meta_id IN (
    SELECT mid
    FROM (
    SELECT t1.meta_id AS mid FROM wp_cdp_postmeta as t1
    JOIN wp_cdp_postmeta as t2 ON t1.post_id = t2.post_id
    WHERE t1.meta_key = CONCAT(‘_’,t2.meta_key)
    AND t1.meta_key LIKE ‘_article_%’
    AND (t2.meta_value = ” OR t2.meta_value = ‘0’)
    AND t1.post_id BETWEEN 0 AND 400000
    ) x
    );

    — remove “AND t1.post_id BETWEEN 0 AND 400000” if you don’t want to limit the query

    — delete empty “article” postmeta rows
    DELETE FROM wp_postmeta WHERE meta_key LIKE ‘article%’ AND (meta_value = ” OR meta_value = ‘0’) AND post_id BETWEEN 0 AND 400000;

    — remove “AND t1.post_id BETWEEN 0 AND 400000” if you don’t want to limit the query

    — change DELETE to SELECT if you want to get a count before you run these statements. Always back up your database before your run a DELETE statement

  • This is really strange, the fact that is is 255 and not 191. I was part of a discussion about raising the limit of option_name to 255 and they decided there that 191 would be better since the index is only capable of indexing 191 characters. I’ve never noticed that the meta_key field allowed 255, or I have, but I did not think about the fact the the index only works on the first 191 characters.

    I find it a little amusing that the length of this db field does not consider the max length of the key.

    I used to use this to force the length of the options name field to be longer and not allow WP updates to change it. https://github.com/Hube2/wp-update-prevent-db-changes. I’ll need to look forcing the meta_key field to stay at 191.

    Thanks for making me aware of this

  • I actually like the 3rd solution from the post you linked to better, but I will probably do the change of the database as well, after making sure I don’t have any meta_keys > 191 characters though. Don’t know about anyone else, by I have a feeling that this effects more than that one query as indicated in the posts. Any time you use a db column in a WHERE or ORDERBY in a query, that field length not matching the key length is going to effect the query, contrary to what is said about it otherwise.

  • John, you’ve provided a lot of help in the past on many acf issues, so glad this info was useful. Thanks as well for the wp-update-prevent-db-changes file. That will be useful.

  • Storing the CPTs metadata in custom database tables might be a good solution to greatly improve the performance of database queries. The problem and the solution are explained here

Viewing 14 posts - 1 through 14 (of 14 total)

The topic ‘Suggestions for scaling ACF’ is closed to new replies.