fancier

Member
Oct 23, 2012
13
0
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
 

STS Admin

Well-Known Member
Jul 8, 2012
46
0
56
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.

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

fancier

Member
Oct 23, 2012
13
0
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
 

fancier

Member
Oct 23, 2012
13
0
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
 

fancier

Member
Oct 23, 2012
13
0
1
cPanel Access Level
Website Owner
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
 
Last edited:

fancier

Member
Oct 23, 2012
13
0
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)
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
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
 
Last edited:

thinkbot

Well-Known Member
Oct 30, 2012
326
1
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
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,201
363
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

Member
Oct 23, 2012
13
0
1
cPanel Access Level
Website Owner
[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

Member
Oct 23, 2012
13
0
1
cPanel Access Level
Website Owner
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

Well-Known Member
Oct 30, 2012
326
1
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
 
Last edited:

fancier

Member
Oct 23, 2012
13
0
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
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
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)
 

fancier

Member
Oct 23, 2012
13
0
1
cPanel Access Level
Website Owner
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

Well-Known Member
Oct 30, 2012
326
1
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