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
     
    #1 fancier, Jul 19, 2013
  2. STS Admin

    STS Admin Well-Known Member

    Joined:
    Jul 8, 2012
    Messages:
    46
    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.

     
    #2 STS Admin, Jul 19, 2013
  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
     
    #3 fancier, Jul 20, 2013
  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
     
    #4 thinkbot, Jul 20, 2013
  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
     
    #5 fancier, Jul 20, 2013
  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
     
    #6 thinkbot, Jul 20, 2013
  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)
     
    #8 fancier, Jul 21, 2013
  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
     
    #10 thinkbot, Jul 21, 2013
  11. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    36,345
    Likes Received:
    1,209
    Trophy Points:
    363
    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.
     
    #11 cPanelMichael, Jul 22, 2013
  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
     
    #12 fancier, Jul 22, 2013
  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
     
    #13 fancier, Jul 22, 2013
  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
     
    #15 fancier, Jul 23, 2013
  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)
     
    #16 thinkbot, Jul 23, 2013
  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
     
    #17 fancier, Jul 23, 2013
  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
     
    #18 thinkbot, Jul 23, 2013
(You must log in or sign up to post here.)
Loading...
Similar Threads - cron kill slow
  1. vronn

    cronjob killed unexpected

    vronn, Dec 22, 2016, in forum: General Discussion
    Replies:
    2
    Views:
    283
    cPanelMichael
    Dec 23, 2016
  2. Jerms

    Cron job killing mysql - /usr/local/cpanel/scripts/update_db_cache

    Jerms, Oct 1, 2015, in forum: Database Discussions
    Replies:
    3
    Views:
    1,283
    cPanelMichael
    Oct 5, 2015
  3. rudtek

    Cron Dameon emails -ambiguous redirect

    rudtek, Jul 19, 2017 at 5:57 PM, in forum: General Discussion
    Replies:
    3
    Views:
    59
    cPanelMichael
    Jul 20, 2017 at 11:12 AM
  4. unlockr

    SOLVED crons stop working after upgrade from 5.4 to 5.6.30!

    unlockr, Jul 17, 2017, in forum: General Discussion
    Replies:
    5
    Views:
    75
    cPanelMichael
    Jul 19, 2017 at 2:56 PM
  5. andysag

    WHMCS cron job not working error - jailshell/5: command not found

    andysag, Jul 1, 2017, in forum: General Discussion
    Replies:
    4
    Views:
    92
    rpvw
    Jul 1, 2017

Share This Page