DB is consuming a lot of CPU and user sessions were not disconnected

book

Article ID: 206468

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

Yesterday Automic system consumed a lot of CPU on the DB side, we could not find any issues on Automic except we could not login to the Web UI.

We have seen more than 60 concurrent users connected via Web UI looks like duplicated sessions for the same user's name

After manually restarting JWP/JCP and disconnecting these users sessions the system was back to normal.

Today we see again that there are a lot of CPU consumed on the DB server

Error messages:  

  ----------------------- Stack Trace -----------------------

20201129/200304.630 - 32               com.automic.database.api.DBException: DELETE TOP (1) FROM MQ1CP005 WITH(READPAST) OUTPUT deleted.MQCP_PK,deleted.MQCP_CAddr,deleted.MQCP_BAcv,deleted.MQCP_BAddr,deleted.MQCP_BSRName,deleted.MQCP_Status,deleted.MQCP_Msg,deleted.MQCP_BTable,deleted.MQCP_CSRName,deleted.MQCP_PhysAddr

20201129/200304.630 - 32                     at com.automic.database.impl.DBConnectionImpl.executeInternal(DBConnectionImpl.java:437)

20201129/200304.630 - 32                     at com.automic.database.impl.DBConnectionImpl.removeRow(DBConnectionImpl.java:335)

20201129/200304.630 - 32                     at com.automic.kernel.impl.MQRecordReader.execute(MQRecordReader.java:26)

20201129/200304.630 - 32                     at com.automic.kernel.impl.DBAction.execute(DBAction.java:158)

20201129/200304.630 - 32                     at com.automic.kernel.impl.DBAction.execute(DBAction.java:130)

20201129/200304.630 - 32                     at com.automic.kernel.impl.DBAction.execute(DBAction.java:112)

20201129/200304.630 - 32                     at com.automic.kernel.impl.MessageInQueue$1.runSave(MessageInQueue.java:57)

20201129/200304.630 - 32                     at com.automic.kernel.impl.ExecutorTaskWrapper.run(ExecutorTaskWrapper.java:34)

20201129/200304.630 - 32                     at com.automic.kernel.impl.MessageInQueue.readNewMessage(MessageInQueue.java:65)

20201129/200304.630 - 32                     at com.automic.kernel.impl.MessageInQueue.execute(MessageInQueue.java:31)

20201129/200304.630 - 32                     at com.automic.kernel.impl.NetworkMessageDispatcher$NetworkMessageRunnable.runSave(NetworkMessageDispatcher.java:117)

20201129/200304.630 - 32                     at com.automic.kernel.impl.ExecutorTaskWrapper.run(ExecutorTaskWrapper.java:34)

20201129/200304.630 - 32                     at com.automic.kernel.impl.NetworkMessageDispatcher.dispatch(NetworkMessageDispatcher.java:71)

20201129/200304.630 - 32                     at com.automic.kernel.impl.RunnableMessage.runSave(RunnableMessage.java:35)

20201129/200304.630 - 32                     at com.automic.kernel.impl.ExecutorTaskWrapper.run(ExecutorTaskWrapper.java:34)

20201129/200304.630 - 32                     at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

20201129/200304.630 - 32                     at java.util.concurrent.FutureTask.run(Unknown Source)

20201129/200304.630 - 32                     at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

20201129/200304.630 - 32                     at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

20201129/200304.630 - 32                     at java.lang.Thread.run(Unknown Source)

20201129/200304.630 - 32               Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 295) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

20201129/200304.630 - 32                     at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)

20201129/200304.630 - 32                     at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4965)

20201129/200304.630 - 32                     at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1786)

20201129/200304.630 - 32                     at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1039)

20201129/200304.630 - 32                     at com.automic.database.impl.UC4ResultSetImpl.next(UC4ResultSetImpl.java:46)

20201129/200304.630 - 32                     at com.automic.database.impl.DBConnectionImpl.handleResult(DBConnectionImpl.java:488)

20201129/200304.630 - 32                     at com.automic.database.impl.DBConnectionImpl.executeInternal(DBConnectionImpl.java:433)

20201129/200304.630 - 32                     ... 19 more


Logs from the server and traces from the JCP attached

Environment

Release : 12.2

Component : AUTOMATION ENGINE

Resolution

The ODBC driver is very old. We recommend ODBC driver 17

https://www.microsoft.com/en-us/download/details.aspx?id=56567

The JDBC driver is also very old

The JCP Maximum Heap Memory: '455' MB'

We can increase this to 2 gb (2048)

The physical memory is also 16 GB which needs to be increased

Also, we suggest using MARS connection

ODBCVAR=NNNNNNRN,DSN=UC4;UID=uc4;PWD=???;Mars_Connection=Yes - Microsoft SQL Server with MARS

Problem did not reoccur after the changes were made.