Do I need MySQL Group Replication or a Single Server is enough for Click Counting, etc?

Question

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?

Answer 1

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 id of what you are counting and an INT UNSIGNED (4 billion max). By segregating this from the rest of stuff associated with id, 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.