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
my.cnf settings:
[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
output of MySQLTuner:
>> MySQLTuner 1.7.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
[--] 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)
Can I make changes to my my.cnf to prevent server from hanging ?
High CPU usage --> Inadequate indexes and/or poorly written queries. Period. End of discussion. So show us the queries and SHOW CREATE TABLE.
Oh, there is something that can hurt CPU:
query_cache_size=2084M -- No more than 50M !
When any write occurs, all entries in the QC for the modified table are purged. The bigger the QC, the more costly this is.
If you are using entirely InnoDB, then change this:
innodb_buffer_pool_size=4G -- to 30G
Much too big:
max_connections=100000 -- drop to 1000
tmp_table_size=5042M -- 400M
thread_cache_size=2042M -- 10 is usually sufficient
OUCH!! That is 2 billion entries, not 2GB! Drop it to a plain 10 !!
I quit. Remove all the changes you made, then add one change:
innodb_buffer_pool_size=4G -- to 30G
That will keep you out of trouble. Then we can discuss the slowest query.