Notifications

1493 views

Description

When processing email.read events, the following can occur due to some changes made in Jakarta causing a slow query:

  • longer time to process email.read events (inbound email specifically)
  • backlog of events due to longer processing email.read events (delays for other events in the same queue)

Note: We have an internal monitoring alert that may get triggered by this PRB depending on the delay introduced by this slow query.  

Alert Type: Event Queue Critical

What is the slow query causing the delay?

SELECT sys_email00000.`sys_id`, 'sys_email0000' AS `sys_table_name` FROM sys_email0000 sys_email00000 WHERE sys_email00000.`message_id` = '<CAOa+Ohgpw+r1Z4ktHBkHmCY2DnJbM3dByP3qhPY8tvj06xeaxw@mail.gmail.com>') UNION ALL (SELECT sys_email00010.`sys_id`, 'sys_email0001' AS `sys_table_name` FROM sys_email0001 sys_email00010 WHERE sys_email00010.`message_id` = '<CAOa+Ohgpw+r1Z4ktHBkHmCY2DnJbM3dByP3qhPY8tvj06xeaxw@mail.gmail.com>') UNION ALL (SELECT sys_email00020.`sys_id`, 'sys_email0002' AS `sys_table_name` FROM sys_email0002 sys_email00020 WHERE sys_email00020.`message_id` = '<CAOa+Ohgpw+r1Z4ktHBkHmCY2DnJbM3dByP3qhPY8tvj06xeaxw@mail.gmail.com>') UNION ALL (SELECT sys_email00030.`sys_id`, 'sys_email0003' AS `sys_table_name` FROM sys_email0003 sys_email00030 WHERE sys_email00030.`message_id` = '<CAOa+Ohgpw+r1Z4ktHBkHmCY2DnJbM3dByP3qhPY8tvj06xeaxw@mail.gmail.com>') UNION ALL ... UNION ALL (SELECT sys_email0.`sys_id`, 'sys_email' AS `sys_table_name` FROM sys_email sys_email0 WHERE sys_email0.`message_id` = '<CAOa+Ohgpw+r1Z4ktHBkHmCY2DnJbM3dByP3qhPY8tvj06xeaxw@mail.gmail.com>'

This query is triggered for each inbound email action that runs and the query can take as high as 10 minutes to process.

 

Steps to Reproduce

 

  • Process an inbound email (email.read event)
  • The inbound email can take an excessive amount of time to process.
    • While it is processing, notice the worker thread running the events process job is stuck for an excessive time making a query from:

       com.glide.notification.inbound.classify.RecordLocatorStrategy.matchByInReplyToMessageId(RecordLocatorStrategy.java:223)

      main,glide.scheduler.worker.7,4,events process 1 (16263376 ms)
      java.net.SocketInputStream.socketRead0(Native Method)
      java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
      java.net.SocketInputStream.read(SocketInputStream.java:171)
      java.net.SocketInputStream.read(SocketInputStream.java:141)
      java.io.BufferedInputStream.fill(BufferedInputStream.java:246)
      java.io.BufferedInputStream.read1(BufferedInputStream.java:286)
      java.io.BufferedInputStream.read(BufferedInputStream.java:345)
      org.mariadb.jdbc.internal.common.packet.buffer.ReadUtil.readFully(ReadUtil.java:82)
      org.mariadb.jdbc.internal.common.packet.buffer.ReadUtil.readFully(ReadUtil.java:92)
      org.mariadb.jdbc.internal.common.packet.RawPacket.nextPacket(RawPacket.java:77)
      org.mariadb.jdbc.internal.common.packet.SyncPacketFetcher.getRawPacket(SyncPacketFetcher.java:67)
      org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:941)
      org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1050)
      org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1030)
      org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:289)
      org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:385)
      sun.reflect.GeneratedMethodAccessor22.invoke(Unknown Source)
      sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      java.lang.reflect.Method.invoke(Method.java:498)
      com.glide.db.StatementWrapper.invoke(StatementWrapper.java:40)
      com.sun.proxy.$Proxy7.execute(Unknown Source)
      com.glide.db.DBI.executeStatement0(DBI.java:1016)
      com.glide.db.DBI.executeStatement(DBI.java:972)
      com.glide.db.DBI.executeStatement(DBI.java:941)
      com.glide.db.DBI.executeStatement(DBI.java:929)
      com.glide.db.DBQuery.executeAsResultSet0(DBQuery.java:322)
      com.glide.db.DBQuery.executeAsResultSet0(DBQuery.java:297)
      com.glide.db.DBQuery.executeAndReturnTable(DBQuery.java:263)
      com.glide.db.DBAction.executeNormal(DBAction.java:234)
      com.glide.db.DBAction.executeAndReturnException(DBAction.java:195)
      com.glide.db.RDBMSQueryContext.executeQuery(RDBMSQueryContext.java:46)
      com.glide.db.DBQuery.execute(DBQuery.java:2096)
      com.glide.db.meta.Table.queryBasic(Table.java:325)
      com.glide.db.meta.Table.query(Table.java:199)
      com.glide.script.GlideRecordITable.query(GlideRecordITable.java:95)
      com.glide.script.GlideRecord.query0(GlideRecord.java:3163)
      com.glide.script.GlideRecord.query(GlideRecord.java:2888)
      com.glide.notification.inbound.classify.RecordLocatorStrategy.matchByInReplyToMessageId(RecordLocatorStrategy.java:223) >>> method calling the problematic query
      com.glide.notification.inbound.classify.RecordLocatorStrategy.locate(RecordLocatorStrategy.java:87)
      com.glide.notification.inbound.classify.EmailClassifier.classify(EmailClassifier.java:90)
      com.glide.notification.inbound.InboundEmailActionHandler.impersonateUserAndClassify(InboundEmailActionHandler.java:785)
      com.glide.notification.inbound.InboundEmailActionHandler.executeScriptInScope(InboundEmailActionHandler.java:110)
      com.glide.policy.ScriptActionHandler.process0(ScriptActionHandler.java:60)
      com.glide.policy.ScriptActionHandler.process(ScriptActionHandler.java:39)
      com.glide.policy.EventProcessor.processEventDuringNormalOperation(EventProcessor.java:174)
      com.glide.policy.EventProcessor.processEvent(EventProcessor.java:109)
      com.glide.policy.EventProcessor.process(EventProcessor.java:71)
      com.glide.policy.EventManager.processEvents(EventManager.java:288)
      com.glide.policy.EventManager._process(EventManager.java:166)
      com.glide.policy.EventManager.processDelegatedEvents(EventManager.java:140)
      com.glide.script.GlideSystem.js_processDelegatedEvents(GlideSystem.java:692)
      sun.reflect.GeneratedMethodAccessor270.invoke(Unknown Source)
      sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      java.lang.reflect.Method.invoke(Method.java:498)
      org.mozilla.javascript.MemberBox.invoke(MemberBox.java:138)
      org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:638)
      org.mozilla.javascript.FunctionObject.call(FunctionObject.java:582)
      org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2582)
      org.mozilla.javascript.optimizer.OptRuntime.callProp0(OptRuntime.java:85)
      org.mozilla.javascript.gen._refname__143._c_script_0(:1)
      org.mozilla.javascript.gen._refname__143.call()
      org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:563)
      org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3421)
      org.mozilla.javascript.gen._refname__143.call()
      org.mozilla.javascript.gen._refname__143.exec()
      com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:263)
      com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:110)
      com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:76)
      com.glide.script.ScriptEvaluator.evaluateStringWithPrefix(ScriptEvaluator.java:60)
      com.glide.script.Evaluator.evaluatePossiblePrefixedString(Evaluator.java:202)
      com.glide.job.RunScriptJob.evaluateScript(RunScriptJob.java:163)
      com.glide.job.RunScriptJob.execute(RunScriptJob.java:86)
      com.glide.schedule.JobExecutor.executeJob(JobExecutor.java:103)
      com.glide.schedule.JobExecutor.execute(JobExecutor.java:89)
      com.glide.schedule.GlideScheduleWorker.executeJob(GlideScheduleWorker.java:219)
      com.glide.schedule.GlideScheduleWorker.lambda$process$17(GlideScheduleWorker.java:161)
      com.glide.schedule.GlideScheduleWorker$$Lambda$40/16773852.run(Unknown Source)
      com.glide.worker.TransactionalWorkerThread.executeInTransaction(TransactionalWorkerThread.java:35)
      com.glide.schedule.GlideScheduleWorker.process(GlideScheduleWorker.java:161)
      com.glide.schedule.GlideScheduleWorker.run(GlideScheduleWorker.java:72)

Workaround

The workaround for this issue is to index the message_id column on the sys_email table: Use the following to see how to create a database index (admin access) Create a table index

Note: Add this to the sys_email shards, it may take some time to complete.


Related Problem: PRB1100030

Seen In

There is no data to report.

Fixed In

Jakarta Patch 4
Kingston

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-06-25 01:56:01
Published:2017-09-14