Oracle和SQL Server之间的多线程数据传输 - 网络性能

Question

我们想要在Oracle 11g R2和SQL Server 2014之间快速传输一大批数据，容量超过20TB，拥有数千个表格和数十亿条记录（这是一个为期五年的数据仓库）。

由于我们需要传输的表格和分区数量巨大，约为40k个表格和分区，SSIS并不可行。我们拥有一些营销应用程序、活动管理器、挖掘模型等，它们运行在不同的架构中。其中一些每天会创建150个新表格，而“删除表格率”约为100个/天。这些架构正是我们无法使用SSIS的原因，因为我们需要传输它们。

因此，我们开发了一个C# 2013的应用程序，并使用了.Net 4.5.1，它是多线程的。每个线程读取Oracle中的一个表格/分区，在SQL Server中创建相同的表格/分区模式，然后继续从Oracle中选择数据，并批量插入到SQL Server中，最后创建所有未决的约束和索引。

我们面临的主要问题之一是传输速度。与SSIS的性能相比，在传输包含30个分区的1个事实表（每月事实表，每日分区，每个分区约有3000万条记录，60+列）时，我们发现该应用程序从未使用全网络速度，而SSIS使用了100%的速度（我们使用Attunity SSIS Connectors for Oracle...其中可能有一个传输速度优势，但我不确定），如果可以的话，我们希望改善这一点。

以下是负责写入的代码块。

//private static async Task saveDataBlock(IDataReader reader, string destinationTable, int batchSize)
private static void saveDataBlock(IDataReader reader, string destinationTable, int batchSize)
{
    //System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(getConnString(destinationCS));
    //System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(getConnString(destinationCS), System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity & System.Data.SqlClient.SqlBulkCopyOptions.KeepNulls & System.Data.SqlClient.SqlBulkCopyOptions.TableLock);
    using (SqlBulkCopy bc = new SqlBulkCopy(getConnString(destinationCS)))
    {
        bc.BulkCopyTimeout = 0;
        bc.DestinationTableName = destinationTable;
        bc.BatchSize = batchSize; //2500,5000,10000.. best so far, 5000
        //bc.BatchSize = 0;
        bc.NotifyAfter = batchSize;
        bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);
        //bc.EnableStreaming = true;            

        bc.ColumnMappings.Clear();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            bc.ColumnMappings.Add(reader.GetName(i), reader.GetName(i));
        }

        bc.WriteToServer(reader);
        //await bc.WriteToServerAsync(reader);
        //bc.Close();
    }

    //System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(getConnString(destinationCS), SqlBulkCopyOptions.UseInternalTransaction);            

}

有没有关于在SQL Server或者C#应用程序中的SQLBulkCopy对象可以测试或配置的任何建议（代码中注释的部分来自之前已测试过的选项）？

PS：以下是我们环境的一些信息...

Oracle服务器客户端上行带宽为2 Gbps，SQL Server下行带宽为1 Gbps，Oracle是一个32核系统，我们测试的SQL Server是一个16核Win Server 2012 R2系统... SSIS网络传输速度为1 Gbps全速，C#应用程序的网络传输速度约为70 Mbps，使用16个线程...

编辑以提供有关传输速度的更多信息：

我们应用程序测试的更多信息：

2个线程 = 15-25 Mbps

4个线程 = 30-40 Mbps

8个线程 = 60-65 Mbps

16个线程 = 65-70 Mbps

超过16个线程（每个系统内核的1个应用程序线程）会将性能降至30-50 Mbps。

我们的SAN非常适合高I/O计数的速度超过500 MB/s

我们最好的批处理大小时间是基于每批次2500-5000行的值获得的（使用8-16个线程，在5分钟内处理了大约1500万行）

现在我们的应用程序将数据从Oracle中的一个表/分区传输到SQL Server中的另一个表/分区...我们有一些非分区表，其中包含100多万行数据...对于这些表，我们测试了多个线程读取同一张表...我们成功地完成了读取过程，但在批量插入数据时失败。

每个线程对一个数字列执行MOD操作以读取同一张表。

select * from schema.table where MOD(NUMERIC_COLUMN, N) = 0 to N-1

N是我们运行的线程数... 我们尝试重新创建一些 SSIS 行为，以最大化 Oracle/SQL 中的线程使用和数据读取/写入。在 SSIS 中，我们可以在目标地设置每个线程的 LOCK TABLE 选项，并且它可以完美运行。但是，在我们的应用程序中执行此操作时，每个线程在插入期间都会锁定表，最终导致我们的并行设计变成了串行 :( （这不是问题的主要原因，但如果有人对此有建议，我们将不胜感激）

- figurettipy

---

出于好奇，你能帮我理解一下 SSIS不是一个选项，因为我们需要传输的表格和分区数量非常庞大。看起来它与你自己开发的系统相比处理速度还不错。 - billinkc

那你要创建多少个线程？同时运行许多线程会对性能造成很大的影响。请展示更多代码。 - T McKeown

@BradD 实际上我没有控制等待类型/时间。 - figurettipy

@billinkc，问题在于我们在不同的Oracle模式中有大约40K个表...因为一些来自市场部门的应用程序在我们的DWH（内部开发）上运行，我们的用户可以创建数据段，他们的段创建最终表，这大约是150个新表...其中一些带有索引...如果我们创建一个DTS任务，我们需要预先创建所有对象...创建连接，匹配列，并使用attunity驱动程序优化每个流...这对团队来说并不容易...我们还需要将我们的Oracle包和视图转换为SQL Server过程 - figurettipy

首先，我希望你知道它是SSIS，而不是DTS。其次，自从DTS时代以来，就可以通过编程方式创建包。你应该能够自动创建所需的包，并让SSIS完成所有工作。这就是它的用途。 - John Saunders

Answer 1

在1Gbps的链路上，您距离理论最大吞吐量并不远。1Gbps的最大吞吐量是125 MB/s，没有任何开销。实际上，您真正看到的是90 MB/s，如果您通过第三层交换机、路由器或防火墙进行数据包处理，吞吐量将会下降。您最好的选择是将服务器放在同一网络段，并在它们之间获得更高速度的链接。我怀疑当线程数增加时，吞吐量下降的原因是线程争夺对NIC的访问（或NIC由于大数据包大小而引入延迟），线程必须暂停执行（休眠）并重试。

我认为您的问题是吞吐量。尝试通过绑定两个1Gbps连接来获得更高速度的连接。或者，如果服务器彼此靠近，请使用交叉线缆直接连接它们（无需交换机），以查看传输速度是否增加。如果速度确实提高了，那么您就知道存在吞吐量问题。如果没有提高，至少您已经排除了一个可能性。

- Michael Humelsine