• 数据库调优过程(一):SqlServer批量复制(bcp)[C#SqlBulkCopy]性能极低问题


    • 背景

      最近一段给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:19s

    records 500000
    batchsize 10000
    insert two tables(first table with 0ix,1pk)
    use times:first table use:22s,second table use:14s

    records 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

  • 相关阅读:
    js 时差转换 getTimezoneOffset()
    js 验证对象是否为数组
    mac下的一些mysql操作
    mac下mysql 1045 (28000): Access denied for user 'root'@'localhost' (using password:
    canvas 画布 文字描边
    background-size: contain 与cover的区别,以及ie78的兼容写法
    tortoisegit 常见错误disconnected no supported authentication methods available(server sent: publickey)
    windows 下git 的配置安装与使用
    Spring中Bean的生命周期
    HBase--DependentColumnFilter(参考例过滤器 )详解
  • 原文地址:https://www.cnblogs.com/yy3b2007com/p/5628741.html
Copyright © 2020-2023  润新知