每一分钟写入 10 万行数据，有啥好的方案吗？

[goodspb]

这 10 万数据在接下来的 2 分钟还要操作（ update ）

当然 db 是 mysql，应用是 spring boot

在说一下前情提要：

有一个业务，如果用户开启了就需要每分钟给他生成一条数据。所以 10 万是我看实际用户量的预估值

[MintZX]

应该是峰值吧，你不可能 24 小时一直一分钟 10 万吧？

先写到 redis，然后处理也在 redis 处理，然后不忙了再慢慢处理就好了。

[misaka19000]

先堆机器

[nooper]

不難吧，很快。

[qiayue]

一秒 1667 条，有什么难度？

[goodspb]

@MintZX 还真的是 24 小时，需求内容就是这样。不过当然了，最后每个人保留后 100 条数据，数据可以清理

[fightingCode948]

有个东西叫 mq

[qiayue]

如果一个请求带来一行数据，那就是设计有问题了

[goodspb]

@qiayue 写入应该不简单，单主情况下

[rushssss]

一秒 1667 条，有什么难度？ MySQL 完全可以 hold 住

[GM]

@rushssss
一秒 1667 条不难，难的是全天 24 小时都这样。

当然我不相信这个是真是需求，大概率是设了一个理想上限，实际运行起来平均每分钟百十来条。

[bagheer]

入库后再处理 ×
处理后再入库 √

[angryfish]

批量还是单条 insert，策略是不一样的。批量很容易。单条估计够呛。要想办法合并

[opengps]

100000/60=1666.6 如果单行 1k，那么这个数字在 mysql 及时单条写入也不夸张，普通 5400 机械硬盘都能达到，ssd 就更轻松了。
参考： https://www.opengps.cn/Blog/View.aspx?id=422

[dongisking]

插入的不成压力，之前用一个 excel 更新 30w 数据库的功能，通过组装语句
```
UPDATE table_name SET col_1_name = CASE
WHEN id = '1' THEN 'col_1_value'
WHEN id = '2' THEN 'col_1_value'
ELSE col_1_name END,
col_2_name = CASE
WHEN id = '1' THEN 'col_2_value'
WHEN id = '2' THEN 'col_2_value'
ELSE col_2_name END
WHERE id IN('1','2')
```
一分钟完成更新

[goodspb]

@GM #10 是的，10W 的确是一个预估值，有可能是 20 万

[947211232]

如果数据都要入库，那用时序数据库；如果每天晚上才筛选少量入库，则 redis 缓存、mq 队列。

[nvkou]

业务不行，性能来凑。预算不行，打回重做

[shoaly]

@GM 真正跑起来 除以 10 就行了, 一般拍脑门的老大 都喜欢随便说的

[goodspb]

@shoaly #18 我是统计了过往业务量得出的一个预估值，不是产品给我，是我自己估

[goodspb]

@angryfish #12 应该是可以合并

[reus]

不要提前优化

[shoaly]

@goodspb 假设, 你有很多万的用户, 这 10w 是日活的话, 那么卡死你们服务器 估计不是这个每分钟一条的请求, 而是客户正常点 app 的请求, 那个比 一分钟一次来的更容易崩... 所以如果他正常用都没崩的话, 你 1 分钟搜集一次 妥妥没问题

[594duck]

V2er 们真的虎，动不动 1 秒 1667 条有什么难度，2 分钟后的 update 操作怎么弄。你们说呀，嘴巴动动真的简单。

楼主我和你说，像你这样的数据，能不 update 就不 update,另外你的 update 让大数据团队也难干活。我建议你是走流水一样的操作，这样主库不开索引 ，只管写。后面弄个几个从库只读，用来做业务查询 ，读写分离。

另外你这量级的话，计算一下数据量，我建议走 DRDS 或者类似的 mysql 集群方案，因为你要考虑到将来你的数据总量实在是太大了。

