0

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?

5
  • why do you think that the slow down is DB related? – Mark Kaplun Oct 28 '15 at 18:32
  • Publishing new post is slower every day, during adding new posts CPU load is bigger. Am I right? Beside that, database is bigger and bigger every day. – Advanced SEO Oct 28 '15 at 19:02
  • DB work at on O(nlogn) complexity. It is unlikely that if you have 100k posts you will be able to feel the difference in performance when there are 101k posts (should be a 1% performance change which should not be noticeable to human). In time long past yoast SEO would bring performance down due to generating its google news feed from scratch when a post was published. – Mark Kaplun Oct 28 '15 at 19:28
  • 1
    If publishing posts seems to be a bottleneck, I'd take a look at what's happening (ie what code is run) at that particular time. – JMB Oct 28 '15 at 21:46
  • You can also put indexes on columns used by slow queries. – RMS Oct 29 '15 at 14:30
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.