You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@syncope.apache.org by ernst Developer <er...@gmail.com> on 2012/10/08 21:18:44 UTC
Mysql workload
Hi,
When Syncope is not being used, I still see a substantial MySql workload,
caused by Syncope.
I was wondering what is causing this behavior, and I found out that a
specific quartz related set of queries is the reason for this.
One of the queries:
UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITING' WHERE TRIGGER_NAME =
'Trigger_job-1' AND TRIGGER_GROUP = 'DEFAULT' AND TRIGGER_STATE = 'ACQUIRED'
This query takes a long time to run, and is consuming a substantial part of
the processing power of the hardware.
What is the reason for this statement? What can I do about it, I mean, what
can I do to decrease the amount of queries being executed?
Cheers!
Ernst
Re: Mysql workload
Posted by ernst Developer <er...@gmail.com>.
Hi Francesco,
Here is the information on triggers from my database. I only provide the
show index on the QRTZ_TRIGGERS table, because this is the table that is
used in the query I have send to you yesterday. My question is: is this the
correct trigger?
mysql> show index from syncope.QRTZ_TRIGGERS;
+---------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| QRTZ_TRIGGERS | 0 | PRIMARY | 1 | TRIGGER_NAME | A
| 1 | NULL | NULL | | BTREE | |
| QRTZ_TRIGGERS | 0 | PRIMARY | 2 | TRIGGER_GROUP | A
| 1 | NULL | NULL | | BTREE | |
| QRTZ_TRIGGERS | 1 | JOB_NAME | 1 | JOB_NAME | A
| 1 | NULL | NULL | | BTREE | |
| QRTZ_TRIGGERS | 1 | JOB_NAME | 2 | JOB_GROUP | A
| 1 | NULL | NULL | | BTREE | |
+---------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
Strange thing here is that:
mysql> select count(*) from QRTZ_TRIGGERS;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
We have only one record? Strange that it takes so long.
Do you have any suggestion?
Regards,
Ernst
2012/10/9 ernst Developer <er...@gmail.com>
> Hi Francesco,
>
> Thanks for the explanation. I will have a look at the tables, and check if
> the indexes exist.
>
> Can you please specify the indexes I need to have?
>
> Regards,
> Ernst
>
>
> 2012/10/9 Francesco Chicchiriccò <il...@apache.org>
>
>> On 08/10/2012 21:18, ernst Developer wrote:
>>
>>> Hi,
>>>
>>> When Syncope is not being used, I still see a substantial MySql
>>> workload, caused by Syncope.
>>> I was wondering what is causing this behavior, and I found out that a
>>> specific quartz related set of queries is the reason for this.
>>>
>>> One of the queries:
>>>
>>> UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITING' WHERE TRIGGER_NAME =
>>> 'Trigger_job-1' AND TRIGGER_GROUP = 'DEFAULT' AND TRIGGER_STATE = 'ACQUIRED'
>>>
>>> This query takes a long time to run, and is consuming a substantial part
>>> of the processing power of the hardware.
>>>
>>> What is the reason for this statement? What can I do about it, I mean,
>>> what can I do to decrease the amount of queries being executed?
>>>
>>
>> Hi Ernst,
>> this happens because Syncope delegates to Quartz the handling of
>> scheduled jobs.
>> Jobs take care of reporting, notification, propagation, synchronization
>> from external resources and also user-defined actions.
>>
>> Quartz will then need to check whether it's time to fire a new trigger or
>> not once in a while, and this is the kind of workload you are seeing at the
>> moment.
>> In order to change this you can:
>> a. check if the MySQL DB schema for QRTZ_* tables is lacking some
>> indexes
>> b. since the highest frequency is probably due to NotificanJob which is
>> triggered every 2 minutes, you can override JobInstanceLoader.load() and
>> replace the cron expression "0 0/2 * * * ?" with something more suitable
>> for you
>>
>> (b) is clearly far from perfection, so I've opened SYNCOPE-216 for this.
>>
>> Regards.
>>
>> --
>> * Francesco* Chicchiriccò
>>
>> ASF Member, Apache Cocoon PMC and Apache Syncope PPMC Member
>> http://people.apache.org/~**ilgrosso/<http://people.apache.org/~ilgrosso/>
>>
>>
>
Re: Mysql workload
Posted by ernst Developer <er...@gmail.com>.
Findings until now:
- Only one row exists in the table "QRTZ_TRIGGERS";
- If I do the update direct in mysql client, it is really fast.
I do not have a conclusion right now.
2012/10/9 Francesco Chicchiriccò <il...@apache.org>
> On 09/10/2012 09:38, ernst Developer wrote:
>
> Hi Francesco,
>
> Thanks for the explanation. I will have a look at the tables, and check
> if the indexes exist.
>
> Can you please specify the indexes I need to have?
>
>
> I guess - since I am not a Quartz expert - indexes on foreign keys in
> order to speed up JOINs.
>
> Please share your findings once done :-)
>
> Regards.
>
>
> 2012/10/9 Francesco Chicchiriccò <il...@apache.org>
>
>> On 08/10/2012 21:18, ernst Developer wrote:
>>
>>> Hi,
>>>
>>> When Syncope is not being used, I still see a substantial MySql
>>> workload, caused by Syncope.
>>> I was wondering what is causing this behavior, and I found out that a
>>> specific quartz related set of queries is the reason for this.
>>>
>>> One of the queries:
>>>
>>> UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITING' WHERE TRIGGER_NAME =
>>> 'Trigger_job-1' AND TRIGGER_GROUP = 'DEFAULT' AND TRIGGER_STATE = 'ACQUIRED'
>>>
>>> This query takes a long time to run, and is consuming a substantial part
>>> of the processing power of the hardware.
>>>
>>> What is the reason for this statement? What can I do about it, I mean,
>>> what can I do to decrease the amount of queries being executed?
>>>
>>
>> Hi Ernst,
>> this happens because Syncope delegates to Quartz the handling of
>> scheduled jobs.
>> Jobs take care of reporting, notification, propagation, synchronization
>> from external resources and also user-defined actions.
>>
>> Quartz will then need to check whether it's time to fire a new trigger or
>> not once in a while, and this is the kind of workload you are seeing at the
>> moment.
>> In order to change this you can:
>> a. check if the MySQL DB schema for QRTZ_* tables is lacking some
>> indexes
>> b. since the highest frequency is probably due to NotificanJob which is
>> triggered every 2 minutes, you can override JobInstanceLoader.load() and
>> replace the cron expression "0 0/2 * * * ?" with something more suitable
>> for you
>>
>> (b) is clearly far from perfection, so I've opened SYNCOPE-216 for this.
>>
>> Regards.
>>
> --
> Francesco Chicchiriccò
>
> ASF Member, Apache Cocoon PMC and Apache Syncope PPMC Memberhttp://people.apache.org/~ilgrosso/
>
>
Re: Mysql workload
Posted by Francesco Chicchiriccò <il...@apache.org>.
On 09/10/2012 09:38, ernst Developer wrote:
> Hi Francesco,
>
> Thanks for the explanation. I will have a look at the tables, and
> check if the indexes exist.
>
> Can you please specify the indexes I need to have?
I guess - since I am not a Quartz expert - indexes on foreign keys in
order to speed up JOINs.
Please share your findings once done :-)
Regards.
> 2012/10/9 Francesco Chicchiriccò <ilgrosso@apache.org
> <ma...@apache.org>>
>
> On 08/10/2012 21:18, ernst Developer wrote:
>
> Hi,
>
> When Syncope is not being used, I still see a substantial
> MySql workload, caused by Syncope.
> I was wondering what is causing this behavior, and I found out
> that a specific quartz related set of queries is the reason
> for this.
>
> One of the queries:
>
> UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITING' WHERE
> TRIGGER_NAME = 'Trigger_job-1' AND TRIGGER_GROUP = 'DEFAULT'
> AND TRIGGER_STATE = 'ACQUIRED'
>
> This query takes a long time to run, and is consuming a
> substantial part of the processing power of the hardware.
>
> What is the reason for this statement? What can I do about it,
> I mean, what can I do to decrease the amount of queries being
> executed?
>
>
> Hi Ernst,
> this happens because Syncope delegates to Quartz the handling of
> scheduled jobs.
> Jobs take care of reporting, notification, propagation,
> synchronization from external resources and also user-defined actions.
>
> Quartz will then need to check whether it's time to fire a new
> trigger or not once in a while, and this is the kind of workload
> you are seeing at the moment.
> In order to change this you can:
> a. check if the MySQL DB schema for QRTZ_* tables is lacking
> some indexes
> b. since the highest frequency is probably due to NotificanJob
> which is triggered every 2 minutes, you can override
> JobInstanceLoader.load() and replace the cron expression "0 0/2 *
> * * ?" with something more suitable for you
>
> (b) is clearly far from perfection, so I've opened SYNCOPE-216 for
> this.
>
> Regards.
>
--
Francesco Chicchiriccò
ASF Member, Apache Cocoon PMC and Apache Syncope PPMC Member
http://people.apache.org/~ilgrosso/
Re: Mysql workload
Posted by ernst Developer <er...@gmail.com>.
Hi Francesco,
Thanks for the explanation. I will have a look at the tables, and check if
the indexes exist.
Can you please specify the indexes I need to have?
Regards,
Ernst
2012/10/9 Francesco Chicchiriccò <il...@apache.org>
> On 08/10/2012 21:18, ernst Developer wrote:
>
>> Hi,
>>
>> When Syncope is not being used, I still see a substantial MySql workload,
>> caused by Syncope.
>> I was wondering what is causing this behavior, and I found out that a
>> specific quartz related set of queries is the reason for this.
>>
>> One of the queries:
>>
>> UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITING' WHERE TRIGGER_NAME =
>> 'Trigger_job-1' AND TRIGGER_GROUP = 'DEFAULT' AND TRIGGER_STATE = 'ACQUIRED'
>>
>> This query takes a long time to run, and is consuming a substantial part
>> of the processing power of the hardware.
>>
>> What is the reason for this statement? What can I do about it, I mean,
>> what can I do to decrease the amount of queries being executed?
>>
>
> Hi Ernst,
> this happens because Syncope delegates to Quartz the handling of scheduled
> jobs.
> Jobs take care of reporting, notification, propagation, synchronization
> from external resources and also user-defined actions.
>
> Quartz will then need to check whether it's time to fire a new trigger or
> not once in a while, and this is the kind of workload you are seeing at the
> moment.
> In order to change this you can:
> a. check if the MySQL DB schema for QRTZ_* tables is lacking some indexes
> b. since the highest frequency is probably due to NotificanJob which is
> triggered every 2 minutes, you can override JobInstanceLoader.load() and
> replace the cron expression "0 0/2 * * * ?" with something more suitable
> for you
>
> (b) is clearly far from perfection, so I've opened SYNCOPE-216 for this.
>
> Regards.
>
> --
> * Francesco* Chicchiriccò
>
> ASF Member, Apache Cocoon PMC and Apache Syncope PPMC Member
> http://people.apache.org/~**ilgrosso/<http://people.apache.org/~ilgrosso/>
>
>
Re: Mysql workload
Posted by Francesco Chicchiriccò <il...@apache.org>.
On 08/10/2012 21:18, ernst Developer wrote:
> Hi,
>
> When Syncope is not being used, I still see a substantial MySql
> workload, caused by Syncope.
> I was wondering what is causing this behavior, and I found out that a
> specific quartz related set of queries is the reason for this.
>
> One of the queries:
>
> UPDATE QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITING' WHERE TRIGGER_NAME
> = 'Trigger_job-1' AND TRIGGER_GROUP = 'DEFAULT' AND TRIGGER_STATE =
> 'ACQUIRED'
>
> This query takes a long time to run, and is consuming a substantial
> part of the processing power of the hardware.
>
> What is the reason for this statement? What can I do about it, I mean,
> what can I do to decrease the amount of queries being executed?
Hi Ernst,
this happens because Syncope delegates to Quartz the handling of
scheduled jobs.
Jobs take care of reporting, notification, propagation, synchronization
from external resources and also user-defined actions.
Quartz will then need to check whether it's time to fire a new trigger
or not once in a while, and this is the kind of workload you are seeing
at the moment.
In order to change this you can:
a. check if the MySQL DB schema for QRTZ_* tables is lacking some indexes
b. since the highest frequency is probably due to NotificanJob which
is triggered every 2 minutes, you can override JobInstanceLoader.load()
and replace the cron expression "0 0/2 * * * ?" with something more
suitable for you
(b) is clearly far from perfection, so I've opened SYNCOPE-216 for this.
Regards.
--
Francesco Chicchiriccò
ASF Member, Apache Cocoon PMC and Apache Syncope PPMC Member
http://people.apache.org/~ilgrosso/