Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Server Operation

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 1st May 2013, 19: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 20:02.
Reply With Quote
Sponsored Links
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ISPConfig 3 system installation script Mark_NL Tips/Tricks/Mods 14 18th November 2014 07:34
help cant access anything locally or from ip jimmie Installation/Configuration 14 5th March 2013 08:58
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 14:58
ISP Config hesitation when opening web pages frankb Installation/Configuration 7 15th December 2008 14:06
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 10:11


All times are GMT +2. The time now is 23:08.


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