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.
Thread Tools Display Modes
Old 1st May 2013, 19:55
nackgr nackgr is offline
Join Date: Nov 2012
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
Default Mysql high cpu usage 200-300%


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



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

# Semisynchronous Replication

# uncomment next line on MASTER
# uncomment next line on SLAVE

# Others options for Semisynchronous Replication


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 



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


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 01:42.

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