View Single Post
  #1  
Old 1st May 2013, 18:55
nackgr nackgr is offline
Member
 
Join Date: Nov 2012
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
Default 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

Last edited by nackgr; 1st May 2013 at 19:02.
Reply With Quote
Sponsored Links