HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Server Operation (http://www.howtoforge.com/forums/forumdisplay.php?f=5)
-   -   Mysql high cpu usage 200-300% (http://www.howtoforge.com/forums/showthread.php?t=61683)

nackgr 1st May 2013 18:55

Mysql high cpu usage 200-300%
 
Hello!

my dedicated 16gb ram/ i7 cpu i starting to acting strange! mysql takes to much cpu until server stop react,

about fragments tables i used optimize etcetc etc but number keeps going bigger

mysqltuner report
Code:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 39M (Tables: 620)
[--] Data in InnoDB tables: 60M (Tables: 1297)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 65)
[!!] Total fragmented tables: 1300

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11s (82 q [7.455 qps], 12 conn, TX: 587K, RX: 10K)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 1.4G global + 10.3M per thread (151 max threads)
[OK] Maximum possible memory usage: 2.9G (24% of installed RAM)
[OK] Slow queries: 0% (0/82)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Key buffer size / total MyISAM indexes: 1.0G/22.2M
[!!] Key buffer hit rate: 93.0% (759 cached / 53 reads)
[!!] Query cache efficiency: 4.5% (3 cached / 67 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 27 sorts)
[!!] Temporary tables created on disk: 43% (20 on disk / 46 total)
[OK] Thread cache hit rate: 83% (2 created / 12 connections)
[OK] Table cache hit rate: 85% (40 open / 47 opened)
[OK] Open file limit used: 3% (40/1K)
[OK] Table locks acquired immediately: 99% (109 immediate / 110 locks)
[!!] Connections aborted: 16%
[OK] InnoDB data size / buffer pool: 60.8M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 6M, or use smaller result sets)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)

my.cnf
Code:

[mysqld]
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the

user=mysql

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Semisynchronous Replication

# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1



;performance_schema

query_cache_limit= 6M
query_cache_size= 96M
tmp_table_size= 128M
max_heap_table_size= 128M
thread_cache_size= 8
table_cache= 512
join_buffer_size= 90K
key_buffer_size= 1024M
max_allowed_packet= 64M
sort_buffer_size= 4M
read_buffer_size= 4M
read_rnd_buffer_size = 2m
myisam_sort_buffer_size = 64m
tmp_table_size = 128m
query_cache_type = 1
wait_timeout = 300

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log_slow_queries=/var/log/mysql/slow-query.log
long_query_time=20
log_queries_not_using_indexes=YES



All times are GMT +2. The time now is 13:25.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.