Split database on large site?

Question

What will be best way to split large database on single site, not multi-site wp installation? HyperDB is maybe for this (just, less than 10 webmasters managed to figure out how to install it, according to active installs).

Currently site has about 350k posts, it is on good VPS. Site is running quite fast in fronted, opening pages, browsings... But it slows down at back-end when publishing new posts, every day lots of new posts are added.

Site has this permalink structure:

sitename.com/year/month/day/postid/post-name Practically just new posts are edited, and once published post are not changed.

Any idea how to speed up database writing, or better, to have published post in one database, and have new database for future posts?

---

Update:

First installation: Is installation in root of site with new database, without old, published posts, just for future posts. Permalink structure is as it was:

sitename.com/year/month/day/postid/post-name

Second installation: Created folder "2015" in root of site and installed WP in that folder with sql database of original WP installation from root, with all posts up to end of September, just permalink are changed to:

sitename.com/month/day/postid/post-name

and it will give the same URL structure as it was (because WP is in sub-folder "2015"):

sitename.com/year/month/day/postid/post-name

This approach is working when I need to split database once per year (for example for year 2014). For every past year I can have one folder named by past year and with WP installation in that folder with database with posts of that past year.

That way when visitor try to open URL for example:

sitename.com/2014/01/01/post-name-here

server will look first in sub-folder /2014/ and there is WP installation which will return that post.

Problem: When database is too big for current year I tried to split posts from current year into two databases, one have all published posts from this year up to end of September, and another will have future post.

I have this:

In root of site I have folder "2015" with WP installation with permalink structure:

sitename.com/month/day/postid/post-name

And in root WP installation with permalink structure:

sitename.com/year/month/day/postid/post-name

If I try to open URL:

sitename.com/2015/09/01/post-name-here

server will check in folder "2015" and WP installation from that folder will return post from 1st September, and it works fine.

If I publish new post today in WP installation in root of site it will have URL like this: sitename.com/2015/10/29/post-name-here

And If I try to open it, server will look in WP installation inside of folder "2015", but that installation have posts just up to end of September, and I get 404. After that server will not look inside WP installation in root of site where that post is.

Question:

How to make server look in WP installation inside sub folder "2015", and if there is no post in that installation to look for that post inside WP installation in root of site?

Answer 1

I had a similar issue on a website with ~100k rows in wp_posts and ~500k in wp_postmeta.

Truth is WordPress have some really slow queries in the admin when you start to have a lot of posts.

The best way to find the bottleneck is to use the Debug Bar plugin.

You will probably find out some crazy slow queries like the following:

SELECT DISTINCT meta_key
FROM lc_postmeta
WHERE meta_key NOT BETWEEN '_' AND '_z'
HAVING meta_key NOT LIKE '\\_%'
ORDER BY meta_key
LIMIT 30

or

SELECT ID
FROM lc_posts
WHERE post_type = 'attachment'
AND post_mime_type LIKE 'audio%'
LIMIT 1

and

SELECT ID
FROM lc_posts
WHERE post_type = 'attachment'
AND post_mime_type LIKE 'video%'
LIMIT 1

These are known issues unfortunately but you can tweak them to load faster.

For example i simply removed the custom fields metabox which i didn't use and gain 50% load time.

add_action( 'admin_menu' , 'pu_remove_post_custom_fields' );

function pu_remove_post_custom_fields() {
    remove_meta_box( 'postcustom' , 'post' , 'normal' ); 
}

I am not sure splitting your DB into multiple read instances would help.

Answer 2

You can't split the DB without losing functionality like search,rss,tags,categories. If you don't care about it then you need to switch your site into a network have a site per year or maybe even month.

Once you have arranged the network there are all kind of ways to aggregate content from all the sites of the network which you can implement for the features you care about.