Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

Enable the slow query log

Discussion in 'Workarounds and Optimization' started by Massoud, Feb 21, 2011.

  1. Massoud

    Massoud Active Member

    Joined:
    Feb 20, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    56
    How can
    Enable the slow query log to troubleshoot bad queries?
     
    #1 Massoud, Feb 21, 2011
  2. Massoud

    Massoud Active Member

    Joined:
    Feb 20, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    56
    Re: Extreme disk writes and server IO load from mysqld.

    After running the script I got this result:

    >> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 5M (Tables: 217)
    [!!] Total fragmented tables: 1

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 18m 15s (6K q [6.058 qps], 260 conn, TX: 19M, RX: 1M)
    [--] Reads / Writes: 65% / 35%
    [--] Total buffers: 314.0M global + 2.8M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.7G (66% of installed RAM)
    [OK] Slow queries: 0% (0/6K)
    [OK] Highest usage of available connections: 3% (16/500)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/2.3M
    [OK] Key buffer hit rate: 99.8% (121K cached / 236 reads)
    [OK] Query cache efficiency: 74.1% (3K cached / 5K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 264 sorts)
    [!!] Joins performed without indexes: 20
    [OK] Temporary tables created on disk: 17% (19 on disk / 107 total)
    [OK] Thread cache hit rate: 93% (16 created / 260 connections)
    [OK] Table cache hit rate: 97% (244 open / 250 opened)
    [OK] Open file limit used: 5% (478/8K)
    [OK] Table locks acquired immediately: 99% (2K immediate / 2K locks)

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Variables to adjust:
    join_buffer_size (> 132.0K, or always use indexes with joins)


    I change joing_buffer_size to 256K but simlar error has been generated
    in addition I do not know how should I Enable the slow query log to troubleshoot bad queries
    and
    my current: my.conf configuration files is this:

    [mysqld]
    datadir=/var/lib/mysql
    skip-locking
    skip-innodb
    skip-networking
    safe-show-database
    query_cache_limit=2M
    query_cache_size=32M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=200
    max_connections=500
    interactive_timeout=10
    wait_timeout=20
    connect_timeout=20
    thread_cache_size=128
    table_cache = 4096
    join_buffer_size =132K
    key_buffer=256M ## 128MB for every 1GB of RAM
    thread_concurrency=2 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1

    [safe_mysqld]
    open_files_limit=8192

    [mysqldump]
    quick
    max_allowed_packet=16M

    [mysql]
    no-auto-rehash
    #safe-updates

    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [mysqlhotcopy]
    interactive-timeout


    Database Collation is UTF-General-Ci
    Please help me to modify setting to best performance my VPS configuration is:
    Dual Core 2GHZ Processor
    4GB RAM
     
    #2 Massoud, Feb 21, 2011
  3. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    16,429
    Likes Received:
    415
    Trophy Points:
    583
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    I've moved your posts to it's own thread.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #3 Infopro, Feb 21, 2011
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    Trophy Points:
    238
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Because you are running MySQL 5.0.91 per the mysqltuner.pl report you've provided, you would add the following to /etc/my.cnf file to enable the slow query log:

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    After that, then do the following commands to create the file with the right ownership and file permissions:

    Code:
    touch /var/lib/mysql/slow.log
chmod 660 /var/lib/mysql/slow.log
chown mysql:mysql /var/lib/mysql/slow.log
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #4 cPanelTristan, Feb 21, 2011
  5. Massoud

    Massoud Active Member

    Joined:
    Feb 20, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    56
    Thank you for your reply.
    How can I find this problem: "Joins performed without indexes" and how should I solve this?

    in addition I did what you said but the slow-log file is always empty.

    Now my result is something like that:
    >> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at MySQLTuner
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 12M (Tables: 357)
    [--] Data in InnoDB tables: 16K (Tables: 1)
    [!!] Total fragmented tables: 3

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10h 47m 14s (516K q [13.288 qps], 18K conn, TX: 1B, RX: 101M)
    [--] Reads / Writes: 65% / 35%
    [--] Total buffers: 258.0M global + 9.2M per thread (100 max threads)
    [OK] Maximum possible memory usage: 1.2G (46% of installed RAM)
    [OK] Slow queries: 0% (3/516K)
    [OK] Highest usage of available connections: 27% (27/100)
    [OK] Key buffer size / total MyISAM indexes: 200.0M/3.2M
    [OK] Key buffer hit rate: 100.0% (13M cached / 1K reads)
    [OK] Query cache efficiency: 72.4% (279K cached / 385K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 18K sorts)
    [!!] Joins performed without indexes: 401
    [OK] Temporary tables created on disk: 19% (690 on disk / 3K total)
    [OK] Thread cache hit rate: 99% (27 created / 18K connections)
    [OK] Table cache hit rate: 98% (419 open / 425 opened)
    [OK] Open file limit used: 70% (794/1K)
    [OK] Table locks acquired immediately: 98% (153K immediate / 155K locks)
    [OK] InnoDB data size / buffer pool: 16.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Variables to adjust:
    join_buffer_size (> 4.0M, or always use indexes with joins)
     
    #5 Massoud, Feb 26, 2011
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    Trophy Points:
    238
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Since mysqltuner.pl continues to indicate that the slow query log doesn't exist, it would appear it wasn't properly enabled. Please provide your /etc/my.cnf file contents and run the following commands for us to see the output:

    Code:
    mysql_config --version
