我们正在运行一个定制的广告服务器解决方案，在某些情况下，我们在MySQL中存储和增加/减少计数和聚合值，以进行优化。
我知道在MySQL中保存计数并不理想，尽管我不能100%确定原因。我的猜测是，写入磁盘的速度比写入内存的速度慢，因此将计数保存在Redis中会比将它们保存在MySQL中更好。但这会给我们的系统增加几层额外的复杂性，并意味着对多个组件进行严重的更改。
在过去，我们看到连接数堆积起来，CPU利用率上升，但在缓存大多数请求并切换到更快的磁盘后，我们设法提高了这一限制。我们目前每天处理1000-2000万个请求，采用主从MySQL配置，其中写操作发送到主服务器，大部分读取操作从备用服务器完成。此外，添加另一台从服务器根本不是问题。
我关注的是主服务器上的写操作，尽管它们只是在5个表中的每个表中递增一行的微小操作。
因此，我的问题是：根据您的经验，在达到限制之前，我可以期望在MySQL中安全地执行每秒多少次这样的小写操作？我听说有人同时处理10万个连接，但如果所有这些连接都试图在同一秒内增加一行怎么办？在需要时扩大规模的好策略是什么？
谢谢!
2条答案
按热度按时间xmq68pz91#
For InnoDB, you will probably be OK with SSDs.
20M
UPDATEsper day = 230/second; more if there are spikes.Spinning disk (HDD): 100 writes per second.
SSD: Maybe 1000/sec.
Batched
INSERTsrun faster.Some variants of RAID run faster.
A RAID controller with Battery Backed Write Cache runs really fast (for writes) (until the cache is saturated).
For keeping "likes" or "views" or "clicks" it is probably better to keep the counters in a separate table from the rest of the columns about the page/product/whatever. This way, updating the counter and dealing with the other data interfere less.
There are several settings to consider tuning:
innodb_flush_log_at_trx_commit = 2(Tradeoff for speed instead of security)sync_binlog = OFFCombine multiple actions into a single
BEGIN..COMMITtransaction. But be cautious about deadlocks, etc.Perhaps you did not mean 100K connections? That is a huge number. More than a few dozen running threads (irrespective of the number of sleeping threads) is quite a lot.
Another approach to counting clicks is not to record them in real time, but scrape the web server log every, say, hour. Have a separate process to count all the clicks for the hour (or each 5 minutes or whatever) into a single number to feed to an
UPDATE. That would be more work for a separate server, but very little effort for the database.ocebsuys2#
如果您坚持使用MySQL，那么我建议您在内存表中使用
Reference
BTW
因此，我的问题是：根据您的经验，在达到限制之前，我可以期望在MySQL中安全地执行每秒多少次这样的小写操作？
我不认为在座的任何人能给你明确的答案。