So, one of my clients are using WP as a headless CMS on a local server. I can see how this is useful, the WP API works for them. I just moved the install to ClassicPress.
This has sped things up, but their database is bloated, because they have too many posts, and all these posts use custom post types and metaboxes. Ive run all the usual optimizations, and some SQL queries to select and remove useless postmeta.
Here’s the thing, none of the post types are using the “post_content” field (default wordpress field) or featured image, etc - - all the data is stored in metaboxes.
So is it possible for me to “strip out” the default “post_content” and featured image fields from the classicpress install and the corresponding database? Are there any Classicpress plugins that can modify the database schema and remove the fields I don’t need?
Your best option is to use SQL to set post_content to null and delete featured images. Either using MySQL CLI or WP-CLI to execute SQL or phpMyAdmin/Adminer.
Something like this, but please test on dev before doing it on the production site and make backups:
UPDATE wp_posts SET post_content = NULL WHERE post_type = 'post';
For featured images, this might work:
DELETE FROM wp_postmeta WHERE meta_key = '_thumbnail_id';
You can use variations of these for other fields too.
What you’re asking for won’t achieve much. It would be far better to see what can be moved out of postmeta and instead into either post_content or custom taxonomies.
If you want to improve admin performance, adding Redis object caching can help.
Tuning your MySQL server will also help using MySQLTuner, if technically possible.
On another note, I would love to hear what the headless setup looks like and how the static files are generated and deployed. CP is excellent as headless CMS.
Pods 2.7.31 (the last version that works with CPress)
Enabled “Table Storage” in pods - - this stores all metabox fields in separate tables, one table for each CPT, similar to headless CMS options like Directus. This significantly reduces the load on postmeta. Details here: Meta vs. Table Storage - Pods Docs
Exported all the CPTs (whose custom metabox fields were in meta storage) with WP-API (including all custom fields) and reimported them all into the Table Storage format.
Removed all orphaned entries in postmeta.
Right now the db has 22 custom post types, 56000 posts in total, around 103000 postmeta entries. The client tells me the post count is expected to increase a lot, lot more. Can’t be too specific about the use case but it involves a large user database and project management aspects, which explains the high post count.
At this point, I just want to make sure that my cp_podsrel (acts as a junction table for relationships between pods CPTs, many of the post types have bidirectional relationships with each other) table and cp_postmeta table are as lean as possible. I’ve never dealt with such a large WP/ClassicPress database, tbh, so I have now idea how this will go - - fingers crossed
Worst case scenario I’ll have to move the entire thing to Directus - - which isn’t too bad, but I’m not a fan of the UI, and it doesn’t handle joins as elegantly as WP-Pods.
Yeah the client (or whoever their last dev was) set this up in a very interesting manner. Its a local deployment running on an NVME SSD on the clients server, and is accessed purely from the organization’s ethernet LAN connection. It functions as a project management setup for employees, as well as an LMS for onboarding new hires.
CP can easily cope with many times that number of posts and postmeta. In fact, it seems you now only have two pieces of meta per post. That’s actually very good, provided you never run a query that goes from the meta to a post.
Thanks, thats reassuring, I would prefer to keep using ClassicPress and not have to shift to Directus.
I’m curious, is there a maximum table size or number of rows for podsmeta?
Not really. A MySQL database, which is what CP uses, can handle a huge amount of data.
The big no-no is running a query going from the meta to the posts instead of from the posts to the meta. The relationship between the two tables is designed to be one-way. Going the “wrong” way with a large number of postmeta entries would be very slow and might even cause a white screen of death.
yeah I’d love to know more about this too, I thought WP/CP worked with MySQL/MariaDB and nothing else.
Also, regarding indexing mySQL, has anyone checked out this plugin:
By default WordPress uses the MySQL database engine. If we want to use WordPress with PostgreSQL instead, we will need some kind of plugin. PostgreSQL for WordPress (PG4WP) is one such plugin that allows you to install and use WordPress with a PostgreSQL database as a backend.
Once you install [a] plugin find the Postgre files and download then run sudo yum install -y postgresql12-* to install. There are a few more steps but some of the folks at Codeable have done this many times for LARGE databases and successfully sped up the sites without moving to some expensive server.