如果考虑未来增长什么的，最好直接上类 oracle 或者 microsfot sqlserver 方案。数据库成熟 ，能力强尽，只要无脑堆机器 就行。（事实证明自己上 Oracle 在五年成本里和阿里的 DRDS 相比并不贵到哪里去，你看一下 DRDS 的报价就知道了）。

同时可以测一下 Microsoft sqlserver 和 mysql 集群的能力。Sqlserver 能力非常强的。

[sivacohan]

先算下服务器的价格。
要是公司买不起服务器，你这个架构怎么做都没用。

[rushssss]

@594duck 写入不成问题，update 只要能恰当的分库分表也没什么问题，这种情况下没必要搞什么花架子，MySQL 完全可以处理，如果要聚合查询，那就弄个从库单独查就行了

[cco]

mysql 限定死，服务器没给上限，就算玩出花来也满足不了你的不确定数值的需求啊，今天 20W，明天 200W，后天 2000W 。。。。针对不同的需求选择不同的技术，没必要提前满足。迭代，迭代，迭代！

[ywlvs]

每分钟 10 万条数据，一天貌似就超过 1 亿条了噢，这已经超出我的认知范畴了。

[594duck]

@rushssss 他这数据量，随便 select 一下超出行数都直接转为全表扫描。

[Varobjs]

有什么难度吗，本地环境，30 万数据耗时 1min 左右，用的是 on duplicate 插入更新操作

[Varobjs]

而且时间包含，从 mongo 读 30 万数据的时间

[ulpyxua]

用队列+缓存，一天一用户就 100 条，太容易了。

[leeg810312]

@Varobjs 内容才几行都不看全的吗？ 10 万插入的数据还要马上 update，光几十万插入性能有什么用

[Vegetable]

建议你先用土办法做出来试试性能，目测并没有太大压力，24 小时也无所谓

[qiayue]

@594duck 1 秒 1667 条数据，如果是一个 sql 语句批量插入，的确没有任何问题啊。
如果是 1667 次插入，那会有问题，如果真是这样，那就是业务设计有问题

[qiayue]

建议楼主重新梳理一下业务，是否真的有必要这样操作

[xuanbg]

优化的核心点是：为什么每分钟要生成一条数据，并且写入数据库？
1 、能不能按需生成而不是定期生成？如果可以，估计数据能少 99%。
2 、能不能汇总后写入数据库？如果可以，还能少写 99%。

两个点优化下来，每秒只用写 10 行。

[goodspb]

@xuanbg #36
@qiayue #35

本来的方案是按需生成的，用户进来页面才生成。

但是最近想上一个版本，用户不在界面也要生成（主要他选择了他想定时生成）。
然后他还能看到前 100 条记录。

[qiayue]

分库分表

[312ybj]

前两天写着玩， 把自己阿里云数据库的数据库内容扩大了下。 如果单线程，1s 最多 6 条数据插入，特别慢。 后来改了用多线程插入，单个插入，1s 100 条左右。 如果再适当优化，速度还会上去

[Sasasu]

典型的时序数据库负载，推荐 timescale，想省硬盘并且不讨厌 go 就 Influxdb 。

timescale 缺点是硬盘空间占用大，有点是内存占用小，没有不必要的多维度索引开销（表尽量简单），历史数据也可以压缩
influxdb 优点是实时压缩率高，缺点是内存占用多，启动慢，go 写的

不推荐其他监控用的数据库，性能差或者为 k8s 监控特化太多。

也可以去买按次付费的时序存储，不会很贵。

[lff0305]

MQ + Prepared Statement (需要比较新的 MySQL)

[securityCoding]

字面意思拍脑袋的想法是
1:mq 做分发负载
2:batch insert

[594duck]

@qiayue 不要忽视 了还有一个 update 操作，update 操作必然要 select 。他这写入量肯定不能开索引 了，但是不开索引 就是全表扫描再 update 。其实这量级就算是开了索引也没用，因为 select update 。这 select 超过千条自动变成全表扫描（ mysql 机制)。所以

