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

文/马非码

首先声明，我只是个程序员，不是专业的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小时没有合眼，或者只在工地上（下雨天到处都是泥巴，干了的话到时都是泥灰）眯两三个小时，然后继续干，连续这么一个星期！或者还要继续！

（编辑：云计算网_泰州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!