I have developed an API that that send out text messages There are currently three MySql tables running on the same server (sms_account, sms_number and sms_transaction).
I want to use haproxy to load balance and improve the performance to split the requests between multiple web servers but I don't really know how to handle the database part of it. I know I could create a separate database cluster in a master-master scenario but wondered if it could be done better.
I have thought about having one main clustered database that contains an identical database to the webservers. The sms_account and sms_number tables could be updated only on this one and this one could update the web servers sms_account and sms_number tables as these do not change very frequently. I then thought that each web server / mysql instance could periodically update the master database with the records from each of the sms_transaction tables.
It sounds a little messy but thought this way the database load would be really low as all the web server databases would all remain small and fast and the main one could be used for billing.
I'd just like a second opinion on the design. I think this way in a fault scenario any of the web servers could continue working independently of the other servers.
The web servers are literally logging the transaction and that is it. There are no complex queries. I could do the reporting etc from the master database.
Many Thanks
