Forum Moderators: open
This is more of a technique question that I'd like to get some input on.
I'm building a database driven application that will have a central repository that shares its data with up to maybe 200 other sites. The data is basically your standard product stuff.
My company will be hosting all the connecting sites, and I'll have administrative control over them, so theoretically any of these tactics would work to share the data.
I'm wondering if you can tell me if one of these approaches is better than the others, and for what reasons.
1) basic php mysql connection: I could create users in the users table of the central db to allow all remote sites to make native php mysql connections and grab data that way
2) web services: I could write a SOAP service on the central db machine to accept external requests, authenticate against a users table, and return results that way
3) XML-RPC: I could do the same as 2, but with XML RPC
I am most curious to know what approach is the most efficient for managing a lot of connections (and frequently, since I can't just replicate the db many times).
If there's another approach I ought to consider, please let me know.
Thanks
No matter what method you use at the end all of them will make SQL query to retrieve, insert, update or delete a record(s).
If you can not replicate the database to any of the other 200 hosts, can you at least cache some of the content on the web servers and by doing so you keep the DB request slow. You can also try optimizing queries, reducing the sleep time of MySQL connections, removing unnecessary queries, etc.
If the 200 site are on different hosting companies the real issue will be network latency that can make some site slower than others.
Beside all that I have used on the past MySQL with a lot of server connections and a well tuned server (processor power+RAM+RAID) working together with a well coded application can handle a high load of connections.
Enjoy!-)
I think I'm going the web service route, just to make the authentication easier!