阿里的 DRDS 方案是可以行，前面开 32G DRDS，后面开 20 个数据库。百库百表。一年烧掉 100 万在数据库上，保他平安。

我不是吹牛，物流企业都这么干的。结果一算 TCO，5 年 TCO 还是 Oracle 便宜了。

[zzzmh]

mysql 可以做到的，但建议批量插入，目前框架我试了都是单条插入，这种建议 jdbc 自己写 sql，一次 10W 都行。另外 mysql 有几个参数配置，具体哪几个需要百度，可以把写入 IO 提升到 100MB/S

[byzf]

查了下自己的数据库, 不看峰值的话, 只有你说的 2%-3%的数据量, 我用的腾讯云 1 核 1G 内存的数据库, cpu 峰值 4%左右.

所以我觉得你这个数据量扩大个十倍, 应该也不需要系统优化, 换个 4 核的机器就够了.

[byzf]

@594duck 我觉得可以开索引... select 超过千条自动变成全表扫描是啥机制, 该走索引的还是走索引啊.

[reliefe]

-> 有一个业务，如果用户开启了就需要每分钟给他生成一条数据。所以 10 万是我看实际用户量的预估值。
-> @MintZX 还真的是 24 小时，需求内容就是这样。不过当然了，最后每个人保留后 100 条数据，数据可以清理

这种业务通常不那么重要，数据不一定非要落库。落库也可以，当个备份。

直接用 Redis 的 list，还能保证每人 100 条数据。

[hurrytospring]

这不是 iot 的常见场景吗，，不过别人都用的时序数据库。。

[love]

每个用户只用前 100 条？那还为啥要用 mysql ？全放内存就行

[947211232]

偏题了，楼主问得不好，应该说出应用场景，全楼被楼主带歪

[594duck]

@byzf 索引 开了会严重影响写效率。

MYSQL 至少在 5.x 版本的时候索引扫描一旦超过好像 1000 个就会转为全表扫描。这个坑 之酸爽。

[9684xtpa]

那个我来说两句

其实看楼主的描述，这个场景并不清楚
首先，DB 写入 1 分钟 10W 问题不大
然后，每个用户就取 100 条，那么 100 条前面的数据怎么处理
其次，还有 update 的场景又是啥

比如说，数据不需要完全的持久化，历史数据不会再使用，我走 redis 的 zset 就能搞定

再比如说，更新计数的场景，那么你先做一下聚合，然后再去更新，这样就可以降低 DB 的写

如果是全量数据都会用到的话，估计就得做分库分表了


总而言之，在楼主一个模棱两可的需求面前，你是没法给出合适的方案的

[zh1997]

楼主是什么场景， 数据量大， 还频繁 update ？
特别想说这个需求不要接

[2kCS5c0b0ITXE5k2]

最后每个人保留后 100 条数据，数据可以清理 那为什么一定要入库呢...

[csl1995]

@594duck

应该不是固定超过 1000 个转全表扫描，而是因为索引扫描成本比全表扫描高。
查询优化器根据成本计算选择执行计划的时候，如果你不走索引覆盖或者聚簇索引，需要获取的数据比较多，每次获取都需要根据指针再去做磁盘找数据，导致索引扫描的 cost 比全表扫描的 cost 还高。所以查询优化器选用全表扫描作为执行计划。
你可以用 optimizer trace 对比不同方案的 cost 计算。

[E0]

X Y problem?

[lenmore]

我理解这应该是定时任务触发来插入数据的，
那么可以考虑将 10w 个用户的写入作为一个或几个事务来做，也还可以合并这些 insert，比如一次插入 1w 行，这样效率就高很多了。

[zh1997]

如果只是 2 分钟内才修改的话，可以用 mysql 存最近 5 分钟的数据，定时归档到时序数据库

[kalista]

我理解这么频繁更新的数据，可以先放内存里面吧

[cloverzrg2]

