Find the answer to your Linux question:
Results 1 to 3 of 3
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1

    MySQL CPU Usage Spiking

    Having major problems with mysqld causing CPU usage to rocket to 100% on one core for a short time, which usually drops back down again to 1-7%, though sometimes it can last an extended period of time.
    This is a problem because things using mysql become unresponsive in this time.

    What is using mysql:
    -PHPbb forum with light usage
    -Sourcebans game ban database - quite a large database but shouldnt be causing problems since it does a few simple lookups per minute
    -HLStatsX game stats database - very large, has 3 daemons running which constantly receive statistics from approximately ~150 players on game servers (kills, deaths etc)

    Heres my.cnf
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
    # these lines and adjust the connectstring as needed.
    #Disable DNS Hostname Lookup
    #Use a 50mb Query Cache
    query_cache_size = 100m;
    #Turn on query cacheing
    query_cache_type = 1
    #log-slow-queries = /var/log/mysql-slow.log
    long_query_time = 2
    # If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
    # adjust its connection to the management daemon here.
    # Note: ndbd init script requires this to include nodeid!
    # connection string for MySQL Cluster management tool
    mysqld is using 650mb of ram which is fine.

    | Variable_name          | Value |
    | Delayed_insert_threads | 14    |
    | Slow_launch_threads    | 0     |
    | Threads_cached         | 20    |
    | Threads_connected      | 18    |
    | Threads_created        | 24    |
    | Threads_running        | 1     |
    6 rows in set (0.00 sec)
     >>  MySQLTuner 1.0.1 - Major Hayden <>
     >>  Bug reports, feature requests, and downloads at
     >>  Run with '--help' for additional options and output filtering
    [!!] Successfully authenticated with no password - SECURITY RISK!
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.39
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 729M (Tables: 347)
    [--] Data in InnoDB tables: 416K (Tables: 18)
    [--] Data in MEMORY tables: 1023K (Tables: 1)
    [!!] Total fragmented tables: 19
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 13h 20m 56s (2M q [18.666 qps], 98K conn, TX: 2B, RX: 584M)
    [--] Reads / Writes: 25% / 75%
    [--] Total buffers: 526.0M global + 10.2M per thread (151 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.0G (50% of installed RAM)
    [OK] Slow queries: 0% (1K/2M)
    [OK] Highest usage of available connections: 15% (24/151)
    [OK] Key buffer size / total MyISAM indexes: 400.0M/370.3M
    [OK] Key buffer hit rate: 100.0% (5B cached / 728K reads)
    [OK] Query cache efficiency: 45.0% (349K cached / 776K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (325 temp sorts / 53K sorts)
    [!!] Joins performed without indexes: 1005
    [OK] Temporary tables created on disk: 2% (1K on disk / 51K total)
    [OK] Thread cache hit rate: 99% (24 created / 98K connections)
    [!!] Table cache hit rate: 1% (99 open / 7K opened)
    [OK] Open file limit used: 3% (144/4K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 416.0K/8.0M
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        join_buffer_size (> 3.0M, or always use indexes with joins)
        table_cache (> 2048)
    Already run
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    How do i find out what is causing this?

  2. #2
    Linux Guru coopstah13's Avatar
    Join Date
    Nov 2007
    NH, USA
    I know more about oracle than mysql, but I'll give it a shot here. You mentioned a query that isn't executed often, but this might match up with your periodic spikes in CPU. You might try increasing the amount of memory available to mysql, based on your database sizes you could fit it all into memory if you have enough (it seems you do based on your output). I would try searching for mysql profilers, there might be some free ones out there that can let you see what the query being executed is that is causing the CPU spike, this should make it obvious what you need to do.

  3. #3
    There is some query which is taking the time.. In mysql

    # show full processlist\G

    this will tell you that which taking time u need to run it when it start unreponsive... Or load start increasing

  4. $spacer_open

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts