title: Mysql process goes over 400% of CPU usage
body: <p>I'm experiencing some problems with my mysql server When I run top command, it sais that mysql process has taken over 350-500% of CPU
  I'm running centos 6  with 49 GB RAM</p>
  
  <p>my.cnf settings:</p>
  
  <pre><code>[mysqld]
  # delayed_insert_timeout=20
  net_write_timeout=60
  #slow_query_log_file = /var/lib/mysql/mysqld_slow_queries.log
  #long_query_time=10
  #general_log = on
  key_buffer=4024M
  open_files_limit=154378
  innodb_buffer_pool_size=4G
  key_buffer_size=4G
  query_cache_min_res_unit=7096
  max_connections=100000
  sort_buffer_size=100M
  myisam_sort_buffer_size=128M
  tmp_table_size=5042M
  table_definition_cache=4000
  innodb_additional_mem_pool_size=100M
  default-storage-engine=MyISAM
  read_buffer_size=100M
  thread_cache_size=2042M
  query_cache_size=2084M
  join_buffer_size=100M
  innodb_thread_concurrency=12
  max_heap_table_size=5042M
  net_read_timeout=60
  max_allowed_packet=560M
  query_cache_type=1
  read_rnd_buffer_size=100M
  innodb_flush_method=O_DIRECT
  wait_timeout=60
  innodb_file_per_table=1
  skip-name-resolve
  query_cache_limit=100M
  table_open_cache=1000
  interactive_timeout=9
  [mysql]
  #wait_timeout=15
  #net_read_timeout=40
  no-auto-rehash
  #interactive_timeout=15
  [mysql_safe]
  log_error="/var/log/mysqld.log"
  #wait_timeout=15
  #net_read_timeout=40
  #interactive_timeout=15
  open-files-limit=154378
  skip-name-resolve
  [mysqlhotcopy]
  #interactive-timeout=15
  </code></pre>
  
  <p>output of MySQLTuner:</p>
  
  <pre><code> &gt;&gt;  MySQLTuner 1.7.1 - Major Hayden &lt;major@mhtx.net&gt;
   &gt;&gt;  Bug reports, feature requests, and downloads at http://mysqltuner.com/
   &gt;&gt;  Run with '--help' for additional options and output filtering
  
  [--] Skipped version check for MySQLTuner script
  
  
  [!!] Currently running unsupported MySQL version 10.0.30-MariaDB
  [OK] Operating on 64-bit architecture
  
  -------- Log file Recommendations ------------------------------------------------------------------
  [--] Log file: /var/lib/mysql/xx.xx.com.err(177K)
  [OK] Log file /var/lib/mysql/xx.xx.com.err exists
  [OK] Log file /var/lib/mysql/xx.xx.com.err is readable.
  [OK] Log file /var/lib/mysql/xx.xx.com.err is not empty
  [OK] Log file /var/lib/mysql/xx.xx.com.err is smaller than 32 Mb
  [!!] /var/lib/mysql/xx.xx.com.err contains 618 warning(s).
  [!!] /var/lib/mysql/xx.xx.com.err contains 69 error(s).
  [--] 44 start(s) detected in /var/lib/mysql/xx.xx.com.err
  [--] 1) 170416  3:05:14 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 2) 170416  0:28:36 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 3) 170416  0:23:34 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 4) 170415 11:49:57 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 5) 170415  9:25:33 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 6) 170413 23:17:07 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 7) 170413 21:54:37 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 8) 170406 11:14:35 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 9) 170402 18:11:05 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 10) 170402 18:00:51 [Note] /usr/sbin/mysqld: ready for connections.
  [--] 40 shutdown(s) detected in /var/lib/mysql/xx.xx.com.err
  [--] 1) 170416  3:05:11 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 2) 170416  0:28:34 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 3) 170416  0:23:31 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 4) 170415 11:49:55 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 5) 170415  9:24:08 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 6) 170413 23:17:05 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 7) 170413 21:53:09 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 8) 170406 11:13:06 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 9) 170402 18:11:03 [Note] /usr/sbin/mysqld: Shutdown complete
  [--] 10) 170402 18:00:50 [Note] /usr/sbin/mysqld: Shutdown complete
  
  -------- Storage Engine Statistics -----------------------------------------------------------------
  [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA 
  [--] Data in MyISAM tables: 7G (Tables: 221)
  [--] Data in InnoDB tables: 480K (Tables: 14)
  [--] Data in MEMORY tables: 0B (Tables: 1)
  [OK] Total fragmented tables: 0
  
  -------- Security Recommendations ------------------------------------------------------------------
  [OK] There are no anonymous accounts for any database users
  [OK] All database users have passwords assigned
  [!!] There is no basic password file list!
  
  -------- CVE Security Recommendations --------------------------------------------------------------
  [--] Skipped due to --cvefile option undefined
  
  -------- Performance Metrics -----------------------------------------------------------------------
  [--] Up for: 23h 32m 29s (480M q [5K qps], 10M conn, TX: 322G, RX: 18G)
  [--] Reads / Writes: 99% / 1%
  [--] Binary logging is disabled
  [--] Physical Memory     : 47.3G
  [--] Max MySQL memory    : 39105.2G
  [--] Other process memory: 22.7G
  [--] Total buffers: 15.2G global + 400.3M per thread (100000 max threads)
  [--] P_S Max memory usage: 0B
  
  
  [--] Galera GCache Max memory usage: 0B
  [!!] Maximum reached memory usage: 333.8G (706.29% of installed RAM)
  [!!] Maximum possible memory usage: 39105.2G (82749.33% of installed RAM)
  
  
  [!!] Overall possible memory usage with other process exceeded memory
  [OK] Slow queries: 0% (439/480M)
  [OK] Highest usage of available connections: 0% (815/100000)
  [OK] Aborted connections: 0.00%  (40/10747826)
  [!!] Query cache may be disabled by default due to mutex contention.
  [OK] Query cache efficiency: 49.8% (433M cached / 871M selects)
  [OK] Query cache prunes per day: 0
  [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 219K sorts)
  [OK] No joins without indexes
  [!!] Temporary tables created on disk: 41% (726 on disk / 1K total)
  [OK] Thread cache hit rate: 99% (815 created / 10M connections)
  [OK] Table cache hit rate: 96% (691 open / 713 opened)
  [OK] Open file limit used: 0% (870/500K)
  [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
  
  -------- Performance schema ------------------------------------------------------------------------
  [--] Performance schema is disabled.
  [--] Memory used by P_S: 0B
  [--] Sys schema isn't installed.
  
  -------- ThreadPool Metrics ------------------------------------------------------------------------
  [--] ThreadPool stat is enabled.
  [--] Thread Pool Size: 8 thread(s).
  [--] Using default value is good enough for your version (10.0.30-MariaDB)
  
  -------- MyISAM Metrics ----------------------------------------------------------------------------
  [!!] Key buffer used: 23.1% (992M used / 4B cache)
  [OK] Key buffer size / total MyISAM indexes: 4.0G/3.5G
  [OK] Read Key buffer hit rate: 100.0% (1B cached / 200K reads)
  [!!] Write Key buffer hit rate: 83.2% (76K cached / 63K writes)
  
  -------- InnoDB Metrics ----------------------------------------------------------------------------
  [--] InnoDB is enabled.
  [--] InnoDB Thread Concurrency: 12
  [OK] InnoDB File per table is activated
  [OK] InnoDB buffer pool / data size: 4.0G/480.0K
  [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (2.34375 %): 48.0M * 2/4.0G should be equal 25%
  [!!] InnoDB buffer pool instances: 8
  [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
  [OK] InnoDB Read buffer efficiency: 94.55% (8648 hits/ 9146 total)
  [!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
  [OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
  
  -------- AriaDB Metrics ----------------------------------------------------------------------------
  [--] AriaDB is enabled.
  [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
  [!!] Aria pagecache hit rate: 0.0% (4 cached / 4 reads)
  
  -------- TokuDB Metrics ----------------------------------------------------------------------------
  [--] TokuDB is disabled.
  
  -------- XtraDB Metrics ----------------------------------------------------------------------------
  [--] XtraDB is disabled.
  
  -------- RocksDB Metrics ---------------------------------------------------------------------------
  [--] RocksDB is disabled.
  
  -------- Spider Metrics ----------------------------------------------------------------------------
  [--] Spider is disabled.
  
  -------- Connect Metrics ---------------------------------------------------------------------------
  [--] Connect is disabled.
  
  -------- Galera Metrics ----------------------------------------------------------------------------
  [--] Galera is disabled.
  
  -------- Replication Metrics -----------------------------------------------------------------------
  [--] Galera Synchronous replication: NO
  [--] No replication slave(s) for this server.
  [--] This is a standalone server.
  
  -------- Recommendations ---------------------------------------------------------------------------
  General recommendations:
      Control warning line(s) into /var/lib/mysql/xx.xx.com.err file
      Control error line(s) into /var/lib/mysql/xx.xx.com.err file
      MySQL started within last 24 hours - recommendations may be inaccurate
      Reduce your overall MySQL memory footprint for system stability
      Dedicate this server to your database for highest performance.
      Temporary table size is already large - reduce result set size
      Reduce your SELECT DISTINCT queries without LIMIT clauses
      Performance should be activated for better diagnostics
      Consider installing Sys schema from https://github.com/mysql/mysql-sys
  Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
      query_cache_type (=0)
      performance_schema = ON enable PFS
      innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=2G) if possible.
      innodb_buffer_pool_instances(=4)
  </code></pre>
  
  <p>Can I make changes to my my.cnf to prevent server from hanging  ?</p>