incubator-syncope-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ernst Developer <ernst.develo...@gmail.com>
Subject Re: Mysql workload
Date Tue, 09 Oct 2012 07:47:07 GMT
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 <ernst.developer@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ò <ilgrosso@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/>
>>
>>
>

Mime
View raw message