- 背景
最近一段给xx做项目,这边最头疼的事情就是数据库入库瓶颈问题。
- 环境
服务器环境:虚拟机,分配32CPU,磁盘1.4T,4T,5T,6T几台服务器不等同(转速都是7200r),内存64G。
- 排查步骤
排查一:数据库恢复模式为简单模式,数据库和tempdb的初始大小。数据库文件初始化大小100G,日志文件初始化大小50G,两个文件都是自动增长(按10%);tempdb初始化大小10G*4个文件,日志5G*4个文件,两个文件都是自动增长(按10%),分布在两个磁盘中(但看了这篇文章后,有点傻眼了。);
排查二:设置数据库占用最大内存为30G;
排查二:数据库表删掉所有索引,除了PK(OID bigint,Time datetime)分区使用了Time字段(看了这篇文章,为后边担忧);
排查三:系统windows server 2008(Vista内核),升级为windows server 2008 R2 SP1(WIN7内核);
排查四:批量入库一次批量入库的次数,目前还在调整中,把BatchSize设置为一个合适的值,是50W,还是200W呢?(BCP原理篇,推荐参数配置,设置多大合适?)
测试结果:
-- 300w batch insert two tables,batch size:1w ,with (first table with 5ix+pk,second table with 1ix+pk)
--server:ip etwork 8 minutes-- 300w batch insert two tables,batch size:300w,with (first table with 5ix+pk,second table with 1ix+pk)
--server:ip etwork 8 minutes-- 300w batch insert two tables,batch size:1000,with (first table with 0ix+pk,second table with 0ix+pk)
--server:local etwork 8 minutes-- 300w batch insert two tables,batch size:5000,with (first table with 0ix+pk,second table with 0ix+pk)
--server:local etwork 4.5 minutes-- 300w batch insert two tables,batch size:1w,with (first table with 0ix+pk,second table with 0ix+pk)
--server:local etwork 4 minutes
-- 300w batch insert two tables,batch size:5w,with (first table with 0ix+pk,second table with 0ix+pk)
--server:local etwork 5 minutes
-- 300w batch insert two tables,batch size:10w,with (first table with 0ix+pk,second table with 0ix+pk)
--server:local etwork 4.5 minutes
-- 300w batch insert two tables,batch size:50w,with (first table with 0ix+pk,second table with 0ix+pk)
--server:local etwork 5 minutes
测试2:
records 500000
batchsize 5000
insert two tables(first table with 0ix,1pk)
use times:first table use:29s,second table use:19srecords 500000
batchsize 10000
insert two tables(first table with 0ix,1pk)
use times:first table use:22s,second table use:14srecords 500000
batchsize 50000
insert two tables(first table with 0ix,1pk)
use times:first table use: 30s,second table use:18s
排查五:数据库连接字符串把172.21.xxx.xxxwork,修改为(local)work或者.work,是否可以采用共享内存的方式来建立连接呢?目前还未测试。(实战篇,微软文档篇)
连接字符串参数: Data Source - 或 - Server - 或 - Address - 或 - Addr - 或 - Network Address |
默认值: N/A |
要连接的 SQL Server 实例的名称或网络地址。可以在服务器名称之后指定端口号: server=tcp:servername, portnumber 指定本地实例时,始终使用 (local)。若要强制使用某个协议,请添加下列前缀之一: np:(local), tcp:(local), lpc:(local) |
连接字符串参数: Network Library - 或 - Net |
默认值: 'dbmssocn' |
用于建立与 SQL Server 实例的连接的网络库。支持的值包括 dbnmpntw(命名管道)、dbmsrpcn(多协议)、dbmsadsn (Apple Talk)、dbmsgnet (VIA)、dbmslpcn(共享内存)及 dbmsspxn (IPX/SPX) 和 dbmssocn (TCP/IP)。 相应的网络 DLL 必须安装在要连接的系统上。如果不指定网络而使用一个本地服务器(比如“.”或“(local)”),则使用共享内存。 |
排查六:SqlBulkCopy参数SqlBulkCopyOptions设置
1. 有标识列的表
1. 1 SqlBulkCopyOptions.KeepIdentity 必须设置!否则会出现复制过去的数据产生标识列发现变化的情况!
1.2 如果原表的标识列即为主键, 那按1.1 的设置已足够。 如果原表无主键, 那在复制之前必须先清空原表(truncate table), 否则会出现多个相同的标识值的列!
2. 为NULL值的列
2.1 SqlBulkCopyOptions.KeepNulls 必须设置!否则会出现源数据的字段为NULL时, 复制过去却成了默认值!
其它几个选项的说明与分析:
Default 对所有选项使用默认值。
KeepIdentity 保留源标识值。如果未指定,则由目标分配标识值。
CheckConstraints 请在插入数据的同时检查约束。默认情况下,不检查约束。
TableLock 在批量复制操作期间获取批量更新锁。如果未指定,则使用行锁。
KeepNulls 保留目标表中的空值,而不管默认值的设置如何。如果未指定,则空值将由默认值替换(如果适用)。
FireTriggers 指定后,会导致服务器为插入到数据库中的行激发插入触发器。 默认情况下, 是不激发触发器的……
UseInternalTransaction 如果已指定,则每一批批量复制操作将在事务中发生。 在一个事务中执行,要么都成功,要么都不成功。
Default 就没有什么好说的了, 不要
KeepIdentity 和 KeepNulls 上面已有了, 不再分析。
CheckConstraints 不需要, 因为是现成的数据, 既然已在DB中, 必然是通过了约束检查的。
TableLock 不需要, 因为复制时两个库都需要处于单连接状态, 不可能有干扰。
FireTriggers 一般就不需要了吧, 毕竟只是复制数据, 而且是现成的数据……
UseInternalTransaction 关系也不大, 反正复制失败会记录到自定义的日志, 失败了也知道, 重来一次就可以了。
排查七、表是否有trigger,check等。
参考资料:http://stackoverflow.com/questions/15526797/sqlbulkcopy-performance
Prerequisites for Minimal Logging in Bulk Import:https://msdn.microsoft.com/en-us/library/ms190422(v=sql.105).aspx
Optimizing Bulk Import Performance:https://msdn.microsoft.com/en-us/library/ms190421(v=sql.105).aspx
- 别人给的建议
ETL来处理:
针对这个解决方案,搜索了后才知道微软的SSIS中是支持ETL的(实战1、实战2篇,微软文档篇)。具体需要测试才知道结果。
内存处理:
就是把未能处理的数据放到内存中,使用Redis或者memcached来存储,之后把这样的数据源排队性的存储到sqlsever 2008 R2数据库中,该方案可行性还需要测试,到底需要多大的设备,一个上边同样配置的虚拟机能存储多少条记录会内存满负载,这是该方案是否成立的根本问题,问题是我们需要的是数据量在几百亿量级的数据,这样的情况需要测试才会有结论。
另外,看到SqlServer2014(SQL 2014新功能介绍系列1 – 内存中 OLTP (In-Memory OLTP))也做了比较大的调整,基本上支持内存存储,可以异步快速存储到内存中,该方案以目前设备来说,恐怕行不通。
其他,后来搜索过程中学习到了怎么实现一个复制的,复制的场景的应用模式(实战篇)。
用Oracle来替换SqlServer:
该方案我也早想去这么去做,回去就给公司建议,不过公司一定会PASS的。。。
参考资料:
DBA博客:http://www.cnblogs.com/CareySon/archive/2012/05/08/2489748.html
DBA问题查找经验总结:http://blog.csdn.net/yynetsdk/article/details/6749529
SqlBulkCopy 实现原汁原味复制的注意事项:http://blog.csdn.net/yenange/article/details/35837247