解決POI大資料匯出Excel記憶體溢位、應用假死

怪咖_OOP發表於2017-09-18
大資料Excel記憶體溢位

最近公司一個06年統計專案在匯出Excel時造成應用伺服器記憶體溢位、假死現象；檢視程式碼發現問題一次查詢一整年的資料導致堆記憶體被撐爆（假死），隨後改用批量查詢往Excel中寫資料，同樣的問題又出現了！！！隨後在網上查閱了部分資料只是在POI大資料匯出API的基礎上寫的demo示例無任何參考價值...

解決記憶體溢位常用方法就是開啟GC日誌

{Heap before GC invocations=29 (full 14):
 par new generation   total 306688K, used 306687K [0x0000000080000000, 0x0000000094cc0000, 0x0000000094cc0000)
  eden space 272640K, 100% used [0x0000000080000000, 0x0000000090a40000, 0x0000000090a40000)
  from space 34048K,  99% used [0x0000000090a40000, 0x0000000092b7ffe0, 0x0000000092b80000)
  to   space 34048K,   0% used [0x0000000092b80000, 0x0000000092b80000, 0x0000000094cc0000)
 concurrent mark-sweep generation total 1756416K, used 1756415K [0x0000000094cc0000, 0x0000000100000000, 0x0000000100000000)
 Metaspace       used 43496K, capacity 44680K, committed 45056K, reserved 1089536K
  class space    used 5254K, capacity 5515K, committed 5632K, reserved 1048576K
2017-09-12T21:55:02.954+0800: 239.209: [Full GC (Allocation Failure) 2017-09-12T21:55:02.954+0800: 239.209: [CMS: 1756415K->1756415K(1756416K), 5.4136680 secs] 2063103K->1971243K(2063104K), [Metaspace: 43496K->43496K(1089536K)], 5.4138690 secs] [Times: user=5.41 sys=0.00, real=5.41 secs] 
Heap after GC invocations=30 (full 15):
 par new generation   total 306688K, used 214827K [0x0000000080000000, 0x0000000094cc0000, 0x0000000094cc0000)
  eden space 272640K,  78% used [0x0000000080000000, 0x000000008d1cacb0, 0x0000000090a40000)
  from space 34048K,   0% used [0x0000000090a40000, 0x0000000090a40000, 0x0000000092b80000)
  to   space 34048K,   0% used [0x0000000092b80000, 0x0000000092b80000, 0x0000000094cc0000)
 concurrent mark-sweep generation total 1756416K, used 1756415K [0x0000000094cc0000, 0x0000000100000000, 0x0000000100000000)
 Metaspace       used 43238K, capacity 44256K, committed 45056K, reserved 1089536K
  class space    used 5213K, capacity 5441K, committed 5632K, reserved 1048576K
}複製程式碼

主要資訊：
2017-09-12T21:55:02.954+0800: 239.209: [Full GC (Allocation Failure) 2017-09-12T21:55:02.954+0800: 239.209: [CMS: 1756415K->1756415K(1756416K), 5.4136680 secs] 2063103K->1971243K(2063104K), [Metaspace: 43496K->43496K(1089536K)], 5.4138690 secs] [Times: user=5.41 sys=0.00, real=5.41 secs]

通過檢視GC日誌發現堆空間、元空間不能被回收(物件強引用導致)

解決方法：

檢視業務程式碼：

SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000);
    for(int i=1;i<=pageCount;i++){
        int tableNum = i;
        int pageIndex = i;
        //分頁資料查詢
        List<Map<String, Object>> maps = dbFactory.getJdbcTemplate().queryForList(finalSql,(pageIndex-1)*pageSize,pageIndex*pageSize);
        SXSSFSheet sheet = sxssfWorkbook.createSheet("sheet"+tableNum);
        SXSSFRow sxssfRow = sheet.createRow(0);
        for(int a=0;a<titles.length;a++){
            sxssfRow.createCell(a).setCellValue(titles[a]);
        }
        for(int a=1;a<=maps.size();a++){
            SXSSFRow sxssfRow = sheet.createRow(a);
            Map<String,Object> data = maps.get(a-1);
            Set<String> keySet = data.keySet();
            Iterator<String> iterator = keySet.iterator();
            int cell = 0;
            while(iterator.hasNext()){
                String key = iterator.next();
                Object valueObject = data.get(key);
                SXSSFCell sxssfCell =  sxssfRow.createCell(cell);
                sxssfCell.setCellValue(valueObject==null?"":valueObject.toString()); 
                cell++;
            }
        }
        //資料清理
        maps.clear();
        //設定空引用
        maps = null;
    }

    FileOutputStream fos = new FileOutputStream(tempPath+fileName);
    sxssfWorkbook.write(fos);
    fos.close();
    sxssfWorkbook.dispose();複製程式碼

程式碼中資料清理、設定空引用都做了，為什麼還是不能被回收呢？？？

通過JVM自帶檢測工具jmap檢視活躍物件

jmap使用說明：論持久戰之Java效能監控工具（jmap）

image.png
image.png

重大發現原來是org.apache.poi.xssf.streaming.SXSSFCell、org.apache.poi.xssf.streaming.SXSSFCell$PlainStringValue、org.apache.poi.xssf.streaming.SXSSFRow這三個鬼把記憶體佔完了

優化程式碼

SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000);
    SXSSFCell sxssfCell = null;
    SXSSFRow sxssfRow = null;
    for(int i=1;i<=pageCount;i++){
        int tableNum = i;
        int pageIndex = i;
        List<Map<String, Object>> maps = dbFactory.getJdbcTemplate().queryForList(finalSql,(pageIndex-1)*pageSize,pageIndex*pageSize);
        SXSSFSheet sheet = sxssfWorkbook.createSheet("sheet"+tableNum);
        sxssfRow = sheet.createRow(0);
        for(int a=0;a<titles.length;a++){
            sxssfRow.createCell(a).setCellValue(titles[a]);
        }
        for(int a=1;a<=maps.size();a++){
            sxssfRow = sheet.createRow(a);
            Map<String,Object> data = maps.get(a-1);
            Set<String> keySet = data.keySet();
            Iterator<String> iterator = keySet.iterator();
            int cell = 0;
            while(iterator.hasNext()){
                String key = iterator.next();
                Object valueObject = data.get(key);
                sxssfCell =  sxssfRow.createCell(cell);
                sxssfCell.setCellValue(valueObject==null?"":valueObject.toString()); 
                cell++;
            }
            //map資料清理
            data.clear();
        }
        //資料清理
        maps.clear();
        //設定空引用
        maps = null;
    }

    FileOutputStream fos = new FileOutputStream(tempPath+fileName);
    sxssfWorkbook.write(fos);
    fos.close();
    sxssfWorkbook.dispose();複製程式碼

程式SXSSFRow、SXSSFCell這兩個物件持有一個引用，每當新建立一個物件時候原來引用失效jvm會自動回收

以上屬於原創文章，轉載請註明作者@怪咖
QQ:208275451
Email:yangzhao_java@163.com

相關文章