首页 > 语言 > 关键词 > SQLServer最新资讯 > 正文

我如何在SQLServer上每天处理四亿三千万记录

2015-05-13 14:54 · 稿源:博客园

文/马非码

首先声明,我只是个程序员,不是专业的DBA,以下这篇文章是从一个问题的解决过程去写的,而不是一开始就给大家一个正确的结果,如果文中有不对的地方,请各位数据库大牛给予指正,以便我能够更好的处理此次业务。

项目背景

这是给某数据中心做的一个项目,项目难度之大令人发指,这个项目真正的让我感觉到了,商场如战场,而我只是其中的一个小兵,太多的战术,太多的高层之间的较量,太多的内幕了。具体这个项目的情况,我有空再写相关的博文出来。

这个项目是要求做环境监控,我们暂且把受监控的设备称为采集设备,采集设备的属性称为监控指标。项目要求:系统支持不少于10w个监控指标,每个监控指标的数据更新不大于20秒,存储延迟不超过120秒。那么,我们可以通过简单的计算得出较理想的状态——要存储的数据为:每分钟30w,每个小时1800w,也就是每天4亿3千两百万。而实际,数据量会比这个大5%左右。(实际上大部分是信息垃圾,可以通过数据压缩进行处理的,但是别人就是要搞你,能咋办)

上面是项目要求的指标,我想很多有不少大数据处理经验的同学都会呲之以鼻,就这么点?嗯,我也看了很多大数据处理的东西,但是之前没处理过,看别人是头头是道,什么分布式,什么读写分离,看起来确实很容易解决。但是,问题没这么简单,上面我说了,这是一个非常恶劣的项目,是一个行业恶性竞争典型的项目。

没有更多的服务器,而是这个服务器除了搭配数据库、集中采集器(就是数据解析、告警、存储的程序),还要支持30w点的北向接口(SNMP),在程序没有优化之前CPU常年占用80%以上。因为项目要求要使用双机热备,为了省事,减少不必要的麻烦,我们把相关的服务放在一起,以便能够充分利用HA的特性(外部购买的HA系统)

系统数据正确性要求极其变态,要求从底层采集系统到最上层的监控系统,一条数据都不能差

我们的系统架构如下,可以看到,其中数据库压力非常之大,尤其在LevelA节点:

硬件配置如下:

CPU:英特尔® 至强® 处理器 E5-2609 (4核, 2.40GHz, 10MB, 6.4 GT/s)

内存:4GB (2x2GB) DDR3 RDIMM Memory, 1333MHz,ECC

硬盘:500GB 7200 RPM 3.5'' SATA3 硬盘,Raid5.

数据库版本

采用的是SQLServer2012标准版,HP提供的正版软件,缺少很多企业版的NB功能。

写入瓶颈

首先遇到的第一个拦路虎就是,我们发现现有的程序下,SQLServer根本处理不了这么多的数据量,具体情况是怎样的呢?

我们的存储结构

一般为了存储大量的历史数据,我们都会进行一个物理的分表,否则每天上百万条的记录,一年下来就是几亿条。因此,原来我们的表结构是这样的:

