Here seems to be one issue. (I'm not sure if that is the one that
hangs the whole process but...)
7 seconds to do the following query. I have a link on a wiki page that
says ...find by contract_number. You click on it it takes you to the
query which fills in the custome field with 123456, but since there is
no way for me to stop the query, it runs, users enters the real
contract # and clicks update again.
1. Is there a way to link(redirect to) a query without running it?
2. Even do mysql puts this query in memory and running it from query
browser second time should take less then a 1sec, it seems as after
any tickets have been updated this query is re-run.
8:19:26 Query_time: 7 Lock_time: 0 Rows_sent: 0 Rows_examined:
276682
8:23:00 Query_time: 10 Lock_time: 0 Rows_sent: 0 Rows_examined:
276692
.......
3. Is there a way to speed up that query somehow? We still need to use
contains but maybe changing it or some other sql optimization ?
SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.status
AS status,t.type AS type,t.priority AS priority,t.milestone AS
milestone,t.component AS component,t.time AS time,t.changetime AS
changetime,priority.value AS priority_value,contract_number.value AS
contract_number
FROM ticket AS t
LEFT OUTER JOIN ticket_custom AS contract_number ON
(id=contract_number.ticket AND contract_number.name='contract_number')
LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND
priority.name=priority)
WHERE COALESCE(t.status,'') IN
('accepted','assigned','closed','new','reopened') AND
COALESCE(contract_number.value,'') LIKE '%123456%' ESCAPE '/'
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS
signed),t.id) AS foo;
Thanks,
Lucas
On Feb 24, 2:48 pm, Rowan <[email protected]> wrote:
> Check the mysql slow query log
> (seehttp://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html)
>
> On Feb 25, 6:59 am, Lukasz Szybalski <[email protected]> wrote:
>
> > Hello,
> > I was wondering if you guys could help me determine what the problem
> > might be with trac/mysql. We were going smooth up until 110K tickets
> > in trac. What happens now is that after a while (3h) mysql starts
> > using 100% of the cpu, while all the other requests are waiting. I
> > need to determine which query is that? Who's running it, etc? or
> > whether it is my mysql settings?
>
> > Ideas?
>
> > my.mysql:
>
> > key_buffer = 1600M
> > max_allowed_packet = 16M
> > thread_stack = 128K
> > query_cache_limit = 1M
> > query_cache_size = 64M
>
> > show status like 'qc%';
> > +-------------------------+----------+
> > | Variable_name | Value |
> > +-------------------------+----------+
> > | Qcache_free_blocks | 62 |
> > | Qcache_free_memory | 66876024 |
> > | Qcache_hits | 45233 |
> > | Qcache_inserts | 1606 |
> > | Qcache_lowmem_prunes | 0 |
> > | Qcache_not_cached | 670 |
> > | Qcache_queries_in_cache | 159 |
> > | Qcache_total_blocks | 408 |
> > +-------------------------+----------+
> > 8 rows in set (0.00 sec)
>
> > show variables like 'query%';
> > +------------------------------+----------+
> > | Variable_name | Value |
> > +------------------------------+----------+
> > | query_alloc_block_size | 8192 |
> > | query_cache_limit | 1048576 |
> > | query_cache_min_res_unit | 4096 |
> > | query_cache_size | 67108864 |
> > | query_cache_type | ON |
> > | query_cache_wlock_invalidate | OFF |
> > | query_prealloc_size | 8192 |
> > +------------------------------+----------+
> > 7 rows in set (0.00 sec)
>
> > What can I do to trouble shoot this problem?
>
> > Thanks,
> > Lucas
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/trac-users?hl=en.
