cron job to kill slow query issue

fancier · Jul 19, 2013

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

STS Admin · Jul 19, 2013

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.

while( $row = mysql_fetch_array( $result, $con ) )

fancier · Jul 20, 2013

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

thinkbot · Jul 20, 2013

if you got slow queries, post your my.cnf config, hardware spec etc and mysqltuner result

fancier · Jul 20, 2013

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

thinkbot · Jul 20, 2013

and mysqltuner please

fancier · Jul 20, 2013

thinkbot said:
and mysqltuner please

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

fancier · Jul 21, 2013

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)

thinkbot · Jul 21, 2013

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

thinkbot · Jul 21, 2013

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

cPanelMichael · Jul 22, 2013

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.

fancier · Jul 22, 2013

[mysqld]
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
query_cache_size = 30M
max_heap_table_size = 16M
max_connections = 30
interactive_timeout = 25
wait_timeout = 30
connect_timeout = 2
max_connect_errors = 10
query_cache_type = 1
tmp_table_size = 64M
join_buffer_size = 1M
concurrent_insert=2
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_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

[mysql]
no-auto-rehash

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

fancier · Jul 22, 2013

cPanelMichael said:
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.

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

thinkbot · Jul 22, 2013

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

fancier · Jul 23, 2013

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

thinkbot · Jul 23, 2013

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)

fancier · Jul 23, 2013

130722 16:32:44 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:32:44 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:32:44 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:32:44 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:32:44 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:32:44 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:33:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:33:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:33:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:33:31 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:33:45 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:33:45 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:33:45 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:34:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:34:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:34:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:35 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:35 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:35 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:45 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:46 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:46 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 16:35:50 [Note] /usr/sbin/mysqld: Normal shutdown

130722 16:35:50 [Note] Event Scheduler: Purging the queue. 0 events
130722 16:35:52 InnoDB: Starting shutdown...
130722 16:35:55 InnoDB: Shutdown completed; log sequence number 13 2934210248
130722 16:35:55 [Note] /usr/sbin/mysqld: Shutdown complete

130722 16:35:55 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 16:38:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 16:38:04 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 16:38:04 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 16:38:04 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 16:38:04 [ERROR] Aborting

130722 16:38:04 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:19:21 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:19:22 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:19:22 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:19:22 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:19:22 [ERROR] Aborting

130722 17:19:23 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:20:56 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:20:56 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:20:56 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:20:56 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:20:56 [ERROR] Aborting

130722 17:20:56 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:33:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:33:49 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:33:49 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:33:49 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:33:49 [ERROR] Aborting

130722 17:33:49 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:34:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:34:15 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:34:15 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:34:15 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:34:15 [ERROR] Aborting

130722 17:34:15 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:35:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:35:11 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:35:11 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:35:11 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:35:11 [ERROR] Aborting

130722 17:35:11 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:35:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:35:46 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:35:46 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:35:46 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:35:46 [ERROR] Aborting

130722 17:35:46 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:39:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:39:53 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:39:53 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:39:53 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:39:53 [ERROR] Aborting

130722 17:39:53 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:40:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:40:24 [Note] Plugin 'FEDERATED' is disabled.
130722 17:40:24 InnoDB: Initializing buffer pool, size = 8.0M
130722 17:40:24 InnoDB: Completed initialization of buffer pool
130722 17:40:24 InnoDB: Started; log sequence number 13 2934210248
130722 17:40:24 [Note] Event Scheduler: Loaded 0 events
130722 17:40:24 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.70-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130722 17:41:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:13 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:13 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:13 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:13 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:13 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:13 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:13 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:13 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:14 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:14 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:14 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:14 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:14 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:15 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:15 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:15 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:15 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:15 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:15 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:15 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:41:18 [Note] /usr/sbin/mysqld: Normal shutdown

130722 17:41:18 [Note] Event Scheduler: Purging the queue. 0 events
130722 17:41:20 InnoDB: Starting shutdown...
130722 17:41:22 InnoDB: Shutdown completed; log sequence number 13 2934304301
130722 17:41:22 [Note] /usr/sbin/mysqld: Shutdown complete

130722 17:41:22 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:41:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:41:23 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:41:23 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:41:23 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:41:23 [ERROR] Aborting

