[dev] horde/scripts/alarms.php

[Didi Rieder]

Quoting Matt Selsky <selsky at columbia.edu>:

>> But wouldn't it be better to try to reduce the number of statements at
>> first place,
>> since this script should be running periodically every 5 minutes as  
>> suggested?
>
> According to this artcile from 2006:
> http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/
>
> "Do not forget to close prepared statements - Many memory leaks reported
> in MySQL Server turned out to be prepare statements or cursors which
> were forgotten to be closed. Watch Com_stmt_prepare and Com_stmt_close
> to see if you’re closing all prepared statements. In newer versions you
> can also use prepared_stmt_count variable to track number of open
> statements diretly. You can also adjust max_prepared_stmt_count variable
> which limits how many statements can be open at the same time to avoid
> overload.
>
> Check execution rate You can take a look at Com_stmt_execute to
> Com_stmt_prepare ratio to see how many times each statement is executed.
> If it is executed only once standard statements might be better for you,
> or might be you just need to cache prepared statements in your
> application (avoid closing and recycle). Note in some cases it still can
> be better to use prepared statements even if you use each only once due
> to data conversion."
>
> Can you check the results of "SHOW STATUS LIKE 'Com_stmt_%" and "SHOW
> STATUS LIKE 'qc%" ?

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

mysql> SHOW GLOBAL STATUS LIKE 'qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 84102536 |
| Qcache_hits             | 847      |
| Qcache_inserts          | 20924    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 16560    |
| Qcache_queries_in_cache | 20671    |
| Qcache_total_blocks     | 41373    |
+-------------------------+----------+
8 rows in set (0.00 sec)

This is after a fresh restart of the DB and the execution of the  
alarms.php script.

>
> See also
> http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html
>
> Likely we need to call MDB2's free() on our prepared statements. And we
> may need to call freePrepared() on the DB prepared statements.

It seems so....

Didi

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