MYSQL : High CPU Usage

abdelhost77 · Nov 16, 2013

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

Code:

[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

Code:

################################
Mysqltuner.pl



 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  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.70-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 35132)
[--] Data in InnoDB tables: 882M (Tables: 23776)
[--] Data in MEMORY tables: 2M (Tables: 600)
[!!] Total fragmented tables: 2887

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 2h 7m 56s (32M q [348.287 qps], 312K conn, TX: 216B, RX: 3B)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 274.0M global + 52.6M per thread (200 max threads)
[!!] Maximum possible memory usage: 10.5G (137% of installed RAM)
[OK] Slow queries: 0% (8/32M)
[OK] Highest usage of available connections: 14% (28/200)
[OK] Key buffer size / total MyISAM indexes: 8.0M/481.5M
[OK] Key buffer hit rate: 98.9% (56M cached / 597K reads)
[OK] Query cache efficiency: 81.2% (12M cached / 14M selects)
[!!] Query cache prunes per day: 889680
[OK] Sorts requiring temporary tables: 0% (212 temp sorts / 452K sorts)
[!!] Joins performed without indexes: 83360
[!!] Temporary tables created on disk: 35% (442K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 6% (10K open / 152K opened)
[OK] Open file limit used: 16% (8K/50K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
[!!] InnoDB data size / buffer pool: 882.1M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    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
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 128M)
    join_buffer_size (> 50.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    thread_cache_size (start at 4)
    table_cache (> 10240)
    innodb_buffer_pool_size (>= 882M)

thinkbot · Nov 16, 2013

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

abdelhost77 · Nov 16, 2013

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 :

Code:

[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 ??

thinkbot · Nov 16, 2013

yes, exactly

cPanelMichael · Nov 17, 2013

Hello

Feel free to apply the new settings and let us know if you experience better results.

Thank you.

abdelhost77 · Nov 21, 2013

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

thinkbot · Nov 21, 2013

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

Swappiness - Wikipedia, the free encyclopedia

techwelfare · Mar 27, 2014

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?

thinkbot said:
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

thinkbot · Mar 27, 2014

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

techwelfare · Mar 27, 2014

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

thinkbot · Mar 27, 2014

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

- - - 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

techwelfare · Mar 27, 2014

here is the log----------------

Code:

*******

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.

thinkbot · Mar 27, 2014

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

techwelfare · Mar 27, 2014

staying in which directory i will run the command?

techwelfare · Mar 27, 2014

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

Code:

[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.

techwelfare · Mar 28, 2014

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

Code:

[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.

thinkbot said:
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

thinkbot · Mar 30, 2014

rerun mysqltuner.pl and post the results, also run pt-query-digest to review slow log

cd /root
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest

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

and post slow.txt here

abdelhost77 · Oct 21, 2014

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 :

Code:

./mysqltuner.pl

>>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at [URL='http://mysqltuner.com/']MySQLTuner-perl by major[/URL]
>>  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.

Thread starter	Similar threads	Forum	Replies	Date
R	Mysqld - high CPU usage	Workarounds and Optimization	4	Oct 7, 2019
M	MYSQL high cpu usage issue	Workarounds and Optimization	1	Oct 13, 2018
B	MySQL Very High CPU Usage issue	Workarounds and Optimization	1	Apr 2, 2018
	MySQL Database crashed/CPU higher on server to down	Workarounds and Optimization	10	Oct 21, 2017
U	Help - optimize mysqltunner to reduce high cpu usage	Workarounds and Optimization	5	Nov 14, 2016

Search

Search

MYSQL : High CPU Usage

abdelhost77

Well-Known Member

thinkbot

Well-Known Member

abdelhost77

Well-Known Member

thinkbot

Well-Known Member

cPanelMichael

Administrator

abdelhost77

Well-Known Member

thinkbot

Well-Known Member

techwelfare

Member

thinkbot

Well-Known Member

techwelfare

Member

thinkbot

Well-Known Member

techwelfare

Member

thinkbot

Well-Known Member

techwelfare

Member

techwelfare

Member

techwelfare

Member

thinkbot

Well-Known Member

abdelhost77

Well-Known Member