130722 17:41:23 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:42:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:42:30 [Note] Plugin 'FEDERATED' is disabled.
130722 17:42:30 InnoDB: Initializing buffer pool, size = 8.0M
130722 17:42:30 InnoDB: Completed initialization of buffer pool
130722 17:42:31 InnoDB: Started; log sequence number 13 2934304301
130722 17:42:31 [Note] Event Scheduler: Loaded 0 events
130722 17:42:31 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.70-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130722 17:43:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:15 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:15 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:18 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:18 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:18 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:18 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:19 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:19 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:19 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:20 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:20 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:20 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:22 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:22 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:43:59 [Note] /usr/sbin/mysqld: Normal shutdown

130722 17:43:59 [Note] Event Scheduler: Purging the queue. 0 events
130722 17:44:01 InnoDB: Starting shutdown...
130722 17:44:06 InnoDB: Shutdown completed; log sequence number 13 2934359876
130722 17:44:06 [Note] /usr/sbin/mysqld: Shutdown complete

130722 17:44:06 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:44:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:44:07 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:44:07 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:44:07 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:44:07 [ERROR] Aborting

130722 17:44:07 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:52:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:52:02 [Note] Plugin 'FEDERATED' is disabled.
130722 17:52:02 InnoDB: Initializing buffer pool, size = 8.0M
130722 17:52:02 InnoDB: Completed initialization of buffer pool
130722 17:52:02 InnoDB: Started; log sequence number 13 2934359876
130722 17:52:02 [Note] Event Scheduler: Loaded 0 events
130722 17:52:02 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.70-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130722 17:53:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:53:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:53:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:53:16 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:53:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:53:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:54:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:54:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:54:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:54:03 [Note] /usr/sbin/mysqld: Normal shutdown

130722 17:54:03 [Note] Event Scheduler: Purging the queue. 0 events
130722 17:54:05 InnoDB: Starting shutdown...
130722 17:54:07 InnoDB: Shutdown completed; log sequence number 13 2934360113
130722 17:54:07 [Note] /usr/sbin/mysqld: Shutdown complete

130722 17:54:07 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:54:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:54:08 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130722 17:54:08 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
Unknown suffix 'A' used for variable 'concurrent-insert' (value 'ALWAYS')
130722 17:54:08 [ERROR] /usr/sbin/mysqld: Error while setting value 'ALWAYS' to 'concurrent-insert'
130722 17:54:08 [ERROR] Aborting

130722 17:54:08 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:55:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:55:28 [Note] Plugin 'FEDERATED' is disabled.
130722 17:55:28 InnoDB: Initializing buffer pool, size = 8.0M
130722 17:55:28 InnoDB: Completed initialization of buffer pool
130722 17:55:28 InnoDB: Started; log sequence number 13 2934360113
130722 17:55:28 [Note] Event Scheduler: Loaded 0 events
130722 17:55:28 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.70-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130722 17:56:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:56:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:56:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:56:19 [Note] /usr/sbin/mysqld: Normal shutdown

130722 17:56:19 [Note] Event Scheduler: Purging the queue. 0 events
130722 17:56:19 InnoDB: Starting shutdown...
130722 17:56:23 InnoDB: Shutdown completed; log sequence number 13 2934360113
130722 17:56:23 [Note] /usr/sbin/mysqld: Shutdown complete

130722 17:56:23 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 17:56:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 17:56:24 [Note] Plugin 'FEDERATED' is disabled.
130722 17:56:24 InnoDB: Initializing buffer pool, size = 8.0M
130722 17:56:24 InnoDB: Completed initialization of buffer pool
130722 17:56:24 InnoDB: Started; log sequence number 13 2934360113
130722 17:56:24 [Note] Event Scheduler: Loaded 0 events
130722 17:56:24 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.70-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130722 17:57:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:57:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:57:02 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:58:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:58:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:58:01 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:58:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:58:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:58:17 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired
130722 17:58:39 [Note] /usr/sbin/mysqld: Normal shutdown

130722 17:58:39 [Note] Event Scheduler: Purging the queue. 0 events
130722 17:58:41 InnoDB: Starting shutdown...
130722 17:58:44 InnoDB: Shutdown completed; log sequence number 13 2936579622
130722 17:58:44 [Note] /usr/sbin/mysqld: Shutdown complete

130722 17:58:44 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130722 18:01:10 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130722 18:01:10 [Note] Plugin 'FEDERATED' is disabled.
130722 18:01:10 InnoDB: Initializing buffer pool, size = 8.0M
130722 18:01:10 InnoDB: Completed initialization of buffer pool
130722 18:01:10 InnoDB: Started; log sequence number 13 2936579622
130722 18:01:10 [Note] Event Scheduler: Loaded 0 events
130722 18:01:10 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.70-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130723 4:25:42 [Note] /usr/sbin/mysqld: Normal shutdown

