help setting my.cnf, mysql 100% cpu usage

bou3lam · Apr 1, 2013

hi
please someone help set my.cnf for my box, currently its seems i dont have a correct one :

Code:

[email protected] [/]# cat /etc/my.cnf
[mysqld]
innodb_file_per_table=1
local-infile=0
open_files_limit=10192
[email protected]

i have run the mysqltuner :

Code:

[email protected] [/usr/local/cpanel/3rdparty/mysqltuner]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 381M (Tables: 1648)
[--] Data in InnoDB tables: 224K (Tables: 14)
[--] Data in MEMORY tables: 124K (Tables: 4)
[!!] Total fragmented tables: 64

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6d 18h 23m 10s (143M q [246.019 qps], 1M conn, TX: 32B, RX: 14B)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (2% of installed RAM)
[OK] Slow queries: 0% (95/143M)
[OK] Highest usage of available connections: 43% (65/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/223.1M
[OK] Key buffer hit rate: 100.0% (301B cached / 52M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 1% (53K temp sorts / 4M sorts)
[!!] Joins performed without indexes: 12349
[OK] Temporary tables created on disk: 1% (47K on disk / 2M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 7M opened)
[OK] Open file limit used: 1% (112/10K)
[OK] Table locks acquired immediately: 99% (148M immediate / 148M locks)
[OK] InnoDB data size / buffer pool: 224.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (start at 4)
    table_cache (> 64)

thank you

bou3lam · Apr 2, 2013

anyone please ? my cpu is always and ever 100%

storminternet · Apr 3, 2013

Set the value for query_cache_size is equal to 8 M or more than that. Run mysqladmin pr on shell and see which database is having more process list. Accordingly either optimize it or disable it and then monitor your server's performance.

ES - George · Apr 3, 2013

[mysqld]
innodb_file_per_table=1
local-infile=0
open_files_limit=10192
query_cache_size=8M
join_buffer_size=128K
thread_cache_size=4
table_cache=64

Then restart MySQL

I suggest running mysqlcheck -aor after to optimise and repair all tables

bou3lam · Apr 3, 2013

HVH - George said:
[mysqld]
innodb_file_per_table=1
local-infile=0
open_files_limit=10192
query_cache_size=8M
join_buffer_size=128K
thread_cache_size=4
table_cache=64

Then restart MySQL

I suggest running mysqlcheck -aor after to optimise and repair all tables

seems working

thanks a lot, load of mysql now is never up to 5% cpu ! will check tomorrow

ES - George · Apr 4, 2013

Great to hear. I suggest re-running the mysqltuner script after 24 hours of mysql uptime, and re-posting the results here so we can give you further advice.

bou3lam · Apr 5, 2013

hi . the load yesterday and today was 26% i guess its normal , here the mysql tuner stats and recommendations :

Code:

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 391M (Tables: 1650)
[--] Data in InnoDB tables: 5M (Tables: 332)
[--] Data in MEMORY tables: 124K (Tables: 21)
[!!] Total fragmented tables: 64

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 2h 33m 37s (22M q [125.373 qps], 555K conn, TX: 6B, RX: 2B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 42.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 457.2M (2% of installed RAM)
[OK] Slow queries: 0% (0/22M)
[OK] Highest usage of available connections: 15% (24/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/229.0M
[OK] Key buffer hit rate: 100.0% (47B cached / 16M reads)
[OK] Query cache efficiency: 81.4% (16M cached / 19M selects)
[!!] Query cache prunes per day: 422044
[OK] Sorts requiring temporary tables: 0% (8K temp sorts / 883K sorts)
[!!] Joins performed without indexes: 2767
[OK] Temporary tables created on disk: 3% (12K on disk / 397K total)
[OK] Thread cache hit rate: 99% (1K created / 555K connections)
[!!] Table cache hit rate: 0% (64 open / 2M opened)
[OK] Open file limit used: 0% (107/11K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
[OK] InnoDB data size / buffer pool: 5.3M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 64)

what do you think please
many thanks

bou3lam · Apr 11, 2013

following :

[email protected] [/]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 403M (Tables: 1769)
[--] Data in InnoDB tables: 5M (Tables: 335)
[--] Data in MEMORY tables: 124K (Tables: 23)
[!!] Total fragmented tables: 71

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 20h 2m 27s (73M q [108.647 qps], 2M conn, TX: 23B, RX: 9B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 42.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 457.2M (2% of installed RAM)
[OK] Slow queries: 0% (0/73M)
[OK] Highest usage of available connections: 17% (26/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/236.3M
[OK] Key buffer hit rate: 100.0% (181B cached / 65M reads)
[OK] Query cache efficiency: 75.7% (46M cached / 61M selects)
[!!] Query cache prunes per day: 604669
[OK] Sorts requiring temporary tables: 1% (34K temp sorts / 3M sorts)
[!!] Joins performed without indexes: 12330
[OK] Temporary tables created on disk: 3% (49K on disk / 1M total)
[OK] Thread cache hit rate: 99% (10K created / 2M connections)
[!!] Table cache hit rate: 0% (64 open / 9M opened)
[OK] Open file limit used: 1% (127/11K)
[OK] Table locks acquired immediately: 99% (26M immediate / 26M locks)
[OK] InnoDB data size / buffer pool: 5.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 64)

mysql load now is 27% , what do you think please

ES - George · Apr 14, 2013

I'd suggest running mysqlcheck -Aor first to optimise and repair tables

You might also like to increase the caching size. Change table cache from 64 to 1024

bou3lam · Apr 14, 2013

after icreasing the cache and restarting the mysql cpu usage jumped to 200% !!! this the current sql command with such high usage :
/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.mydomain.com.err --open-files-limit=12276 --pid-file=/var/lib/mysql/server.mydomain.com.pid

bou3lam · Apr 14, 2013

re-modified it to 64, 25% again, guess this is fine

thanks !

bandungdown · Apr 21, 2013

You can see poseses mysql

watch mysqladmin process

Thread starter	Similar threads	Forum	Replies	Date
S	I need help optimizing MySQL	Workarounds and Optimization	5	Nov 20, 2022
S	How to optimize mysql - help	Workarounds and Optimization	2	Mar 11, 2020
P	MySQL settings help needed	Workarounds and Optimization	1	Jul 1, 2014
E	Requesting help optimizing MySQL settings	Workarounds and Optimization	5	Nov 15, 2013
W	Need help optimize mysql setting	Workarounds and Optimization	6	Jul 8, 2012

Search

Search

help setting my.cnf, mysql 100% cpu usage

bou3lam

Active Member

bou3lam

Active Member

storminternet

Well-Known Member

ES - George

Well-Known Member

bou3lam

Active Member

ES - George

Well-Known Member

bou3lam

Active Member

bou3lam

Active Member

ES - George

Well-Known Member

bou3lam

Active Member

bou3lam

Active Member

bandungdown

Registered