Skip to main content

MYSQL : High CPU Usage

abdelhost77
Dear Mysql Experts , My server last days has anormal high cpu consumption from mysql, Server 4CPU 3G, RAM 8G Please help optimize, here after mysqltuner.pl and etc/my.cnf [mysqld] innodb_file_per_table=1 set-variable = max_connections=200 log-slow-queries=/var/lib/mysql/slow.log safe-show-database query_cache_size=128M join_buffer_size=50M tmp_table_size=128M max_heap_table_size=128M table_cache=10K long_query_time =10 open_files_limit=50000
################################ Mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at

Comments

17 comments

Date Votes
  • thinkbot
    Your settings are horrible Set: [mysqld] skip-name-resolve myisam_use_mmap=1 max_connections=100 max_user_connections = 50 log-slow-queries=mysql-slow.log long_query_time = 0.1 safe-show-database query_cache_type = 1 query_cache_size = 75M query_cache_limit = 2M join_buffer_size=1M read_rnd_buffer_size=1M max_allowed_packet = 10M tmp_table_size=25M max_heap_table_size=25M table_open_cache = 7500 thread_cache_size = 25 open_files_limit=30000 key_buffer_size = 500M myisam_sort_buffer_size = 256M innodb_file_per_table=1 innodb_buffer_pool_size = 1G innodb_log_file_size = 64M innodb_log_buffer_size = 8M remoe old log files rm -rf /var/lib/mysql/ib_logfile* restart mysql service mysql restart
    0
  • abdelhost77
    Thanks thinkbot , just to avoid any surprise i would like to confirm you that im in CEntos 64Bits So you confirm to put exactly the below codes in /etc/my.cnf : [mysqld] skip-name-resolve myisam_use_mmap=1 max_connections=100 max_user_connections = 50 log-slow-queries=mysql-slow.log long_query_time = 0.1 safe-show-database query_cache_type = 1 query_cache_size = 75M query_cache_limit = 2M join_buffer_size=1M read_rnd_buffer_size=1M max_allowed_packet = 10M tmp_table_size=25M max_heap_table_size=25M table_open_cache = 7500 thread_cache_size = 25 open_files_limit=30000 key_buffer_size = 500M myisam_sort_buffer_size = 256M innodb_file_per_table=1 innodb_buffer_pool_size = 1G innodb_log_file_size = 64M innodb_log_buffer_size = 8M
    Then remove LOGS and restart MYSQL ??
    0
  • thinkbot
    yes, exactly
    0
  • cPanelMichael
    Hello :) Feel free to apply the new settings and let us know if you experience better results. Thank you.
    0
  • abdelhost77
    Hello, The new settings were very helpful from CPU point of view, in fact the %CPU has decreased, but what i see actually is that Mysql eating more RAM ( around 33 % of RAM in average ) PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 27394 mysql 20 0 5086m 2.4g 239m S 6.0 31.9 336:36.98 mysqld What are settings impacting RAM consumption, i guess innodb_buffer_pool_size ? Can i decrease it from 1G to 500M fro example ? any other ideas please ? Thanks
    0
  • thinkbot
    Nope, since [!!] InnoDB data size / buffer pool: 882.1M/8.0M This means, there is at least 882 MB of innodb data that can be buffered in RAM memory to speed up the operations It's always a cost when you optimize, you just need to decide whether its CPU/RAM/DISK smaller buffers (smaller RAM usage) means bigger Disk AND CPU usage And remember that this RAM usage in ps command represents usage with buffers/cache which OS will clear when it needs it, so don't worry You should worry only when it starts swapping You can set in the end of /etc/smartctl.conf vm.swappiness = 10 then save and run smartctl -p [url=http://en.wikipedia.org/wiki/Swappiness]Swappiness - Wikipedia, the free encyclopedia
    0
  • techwelfare
    Hi we did this exactly, our server is -- removed --, standard plan. after doing everything noted here when i go to restart mysql it says below Waiting for mysql to restart...............................................................finished. mysql has failed, please contact the sysadmin (result was "mysql is not running"). so my site is stopped now, i am in bif trouble could anybody assist please? [quote="thinkbot, post: 1509431">Your settings are horrible Set: [mysqld] skip-name-resolve myisam_use_mmap=1 max_connections=100 max_user_connections = 50 log-slow-queries=mysql-slow.log long_query_time = 0.1 safe-show-database query_cache_type = 1 query_cache_size = 75M query_cache_limit = 2M join_buffer_size=1M read_rnd_buffer_size=1M max_allowed_packet = 10M tmp_table_size=25M max_heap_table_size=25M table_open_cache = 7500 thread_cache_size = 25 open_files_limit=30000 key_buffer_size = 500M myisam_sort_buffer_size = 256M innodb_file_per_table=1 innodb_buffer_pool_size = 1G innodb_log_file_size = 64M innodb_log_buffer_size = 8M remoe old log files rm -rf /var/lib/mysql/ib_logfile* restart mysql service mysql restart
    0
  • thinkbot
    please copy the contents of mysql error file tail -n 100 /var/lib/mysql/*.err temporary remove this line myisam_use_mmap probably this variable is not supported in your mysql 5.1
    0
  • techwelfare
    hi i deleted the line myisam_use_mmap=1 but still the same problem. i do not understand what you mean by please copy the contents of mysql error file tail -n 100 /var/lib/mysql/*.err
    0
  • thinkbot
    mysql generates logs, where it will write whats the problem, so please copy the content of /var/lib/mysql/*.err you can run it like this tail -n 100 /var/lib/mysql/*.err and copy here [COLOR="silver">- - - Updated - - - and did you remove old log files as I wrote few posts before ? remoe old log files rm -rf /var/lib/mysql/ib_logfile* restart mysql service mysql restart
    0
  • techwelfare
    here is the log---------------- ******* 140326 09:56:38 mysqld_safe mysqld from pid file /var/lib/mysql/server.cashfire.org.pid ended 140326 09:56:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140326 9:56:39 [Note] Plugin 'FEDERATED' is disabled. 140326 9:56:39 InnoDB: The InnoDB memory heap is disabled 140326 9:56:39 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140326 9:56:39 InnoDB: Compressed tables use zlib 1.2.3 140326 9:56:39 InnoDB: Using Linux native AIO 140326 9:56:39 InnoDB: Initializing buffer pool, size = 128.0M 140326 9:56:39 InnoDB: Completed initialization of buffer pool 140326 9:56:39 InnoDB: highest supported file format is Barracuda. 140326 9:56:39 InnoDB: Waiting for the background threads to start 140326 9:56:40 InnoDB: 5.5.36 started; log sequence number 3447912 140326 9:56:40 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 140326 9:56:40 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 140326 9:56:40 [Note] Server socket created on IP: '0.0.0.0'. 140326 9:56:40 [Note] Event Scheduler: Loaded 0 events 140326 9:56:40 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.36-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140326 9:58:40 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/files' is marked as crashed and should be repaired 140326 9:58:40 [Warning] Checking table: './buxmillc_cashfiredb/files' 140326 9:59:03 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/users_earn' is marked as crashed and should be repaired 140326 9:59:03 [Warning] Checking table: './buxmillc_cashfiredb/users_earn' 140326 9:59:03 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/users_earn_ref' is marked as crashed and should be repaired 140326 9:59:03 [Warning] Checking table: './buxmillc_cashfiredb/users_earn_ref' 140326 10:04:37 [ERROR] /usr/sbin/mysqld: Table './eximstats/smtp' is marked as crashed and should be repaired 140326 10:04:37 [Warning] Checking table: './eximstats/smtp' 140326 10:04:37 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired 140326 10:04:37 [Warning] Checking table: './eximstats/sends' 140326 10:45:35 mysqld_safe Number of processes running now: 0 140326 10:45:35 mysqld_safe mysqld restarted 140326 10:45:35 [Note] Plugin 'FEDERATED' is disabled. 140326 10:45:35 InnoDB: The InnoDB memory heap is disabled 140326 10:45:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140326 10:45:35 InnoDB: Compressed tables use zlib 1.2.3 140326 10:45:35 InnoDB: Using Linux native AIO 140326 10:45:35 InnoDB: Initializing buffer pool, size = 128.0M 140326 10:45:35 InnoDB: Completed initialization of buffer pool 140326 10:45:35 InnoDB: highest supported file format is Barracuda. 140326 10:45:36 InnoDB: Waiting for the background threads to start 140326 10:45:37 InnoDB: 5.5.36 started; log sequence number 3447912 140326 10:45:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 140326 10:45:37 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 140326 10:45:37 [Note] Server socket created on IP: '0.0.0.0'. 140326 10:45:37 [ERROR] /usr/sbin/mysqld: Table './mysql/db' is marked as crashed and should be repaired 140326 10:45:37 [Warning] Checking table: './mysql/db' 140326 10:45:37 [ERROR] 1 client is using or hasn't closed the table properly 140326 10:45:37 [Note] Event Scheduler: Loaded 0 events 140326 10:45:37 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.36-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140326 10:45:37 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/links' is marked as crashed and should be repaired 140326 10:45:37 [Warning] Checking table: './buxmillc_cashfiredb/links' 140326 10:45:37 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/ips' is marked as crashed and should be repaired 140326 10:45:37 [Warning] Checking table: './buxmillc_cashfiredb/ips' 140326 12:37:47 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cfforumdb/phpbb_config' is marked as crashed and should be repaired 140326 12:37:47 [Warning] Checking table: './buxmillc_cfforumdb/phpbb_config' 140326 12:37:47 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cfforumdb/phpbb_users' is marked as crashed and should be repaired 140326 12:37:47 [Warning] Checking table: './buxmillc_cfforumdb/phpbb_users' 140326 12:37:47 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cfforumdb/phpbb_sessions' is marked as crashed and should be repaired 140326 12:37:47 [Warning] Checking table: './buxmillc_cfforumdb/phpbb_sessions' 140327 05:48:27 mysqld_safe Number of processes running now: 0 140327 05:48:27 mysqld_safe mysqld restarted 140327 5:48:27 [Note] Plugin 'FEDERATED' is disabled. 140327 5:48:27 InnoDB: The InnoDB memory heap is disabled 140327 5:48:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140327 5:48:27 InnoDB: Compressed tables use zlib 1.2.3 140327 5:48:27 InnoDB: Using Linux native AIO 140327 5:48:27 InnoDB: Initializing buffer pool, size = 128.0M 140327 5:48:27 InnoDB: Completed initialization of buffer pool 140327 5:48:27 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 140327 5:48:27 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 140327 5:48:28 InnoDB: Waiting for the background threads to start 140327 5:48:29 InnoDB: 5.5.36 started; log sequence number 3447922 140327 5:48:29 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 140327 5:48:29 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 140327 5:48:29 [Note] Server socket created on IP: '0.0.0.0'. 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './mysql/db' is marked as crashed and should be repaired 140327 5:48:29 [Warning] Checking table: './mysql/db' 140327 5:48:29 [ERROR] 1 client is using or hasn't closed the table properly 140327 5:48:29 [Note] Event Scheduler: Loaded 0 events 140327 5:48:29 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.36-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/links' is marked as crashed and should be repaired 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/links' is marked as crashed and should be repaired 140327 5:48:29 [Warning] Checking table: './buxmillc_cashfiredb/links' 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/users' is marked as crashed and should be repaired 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/users' is marked as crashed and should be repaired 140327 5:48:29 [Warning] Checking table: './buxmillc_cashfiredb/users' 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/downloads' is marked as crashed and should be repaired 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/ips' is marked as crashed and should be repaired 140327 5:48:29 [Warning] Checking table: './buxmillc_cashfiredb/downloads' 140327 5:48:29 [Warning] Checking table: './buxmillc_cashfiredb/ips' 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/users_earn' is marked as crashed and should be repaired 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/users_earn' is marked as crashed and should be repaired 140327 5:48:29 [Warning] Checking table: './buxmillc_cashfiredb/users_earn' 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/users_earn_ref' is marked as crashed and should be repaired 140327 5:48:29 [Warning] Checking table: './buxmillc_cashfiredb/users_earn_ref' 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/files' is marked as crashed and should be repaired 140327 5:48:29 [Warning] Checking table: './buxmillc_cashfiredb/files' 140327 5:48:29 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/campaigns' is marked as crashed and should be repaired 140327 5:48:29 [Warning] Checking table: './buxmillc_cashfiredb/campaigns' 140327 05:48:33 mysqld_safe Number of processes running now: 0 140327 05:48:33 mysqld_safe mysqld restarted 140327 5:48:33 [Note] Plugin 'FEDERATED' is disabled. 140327 5:48:33 InnoDB: The InnoDB memory heap is disabled 140327 5:48:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140327 5:48:33 InnoDB: Compressed tables use zlib 1.2.3 140327 5:48:33 InnoDB: Using Linux native AIO 140327 5:48:34 InnoDB: Initializing buffer pool, size = 128.0M 140327 5:48:34 InnoDB: Completed initialization of buffer pool 140327 5:48:34 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 140327 5:48:34 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 140327 5:48:34 InnoDB: Waiting for the background threads to start 140327 5:48:35 InnoDB: 5.5.36 started; log sequence number 3447922 140327 5:48:35 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 140327 5:48:35 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 140327 5:48:35 [Note] Server socket created on IP: '0.0.0.0'. 140327 5:48:35 [Note] Event Scheduler: Loaded 0 events 140327 5:48:35 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.36-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140327 5:48:35 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/links' is marked as crashed and should be repaired 140327 5:48:35 [Warning] Checking table: './buxmillc_cashfiredb/links' 140327 5:48:38 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/ips' is marked as crashed and should be repaired 140327 5:48:38 [Warning] Checking table: './buxmillc_cashfiredb/ips' 140327 05:48:40 mysqld_safe Number of processes running now: 0 140327 05:48:40 mysqld_safe mysqld restarted 140327 5:48:40 [Note] Plugin 'FEDERATED' is disabled. 140327 5:48:40 InnoDB: The InnoDB memory heap is disabled 140327 5:48:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140327 5:48:40 InnoDB: Compressed tables use zlib 1.2.3 140327 5:48:40 InnoDB: Using Linux native AIO 140327 5:48:40 InnoDB: Initializing buffer pool, size = 128.0M 140327 5:48:40 InnoDB: Completed initialization of buffer pool 140327 5:48:40 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 140327 5:48:40 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 140327 5:48:40 InnoDB: Waiting for the background threads to start 140327 5:48:41 InnoDB: 5.5.36 started; log sequence number 3447922 140327 5:48:41 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 140327 5:48:41 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 140327 5:48:41 [Note] Server socket created on IP: '0.0.0.0'. 140327 5:48:41 [Note] Event Scheduler: Loaded 0 events 140327 5:48:41 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.36-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140327 5:48:41 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/files' is marked as crashed and should be repaired 140327 5:48:41 [Warning] Checking table: './buxmillc_cashfiredb/files' 140327 05:48:46 mysqld_safe Number of processes running now: 0 140327 05:48:46 mysqld_safe mysqld restarted 140327 5:48:46 [Note] Plugin 'FEDERATED' is disabled. 140327 5:48:46 InnoDB: The InnoDB memory heap is disabled 140327 5:48:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140327 5:48:46 InnoDB: Compressed tables use zlib 1.2.3 140327 5:48:46 InnoDB: Using Linux native AIO 140327 5:48:46 InnoDB: Initializing buffer pool, size = 128.0M 140327 5:48:46 InnoDB: Completed initialization of buffer pool 140327 5:48:46 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 140327 5:48:46 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 140327 5:48:46 InnoDB: Waiting for the background threads to start 140327 5:48:47 InnoDB: 5.5.36 started; log sequence number 3447922 140327 5:48:47 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 140327 5:48:47 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 140327 5:48:47 [Note] Server socket created on IP: '0.0.0.0'. 140327 5:48:47 [Note] Event Scheduler: Loaded 0 events 140327 5:48:47 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.36-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140327 05:48:52 mysqld_safe Number of processes running now: 0 140327 05:48:52 mysqld_safe mysqld restarted 140327 5:48:52 [Note] Plugin 'FEDERATED' is disabled. 140327 5:48:52 InnoDB: The InnoDB memory heap is disabled 140327 5:48:52 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140327 5:48:52 InnoDB: Compressed tables use zlib 1.2.3 140327 5:48:52 InnoDB: Using Linux native AIO 140327 5:48:52 InnoDB: Initializing buffer pool, size = 128.0M 140327 5:48:52 InnoDB: Completed initialization of buffer pool 140327 5:48:52 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 140327 5:48:52 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 140327 5:48:52 InnoDB: Waiting for the background threads to start 140327 5:48:53 InnoDB: 5.5.36 started; log sequence number 3447922 140327 5:48:53 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 140327 5:48:53 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 140327 5:48:53 [Note] Server socket created on IP: '0.0.0.0'. 140327 5:48:53 [Note] Event Scheduler: Loaded 0 events 140327 5:48:53 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.36-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140327 5:48:53 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/files' is marked as crashed and should be repaired 140327 5:48:53 [Warning] Checking table: './buxmillc_cashfiredb/files' 140327 5:48:55 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/ips' is marked as crashed and should be repaired 140327 5:48:55 [Warning] Checking table: './buxmillc_cashfiredb/ips' 140327 05:48:59 mysqld_safe Number of processes running now: 0 140327 05:48:59 mysqld_safe mysqld restarted 140327 5:48:59 [Note] Plugin 'FEDERATED' is disabled. 140327 5:48:59 InnoDB: The InnoDB memory heap is disabled 140327 5:48:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140327 5:48:59 InnoDB: Compressed tables use zlib 1.2.3 140327 5:48:59 InnoDB: Using Linux native AIO 140327 5:48:59 InnoDB: Initializing buffer pool, size = 128.0M 140327 5:48:59 InnoDB: Completed initialization of buffer pool 140327 5:48:59 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 140327 5:48:59 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 140327 5:49:00 InnoDB: Waiting for the background threads to start 140327 5:49:01 InnoDB: 5.5.36 started; log sequence number 3447922 140327 5:49:01 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 140327 5:49:01 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 140327 5:49:01 [Note] Server socket created on IP: '0.0.0.0'. 140327 5:49:01 [Note] Event Scheduler: Loaded 0 events 140327 5:49:01 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.36-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140327 5:49:01 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/ips' is marked as crashed and should be repaired 140327 5:49:01 [Warning] Checking table: './buxmillc_cashfiredb/ips' 140327 5:49:01 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cashfiredb/files' is marked as crashed and should be repaired 140327 5:49:01 [Warning] Checking table: './buxmillc_cashfiredb/files' 140327 5:51:02 [ERROR] /usr/sbin/mysqld: Table './cphulkd/logins' is marked as crashed and should be repaired 140327 5:51:02 [Warning] Checking table: './cphulkd/logins' 140327 5:51:02 [ERROR] /usr/sbin/mysqld: Table './cphulkd/brutes' is marked as crashed and should be repaired 140327 5:51:02 [Warning] Checking table: './cphulkd/brutes' 140327 5:52:40 [ERROR] /usr/sbin/mysqld: Table './eximstats/smtp' is marked as crashed and should be repaired 140327 5:52:40 [Warning] Checking table: './eximstats/smtp' 140327 5:52:40 [ERROR] /usr/sbin/mysqld: Table './eximstats/sends' is marked as crashed and should be repaired 140327 5:52:40 [Warning] Checking table: './eximstats/sends' 140327 5:52:40 [ERROR] /usr/sbin/mysqld: Table './eximstats/defers' is marked as crashed and should be repaired 140327 5:52:40 [Warning] Checking table: './eximstats/defers' 140327 5:52:40 [ERROR] /usr/sbin/mysqld: Table './eximstats/failures' is marked as crashed and should be repaired 140327 5:52:40 [Warning] Checking table: './eximstats/failures' 140327 6:47:09 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cfforumdb/phpbb_config' is marked as crashed and should be repaired 140327 6:47:09 [Warning] Checking table: './buxmillc_cfforumdb/phpbb_config' 140327 6:47:09 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cfforumdb/phpbb_users' is marked as crashed and should be repaired 140327 6:47:09 [Warning] Checking table: './buxmillc_cfforumdb/phpbb_users' 140327 6:47:09 [ERROR] /usr/sbin/mysqld: Table './buxmillc_cfforumdb/phpbb_sessions' is marked as crashed and should be repaired 140327 6:47:09 [Warning] Checking table: './buxmillc_cfforumdb/phpbb_sessions' 140327 10:03:19 [Note] /usr/sbin/mysqld: Normal shutdown 140327 10:03:19 [Note] Event Scheduler: Purging the queue. 0 events 140327 10:03:21 [Warning] /usr/sbin/mysqld: Forcing close of thread 275801 user: 'leechprotect' 140327 10:03:21 [Warning] /usr/sbin/mysqld: Forcing close of thread 129567 user: 'eximstats' 140327 10:03:21 InnoDB: Starting shutdown... 140327 10:03:22 InnoDB: Shutdown completed; log sequence number 3447922 140327 10:03:22 [Note] /usr/sbin/mysqld: Shutdown complete 140327 10:03:22 mysqld_safe mysqld from pid file /var/lib/mysql/server.cashfire.org.pid ended 140327 10:03:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140327 10:03:24 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead. 140327 10:03:24 [Note] Plugin 'FEDERATED' is disabled. 140327 10:03:24 InnoDB: The InnoDB memory heap is disabled 140327 10:03:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140327 10:03:24 InnoDB: Compressed tables use zlib 1.2.3 140327 10:03:24 InnoDB: Using Linux native AIO 140327 10:03:24 InnoDB: Initializing buffer pool, size = 1.0G 140327 10:03:24 InnoDB: Completed initialization of buffer pool 140327 10:03:24 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 64 MB InnoDB: Database physically writes the file full: wait... InnoDB: Error: log file ./ib_logfile1 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 67108864 bytes! 140327 10:03:25 [ERROR] Plugin 'InnoDB' init function returned error. 140327 10:03:25 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140327 10:03:25 [ERROR] /usr/sbin/mysqld: unknown option '--safe-show-database' 140327 10:03:25 [ERROR] Aborting ******* 140327 14:35:10 mysqld_safe mysqld from pid file /var/lib/mysql/server.cashfire.org.pid ended 140327 15:25:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140327 15:25:06 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead. 140327 15:25:06 [Note] Plugin 'FEDERATED' is disabled. 140327 15:25:06 InnoDB: The InnoDB memory heap is disabled 140327 15:25:06 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140327 15:25:06 InnoDB: Compressed tables use zlib 1.2.3 140327 15:25:06 InnoDB: Using Linux native AIO 140327 15:25:06 InnoDB: Initializing buffer pool, size = 1.0G 140327 15:25:06 InnoDB: Completed initialization of buffer pool InnoDB: Error: log file ./ib_logfile1 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 67108864 bytes! 140327 15:25:06 [ERROR] Plugin 'InnoDB' init function returned error. 140327 15:25:06 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140327 15:25:06 [ERROR] /usr/sbin/mysqld: unknown option '--safe-show-database' 140327 15:25:06 [ERROR] Aborting 140327 15:25:06 [Note] /usr/sbin/mysqld: Shutdown complete 140327 15:25:06 mysqld_safe mysqld from pid file /var/lib/mysql/server.cashfire.org.pid ended -----------------------------------------------------------------------------------------------------------
    yes also i had deleted /var/lib/mysql/ib_logfile0 and i see again it has been created so i delete again .and found another /var/lib/mysql/ib_logfile1 and deleted this one too.
    0
  • thinkbot
    ok the problem was safe-show-databas delete this line from my.cnf and then restart logfiles will be recreated, its ok after restart, run from command line mysqlcheck -A --auto-repair this will check the tables and repair the crashed ones, for example: Warning] Checking table: './cphulkd/logins' 140327 5:51:02 [ERROR] /usr/sbin/mysqld: Table './cphulkd/brutes' is marked as crashed and should be repaired and replace log-slow-queries with slow-query-log
    0
  • techwelfare
    staying in which directory i will run the command?
    0
  • techwelfare
    ok i run the command too from var/lib/mysql all said ok so now it worked as per your instruction. thank you so much for being so co-operative so far, BUT problem is same as before it does not kill too much of cpu usage it is again going same as before, above 100% and the result is too slow as usual. below is my.cnf file as now [mysqld] skip-name-resolve max_connections=100 max_user_connections = 50 slow-query-log=mysql-slow.log long_query_time = 0.1 query_cache_type = 1 query_cache_size = 75M query_cache_limit = 2M join_buffer_size=1M read_rnd_buffer_size=1M max_allowed_packet = 10M tmp_table_size=25M max_heap_table_size=25M table_open_cache = 7500 thread_cache_size = 25 open_files_limit=30000 key_buffer_size = 500M myisam_sort_buffer_size = 256M innodb_file_per_table=1 innodb_buffer_pool_size = 1G innodb_log_file_size = 64M innodb_log_buffer_size = 8M
    please let us know if anything could solve my problem actually.
    0
  • techwelfare
    ok i run the command too from var/lib/mysql all said ok so now it worked as per your instruction. thank you so much for being so co-operative so far, BUT problem is same as before it does not kill too much of cpu usage it is again going same as before, above 100% and the result is too slow as usual. below is my.cnf file as now [mysqld] skip-name-resolve max_connections=100 max_user_connections = 50 slow-query-log=mysql-slow.log long_query_time = 0.1 query_cache_type = 1 query_cache_size = 75M query_cache_limit = 2M join_buffer_size=1M read_rnd_buffer_size=1M max_allowed_packet = 10M tmp_table_size=25M max_heap_table_size=25M table_open_cache = 7500 thread_cache_size = 25 open_files_limit=30000 key_buffer_size = 500M myisam_sort_buffer_size = 256M innodb_file_per_table=1 innodb_buffer_pool_size = 1G innodb_log_file_size = 64M innodb_log_buffer_size = 8M
    please let us know if anything could solve my problem actually. [quote="thinkbot, post: 1606412">ok the problem was safe-show-databas delete this line from my.cnf and then restart logfiles will be recreated, its ok after restart, run from command line mysqlcheck -A --auto-repair this will check the tables and repair the crashed ones, for example: Warning] Checking table: './cphulkd/logins' 140327 5:51:02 [ERROR] /usr/sbin/mysqld: Table './cphulkd/brutes' is marked as crashed and should be repaired and replace log-slow-queries with slow-query-log
    0
  • thinkbot
    rerun mysqltuner.pl and post the results, also run pt-query-digest to review slow log cd /root wget
    0
  • abdelhost77
    Hello Sir , After your last suggestions, the SQL work like a charm during months but recently i found that Mysql start crashing several time each day, you will find below : mysqltuner.pl result, etc/my.cnf ; and Logs showing crash from /var/lib/mysql/*err Please advice : ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at MySQLTuner-perl by major >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.73-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) [!!] InnoDB is enabled but isn't being used Argument "" isn't numeric in numeric gt (>) at ./mysqltuner.pl line 564 (#1) (W numeric) The indicated string was fed as an argument to an operator that expected a numeric value instead. If you're fortunate the message will identify which operator was so unfortunate. [OK] Total fragmented tables: -------- Security Recommendations ------------------------------------------- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) [OK] All database users have passwords assigned ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) Argument "" isn't numeric in numeric eq (==) at ./mysqltuner.pl line 623 (#1) -------- Performance Metrics ------------------------------------------------- [--] Up for: 8m 47s (39K q [74.679 qps], 831 conn, TX: 135M, RX: 6M) [--] Reads / Writes: 87% / 13% [--] Total buffers: 1.6G global + 4.4M per thread (100 max threads) [OK] Maximum possible memory usage: 2.0G (26% of installed RAM) [OK] Slow queries: 0% (0/39K) [OK] Highest usage of available connections: 10% (10/100) [!!] Cannot calculate MyISAM index size - re-run script as root user [OK] Query cache efficiency: 56.3% (18K cached / 33K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts) [!!] Joins performed without indexes: 595 [!!] Temporary tables created on disk: 29% (1K on disk / 3K total) [OK] Thread cache hit rate: 98% (10 created / 831 connections) [OK] Table cache hit rate: 99% (706 open / 713 opened) [OK] Open file limit used: 1% (955/50K) [OK] Table locks acquired immediately: 99% (21K immediate / 21K locks) -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: join_buffer_size (> 1.0M, or always use indexes with joins) tmp_table_size (> 25M) max_heap_table_size (> 25M) ################################## ############ /etc/my.cnf ############# ################################## [mysqld] skip-name-resolve myisam_use_mmap=1 max_connections=100 max_user_connections = 50 log-slow-queries=mysql-slow.log long_query_time = 1 safe-show-database query_cache_type = 1 query_cache_size = 75M query_cache_limit = 2M join_buffer_size=1M read_rnd_buffer_size=1M max_allowed_packet=268435456 tmp_table_size=25M max_heap_table_size=25M table_open_cache = 7500 thread_cache_size = 25 open_files_limit=50000 key_buffer_size = 500M myisam_sort_buffer_size = 256M innodb_file_per_table=1 innodb_buffer_pool_size = 1G innodb_log_file_size = 64M innodb_log_buffer_size = 8M ############################## ########## LOGS################# ############################## 140830 2:02:23 [Warning] 'db' entry 'xxx\_ecole ' ignored in --skip-name-resolve mode. 140830 2:02:23 [Warning] 'db' entry 'xxx\_wp469 ' ignored in --skip-name-resolve mode. 140830 2:02:23 [Warning] 'db' entry 'xxx\_wp622 ' ignored in --skip-name-resolve mode. InnoDB: Error: tried to read 16384 bytes at offset 0 32768. InnoDB: Was only able to read 8192. 140830 2:14:41 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: File operation call: 'read'. InnoDB: Cannot continue operation. 140830 02:14:43 mysqld_safe Number of processes running now: 0 140830 02:14:43 mysqld_safe mysqld restarted 140830 2:14:44 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead. 140830 2:14:44 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead. 140830 2:14:44 [Note] Plugin 'FEDERATED' is disabled. 140830 2:14:44 InnoDB: Initializing buffer pool, size = 1.0G 140830 2:14:44 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 7 1329764116 140830 2:14:44 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 7 1329764445 140830 2:16:52 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 140830 2:16:53 InnoDB: Started; log sequence number 7 1329764445 140830 2:16:53 [Warning] 'user' entry 'xxx@xxx' ignored in --skip-name-resolve mode. 140830 2:16:53 [Warning] 'user' entry 'xxx@xxx' ignored in --skip-name-resolve mode. 140830 2:16:53 [Warning] 'user' entry 'xxx@xxx' ignored in --skip-name-resolve mode.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post