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.

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:
    15,442
    Likes Received:
    285
    Trophy Points:
    433
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    I've moved your posts to it's own thread.
     
    #3 Infopro, Feb 21, 2011
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    24
    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
     
    #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,622
    Likes Received:
    24
    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.
     
    #6 cPanelTristan, Mar 1, 2011
  7. postcd

    postcd Well-Known Member

    Joined:
    Oct 22, 2010
    Messages:
    646
    Likes Received:
    11
    Trophy Points:
    68
    To start loging slow queries you might need to restart mysql? service mysql restart
     
    #7 postcd, Mar 1, 2011
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    24
    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.
     
    #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,622
    Likes Received:
    24
    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!
     
    #10 cPanelTristan, Dec 5, 2011
(You must log in or sign up to post here.)
Loading...
Similar Threads - Enable slow query
  1. Dhoom

    How do I enable AllowOverride All in WHM

    Dhoom, Jun 24, 2017 at 7:46 PM, in forum: General Discussion
    Replies:
    1
    Views:
    41
    cPanelMichael
    Jun 26, 2017 at 1:11 PM
  2. fmntf

    Enable PHP-FPM in Addon Domain

    fmntf, Jun 23, 2017 at 5:58 AM, in forum: EasyApache
    Replies:
    3
    Views:
    40
    cPanelMichael
    Jun 23, 2017 at 8:31 AM
  3. sam2noone

    How to Enable DNS Edit option.

    sam2noone, Jun 19, 2017, in forum: Bind / DNS / Nameserver Issues
    Replies:
    1
    Views:
    43
    cPanelMichael
    Jun 20, 2017
  4. savastgfx

    Backup configuration warning: NOTE: Filesystem quotas are enabled.

    savastgfx, Jun 11, 2017, in forum: Data Protection
    Replies:
    2
    Views:
    64
    Infopro
    Jun 12, 2017
  5. orrevs

    How to enable Caching

    orrevs, May 17, 2017, in forum: EasyApache
    Replies:
    3
    Views:
    185
    Jafar Muhammed
    May 23, 2017

Share This Page