The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL Slow query not working

Discussion in 'Workarounds and Optimization' started by sahostking, Oct 7, 2014.

  1. sahostking

    sahostking Well-Known Member

    Joined:
    May 15, 2012
    Messages:
    300
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Cape Town, South Africa
    cPanel Access Level:
    Root Administrator
    Hi guys,

    I notice the following today :
    Code:
    # Query_time: 0.000155  Lock_time: 0.000023 Rows_sent: 1  Rows_examined: 18
    Tons of queries not slower than what I set in /etc/my.cnf
    Code:
    slow-query-log = 1
    slow-query-log-file = /var/log/mysql-slow.log
    long_query_time = 20
    log-queries-not-using-indexes

    Any ideas why?

    I'm running the following mysql version:
    Server version: 5.6.16-cll-lve MySQL Community Server (GPL)
     
    #1 sahostking, Oct 7, 2014
    Last edited: Oct 7, 2014
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. sahostking

    sahostking Well-Known Member

    Joined:
    May 15, 2012
    Messages:
    300
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Cape Town, South Africa
    cPanel Access Level:
    Root Administrator
    Any ideas yet guys?

    Server version: 5.6.16-cll-lve MySQL Community Server (GPL)

    Code:
    slow-query-log = 1
    slow-query-log-file = /var/log/mysql-slow.log
    log-error = /var/log/mysqld.log
    long_query_time = 5
    log-queries-not-using-indexes

    I'm seeing these in slow query log all the time, looks like it's logging everything. :


    Code:
    # User@Host: cphulkd[cphulkd] @ localhost []  Id:  1146
    # Query_time: 0.000145  Lock_time: 0.000018 Rows_sent: 1  Rows_examined: 3
    SET timestamp=1413611043;
    SELECT COUNT(*) FROM logins WHERE IP='' and LOGINTIME > FROM_UNIXTIME('1413610143');
    # User@Host: cphulkd[cphulkd] @ localhost []  Id:  1146
    # Query_time: 0.000122  Lock_time: 0.000026 Rows_sent: 1  Rows_examined: 3
    SET timestamp=1413611043;
    SELECT COUNT(*) FROM logins WHERE USER='' AND STATUS=0 AND SERVICE='imap' AND LOGINTIME > FROM_UNIXTIME('1413610743');
    # User@Host: cphulkd[cphulkd] @ localhost []  Id:  1146
    # Query_time: 0.000152  Lock_time: 0.000026 Rows_sent: 1  Rows_examined: 3
    SET timestamp=1413611043;
    SELECT COUNT(*) FROM logins WHERE USER='' AND STATUS=0 AND SERVICE='mail' AND LOGINTIME > FROM_UNIXTIME('1413610743');
    # Time: 141018  7:44:05
    # User@Host: cphulkd[cphulkd] @ localhost []  Id:  1149
    # Query_time: 0.000148  Lock_time: 0.000028 Rows_sent: 1  Rows_examined: 3
    SET timestamp=1413611045;
    SELECT COUNT(*) FROM logins WHERE IP='41.13.74.170' and LOGINTIME > FROM_UNIXTIME('1413610145');
    # User@Host: cphulkd[cphulkd] @ localhost []  Id:  1149
    # Query_time: 0.000113  Lock_time: 0.000024 Rows_sent: 1  Rows_examined: 3
    SET timestamp=1413611045;
    SELECT COUNT(*) FROM logins WHERE USER='' AND STATUS=0 AND SERVICE='pop3' AND LOGINTIME > FROM_UNIXTIME('1413610745');
    # User@Host: cphulkd[cphulkd] @ localhost []  Id:  1149
    # Query_time: 0.000112  Lock_time: 0.000035 Rows_sent: 1  Rows_examined: 3
    SET timestamp=1413611045;
    SELECT COUNT(*) FROM logins WHERE USER='' AND STATUS=0 AND SERVICE='mail' AND LOGINTIME > FROM_UNIXTIME('14136
     
  4. smoge

    smoge Well-Known Member

    Joined:
    Jul 2, 2004
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    Disable log-queries-not-using-indexes
     
  5. sahostking

    sahostking Well-Known Member

    Joined:
    May 15, 2012
    Messages:
    300
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Cape Town, South Africa
    cPanel Access Level:
    Root Administrator
    Thanks that worked.

    What is a reasonable setting for slow query time? I have mine on 5 seconds? Is this good enough for shared hosting?
     
Loading...

Share This Page