I'm facing GC overhead limit exceeded. I'm trying to run a heavy query(with multiple joins) and store the result in report.
Below is the query:
select o.ID_KEY as orderId, o.ORDER_NUMBER as orderNumber, a.ADL_ACCOUNT as adlAccount, o.BASL_ACCOUNT as baslAccount,
a.FULL_NAME as customerName,
a.MOBILE_NO as mobileNumber,
CASE
WHEN dr_a.STATUS_CODE = '200'
THEN 'Y'
ELSE 'N'
END adlPaymentPosting,
CASE
WHEN dr_b.STATUS_CODE = '200'
THEN 'Y'
ELSE 'N'
END baslPaymentPosting,
CASE
WHEN o.ORDER_STATUS IN ('INSTALLATION_COMPLETED', 'COMPLETED')
THEN 'Y'
ELSE 'N'
END adlChargePosting,
CASE
WHEN o.ORDER_STATUS IN
('ATOM_SUCCESS', 'INSTALLATION_SCHEDULED', 'INSTALLATION_STARTED', 'INSTALLATION_COMPLETED',
'COMPLETED')
THEN 'Y'
ELSE 'N'
END baslChargePosting,
CASE
WHEN o.PAYMENT_STATUS = 'COMPLETED'
THEN 'Y'
ELSE 'N'
END paymentStatus,
o.ORDER_STATUS as orderStatus,
o.AMOUNT as paymentAmount,
accAdd.CITY as orderCity,
pd.RESPONSE_CODE as TransactionId,
o.SUBSCRIPTION_COST as adlAmount,
o.INSTALLATION_COST + o.HARDWARE_COST as baslAmount,
ad.FIRST_NAME || ' ' || ad.LAST_NAME as agentName,
ad.MOBILE_NO as agentMobileNumber,
pd.payment_gateway as PaymentGateway,
o.CREATED_ON as orderDate,
ct.CIRCLE_NAME as circle,
NVL(q111,0) as total111,
NVL(q112,0) as total112,
NVL(q113,0) as total113,
NVL(q111,0) + NVL(q112,0) + NVL(q113,0) as total,
(SELECT TYPE FROM ABC WHERE ID_KEY = (SELECT PLAN_ID FROM XYZ WHERE ORDER_ID = o.ID_KEY AND ROWNUM=1)) as type
from ORDERS o
INNER JOIN
ACCOUNT a
on o.ACCOUNT_ID = a.ID_KEY
LEFT OUTER JOIN
DCAP_RESPONSE dr_a
on o.ID_KEY = dr_a.ORDER_ID and dr_a.REQ_TYPE = 'ADL' AND dr_a.STATUS_CODE = (SELECT STATUS_CODE FROM DCAP_RESPONSE WHERE ID_KEY = (SELECT MAX(ID_KEY) FROM DCAP_RESPONSE) AND ID_KEY = o.ID_KEY)
LEFT OUTER JOIN
DCAP_RESPONSE dr_b
on o.ID_KEY = dr_b.ORDER_ID and dr_b.REQ_TYPE = 'BASL' AND dr_a.STATUS_CODE = (SELECT STATUS_CODE FROM DCAP_RESPONSE WHERE ID_KEY = (SELECT MAX(ID_KEY) FROM DCAP_RESPONSE) AND ID_KEY = o.ID_KEY)
LEFT OUTER JOIN
payment_details pd
on o.ID_KEY = pd.ORDER_ID
LEFT OUTER JOIN
ACCOUNT_ADDRESS accAdd
on o.SHIPPING_ADDRESS_ID = accAdd.ID_KEY
LEFT OUTER JOIN
agent_orders ao
on o.ID_KEY = ao.ORDER_ID
LEFT OUTER JOIN
agent_details ad
on ad.AGENT_ID = ao.AGENT_ID
LEFT OUTER JOIN
cities ct
on accAdd.CITY = ct.NAME
LEFT OUTER JOIN (
SELECT ORDER_ID,
MAX(CASE WHEN PRODUCT_ID =111 THEN QUANTITY END) AS q111,
MAX(CASE WHEN PRODUCT_ID =112 THEN QUANTITY END) AS q112,
MAX(CASE WHEN PRODUCT_ID =113 THEN QUANTITY END) AS q113
FROM ORDER_HARDWARE_ITEM
GROUP BY ORDER_ID) oh
on oh.ORDER_ID = o.ID_KEY
WHERE (o.LAST_UPDATED_ON BETWEEN (SELECT TRUNC((SYSDATE-1), 'month') from dual) AND (TRUNC(SYSDATE)-1/86400)) AND (o.ORDER_TYPE = 'BUY_PRODUCT')
Below is the code for storing in excel:
public <T> void writeToExcel(String fileName, List<T> data, String sheetName, boolean addExtraMethods) {
OutputStream fos = null;
XSSFWorkbook workbook = null;
FileInputStream inputStream = null;
try {
final String currentDate = simpleDateFormat.format(new Date());
File file = new File(currentDate + fileName + ".xlsx");
if (file.exists()) {
inputStream = new FileInputStream(file);
workbook = new XSSFWorkbook(inputStream);
} else
workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(sheetName);
try {
int rowCount = 0;
int columnCount = 0;
Row row = sheet.createRow(rowCount++);
CellStyle headerCellStyle = reportUtil.getHeaderStyle(sheet);
List<String> fieldNames = getFieldNamesForClass(data.get(0).getClass());
for (String header : fieldNames) {
Cell cell = row.createCell(columnCount);
cell.setCellStyle(headerCellStyle);
cell.setCellValue(header);
sheet.autoSizeColumn(columnCount++);
}
Class<? extends Object> classz = data.get(0).getClass();
for (T t : data) {
row = sheet.createRow(rowCount++);
columnCount = 0;
for (String fieldName : fieldNames) {
Cell rowCell = row.createCell(columnCount);
Method method = null;
try {
method = classz.getMethod("get" + capitalize(fieldName));
Object value = method.invoke(t, (Object[]) null);
if (value != null) {
if (value instanceof String) {
rowCell.setCellValue((String) value);
} else if (value instanceof Long) {
rowCell.setCellValue((Long) value);
} else if (value instanceof Integer) {
rowCell.setCellValue((Integer) value);
} else if (value instanceof Double) {
rowCell.setCellValue((Double) value);
}
}
columnCount++;
} catch (NoSuchMethodException nme) {
method = classz.getMethod("get" + fieldName);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
fos = new FileOutputStream(currentDate + fileName + ".xlsx");
workbook.write(fos);
fos.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (fos != null) {
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (workbook != null) {
workbook.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
// retrieve field names from an interface, ignoring getDecorateClass, getTarget getTargetClass methods,etc
private static List<String> getFieldNamesForClass(Class<?> clazz) throws Exception {
List<String> fieldNames = new ArrayList<String>();
Method[] methods = OrdersDataReportDTO.class.getMethods();
Arrays.sort(methods, (o1, o2) -> {
boolean isCheck = o1.isAnnotationPresent(Sequence.class);
Sequence or1 = o1.getAnnotation(Sequence.class);
Sequence or2 = o2.getAnnotation(Sequence.class);
// nulls last
if (or1 != null && or2 != null) {
return or1.value() - or2.value();
} else if (or1 != null && or2 == null) {
return -1;
} else if (or1 == null && or2 != null) {
return 1;
}
return o1.getName().compareTo(o2.getName());
});
for (Method method : methods) {
if (method.getName().startsWith("get") && !method.getName().equals("getDecoratedClass")
&& !method.getName().equals("getTarget") && !method.getName().equals("getTargetClass"))
fieldNames.add(lowerFirst(method.getName().substring(3)));
}
return fieldNames;
}
// lower the first letter of the field name
private static String lowerFirst(String s) {
if (s.length() == 0)
return s;
return s.substring(0, 1).toLowerCase() + s.substring(1);
}
// capitalize the first letter of the field name
private static String capitalize(String s) {
if (s.length() == 0)
return s;
return s.substring(0, 1).toUpperCase() + s.substring(1);
}
These are error logs:
2022-07-18 | 20:22:38.203 | | | scheduling-1 | ERROR | org.springframework.scheduling.support.TaskUtils$LoggingErrorHandler | Unexpected error occurred in scheduled task
java.lang.OutOfMemoryError: GC overhead limit exceeded
at java.lang.StringBuilder.toString(StringBuilder.java:412)
at org.apache.poi.ss.util.CellReference.formatAsString(CellReference.java:521)
at org.apache.poi.ss.util.CellReference.formatAsString(CellReference.java:495)
at org.apache.poi.xssf.usermodel.XSSFCell.setCellNum(XSSFCell.java:897)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:226)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:200)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:47)
at com.airtel.surveillance.analytics.utils.ExcelUtil.writeToExcel(ExcelUtil.java:64)
I've tried increasing memory:
FROM openjdk:8
ADD target/surveillance-analytics-*.jar surveillance-analytics.jar
EXPOSE 8092
RUN mkdir -p /opt/ssl
COPY srvlnc_prod.cer /opt/ssl/.
RUN keytool -keystore /usr/local/openjdk-8/jre/lib/security/cacerts -storepass changeit -noprompt -trustcacerts -importcert -alias srvlnc_prod_ssl -file /opt/ssl/srvlnc_prod.cer
ENTRYPOINT ["java", "-XX:+UnlockExperimentalVMOptions", "-XX:+UseCGroupMemoryLimitForHeap", "-XX:MaxRAMFraction=2", "-XshowSettings:vm", "-jar", "app.jar"]
I've tried quite a few things, adding MaxRAMFraction, increasing memory on jenkins job:
docker create -m 4G --cpus=4 -e "CONFIG_PROFILE=sit"
Either thread goes to starvation or it throws GC overhead. Logs for thread starvation:
Thread starvation or clock leap detected (housekeeper delta=51s742ms299µs124ns)
Starvation doesn't end after 51s.
Memory consumption:
