从得到SystemOutOfMemoryException而从大的文本文件导入到数据库出路 [英] A way out from getting SystemOutOfMemoryException while importing from large text file into database

问题描述

我们正在使用的ZyWALL从外部入侵保卫我们的服务器。它会产生巨大的尺寸每天的日志文件，通过GB，有时2 GB的。他们通常游戏包含超过1000万线。现在我的任务是写一个将导入这些行到Oracle数据库的应用程序。我在C＃中写它。什么我目前做的是：

1. 我逐行读取日志文件行。我不加载整个文件一次：

   
   
   

   使用（StreamReader的读者=新的StreamReader（C：\ZyWall.log））
   $ { b $ b在（（行= reader.ReadLine（））！= NULL）
   ......
   }




2. 每一行读我根据它的逗号分割线成几部分。

   
   
   

   字符串[] =行line.Split（新的char [] {'，'} ，10）;




3. 然后，我通过线阵迭代，创建一个预定义的DataTable对象的新行，并指定数组值的行中的列。然后，我将行添加到数据表。

在所有行读取到数据表我用OracleBulkCopy写在它的数据到一个物理表中具有相同结构的数据库。但事实是，我得到SystemOutOfMemoryException，因为我的行添加到DataTable对象，也就是第3步。如果我注释掉第3步，然后在任务管理器我看到应用程序占用的内存量稳定，这是一样的东西17000ķ但如果我去掉该步骤的内存使用量的增长，除非有足够的没有内存来分配。是否还有一种方法，我可以使用BulkCopy执行此，否则我将不得不手动做呢？我用BulkCopy becasue它比插入线逐一方式更快。<​​/ p>

解决方案

如果我理解正确的话，您正在加载中的每一行这变得如此之大，达到系统的内存限制表。结果
。如果是这样，你应该找到这个限制。 （例如百万行）。停止此点之前读线很好，写有OracleBulkCopy到目前为止装载的行。清理你的记忆，重新开始。因此，让我总结了伪一切。

  INT lineLimit = GetConfiguration（lineLimit）; 
 INT LINENUMBER = 0; 
的DataTable logZyWall = CreateLogTable（）;使用
 
（StreamReader的读者=新的StreamReader（C：\ZyWall.log））！
 {
，而（（行= reader.ReadLine（））= NULL ）
 {
的DataRow行= ParseThisLine（线）; 
 logZyWall.Rows.Add（行）; 
 LINENUMBER ++; 
如果（LINENUMBER == lineLimit）
 {
 WriteWithOracleBulkCopy（logZyWall）; 
 logZyWall = CreateLogTable（）; 
 LINENUMBER = 0; 
} 
} 
如果（LINENUMBER！= 0）WriteWithOracleBulkCopy（logZyWall）; 
} 
  

we're using ZyWall to guard our servers from external intrusions. It generates daily log files with huge sizes, over a GB, sometimes 2 GBs. They ususally contain more than 10 millions of lines. Now my task is to write an application that will import these lines into Oracle database. I'm writing it in C#. What I'm currently doing is:

1. I read the logfiles line by line. I do not load the whole file at once:

   using(StreamReader reader=new StreamReader("C:\ZyWall.log")) { while ((line=reader.ReadLine())!=null) ...... }

2. Every line read I split the line into parts according to the commas in it.

   string[] lines = line.Split(new Char[] { ',' }, 10);

3. Then I iterate through the lines array, create a new Row for a predefined DataTable object and assign array values to the columns in the row. Then I add the row to the datatable.

After all the lines are read to the datatable I use OracleBulkCopy to write the data in it to a physical table in the database with the same structure. But the thing is I get SystemOutOfMemoryException as I add the lines to the Datatable object, that is the 3rd step. If I comment out the 3rd step then in the task manager I see that the application consumes the stable amount of memory which is something like 17000 K but if I uncomment that step the memory usage grows unless there's no enough memory to allocate. Is there still a way I can use BulkCopy to perform this or will I have to do it manually? I used BulkCopy becasue it's way faster than inserting lines one by one.

解决方案

If I understand correctly, you are loading each line in a table that becomes so large as to reach the limits of the memory of your system.
If so, you should find this limit. (For example 1000000 lines). Stop reading the lines well before this point and write the rows loaded so far with OracleBulkCopy. Cleanup your memory and start again. So let me summarize everything with a pseudocode.

int lineLimit = GetConfiguration("lineLimit"); 
int lineNumber = 0;
DataTable logZyWall = CreateLogTable();

using(StreamReader reader=new StreamReader("C:\ZyWall.log")) 
{ 
    while ((line=reader.ReadLine())!=null)
    {
        DataRow row = ParseThisLine(line);
        logZyWall.Rows.Add(row);
        lineNumber++;
        if(lineNumber == lineLimit)
        {
            WriteWithOracleBulkCopy(logZyWall);
            logZyWall = CreateLogTable();
            lineNumber = 0;
        }
    }
    if(lineNumber != 0) WriteWithOracleBulkCopy(logZyWall);
}

这篇关于从得到SystemOutOfMemoryException而从大的文本文件导入到数据库出路的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！