ls -lah /var/lib/mysql/*.log*
    Also, I really don't suggest running mysqltuner.pl in less than 24 hour increments.

    For the join question, you can simply increase the join_buffer_size to provide for not having indexes for the join queries. You'd have to go through the databases you have otherwise to find which ones have joins that aren't indexed.

    Thanks.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #6 cPanelTristan, Mar 1, 2011
  7. postcd

    postcd Well-Known Member

    Joined:
    Oct 22, 2010
    Messages:
    694
    Likes Received:
    15
    Trophy Points:
    68
    To start loging slow queries you might need to restart mysql? service mysql restart
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #7 postcd, Mar 1, 2011
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    Trophy Points:
    238
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I'm uncertain how MySQL might not have been restarted when it was up for less than 24 hours on the mysqltuner.pl run per the results provided:

    It is true that they would need to restart MySQL, but I think some other inherent issue might be happening here instead given that it had been restarted within a less than 24 hour timeframe at the time of the prior post.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #8 cPanelTristan, Mar 1, 2011
  9. pixeline

    pixeline Registered

    Joined:
    Dec 4, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    51
    cPanel Access Level:
    Reseller Owner
    I think you mean:
    Code:
    log_slow_queries=/var/lib/mysql/slow.log
    , not
    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    Right?
     
    #9 pixeline, Dec 5, 2011
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    Trophy Points:
    238
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    No, I do not:

    MySQL :: MySQL 5.0 Reference Manual :: 5.1.2 Server Command Options

    You can use log-slow-queries in /etc/my.cnf file. Both are acceptable variable names for that file. Please feel free to review the documentation. Here are a series of steps on my machine to show this works perfectly fine:

    Code:
    root@host [~]# mysql_config --version
5.0.92
root@host [~]# mysqladmin var | grep slow
| log_slow_queries                        | OFF        |
| slow_launch_time                        | 2          |
root@host [~]# cat /etc/my.cnf
[mysqld]
innodb_file_per_table=1

root@host [~]# echo "log-slow-queries=/var/lib/mysql/slow.log" >> /etc/my.cnf
root@host [~]# touch /var/lib/mysql/slow.log
root@host [~]# chown mysql:mysql /var/lib/mysql/slow.log
root@host [~]# chmod 660 /var/lib/mysql/slow.log
root@host [~]# /etc/init.d/mysql restart
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 

root@host [~]# mysqladmin var | grep slow
| log_slow_queries                        | ON     |
| slow_launch_time                        | 2      |
root@host [~]# cat /etc/my.cnf
[mysqld]
innodb_file_per_table=1
log-slow-queries=/var/lib/mysql/slow.log
root@host [~]# cat /var/lib/mysql/slow.log 
/usr/sbin/mysqld, Version: 5.0.92-community-log 
(MySQL Community Edition (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
    Also, there is a difference between empty and no slow queries logging. My /var/lib/mysql/slow.log for the slow query log isn't empty. It shows the restart command being issued but nothing logging as I just restarted to create it. Not to mention, I have no queries on my machine that take 10 or more seconds to run.

    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #10 cPanelTristan, Dec 5, 2011
(You must log in or sign up to post here.)
Loading...
Similar Threads - Enable slow query
  1. activa

    Enable AutoSSL for User X API

    activa, Sep 21, 2018, in forum: cPanel Developers
    Replies:
    5
    Views:
    122
    cPanelLauren
    Oct 11, 2018 at 1:14 PM
  2. Vikhyat Singh

    websites stop working after php-fpm is enabled

    Vikhyat Singh, Aug 31, 2018, in forum: General Discussion
    Replies:
    3
    Views:
    136
    cPanelLauren
    Aug 31, 2018
  3. ndhosting

    Enable ssl per user

    ndhosting, Aug 15, 2018, in forum: Security
    Replies:
    3
    Views:
    93
    cPanelMichael
    Aug 15, 2018
  4. benito

    How to enable non-ssl smtp?

    benito, Aug 14, 2018, in forum: E-mail Discussion
    Replies:
    2
    Views:
    120
    benito
    Aug 14, 2018
  5. DesFire

    Quota issue: does it need to be enabled in all partitions?

    DesFire, Aug 13, 2018, in forum: General Discussion
    Replies:
    3
    Views:
    124
    cPanelLauren
    Aug 14, 2018

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice