I'm planning to create a system which tracks visitors clicks into the database. I'm expecting around 1M inserts/day into the Database.
On the backend, I'll have an analytics system which will analyze all the data that's been collected over the days/weeks/months/years.
My question is: is it a practical approach to have 2 different MySQL Servers + 1 Web server? MySQL Server A would insert the clicks into it's DB and it would be connected to MySQL Server B by group replication, so whenever I create reports, etc on MySQL Server B, it doesn't load Server A heavily.
These 2 Database servers would then be connected to the Web Server which would handle all the click requests and displaying the backend reports also.
Is it a practical solution, or is it better to have one bigger server to handle all the MySQL data? Or have multiple MySQL servers that are load balancing each other? Anything else perhaps?
Best Answer
1M
INSERTs/day = 12/second. This is not very heavy -- for either a web server or MySQL.While you presented an argument for having "reports" come produced on a Slave, there are ways to mitigate the load.
But creating and maintaining Summary Table(s), reports become light weight enough to not worry about. The summary table(s) would be keyed by day plus some other dimension(s) and have
COUNT(*),SUM(...), etc. Daily subtotals are (usually) sufficient for weekly/monthly/yearly rollups.For counting clicks -- assuming you just want to count them -- I recommend having a separate table with nothing but the
idof what you are counting and anINT UNSIGNED(4 billion max). By segregating this from the rest of stuff associated withid, there is much less interference.Multiple web servers may not be needed, but scaling by adding another web server is relatively easy and painless. Do, however, keep the number of threads in the web server down to, say, 30.
Each web servers can talk to at least one MySQL server. I don't (yet) see the need for more than one MySQL server. By not having multiple MySQLs, that complexity is avoided.
There are other reasons for using Replication (Group Repl or other) -- backups, HA, etc.; I am merely saying that your functional separation is not sufficient justification.
Another note... If click-counting is done with an HTTP request for which the reply is ignored, then there is no real reason to worry about whether they are queued up and delayed. There will be bursts of more than 12/sec. My Rule of Thumb is that a sustained rate of more than 100/sec. justifies extra consideration.