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.

cron job to kill slow query issue

Discussion in 'Workarounds and Optimization' started by fancier, Jul 19, 2013.

  1. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Code:
    <?php
    
    $con = mysql_connect( "localhost", "user", "pass" ) or die( "can not connect" );
    if( $con ) echo "Connected<br>";
    
    $result = mysql_query( "SHOW FULL PROCESSLIST", $con );
    while( $row = mysql_fetch_array( $result, $con ) )
    {
    
        $process_id = $row["id"];
        if ($row["Time"] > 100 )
        {
            $sql = "KILL $process_id";
            $res = mysql_query( "$sql", $con );
            if( $res )
            {
                echo "Mysql Process ID $process_id has been killed<br>";
            }
        }
        else echo "Row not found?<br>";
    }
    ?>
    
    I tried this code for cron job to kill slow query

    but when i run this cron.php then i got this error

    Connected

    Warning: mysql_fetch_array() expects parameter 2 to be long, resource given in /home/r/public_html/da/cron.php on line 7
     
  2. STS Admin

    STS Admin Well-Known Member

    Joined:
    Jul 8, 2012
    Messages:
    47
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    In the below line the second parameter is type of array that is to be fetched not $con. It's a constant and can take the following values: MYSQL_ASSOC, MYSQL_NUM, and MYSQL_BOTH.

     
  3. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    So guide what change i have to made, my issue is that on high traffic too many slow queries take place that hangs mysql

    I have SMF 2.0.4 default theme
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    if you got slow queries, post your my.cnf config, hardware spec etc and mysqltuner result
     
  5. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Code:
    [mysqld]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer_size = 384M
    max_allowed_packet = 1M
    table_open_cache = 512
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    open_files_limit = 10000
    query_cache_size = 32M
    query_cache_size = 512M
    max_heap_table_size = 16M
    table_cache = 400
    max_connections = 100
    thread_cache_size = 286
    interactive_timeout = 25
    wait_timeout = 100
    connect_timeout = 10
    max_connect_errors = 10
    query_cache_type = 1
    tmp_table_size = 64M
    join_buffer_size = 2M
    net_buffer_length = 2K
    thread_stack = 64K
    low_priority_updates=1
    concurrent_insert=ALWAYS
    long_query_time = 0.1
    slow_query_log = 1
    slow_query_log_file = /var/log/mysql-slow-query.log
    slow_query_log_microseconds_timestamp = 1
    log_queries_not_using_indexes = 1
    innodb_data_home_dir = /var/lib/mysql
    innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
    innodb_log_group_home_dir = /var/lib/mysql
    innodb_buffer_pool_size = 384M
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size = 100M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50
    
    [mysql]
    no-auto-rehash
    
    
    [mysqlhotcopy]
    interactive-timeout
    
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    I have four quad CPU and 3 gb RAM VPS Server, when load is high then too many tables got open, some running, some lock, I see those here /http://realinfo.tv/status.php
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    and mysqltuner please
     
  7. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I did install it yet

    please guide if I change

    innodb_lock_wait_timeout = 50
    long_query_time = 0.1
    wait_timeout = 100
    max_connections = 100


    then it can be useful to decrease load that occur by mysql
     
    #7 fancier, Jul 20, 2013
    Last edited: Jul 20, 2013
  8. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I got that data, now please guide me

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

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 397)
    [--] Data in InnoDB tables: 32M (Tables: 75)
    [--] Data in MEMORY tables: 1M (Tables: 1)
    [!!] Total fragmented tables: 92

    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4d 17h 23m 45s (14M q [34.378 qps], 635K conn, TX: 50B, RX: 3B)
    [--] Reads / Writes: 64% / 36%
    [--] Total buffers: 442.0M global + 12.4M per thread (151 max threads)
    [OK] Maximum possible memory usage: 2.3G (76% of installed RAM)
    [OK] Slow queries: 0% (3K/14M)
    [!!] Highest connection usage: 100% (152/151)
    [OK] Key buffer size / total MyISAM indexes: 384.0M/105.0M
    [OK] Key buffer hit rate: 100.0% (2B cached / 221K reads)
    [OK] Query cache efficiency: 55.5% (5M cached / 9M selects)
    [!!] Query cache prunes per day: 77385
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 255K sorts)
    [OK] Temporary tables created on disk: 7% (5K on disk / 77K total)
    [OK] Thread cache hit rate: 99% (1K created / 635K connections)
    [!!] Table cache hit rate: 2% (512 open / 19K opened)
    [OK] Open file limit used: 7% (788/10K)
    [OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
    [!!] InnoDB data size / buffer pool: 32.4M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 32M)
    table_cache (> 512)
    innodb_buffer_pool_size (>= 32M)
     
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You got very little tables actually,
    Your table cache ratio is small becouse probably many temporary tables in memory, and 7% on disk (which could be optimized)
    But don't worry about table cache ratio, that's not a problem

    I would suggest adjusting settings as below:
    table_open_cache = 1000
    sort_buffer_size = 256K
    read_buffer_size = 128K
    read_rnd_buffer_size = 4M
    thread_cache_size = 20

    thread_cache_size = 286 - remove that, its doubled
    table_cache = 400 - remove that, its the same as table_open_cache

    query_cache_size = 32M
    query_cache_size = 512M

    remove 2 lines of that, leave one
    query_cache_size = 30M

    connect_timeout = 2

    join_buffer_size = 1M

    net_buffer_length = 2K - remove that
    thread_stack = 64K - remove that
    low_priority_updates=1 - remove that
    concurrent_insert=2

    innodb_buffer_pool_size = 384M - this is good, but mysqltuner shows 8M, so it seems like you didnt restart mysql yet after chaning those in my.cnf
    innodb_flush_log_at_trx_commit = 2
    innodb_lock_wait_timeout = 50 - remove that


    when it comes to slow queries
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/log/mysql-slow-query.log > slow.txt

    and copy them here for review
     
    #9 thinkbot, Jul 21, 2013
    Last edited: Jul 21, 2013
  10. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    status.php shows different values, so it seems like server wasn't restarted after last my.cnf changes


    your current slow log is in
    /var/lib/mysql/server-slow.log
    so

    ./pt-query-digest /var/lib/mysql/server-slow.log > slow.txt

    its good to keep that

    slow_query_log_file = server-slow.log

    innodb_buffer_pool_size in current settings from status.php is 8MB

    long_query_time is 10s :|
    horrible, run pt-query-digest and we wil lsee that slow queries
     
  11. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    Yes, finding the reason for the slow query or tuning your MySQL configuration is a much better solution than setting up a cron job to kill them. You risk killing legitimate MySQL processes with such a cron job.

    Thank you.
     
  12. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner

    When i try above my.cnf then I got "The MySQL® server is currently offline. "

    guide where is varable wrong that made "The MySQL® server is currently offline. "

    Mysql works when i put blank my.cnf

    Please guide
     
  13. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Actually I just dont have much knowledge about MYSQL, so I don't able to understand why it got hang in high traffic

    One more strange thing is that it normally hang near about 6:30 pm Indian Time

    So i'm thinking to have cronjob to save mysql from hanging
    thanks
     
  14. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    so you had blank my.cnf previously, or default, not the one you copied

    run this
    Code:
    [mysqld]
    skip-external-locking
    skip-name-resolve
    myisam_use_mmap=1
    
    key_buffer_size = 384M
    table_open_cache = 1000
    sort_buffer_size = 256K
    read_buffer_size = 128K
    read_rnd_buffer_size = 4M
    thread_cache_size = 20
    
    myisam_sort_buffer_size = 64M
    query_cache_size = 30M
    max_heap_table_size = 50M
    max_connections = 100
    wait_timeout = 60
    connect_timeout = 2
    
    query_cache_type = 1
    tmp_table_size = 64M
    join_buffer_size = 1M
    
    concurrent_insert=2
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1		
    log_queries_not_using_indexes = 1
    
    innodb_buffer_pool_size = 384M
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size = 100M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 2
    
    and run pt-query-digest before as wrote
    ./pt-query-digest /var/lib/mysql/server-slow.log > slow.txt

    and copy here slow.txt
     
    #14 thinkbot, Jul 22, 2013
    Last edited: Jul 22, 2013
  15. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Now i have blank my.cnf

    Previously I have default my.cnf

    when I try above variables in my.cnf and reboot server then I got "The MySQL® server is currently offline. "

    Sir my issue is that on high traffic too many tables got open(some running, some sleeping, some locked), that we can see at Server Status it's like Total processes: 146 (10 sleeping, 121 running, 10 locked)


    If i can have some cronjob that check after 1 min or five min and see if there are too many Total Process then it can kill all query of mysql

    please guide or make a cronjob php for me that can do what i needed
     
  16. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Please copy here last 100 lines from error log of mysql

    tail -n 100 /var/lib/mysq/*.err

    to see which variable it doesnt accept



    ----
    Your problem is slow execution of queries, part becouse of not optimized settings, second becouse of queries using temporary tables (means not optimized, slow queries; maybe even not using indexes)
     
  17. fancier

    fancier Member

    Joined:
    Oct 23, 2012
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner

    these are error

    please give some solution
     
  18. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    ok, update my.cnf to the one that I proposed, then run

    rm -rf /var/lib/mysql/ib_logfile*;
    restart mysql

    then run mysqlcheck -r eximstats;

    (since eximstats database seems to be broken)

    you can add to my.cnf after myisam_sort_buffer_size = 64M
    myisam_recover = BACKUP, FORCE
    to have automatic repair, when some MyISAM table breaks

    after restarting mysql, let it run for some time, and make mysqltuner results again
    and run
    cd /root;
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/server-slow.log > slow.txt

    to review slow queries
    you can then copy slow.txt here
     
Loading...

Share This Page