0

This question doesn't have any codes, it's more of a theoretical question. I need guidance on what method to look for. I've searched on google before but found nothing on this specific case.

I have 2 servers(CentOS) where I have MySQL database set up. In one server(server 1) I have Wordpress installation(LAMP) with the database set up. The site is working. However, I want to split the database that is currently on server 1 and host it partially on server 2. Now, I know how to use a remote database in WordPress' wp-config.php file but here I want to use two databases.

In this case, I can try a manual query from the second server. However the reason I want to split the databases because of a single table that includes more than a million entries. Because the table is also going to be split, and the table is wordpress' default post meta table, and I wouldn't know which posts are going to be in which database(more than million entries!), I can't use a custom query. Also I know that default $wbdp only pulls data from 1 database.

To summarize this question, is there any way to use multiple Wordpress Database without having a custom database query?

Improve this question

1 Answer 1

Reset to default
0

No. This is not practically possible primarily because there is no single interface to this table where you could implement the functionality you need, and even if you could, it would introduce some extremely gnarly problems.

Why this is more or less impossible

What you're asking can be simplified to a question about if you could easily shard the wp_postmeta table in Wordpress. (This is relevant because if you could shard a table, it would be a much smaller step to the shards being on multiple databases / servers).

In sharding, some code has to be able to figure out where to look for the result according to some simple algorithm, and then be able to query only that place. For example, if you had 10 million people in a database, you could put them in different tables or different databases split by the first letter of their name*. This means every time the function to get all the information about a person ran, it would first find the right table according to the first letter of their name*.

The reason this is impossible is that there is no single interface to the posts and/or postsmeta table that would allow you to put this code in one place. To achieve what you want, every single place that touched the postmeta table, or did a join to the postmeta table would need altering to figure out which of your two places to look, according to some deterministic algorithm. You'd also have some extreme gnarly problems about figuring out joins (e.g. join across databases/servers is probably possible but sounds like it would quickly defeat any performance gain).

What are you really trying to do?

It sounds like you've got a problem and you think this is the solution, but this is almost definitely not a good solution to whatever your problem is (or any problem in a single Wordpress installation). Probably there's a simpler easier solution, or you've got some bad advice/information somewhere.

Why is it a problem that there's 1M rows? Are you just trying to optimise queries to run faster? Are you running out of disk space somewhere? Do you have some assumptions/limitations you didn't add to your question?

[*] Note you'd probably try to shard by something that created roughly equally sized pieces, and you might create some kind of index, but this is just an example, read the Wikipedia page for more info.

Improve this answer
6
  • Thank you so much for the explanation. The main reason I am looking to do this is because my sever is being overloaded when google tries to crawl the site(entire blog). Google has put almost 100k posts in exception, showing indexed but can't be crawled, the reason is the server being overloaded. So I ran some tests, when Googlebot tries to crawl, PHP processes doesn't take a huge amount of resources(10% max), however, MySQL crashes. I tried scaling it(took the $1000 server temporarily on DO, can't afford that), that doesn't work either. So my only option I am seeing right now is this.
    – Asifur Rahman
    Commented Jul 27, 2020 at 14:35
  • Do you think there might be other options? If yes, please help me in this. Thank you
    – Asifur Rahman
    Commented Jul 27, 2020 at 14:36
  • Ah, that makes sense. Interesting problem. Are you aware you can move just the MySQL database to a new server that would be dedicated to MySQL, and leave the current server just doing PHP? Then you can scale up (or better yet, optimise!) the MySQL server as much as you need.
    – mozboz
    Commented Jul 27, 2020 at 14:40
  • I've tried to host the DB remotely but didn't see a huge difference while I was testing but I will keep it that way to see if Googlebot can crawl it
    – Asifur Rahman
    Commented Jul 28, 2020 at 15:59
  • In the situation you describe you should definitely do this. You won't see a difference for small load (in fact it might even be a tiny bit slower), but when the database is under heavy load you will start to see a difference. Then, if you increase the memory on the MySQL server - all the memory goes to MySQL. You should also be looking at optimising MySQL for the queries that are running, but that's a whole other subject.
    – mozboz
    Commented Jul 28, 2020 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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