List:General Discussion« Previous MessageNext Message »
From:Rick James Date:September 4 2012 6:54pm
Subject:RE: Understanding Slow Query Log
View as plain text  
100 is tantamount to turning off the log.  I prefer 2.

select  count(ENTITY_NAME)
    from  ALERT_EVENTS
    where  EVENT_TIME > date_sub(now(),INTERVAL 60 MINUTE)
      and  status=upper('failed')
      and  ENTITY_NAME='FETL-ImpressionRC-conversion';
begs for the _compound_ index
INDEX(ENTITY_NAME, EVENT_TIME)
This would be even better:
INDEX(ENTITY_NAME, status, EVENT_TIME)

COUNT(*) should be used if you don't need to check the column for being NULL.

> Rows_examined: 141145
That is probably the entire table.  Will the table grow?  If so, the query will get
slower.  Meanwhile, the index I suggested will (probably) be much faster.


> -----Original Message-----
> From: Suresh Kuna [mailto:sureshkumarilu@stripped]
> Sent: Saturday, September 01, 2012 1:03 AM
> To: Adarsh Sharma
> Cc: mysql@stripped
> Subject: Re: Understanding Slow Query Log
> 
> Disable log-queries-not-using-indexes to log only queries > 100 sec.
> 
> Just do "> /var/lib/mysql/slow-queries.log" it will clear the log.
> 
> On Sat, Sep 1, 2012 at 12:34 PM, Adarsh Sharma
> <eddy.adarsh@stripped>wrote:
> 
> > Hi all,
> >
> > I am using Mysql Ver 14.14 Distrib 5.1.58 in which i enabled slow
> > query log by setting below parameters in my.cnf :
> >
> > log-slow-queries=/usr/local/mysql/slow-query.log
> > long_query_time=100
> > log-queries-not-using-indexes
> >
> > I am assuming from the inf. from the internet that long_query_time is
> > in seconds , but i see the slow query log ,  there are lots of
> > statements ( queries ) :
> >
> > # User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111] #
> > Query_time: 0.052784  Lock_time: 0.000043 Rows_sent: 1
> Rows_examined:
> > 141145
> > SET timestamp=1346409734;
> > select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME >
> > date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
> > ENTITY_NAME='FETL-ImpressionRC-conversion';
> > # Time: 120831 10:43:14
> > # User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111] #
> > Query_time: 0.053599  Lock_time: 0.000079 Rows_sent: 1
> Rows_examined:
> > 141145
> > SET timestamp=1346409794;
> > select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME >
> > date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
> > ENTITY_NAME='FETL-click-enhancer-deferred';
> > # User@Host: user1[user2] @ abc.dd.aa.com [192.112.111.111] #
> > Query_time: 0.054185  Lock_time: 0.000086 Rows_sent: 1
> Rows_examined:
> > 141145
> > SET timestamp=1346409794;
> > select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME >
> > date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and
> > ENTITY_NAME='FETL-ImpressionRC-conversion';
> > # Time: 120831 10:43:22
> > # User@Host: user2[user2] @ abc.dd.aa.com [192.112.111.111] #
> > Query_time: 0.000163  Lock_time: 0.000045 Rows_sent: 1
> Rows_examined:
> > 13
> >
> >
> >
> > I don't  understand the query time unit in slow query log because i
> > expect queries to be logged that takes > 100 s. I tested with sleep
> > command for 60s , it doesn't logged in slow query log and when i
> sleep
> > for 120 s it logged but i don't why the other queries are logging in
> slow log.
> >
> > # Query_time: 120.000259  Lock_time: 0.000000 Rows_sent: 1
> Rows_examined:
> > 0
> > SET timestamp=1346443103;
> > SELECT SLEEP(120);
> >
> > And also my slow log is increasing and decided to purge thorogh below
> > command :
> >
> > cat /dev/null > /var/lib/mysql/slow-queries.log
> >
> >
> > Anyone any ideas about this.
> >
> >
> > Thanks
> >
> 
> 
> 
> --
> Thanks
> Suresh Kuna
> MySQL DBA
Thread
Understanding Slow Query LogAdarsh Sharma1 Sep
  • Re: Understanding Slow Query Logyoku ts1 Sep
  • Re: Understanding Slow Query LogSuresh Kuna1 Sep
    • RE: Understanding Slow Query LogRick James4 Sep
      • Re: Understanding Slow Query LogAdarsh Sharma5 Sep
        • Re: Understanding Slow Query LogManuel Arostegui5 Sep
          • Re: Understanding Slow Query LogAdarsh Sharma5 Sep
Re: Understanding Slow Query LogAdarsh Sharma5 Sep
  • RE: Understanding Slow Query LogRick James5 Sep
    • Re: Understanding Slow Query LogAndy Wallace5 Sep
      • RE: Understanding Slow Query LogRick James5 Sep
        • Re: Understanding Slow Query LogAndy Wallace5 Sep