有点好奇为什么这么多数据，会不会 1000 条数据里面只有几条是有价值的

[realpg]

@goodspb #19
一分钟十万条，多久删除记录？
感觉你这个数据库存储文件很快就会过 PB 的样子……

[realpg]

感觉什么业务模型都在楼主脑子里 然后有价值的数据和业务模型啥也没说……

[goodspb]

@realpg #61 多久之后删除还没思考好，反正只保留当前时间往后推算的 100 条数据，其余的可以删除

[realpg]

@goodspb #63
仔细看了下你的全部回复并整理到一起
你这需求难度真心不大
只要数据来源层和写入数据库这两方面都可控


比如用时序数据库，改造查询报表部分，反正就 100 条数据，不要依赖 SQL 语法，直接取出全部数据用算法去计算汇总

用 KV 替代时序数据库也可以，同样改造报表部分

如果保留 MYSQL 本身，可以改变入库操作，采用 UPDATE 式入库替换掉 INSERT 入库（类似用逻辑实现 MYSQL 模拟时序库，代码方便改用代码，代码不方便处理用触发器） ，然后存储设备用高 IOPS 的 NVME 盘，或者干脆引擎用 TiDB，三副本全用 NVME 盘

方案多得是，但是更细节的东西，以及生产数据，就不是随便上网问一嘴就可以白给的了

[Samuelcc]

应该是带 compaction 的 lsm tree 系的数据存储场景

[jones2000]

"有一个业务，如果用户开启了就需要每分钟给他生成一条数据。所以 10 万是我看实际用户量的预估值"

数据应该是每个客户定制生成的吧，客户和客户之间的数据应该不是共享的吧。 这种需求一般就不写库，根据每个客户的 ID 对应一个缓存文件（你这个 1 分钟 1 条数据，1 天也就 60*24 很少的数据量）， 直接写缓存文件， 取的时候根据用户 ID 取对应文件。 写数据 /更新业务直接微服务上容器，自动扩展，如果 1 个容器可以同时更新 5W 个用户数据，后续用户大了，动态部署这个微服务。文件直接用企业级的 NAS 也可以用云运营商的云盘文件也可以。这个开发完，直接加钱买机器就可以扩容。

[my3157]

有个东西叫时序数据库, 对于这种有明显时间特征的数据很适用, 不过时序数据库一般对 update 不友好, 可以先入 MQ , 处理完成后入时序数据库

[mckelvin]

搜索一下「时序数据库」和「列存数据库」

[fly9i]

mysql 分库分表不行吗

[snoopygao]

防火墙日志，目前存在了 MYSQL 中，一秒 1000 条左右，1C1G2T 虚拟机

[keepeye]

具体要看啥数据啊 日志型的分表完全没压力啊

[594duck]

@snoopygao 你敢查么。

存谁都敢，查一下试试看，特别刺激。你这应用和 zabbix 一样。zabbix 就是出了名的写优化读等死。

[594duck]

@csl1995 #55 老哥的回答真的懂行。这其实是有个算法，我们粗暴的归在如果超出 1000 基本就走全表了。 所以他这程序 update 一次那真叫刺激。

[realpg]

@594duck #73
你这个归纳显然是有问题的
不信你弄个百万行的表试试……

[realpg]

我这里最暴躁的一台 MYSQL，单机……

https://imgur.com/Zu24WLu

[mreasonyang]

这个 TPS 主库换好机器单库也基本能抗住了，就是稳定性比较差。而分库分表之后就应该没压力了呀

[woscaizi]

优化业务吧，从单个用户的数据入手；

[594duck]

@realpg 写,和 update 最多的表有多宽。目前库有多大。什么配置

[realpg]

@594duck #78

自己拼的垃圾服务器 从我机房堆里翻出来的

读写密集的数据库是全 int 表，不存在字符串，字段数极多

主要业务场景是 INSERT 进来，受限于数据不是本机软件生成，一个 INSERT 只有一条记录一次 commit，所以写查询比 delete 查询数量多，delete 都是按有效期一次删一片

