Quartz org.quartz.jobStore.selectWithLockSQL consumes CPU performance

[crisionas]

0

Hi!
I'm using Quartz for jobs scheduling, in clustering mode, the db what I use is MySQL Now after period of time, I discover a problem in my environment, that CPU performance decrease on the db server, caused by excessive call of quartz query with command

SELECT * FROM QRTZ_LOCKS WHERE SCHED_NAME = ? AND LOCK_NAME = ? FOR UPDATE
My Quartz config:

{
"quartz.serializer.type":"json",
"quartz.jobStore.misfireThreshold":"1000",
"quartz.jobStore.useProperties":"true",
"quartz.scheduler.instanceId":"AUTO",
"quartz.jobStore.clustered":"true",
"quartz.jobStore.driverDelegateType":"Quartz.Impl.AdoJobStore.MySQLDelegate",
"quartz.jobStore.dataSource":"MyProduct",
"quartz.jobStore.type":"Quartz.Impl.AdoJobStore.JobStoreTX",
"quartz.dataSource.DSS.provider":"MySqlConnector"
}

Also, I tried to use batch mode, but there is not big improvements.

"quartz.scheduler.batchTriggerAcquisitionMaxCount": "500",
 "quartz.scheduler.batchTriggerAcquisitionFireAheadTimeWindow": "1000",

[HamalStar]

I've also encountered this issue, and these are my slow SQL logs from my MySQL database.

# Query_time: 47.995965  Lock_time: 47.995757  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0  Bytes_sent: 0
SELECT * FROM QRTZ_LOCKS WHERE SCHED_NAME = 'QuartzScheduler' AND LOCK_NAME = 'TRIGGER_ACCESS' FOR UPDATE;

My service runs on a cluster, currently consisting of 4 nodes.
During the execution of my time job which runs every 15 minutes, this problem arises frequently.

[lahma]

Correct me if I'm wrong (I'm far from MySQL expert), but shouldn't this query take the time it needs to obtain the lock? So if something else is locking the row then the query will take longer until lock is acquired.