Using a central database server for many sites: pl

Question

Basically, I need some parts of database data synchronized on up to several dozens of sites. The perfect solution would be creating a central server to host that data.

Each pageload will have to fetch data from both database servers - the local and remote one and writes to the remote server will be quite common too.

While the db server can be as fast as desired hardware-wise, I'm cautious of the bottlenecks:

• Multiple database connections must be established on each pageload.
• Latency of the signal traveling between two physical locations.

Am I right to worry? Would it be wiser to synch the databases with cronjobs or other technologies?

---

Along with assigning a bounty, I'm adding to the question hoping an expert with real life experience of this comes along:

What other technologies are there (besides cron) to sync MySql databases?

Answer 1

I have made some database synchronization between client-server php application and used following idea http://vitana-group.com/article/php/data-synchronization

Answer 2

The way Google solved this issue (you get some of the information here. I'm sorry I don't have the link to the actual published paper describing it) is more or less through a series of triggers.

There is one (and by one, I mean thousands) central data hub and a series of clones. Each time a write is needed, a lock is requested of the hub, the write is performed on the clone, which then forwards the change to the hub (thereby releasing the lock). The hub then pushes the data to all of the other clones.

This means that read access can stay almost instant (you have a localized clone per site instance). Local write access will be quick as well. Everything else can be handled asynchronously so that the two servers only need to communicate a lock request and a lock received message before the write starts, and the push can happen after the user has moved on.

This may be a tad much for your needs, but that is how Google does it.