基本 insert 进来的数据有效期是一天，大量分表，没分库。分表依据是基于应用逻辑的，而不是基于数据的算法分表，所
以表行数不均匀，比较少的表几十万行，比较多的 800 万行左右。

数据每天增加的量大致差不多，insert 时间不分散到每个小时，而是按一定逻辑分散。

insert 进来的数据要保存 72 天，之后删除，删旧的，保持数据库内基本保存 73 天的有效数据

删除是我自己写的分布式删除脚本，分散到一天 24 小时除了备份数据时间段的每时每刻

update 是表内数据的主要逻辑，基本主要操作都是 update 一条表记录为一个临时状态值占用，而不是用事务行锁。

[realpg]

存储配置是 数据盘 8 NVME 盘 RAID10 高性能的数据中心级 NVME
机器是洋垃圾二代 E5 双路，配 1.5TB RAM

[jimrok]

两个简单的方案
1. 多个数据库来分担写入
2. 写到 csv 文件里，再用 LOAD DATA LOCAL INFILE 语句把数据载入到表中。

[594duck]

@realpg

过份的谦虚是对人的藐视。

8 NVME 盘 RAID10 高性能的数据中心级 NVME，二代 E5 双路，配 1.5TB RAM 。这配置叫垃圾？

我别的不说你这配置，按照 DELL R730 计价，少说 10 万一台。按照阿里云计价。没有。

从数据层面 来看也就是说你的表最多的一个也就 800 万行。在你的超强配置下并且充分 的利用了优化，撑住。

[594duck]

@realpg 另外我很好奇，什么样公司的机房的垃圾库存有如此牛的机器当垃圾存在。而且既然当垃圾了还能够当生产系统用

同时我也很好奇你的备份方案是什么样的。

大数据团队是怎么和你对接数据的。你这么频繁的加库加表，大数据团队也撑的住折腾。

请教请教。

[privil]

@594duck #83 二代 E5 双路，你看看 E5 现在出到几代，就知道是不是洋垃圾了……

[privil]

@realpg #80 1.5TB RAM 开机自检得多久……

[MintZX]

@goodspb 我看了一圈下来，只需要前 100 的话，不要用什么 mysql 了，直接 redis，每条数据给一个 2 小时 expire time 完事儿。随便 update 。整体来说你这个降级也就在一两千万条数据，redis 轻轻松松。你这个典型的 in memoy db 的需求用 mysql 不是给自己找不痛快。

[nifengwobei]

这里是需要拆解需求吗 后面的 2 分钟后更新增加了算法的时间复杂度(菜逼理解大佬别骂我)

[raaaaaar]

怎么感觉刚刚才在知乎上看到这个问题

[594duck]

@privil 对数据库来说多核 没有高主频有用。所以哪怕是二代 E5 高主频带来的优势更强。

另外我还是很好奇你怎么做备份怎么和大数据团队对接，以二代 E5 的寿命来看，你简直是在玩火。如果一理有问题挂了，你又找不出同样的洋垃圾，你带给公司的灾难身为前运维总监我是不敢想象的。

[gemini767]

这用啥数据库 直接打 log 好了
这啥需求 不能在查看时候再算过去 100 条记录？

[realpg]

@privil #85
如果不按 ESC 中断自检层 MEMTEST 的话，要很久很久很久很久
如果按 ESC 中断自检层 MEMTEST，做普通内存自检，大概 7-30 分钟，时间比较不定，可能是某些内存有响应不好的问题
这机器基本不重启的……


@594duck #83
公司是数据中心啊
都是按需买的
机房里扣除形象项目 政府项目啥的 90%是垃圾服务器在跑应用……


@594duck #83
大数据团队？不存在的。这只是我的一个自己的小私活而已。

