最近公司一个06年统计项目在导出Excel时造成应用服务器内存溢出、假死现象；查看代码发现问题一次查询一整年的数据导致堆内存被撑爆（<span style="color:red;">假死</span>），随后改用批量查询往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: <span style="color:red;">[CMS: 1756415K->1756415K(1756416K), 5.4136680 secs] 2063103K->1971243K(2063104K), [Metaspace: 43496K->43496K(1089536K)], 5.4138690 secs]</span> [Times: user=5.41 sys=0.00, real=5.41 secs]
通过查看GC日志发现<span style="color:#278bd2;">堆空间、元空间不能被回收</span>(对象强引用导致)
解决方法：
查看业务代码：
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
重大发现原来是<span style="color:#278bd2">org.apache.poi.xssf.streaming.SXSSFCell、org.apache.poi.xssf.streaming.SXSSFCell$PlainStringValue、org.apache.poi.xssf.streaming.SXSSFRow</span>这三个鬼把内存占完了
优化代码
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