130723 4:25:42 [Note] Event Scheduler: Purging the queue. 0 events
130723 4:25:42 InnoDB: Starting shutdown...
130723 4:25:46 InnoDB: Shutdown completed; log sequence number 13 3141253536
130723 4:25:46 [Note] /usr/sbin/mysqld: Shutdown complete

130723 04:25:46 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130723 04:27:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130723 4:27:53 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130723 4:27:53 [Note] Plugin 'FEDERATED' is disabled.
130723 4:27:54 InnoDB: Initializing buffer pool, size = 384.0M
130723 4:27:54 InnoDB: Completed initialization of buffer pool
InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size
InnoDB: 6272 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 128000 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
130723 4:27:54 [ERROR] Plugin 'InnoDB' init function returned error.
130723 4:27:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130723 4:27:54 [ERROR] /usr/sbin/mysqld: unknown variable 'slow_query_log_microseconds_timestamp=1'
130723 4:27:54 [ERROR] Aborting

130723 4:27:54 [Note] /usr/sbin/mysqld: Shutdown complete

130723 04:27:54 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130723 04:34:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130723 4:34:09 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
130723 4:34:09 [Note] Plugin 'FEDERATED' is disabled.
130723 4:34:09 InnoDB: Initializing buffer pool, size = 384.0M
130723 4:34:09 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 104857600 bytes!
130723 4:34:09 [ERROR] Plugin 'InnoDB' init function returned error.
130723 4:34:09 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130723 4:34:09 [ERROR] /usr/sbin/mysqld: unknown variable 'slow_query_log_microseconds_timestamp=1'
130723 4:34:09 [ERROR] Aborting

130723 4:34:09 [Note] /usr/sbin/mysqld: Shutdown complete

130723 04:34:09 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130723 04:40:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130723 4:40:59 [Note] Plugin 'FEDERATED' is disabled.
130723 4:40:59 InnoDB: Initializing buffer pool, size = 384.0M
130723 4:40:59 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 104857600 bytes!
130723 4:40:59 [ERROR] Plugin 'InnoDB' init function returned error.
130723 4:40:59 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130723 4:40:59 [ERROR] /usr/sbin/mysqld: unknown variable 'slow_query_log_microseconds_timestamp=1'
130723 4:40:59 [ERROR] Aborting

130723 4:40:59 [Note] /usr/sbin/mysqld: Shutdown complete

130723 04:41:00 mysqld_safe mysqld from pid file /var/lib/mysql/server.realinfo.tv.pid ended
130723 04:45:19 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130723 4:45:20 [Note] Plugin 'FEDERATED' is disabled.
130723 4:45:21 InnoDB: Initializing buffer pool, size = 8.0M
130723 4:45:21 InnoDB: Completed initialization of buffer pool
130723 4:45:21 InnoDB: Started; log sequence number 13 3141253536
130723 4:45:22 [Note] Event Scheduler: Loaded 0 events
130723 4:45:22 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.70-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

these are error

please give some solution

thinkbot · Jul 23, 2013

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

Thread starter	Similar threads	Forum	Replies	Date
M	Auto-deploy from Github, workaround with cronjob and bash	Workarounds and Optimization	2	Jul 11, 2020
N	Configure cPanel Cron Jobs API Command?	Workarounds and Optimization	1	Mar 7, 2020
	Cron Job to auto clean Magento Session files to save inodes	Workarounds and Optimization	3	Apr 29, 2019
A	Softaculous Cron Job Errors with Perl 5.8	Workarounds and Optimization	4	Aug 31, 2017
M	Cronjob that updates cached file and takes about 1.5 minutes	Workarounds and Optimization	1	Sep 25, 2016

Search

Search

cron job to kill slow query issue

fancier

Member

STS Admin

Well-Known Member

fancier

Member

thinkbot

Well-Known Member

fancier

Member

thinkbot

Well-Known Member

fancier

Member

fancier

Member

thinkbot

Well-Known Member

thinkbot

Well-Known Member

cPanelMichael

Administrator

fancier

Member

fancier

Member

thinkbot

Well-Known Member

fancier

Member

thinkbot

Well-Known Member

fancier

Member

thinkbot

Well-Known Member