Abstract:
This post aims to analyze the performance disparity between utilizing Custom Post Types and Custom Fields versus Custom Tables within the WordPress environment. Through a retrospective examination of this initiative, the study explores the environment, methodologies employed, and the resulting conclusions. By drawing from past experiences, actionable insights are provided for organizations seeking to optimize their WordPress setups.
Introduction:
This study is rooted in a practical and real-world scenario encountered during a previous engagement. The context involved a broadcasting company’s need to import Associated Press Stories and Media to its sites in a scheduled and predictable manner. To facilitate this, I developed a plugin to seamlessly ingest posts and media every 5 minutes, integrating them with manually generated posts by the News Department. However, the caveat was that the distributed posts and images could not extend beyond the company’s websites.
Environment:
The WordPress environment was hosted in an Enterprise level Hosting Provider, which provides scalability, image optimization, CDN, and firewall services.
The plugin leveraged the Associated Press API, providing access to stories, media, and associated metadata. Approximately 400 posts and an average of 4 images per post were ingested daily, resulting in approximately 140,000 posts and 560,000 images annually. The number of records in the post and post meta tables were as follows:
- Posts: Approximately 1,000,000 (inclusive of posts, media, revisions, etc.)
- Post Meta: Approximately 4,000,000 (inclusive of thumbnails and metadata)
See the Codex Database Description for more information about these tables.
Methodologies Employed:
The initial approach involved utilizing the native WordPress method of storing posts with a post type of “posts.” Advanced Custom Fields (ACF) were employed to manage metadata about the posts. It’s pertinent to note that the challenges encountered were not unique to ACF usage but also prevalent with Custom Fields, as ACF operates as an “application layer” over Custom Fields. The ingestion process, occurring every 5 minutes, encompassed a mix of new posts and media, as well as updates to existing ones. A key query was implemented to differentiate between new posts and updates, with the WHERE clause filtering for:
- Published posts only.
- Posts sourced from “AP” (utilizing a custom field).
- Comparison based on the “Source Unique ID” to determine new posts or updates.
- If an update, further comparison based on the “Source Version ID” to ascertain the necessity of updates based on version disparities.
The subsequent sections of the white paper will delve into the performance implications of these methodologies and draw conclusions based on the observed outcomes.
Conclusions:
As time progressed, I observed escalating performance issues within the backend and inconsistencies regarding post appearance on the website. The situation deteriorated with each passing day/month. Seeking resolution, I reached out to our Hosting Partner, who directed me towards the New Relic tool, included as part of our Plan.
Upon utilizing the New Relic tool, I identified the AP plugin query as the foremost time-consuming query consistently topping the charts. Delving deeper, I investigated the storage of posts and ACF/Custom Fields, revealing an alarming volume of records stored in the WordPress tables, posts, and post_meta. Each post comprised multiple records in the post table (inclusive of posts, images, revisions, etc.), with numerous corresponding records in the post_meta table.
Executing the query via MySQL WorkBench, I noted several instances of Full Scan/ Full Reads. In MySQL, a full scan, or full table scan, involves the database engine examining every row in a table to fulfill a query request. Here are some drawbacks of Full Scans:
- Resource Intensive.
- Slow Query Execution.
- Negative Impact on Server Load.
- Disk I/O Bottleneck.
- Records Locking and Blocking.
- Inefficient Memory Usage.
- Limited Scalability.
- Introduce Instability in the Database.
Furthermore, the post_meta table indexed by the post id and meta key exacerbated the issue, leading to multiple Full Scans. Alas, altering the default index of the post_meta table was deemed unfavorable due to potential negative impacts on adding and updating records. While indexes bolster query performance, they also present drawbacks:
- Increased Storage Requirements.
- Overhead for Write Operations.
- Slower Data Modification.
- Impact on Insert Performance.
- Increased Memory Usage.
- Complexity for Database Maintenance.
- Index Selection Overhead.
Solution:
After grappling with the notion of integrating Custom Tables, which entail additional knowledge and maintenance, I resolved that Custom Tables were the optimal approach. Drawing from previous experience with PeopleSoft and ORACLE databases, where a tweak in indexing transformed a laborious process into a swift one, I recognized the potential of custom tables and indexes.
Armed with this insight, I established a straightforward custom table to manage supplementary metadata previously housed in the post_meta table. This custom table, indexed by post id, maintained a one-to-one record relationship with posts, significantly reducing the record count compared to the post_meta table.
By implementing a custom query as opposed to the default WP_Query for post retrieval, I successfully alleviated the IO bottleneck, expediting query execution to a fraction of its former duration.
Final Thoughts:
In navigating the labyrinth of database optimization, my journey unveiled the intricate balance between performance enhancement and system stability. Through meticulous examination and iterative refinement, I uncovered the nuanced interplay of indexing strategies, query execution, and database architecture.
In the realm of database optimization, as in life, every obstacle presents an opportunity for growth, and every solution provides a new horizon of possibility.
In closing, it’s crucial to clarify that the performance challenges encountered were not a result of utilizing Advanced Custom Fields (ACF). Rather, they represent inherent hurdles within the WordPress framework. While WordPress offers an array of tools suited to various scenarios, it’s important to recognize that not every issue can be resolved with a single solution. Just as a diverse toolkit contains both hammers and screwdrivers, WordPress provides versatile options to address different needs.
In my experience, ACF stands out as an indispensable resource for developing custom themes and plugins. The functionality it offers, particularly with repeating fields, surpasses its modest cost. Considering the expenses associated with in-house development of comparable features, ACF proves its worth effortlessly. I wholeheartedly recommend every WordPress developer and site owner explore the possibilities offered by this invaluable tool. However, like any potent remedy, it’s essential to read the instructions and apply it judiciously, keeping it out of reach of inexperienced users.
In the upcoming article, I will showcase a series of images illustrating the significant performance improvements achieved in a scenario similar to the one described with 100,000 posts. These enhancements are not client-specific but represent a broader understanding of optimizing WordPress performance.
Additionally, I will provide a link to my GitHub repository containing a free plugin. This plugin has been designed to facilitate benchmark testing. By sharing these insights and resources, I aim to contribute to the WordPress community’s understanding of performance optimization strategies and provide practical tools for developers and users alike. Stay tuned for the article’s release to explore these concepts further.
