Skip to main content

MySQL cpu consumtion is 95%+

ashiftoday
Hi, my VPS has very limited sites not more than 10, VPS has 4 cores and 1GB RAM and 1GB Swap. my average load values (from last several days) is 2+ several times a day and it shows mysql service being consuming the most. what configurations are advised for that, and what information you require to see in order to optimize the mysql configurations. thaks.

Comments

3 comments

Date Votes
  • cPanelMichael
    Hello :) You may receive more user-feedback if you post the output of a MySQL tuner. The following thread should help you get started: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
    0
  • ashiftoday
    below is the mysqltuner output.
    >> MySQLTuner 1.4.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.5.40-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 578M (Tables: 455) [--] Data in InnoDB tables: 990M (Tables: 411) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 0B (Tables: 4) [!!] Total fragmented tables: 24 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1h 8m 48s (341K q [82.675 qps], 2K conn, TX: 2B, RX: 91M) [--] Reads / Writes: 94% / 6% [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads) [OK] Maximum possible memory usage: 583.2M (56% of installed RAM) [OK] Slow queries: 0% (3/341K) [OK] Highest usage of available connections: 9% (15/151) [OK] Key buffer size / total MyISAM indexes: 8.0M/94.5M [OK] Key buffer hit rate: 99.6% (1M cached / 7K reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (7 temp sorts / 42K sorts) [!!] Joins performed without indexes: 4197 [!!] Temporary tables created on disk: 62% (13K on disk / 20K total) [!!] Thread cache is disabled [!!] Table cache hit rate: 4% (400 open / 8K opened) [OK] Open file limit used: 0% (414/50K) [OK] Table locks acquired immediately: 99% (388K immediate / 388K locks) [!!] InnoDB buffer pool / data size: 128.0M/990.8M [OK] InnoDB log waits: 0 -------- 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 Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Set thread_cache_size to 4 as a starting value Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 128.0K, or always use indexes with joins) tmp_table_size (> 16M) max_heap_table_size (> 16M) thread_cache_size (start at 4) table_open_cache (> 400) innodb_buffer_pool_size (>= 990M)
    0
  • cPanelMichael
    Please ensure you let MySQL run for at least 24 hours to ensure more accurate tuner results. Thank you.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post