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.
我的问题是:拥有两个不同的MySQL服务器和一个Web服务器是一种实用的方法吗?MySQL服务器A会将点击插入到它的DB中，它会通过组复制连接到MySQL服务器B，所以每当我在MySQL服务器B上创建报告等时，它不会加载服务器A。
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.
这两个数据库服务器将被连接到Web服务器，Web服务器将处理所有的单击请求并显示后端报告。
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?
这是一个实用的解决方案，还是最好有一个更大的服务器来处理所有的MySQL数据?或者有多个MySQL服务器负载均衡?也许什么?
1 个解决方案
#1
2
1M inserts/day is not a high load by modern standards. That's less than 12 per second on average.
按现代标准，1米/天的插入量并不高。平均每秒钟不到12次。
On sufficiently powerful servers with fast storage and proper tuning of MySQL options, you can expect to support at least 100x that load with a single MySQL server.
在具有快速存储和适当调优MySQL选项的功能强大的服务器上，您可以期望至少支持使用一个MySQL服务器装载的100x。
A better reason to use multiple MySQL servers is redundancy. Inevitably, any MySQL server needs to be upgraded, or you might have hardware failures and need to replace a disk, or other components. To avoid downtime, you should have a standby database server, which stays in sync with the primary server, either using MySQL replication or by disk-level replication like DRBD.
使用多个MySQL服务器的一个更好的理由是冗余。不可避免地，任何MySQL服务器都需要升级，或者可能出现硬件故障，需要替换磁盘或其他组件。为了避免停机，您应该有一个备用数据库服务器，它与主服务器保持同步，可以使用MySQL复制，也可以使用磁盘级复制(如DRBD)。
#1
2
1M inserts/day is not a high load by modern standards. That's less than 12 per second on average.
按现代标准，1米/天的插入量并不高。平均每秒钟不到12次。
On sufficiently powerful servers with fast storage and proper tuning of MySQL options, you can expect to support at least 100x that load with a single MySQL server.
在具有快速存储和适当调优MySQL选项的功能强大的服务器上，您可以期望至少支持使用一个MySQL服务器装载的100x。
A better reason to use multiple MySQL servers is redundancy. Inevitably, any MySQL server needs to be upgraded, or you might have hardware failures and need to replace a disk, or other components. To avoid downtime, you should have a standby database server, which stays in sync with the primary server, either using MySQL replication or by disk-level replication like DRBD.
使用多个MySQL服务器的一个更好的理由是冗余。不可避免地，任何MySQL服务器都需要升级，或者可能出现硬件故障，需要替换磁盘或其他组件。为了避免停机，您应该有一个备用数据库服务器，它与主服务器保持同步，可以使用MySQL复制，也可以使用磁盘级复制(如DRBD)。