CREATE TABLE [dbo].[His20140822](
	[No] [bigint] IDENTITY(1,1) NOT NULL,
	[Dtime] [datetime] NOT NULL,
	[MgrObjId] [varchar](36) NOT NULL,
	[Id] [varchar](50) NOT NULL,
	[Value] [varchar](50) NOT NULL,
 CONSTRAINT [PK_His20140822] PRIMARY KEY CLUSTERED 
(
	[No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

No作为唯一的标识、采集设备Id(Guid)、监控指标Id(varchar(50))、记录时间、记录值。并以采集设备Id和监控指标Id作为索引,以便快速查找。

批量写入

写入当时是用BulKCopy,没错,就是它,号称写入百万条记录都是秒级的

    public static int BatchInert(string connectionString, string desTable, DataTable dt, int batchSize = 500)
    {
        using (var sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)
        {
            BulkCopyTimeout = 300,
            NotifyAfter = dt.Rows.Count,
            BatchSize = batchSize,
            DestinationTableName = desTable
        })
        {
            foreach (DataColumn column in dt.Columns)
                sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
            sbc.WriteToServer(dt);
        }

        return dt.Rows.Count;
    }

存在什么问题?

上面的架构,在每天4千万的数据都是OK的。但是,调整为上述背景下的配置时,集中监控程序就内存溢出了,分析得知,接收的太多数据,放在了内存中,但是没有来得及写入到数据库中,最终导致了生成的数据大于消费的数据,导致内存溢出,程序无法工作。

瓶颈到底在哪里?

是因为RAID磁盘的问题?是数据结构的问题?是硬件的问题?是SQLServer版本的问题?是没有分区表的问题?还是程序的问题?

当时时间只有一个星期,一个星期搞不好,项目监管就要我们滚蛋了,于是,有了连续工作48小时的壮举,有了到处打电话求人的抓鸡……

但是,这个时候需要的是冷静,再冷静……SQLServer版本?硬件?目前都不大可能换的。RAID磁盘阵列,应该不是。那么到底是什么,真TM的冷静不下来。

大家可能体会不到现场那种紧张的气氛,其实过了这么久,我自己也都很难再回到那种情境。但是可以这么说,或许我们现在有了各种方法,或者处于局外人我们有更多思考,但是当一个项目压迫你快到放弃的时候,你那时的想法、考虑在现场环境因素的制约下,都可能出现重大的偏差。有可能让你快速的思考,也有可能思维停滞。有些同事在这种高压的环境下,甚至出现了更多的低级错误,思维已经完全乱了,效率更低了……36小时没有合眼,或者只在工地上(下雨天到处都是泥巴,干了的话到时都是泥灰)眯两三个小时,然后继续干,连续这么一个星期!或者还要继续!

很多人给了很多想法,但是好像有用,又好像没用。等等,为什么是“好像有用,又好像没用”?我隐隐约约中,好像抓住了一丝方向,到底是什么?对了,验证,我们现在是跑在现场环境下,之前没有问题,不代表现在的压力下没有问题,要在一个大型系统中分析这么个小功能,影响太大了,我们应该分解它。是的,是“单元测试”,就是单个方法的测试,我们需要验证每个函数,每个独立的步骤到底耗时在哪里?

逐步测试验证系统瓶颈

修改BulkCopy的参数

首先,我想到的是,修噶BulkCopy的各项参数,BulkCopyTimeout、BatchSize,不断的测试调整,结果总是在某个范围波动,实际并没有影响。或许会影响一些CPU计数,但是远远没有达到我的期望,写入的速度还是在5秒1w~2w波动,远远达不到要求20秒内要写20w的记录。

按采集设备存储

是的,上述结构按每个指标每个值为一条记录,是不是太多的浪费?那么按采集设备+采集时间作为一条记录是否可行?问题是,怎么解决不同采集设备属性不一样的问题?这时,一个同事发挥才能了,监控指标+监控值可以按XML格式存储。哇,还能这样?查询呢,可以用for XML这种形式。

于是有了这种结构:No、MgrObjId、Dtime、XMLData

结果验证,比上面的稍微好点,但是不是太明显。

数据表分区???

那个时候还没有学会这个技能,看了下网上的文章,好像挺复杂的,时间不多了,不敢尝试。

停止其他程序

我知道这个肯定是不行的,因为软件、硬件的架构暂时没法修改。但是我希望验证是不是这些因素影响的。结果发现,提示确实明显,但是还是没有达到要求。

难道是SQLServer的瓶颈?

没辙了,难道这就是SQLServer的瓶颈?上网查了下相关的资料,可能是IO的瓶颈,尼玛,还能怎么办,要升级服务器,要更换数据库了吗,但是,项目方给吗?

等等,好像还有个东西,索引,对索引!索引的存在会影响插入、更新

去掉索引

是的,去掉索引之后查询肯定慢,但是我必须先验证去掉索引是否会加快写入。如果果断把MgrObjId和Id两个字段的索引去掉。

运行,奇迹出现了,每次写入10w条记录,在7~9秒内完全可以写入,这样就达到了系统的要求。

查询怎么解决?

一个表一天要4亿多的记录,这是不可能查询的,在没有索引的情况下。怎么办!?我又想到了我们的老办法,物理分表。是的,原来我们按天分表,那么我们现在按小时分表。那么24个表,每个表只需存储1800w条记录左右。

然后查询,一个属性在一个小时或者几个小时的历史记录。结果是:慢!慢!!慢!!!去掉索引的情况下查询1000多万的记录根本是不可想象的。还能怎么办?

继续分表,我想到了,我们还可以按底层的采集器继续分表,因为采集设备在不同的采集器中是不同的,那么我们查询历史曲线时,只有查单个指标的历史曲线,那么这样就可以分散在不同的表中了。

说干就干,结果,通过按10个采集嵌入式并按24小时分表,每天生成240张表(历史表名类似这样:His_001_2014112615),终于把一天写入4亿多条记录并支持简单的查询这个问题给解决掉了!!!

查询优化

在上述问题解决之后,这个项目的难点已经解决了一半,项目监管也不好意思过来找茬,不知道是出于什么样的战术安排吧。

过了很长一段时间,到现在快年底了,问题又来了,就是要拖死你让你在年底不能验收其他项目。

这次要求是这样的:因为上述是模拟10w个监控指标,而现在实际上线了,却只有5w个左右的设备。那么这个明显是不能达到标书要求的,不能验收。那么怎么办呢?这些聪明的人就想,既然监控指标减半,那么我们把时间也减半,不就达到了吗:就是说按现在5w的设备,那你要10s之内入库存储。我勒个去啊,按你这个逻辑,我们如果只有500个监控指标,岂不是要在0.1秒内入库?你不考虑下那些受监控设备的感想吗?

但是别人要玩你,你能怎么办?接招呗。结果把时间降到10秒之后,问题来了,大家仔细分析上面逻辑可以知道,分表是按采集器分的,现在采集器减少,但是数量增加了,发生什么事情呢,写入可以支持,但是,每张表的记录接近了400w,有些采集设备监控指标多的,要接近600w,怎么破?

于是技术相关人员开会讨论相关的举措。

在不加索引的情况下怎么优化查询?

有同事提出了,where子句的顺序,会影响查询的结果,因为按你刷选之后的结果再处理,可以先刷选出一部分数据,然后继续进行下一个条件的过滤。听起来好像很有道理,但是SQLServer查询分析器不会自动优化吗?原谅我是个小白,我也是感觉而已,感觉应该跟VS的编译器一样,应该会自动优化吧。

具体怎样,还是要用事实来说话:

结果同事修改了客户端之后,测试反馈,有较大的改善。我查看了代码:

难道真的有这么大的影响?等等,是不是忘记清空缓存,造成了假象?

于是让同事执行下述语句以便得出更多的信息:

--优化之前
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
select Dtime,Value from dbo.his20140825 WHERE  Dtime>='' AND Dtime<='' AND MgrObjId='' AND Id=''
SET STATISTICS IO OFF

--优化之后
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
select Dtime,Value from dbo.his20140825 WHERE MgrObjId='' AND Id='' AND Dtime>='' AND Dtime<=''
SET STATISTICS IO OFF

结果如下:

  • 相关推荐
  • 大家在看
  • 关于数据库的高可用高可靠,华为RDS有话说

    不知从何时起,“6·18”成为了“购物节”,这种全民狂欢式购物对电商行业是一个巨大挑战。 618 大促毫秒必争,短时间的高并发会给数据库带来巨大压力。面对暴涨的业务、暴增的订单,企业该如何确保数据库稳定性和可靠性呢?不要担心,华为云数据库的这份应对策略,时刻为您保驾护航。对于数据库来说,可用性和可靠性是永恒的话题。数据库管理员会按照业务的不同要求选择不同的策略保证系统正常运作,其中包含数据库本身内核提供的?

  • 蚂蚁集团自研数据库业务OceanBase独立 胡晓明任董事长

    今日,蚂蚁集团宣布将原有自研数据库产品OceanBase独立,成立由蚂蚁100%控股的数据库公司北京奥星贝斯科技,蚂蚁集团CEO胡晓明亲自担任董事长。

  • “投资数据库”百家号有什么作用?企业品牌必须知道的推广渠道

    备注:本文数据来自站长之家移动传媒平台,文章涉及的数据依托平台大数据计算所得,非百度官方数据,仅供参考。投资数据库是当前百家号中的普通号,目前账号百家号权重为2,综合排名位列779805名,财经分类排名位列18487名,领先了30.2%的百家号。 投资数据库百家号概况 投资数据库的简介为致力于最专业的投资数据服务,是一家主旨明确、领域专注的自媒体作者,截止目前为止他们已经在百家号上发布了超过23篇的游戏内容,最近该作?

  • 黑客泄露暗网托管服务商DH数据库 涉及七千多个帐户密码

    一名黑客日前在网上泄露了全球最大的免费暗网托管服务商Daniel's Hosting (DH)的数据库。当前泄露的数据是,今年 3 月 10 日黑客入侵DH获得的。当时,遭黑客攻击之后,近 7600 个暗网门户网站关闭,攻击者删除了托管门户网站的整个数据库。

  • 数据库业务OceanBase升格为独立公司 蚂蚁集团CEO胡晓明任董事长

    【TechWeb】6月8日消息,蚂蚁集团今天 对外宣布,将自研数据库产品OceanBase独立进行公司化运作,成立由蚂蚁100%控股的数据库公司北京奥星贝斯科技,并由蚂蚁集团CEO胡晓明亲自担任董事长。据了解,新公司将在今年内发布重大版本升级,并计划在未来三年内服务全球超过万家企业客户。对OceanBase独立公司化运作一事,蚂蚁集团表示,“科技是蚂蚁集团的基因,也是蚂蚁集团重要战略方向。经过十年的发展,OceanBase支撑了支付宝、网?

  • 阿里云发布六大新品:第七代ECS、PolarDB-X数据库等重磅登场

    【TechWeb】6月9日消息,在2020阿里云峰会上,阿里云宣布推出第七代ECS、POLARDB-X数据库、视觉智能开放平台等重磅新品。此外,阿里云还发布了新一代数据中台、混合云管理平台、云原生数据仓库等产品及解决方案,目前这些产品均已在阿里云官网上线。第七代ECS高主频实例基于阿里云自研的神龙服务器架构,第七代阿里云ECS的算力比上一代提升了160%,最大支持192个vCPU,吞吐能力提升了一倍,并在存储转发能力、延时、稳定性上有了?

  • 苹果iPhoneApp资源库怎么用 iOS14App资源库使用方法

    苹果在iOS14系统中加入了App资源库的功能,这个功能就是将相同类型的应用添加到同一个组合当中,便于用户快速找到对应的程序应用,这里我们来一起看下App资源库具体的使用方法。

  • 苹果App资源库怎么调整应用位置

    苹果App资源库里的应用程序要怎么调整应用APP的位置,需要进行怎样的操作才可以调整这些应用位置呢,以下我们来一起看下具体的位置调整方法。

  • 设计没灵感?使用免费的Billfish搭建个人灵感库试试

    设计师是一个“创意”至上的职业,很多设计公司的核心理念就是“无创意,不设计”,折射出的是设计师对创意,对创新的极致追求。然而,有很多设计师苦于自己的创意“贫乏”,在工作中苦苦的挣扎。我们经常在提倡创新,但其实内心最清楚,我们设计的很多作品只是在沿用以前的设计思维,特别是一些产量和产值为中心的设计公司。面对如此的市场环境,个人设计师如何快速的找到灵感,常用的方式便是借鉴于各大平台网站,久而久之很难形

  • 小影携手陀螺开启“库布齐沙漠星球节”

    专注移动端视频创作工具与服务的小影科技,宣布携手旅行平台陀螺,以及智云稳定器等品牌,共同开启“库布齐沙漠星球节”,小影将招募旅游类视频达人,深入内蒙古地区开启一场沙漠星空拍摄之旅。 近年来,旅游类达人在短视频领域的影响力逐年递增,根据QuestMobile发布的《泛娱乐用户行为新趋势》调研报告显示, 00 后、 90 后泛娱乐用户喜欢观看的短视频内容中,旅游类内容占比高达36.8%。 更值得关注的是,旅游类达人产出的视频?

  • SEO优化,4个独辟蹊径的策略!

    有的时候SEO工作就是一个数字游戏,每天看着数据的增长,不断的调整相关性的策略,但我们非常清楚,有的时候SEO优化会面临各种调整,特别是同行业之间的竞争。

  • 优速快递聚力品效,不断实践 不断优化

    品效驱动货量 货量牵引收益收益拉动盈利 加速赢取未来品效、品效、品效。打牢集团底盘,以持续、稳定的品效服务驱动网络盈利能力,是集团决胜千里的不二法宝。加之,当前疫情所带来的挑战与机遇的双面性影响,我们更要聚焦品质和效率,伺机而起,不放过任何一个逆势而起的机遇。基于此, 4 月 17 日集团在全网吹响了为期 120 天的“品效提升季——全网在行动”战役号角。即集团总部高管、省区高层管理人员集体深入分拨中心操作一线

  • 车轮"加油"频道,深入优化客户体验

    近年来,得益于我国经济高速发展,人们的生活水平稳定提升,汽车消费呈现井喷之势。众所周知,汽车的使用离不开汽油的支持,因此,汽车加油成为用车养车中的主要消费项目。而使消费者在加油中感到便利和实惠,在当今社会人们日益忙碌,压力越来越大的情况下,显得尤为重要。专注于全国汽车生活服务领域的移动互联网公司车轮互联想车主所想,解车主之难,在全国范围内推出了加油服务,"价格优惠"和"一键加油"是其服务的亮点。目前,

  • 苹果手机App资源库怎么排列

    苹果iPhone手机的App资源库是iOS14上新增的一项国内,这个功能是系统帮助用户自动排布已经安装在手机上的应用程序,但是用户自己可以排列应用程序吗,我们来一起看下。

  • 重磅!快手自建商品分销库 对接品牌不再是大主播特权

    据了解,快手联盟项目大概于今年 5 月下旬正式上线,但官方层面暂未开启大规模招商活动。据一位快手服务商表示,目前平台对入驻品牌商的审核要求还比较严格,通常是直接找品牌商谈合作,不考虑品牌代理商等中间商渠道。

  • 数据化运营,Smartbi助力商业银行打造“数据王国”

    在国内,银行的信息化一直是走在各个行业的前列,BI在银行也有着悠久和广泛的应用。BI可以辅助银行管理者和业务人员的经营决策,提高银行的科学管理水平,是银行信息化不可或缺的一部分,也是银行实现数字化转型的必要手段。银行的BI建设伴随着业务和技术的发展,是一个逐步摸索、不断成熟的过程。刚开始可能只建设某一个部门的一部分报表,在取得一定的成效后,再逐步推广到更多的部门,建设更多的主题,采用更多的可视化方式,支

  • 罗永浩直播数据在哪看 罗永浩直播数据统计

    罗永浩的直播带货数据一直都被很多网友们关注,尤其是他第一场直播的时候就已经获得了1.68亿的销售额,直播间观看人数更是达到了4800万,在如今的直播带货领域可以说是十分顶流。

  • 阅文优化新合同,网文产业的质变何时才能到来?

    距离阅文集团与网文作者的“合同纠纷”已隔一月,昨天,这一事件终于有了阶段性的进展。 6 月 3 日,阅文集团新管理层发布了针对旗下作者的“单本可选新合同”。阅文方面表示,该合同根据不同授权分为三类四种,取消单一格式合同,并对此前充满争议的旧合同进行了十余项修改,以长图的形式为网友们划出重点。 阅文新领导团队在这份合同中表现出来的尊重与诚意,获得了网文大神与业内专家的支持与认可。 与此同时,作为内容产业的?

  • 供不应求!精子库捐精主力是学生:成功可拿五千元补贴

    近年来,随着生活习惯和环境的改变,人类的精子质量也在下降,导致不育不孕的人群逐渐上升。在此背景下,精子库也应运而生。据媒体报道,四川省人类精子库筹建于2003年,于2012年11月在华西第

  • 云测数据:场景化AI数据 破解智能家居“伪智能”

    据IDC的数据研究报告显示,近5年内智能家居市场将以14.9%的复合增长率增长。得益于物联网、人工智能、5G等新型技术的飞速发展,智能家居在短短数年间就已经相继渡过了“自动化”、单品智能化”、“物联网+家居场景”三个阶段,进入了当前的“人工智能+家居场景”的“智能”阶段,开启了智能家居对人的思维和意识的学习与探索。现阶段的智能家居融合了IoT、人工智能、边缘计算等信息技术,以前影视片段中一声令下就能控制所有家具电器、?

  • 参与评论
文明上网理性发言,请遵守新闻评论服务协议