Find the answer to your Linux question:
Results 1 to 3 of 3
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 ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Aug 2006
    Posts
    52

    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
    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    
    # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
    # these lines and adjust the connectstring as needed.
    #ndbcluster
    #ndb-connectstring="nodeid=4;host=localhost:1186"
    
    #Disable DNS Hostname Lookup
    skip-name-resolve
    
    #Use a 50mb Query Cache
    query_cache_size = 100m;
    
    #Turn on query cacheing
    query_cache_type = 1
    
    query_cache_limit=2M
    
    #log-slow-queries = /var/log/mysql-slow.log
    
    long_query_time = 2
    
    wait_timeout=15
    
    connect_timeout=10
    
    interactive_timeout=100
    
    join_buffer_size=3M
    
    max_allowed_packet=16M
    
    table_cache=2048
    
    thread_cache_size=64
    
    sort_buffer_size=2M
    
    read_buffer_size=1M
    
    key_buffer_size=400M
    
    read_rnd_buffer_size=4M
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    [ndbd]
    # 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!
    connect-string="nodeid=2;host=localhost:1186"
    
    [ndb_mgm]
    # connection string for MySQL Cluster management tool
    connect-string="host=localhost:1186"
    mysqld is using 650mb of ram which is fine.

    Code:
    mysql> SHOW STATUS LIKE '%THREAD%';
    +------------------------+-------+
    | 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
    Code:
     perl mysqltuner.pl
    
     >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  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
    Code:
    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
    Location
    NH, USA
    Posts
    3,149
    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
    Just Joined!
    Join Date
    Jan 2008
    Posts
    12
    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

Posting Permissions

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