In response to your points 1. To get the results of the query it has to be run, so no. 2. That's the nature of MySQL each time an update is run on a table the query cache is cleared for any queries that are run on that table. 3. I've not investigated trac's database structure but that is one long query, what is the actual aim of the query? It looks like it returns the number of tickets where contract number has 123456 in it, if that is the case then SELECT count(*) FROM ticket LEFT JOIN ticket_custom ON ticket_custom.ticket=ticket.id WHERE ticket_custom.value LIKE '%123456%' AND ticket_custom.name='contract_number';
On Feb 26, 10:16 am, Lukasz Szybalski <[email protected]> wrote: > 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.
