[dev] horde/scripts/alarms.php

[Didi Rieder]

Quoting Matt Selsky <selsky at columbia.edu>:

>> Is there any chance that the speed of running the alarms.php script can
>> be further increased?
>>
>> On my system it takes about 2 min. to run:
>> real    1m55.010s
>> user    0m40.431s
>> sys     0m2.496s
>>
>> if this is running every 5 min. then the system is almost just doing
>> the alarms job.
>
> What does "SHOW GLOBAL STATUS LIKE 'Com_stmt_%';" show now?  If you

mysql> SHOW GLOBAL STATUS LIKE 'Com_stmt_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_close          | 20632 |
| Com_stmt_execute        | 20663 |
| Com_stmt_fetch          | 0     |
| Com_stmt_prepare        | 20663 |
| Com_stmt_reset          | 0     |
| Com_stmt_send_long_data | 0     |
+-------------------------+-------+
6 rows in set (0.00 sec)


> run EXPLAIN on the SELECT queries that alarms.php runs, does it show  
> all of the indexes being used?  Does the script still take 2 full  
> minutes to run?

Yes it still takes about 2 minutes to run.

real    1m48.667s
user    0m43.593s
sys     0m2.761s

EXPLAIN SELECT event_id, event_uid, event_description, event_location,  
event_private, event_status, event_attendees, event_keywords,  
event_title, event_category, event_recurcount, event_recurtype,  
event_recurenddate, event_recurinterval, event_recurdays, event_start,  
event_end, event_alarm, event_modified, event_exceptions,  
event_creator_id FROM kronolith_events WHERE calendar_id = 'xxx at xxx'  
AND ((event_alarm > 0)) AND ((event_end > '2008-07-21 00:00:00' AND  
event_start < '9999-12-31 00:00:00') OR (event_recurenddate >=  
'2008-07-21 00:00:00' AND event_start <= '9999-12-31 00:00:00' AND  
event_recurtype <> 0));
+----+-------------+------------------+------+------------------------+------------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys           
| key                    | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+------------------------+------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | kronolith_events | ref  | kronolith_calendar_idx  
| kronolith_calendar_idx | 767     | const |    1 | Using where |
+----+-------------+------------------+------+------------------------+------------------------+---------+-------+------+-------------+

EXPLAIN SELECT * FROM nag_tasks WHERE task_owner = ? AND task_alarm >  
0 AND (task_due - (task_alarm * 60) <= ?) AND task_completed = 0;
+----+-------------+-----------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys    | key          
      | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | nag_tasks | ref  | nag_tasklist_idx |  
nag_tasklist_idx | 767     | const |   63 | Using where |
+----+-------------+-----------+------+------------------+------------------+---------+-------+------+-------------+

The queries are using the indices but it seems that the script runs  
about 20,000 queries (10,000 kronolith + 10,000 nag).

Didi

-- 
-------------------------
Didi Rieder
adrieder at sbox.tugraz.at
PGPKey ID: 3431D0B0
-------------------------