Re: why does this query run slowly at times

[Mike Wexler]

Kishore Jalleda wrote:

Explain select gives me this 

table       type   possible_keys   key       key_len   ref    rows   extra
template range  PRIMARY       PRIMARY    4       NULL   55     using where 

surpisingly it never runs slow when i run the query manually, also
when this query runs for long, mytop or show full process list shows
that other queries are running just fine, infact in the time that this
query is running many similar queries are excecuted very fast....
 

What you want to do is when its running slow, see what the value is for 
the "State" field in show processlist for this query. I suspect it will 
show "LOCKED" which means its waiting for something else to complete. 
There will probably be another query that crosses multiple tables 
(including this one) and is running. The running query is probably the 
one causing the problem, but you need to catch it in the act.

Thanks 

Kishore Jalleda 

On 8/9/05, Mike Wexler <[EMAIL PROTECTED]> wrote:
 

Kishore Jalleda wrote:

Try two things. First:

EXPLAIN

SELECT title, template
                      FROM template
                      WHERE templateid IN
(608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121);

Second, when its running slow, do a

SHOW FULL PROCESSLIST

I suspect another, longer running query is running at the same time and has the 
table locked.




   

Hi All,
        We have a query that sometimes takes 10-15 seconds to run,
atleast thats what mytop or the slow log say, usually it runs in less
than 0.04 seconds....

SELECT title, template
                      FROM template
                      WHERE templateid IN
(608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121);

templateid is the primary key, also there are only 500 rows in the
table "template",
(mysql version 4.0.17 on Redhat 7.3 )

whats making the query to run slowly,
the server only  runs at less than 10% cpu utilization, has lots of
ram and currently averages 120 qps ( 10M/day) , and is very fast 99%
of the time except for this query...

also the temp_table_size is set to 128MB, inspite of this the ratio
for Created_tmp_disk_tables/Created_tmp_tables is close to 0.1, could
this be a reason, why is Mysql creating soo many temp tables on the
disk....

Thanks a lot and any help would be appreciated ..

Kishore Jalleda