备份方案？应用层备份。每时每刻分布式备份，以及后半夜备份。因为应用特性导致非工作时间的直接操作性业务压力极低极低，不是关闭服务，是没啥人用了，加班的以及圈子开源部分测试 /研发团队在实际使用。后半夜有 2 小时，所有分布式任务全部停止，只保留对零星用户的服务，同时 MYSQLDUMP

@594duck #89
找不出洋垃圾？我机房现在 E5 二代 /一代的服务器大概有六千多台，每个月还新采购 100 台左右

腾讯什么的 CDN 转包那边甚至全是 1366 平台居多，2019 年 6 月份以后新增加节点才大肆上 2011 的机器

1366 的机器而论，大部分出厂日期是 2009-2010 年，我这边 2016-2017 左右采垃圾然后用于生产环境，这批机器，基本到 2020 年左右才开始逐渐影响应用的故障率稍微多一点，所以开始演进往 2011 平台走，现在新采购以 2011 为主，偶尔赶上大批量极其便宜的 1366 还会搞一些，反正自己应用很多用得上的地方

至于你说的寿命，你对硬件寿命真的，一无所知……而且你对捡垃圾这个行业也一无所知。
用 E5 二代平台的原因，主要是内存便宜。1.5TB 是这一代的内存极限，低于这个的也满地，因为廉价内存而已。
DELL 的十三代平台，垃圾渠道已经开始流通了，最大的阻碍是 D4REG 和 D4LRDIMM 内存太贵而已


比如吧 你现在天天看的国内大视频网站，他们最大的分发是廉价采购的多重转包的资源回收，这部分，基本全是在用 1366 的机器在跑（因为成本低）。


比如，我现在机房有一个小项目，某知名视频站的大概 80Gbps 的分发，16 个机柜，每个机柜 12 台服务器，共计 192 台服务器。
因为这个项目是最近谈的，直接从备用机里提了 192 台 R720xd，客户对 CPU 和内存要求不高，所以上的 2640v2 双路，64G/128G 内存，每台机器 12 块 2TB 垃圾 SSD+魔改 H710 为 HBA 。没几个成本。


如果这个项目要是 2018 年接的，那么基本上当时就是配 R510/C2100 这种机器+双路 L5640+48GRAM+2TB*10 了

[594duck]

@realpg 我大概明白你是做什么的了
主库这么大没有从库，靠 DUMP 备份，根本不谈业务 SLA 了。

另外根据自己使用经验，DELL R 系列服务器在 3 年后的故障率极高 5 年后主板故障率也极高，你跑的业务是 CDN 7x24 小时高负载，2009 年的服务器你说要到你说要到 2020 年才出现 高故障率.以我自身的经验是超出的。

有特例么，肯定有的，什么跑 7 年的 DELL 服务器我也见过。但是 DELL 自己认可也就 5 年，从 DELL 的续保策略上是可以看出来的。

另外我确实不捡垃圾，以我们正规业务来讲，SLA 99.94%是最低要求，捡垃圾带来的收益 根本没必要。所以我明白你的业务场景是 CDN，不需要稳定只需堆量和便宜。

这和企业业务场景一致么，和企业 SLA 业务场景一致么？

[realpg]

@594duck #92
不是 CDN 啊 啥场景 这些机器的 SLA 可用性都高于四个 9

而且，大量的业务可用性是用 HA 机制保障的

我不知道你经手管理过多少物理环境在标准内的大批量设备
硬件故障率，不含硬盘，远没有你想象的那么高

[awanganddong]

首先是基于用户的操作，那就可以基于用户 userid 进行分表操作。这期间可以起队列进行消费。mysql 只需要保存最近 5 分钟数据的话。可以在用户 userid 分表的情况，增加时间段的维度进行冷热数据的过渡。

这里边牵扯到数据部分归档的问题。

[594duck]

@realpg 这和主题无关，但是 dell 自己都说超过 5 年的服务器是非常不稳定的不推荐的。你竟然说 4 个 9 。

你知道 4 个 9 是什么概念的。

[zibber]

写还是用 mongodb 吧