i have centos 7 x64 64 GB RAM and 500 GB of SSD dedicated server.,
And recently cpu usage was really high,
$ uptime
13:50:30 up 169 days, 1:53, 1 user, load average: 37.48, 37.64, 33.60
so i noticed mysqld is taking much more CPU
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
10252 mysql 20 0 69.680g 0.040t 17164 S 792.0 65.4 366:19.06 mysqld
running mysql
Server version: 5.7.21-log MySQL Community Server (GPL)
status shows
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper
Connection id: 248236
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.21-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 hour 15 min 34 sec
Threads: 57 Questions: 1261965 Slow queries: 5923 Opens: 323 Flush tables: 1 Open tables: 297 Queries per second avg: 278.333
my /etc/my.cnf
$ cat /etc/my.cnf
[mysqld]
bind-address = 127.0.0.1
innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0
innodb_buffer_pool_size = 40G
#innodb_io_capacity = 2000
#innodb_read_io_threads = 64
#innodb_thread_concurrency = 0
#innodb_write_io_threads = 64
#character-set-server = utf8
max_connections = 2000
slow-query-log = on
slow-query-log-file = /var/log/mysql-slow-queries1.log
long_query_time = 1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
general-log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
pid-file=/var/run/mysqld/mysqld.pid
How can i find out why is mysqld is using so much CPU ? and how can i fix it ?
i have only 1 mysql database with 8 tables in it.
SHOW VARIABLES LIKE '%buffer%'
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 42949672960 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
24 rows in set (0.02 sec)
SHOW VARIABLES LIKE 'query%'
mysql> SHOW VARIABLES LIKE 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
7 rows in set (0.00 sec)
i ran mysqltuner.pl
$ ./mysqltuner.pl
>> MySQLTuner 1.7.9 - 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.21-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.error.log(2M)
[OK] Log file /var/log/mysqld.error.log exists
[OK] Log file /var/log/mysqld.error.log is readable.
[OK] Log file /var/log/mysqld.error.log is not empty
[OK] Log file /var/log/mysqld.error.log is smaller than 32 Mb
[!!] /var/log/mysqld.error.log contains 705 warning(s).
[!!] /var/log/mysqld.error.log contains 38 error(s).
[--] 144 start(s) detected in /var/log/mysqld.error.log
[--] 1) 2018-04-22T16:37:18.163712Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-04-22T13:17:09.928956Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-04-22T05:16:06.966510Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-04-21T23:54:21.362840Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-04-21T07:49:49.997197Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-04-20T22:51:33.487738Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-04-20T14:24:28.470530Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-04-20T07:49:56.282519Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-04-20T05:19:46.045516Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-04-19T20:21:42.044572Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4 shutdown(s) detected in /var/log/mysqld.error.log
[--] 1) 2018-01-28T16:34:30.099495Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-11-16T02:11:54.584047Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2017-11-15T15:12:49.597191Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2017-11-13T06:13:48.319136Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1G (Tables: 2)
[--] Data in InnoDB tables: 51G (Tables: 7)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] Bug #80860 MySQL 5.7: Avoid testing password when validate_password is activated
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2h 18m 22s (1M q [188.934 qps], 463K conn, TX: 2G, RX: 207M)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory : 62.9G
[--] Max MySQL memory : 42.2G
[--] Other process memory: 1.2G
[--] Total buffers: 40.0G global + 1.1M per thread (2000 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 40.2G (63.87% of installed RAM)
[OK] Maximum possible memory usage: 42.2G (67.18% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 1% (18K/1M)
[OK] Highest usage of available connections: 5% (109/2000)
[OK] Aborted connections: 0.00% (1/463809)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 640K selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 11% (23K temp sorts / 194K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 14 total)
[OK] Thread cache hit rate: 99% (333 created / 463K connections)
[OK] Table cache hit rate: 91% (360 open / 393 opened)
[OK] Open file limit used: 0% (13/5K)
[OK] Table locks acquired immediately: 100% (104 immediate / 104 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[!!] Key buffer size / total MyISAM indexes: 8.0M/393.5M
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 40.0G/51.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.234375 %): 48.0M * 2/40.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 320 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.97% (2966978249 hits/ 2967941460 total)
[!!] InnoDB Write Log efficiency: 4554.14% (12114 hits/ 266 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12380 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- 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.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.error.log file
Control error line(s) into /var/log/mysqld.error.log file
MySQL started within last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: https://dev.mysql.com/doc/refman/5.7/en/innodb-data-log-reconfiguration.html
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
sort_buffer_size (> 256K)
read_rnd_buffer_size (> 256K)
key_buffer_size (> 393.5M)
innodb_buffer_pool_size (>= 51G) if possible.
innodb_log_file_size should be (=5G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=40)
Additional details.
[root@ ]# free -h
total used free shared buff/cache available
Mem: 62G 57G 592M 755M 4.9G 3.9G
Swap: 2.0G 2.0G 20K
here is my updated /etc/my.cnf https://pastebin.com/rdzhAWtC
A) SHOW GLOBAL STATUS;
https://pastebin.com/9u8SHMJ4
B) SHOW GLOBAL VARIABLES;
https://pastebin.com/ytn5RC9u
C) D) SHOW ENGINE INNODB STATUS;
https://pastebin.com/WEtuHFiU
top for most active apps
https://pastebin.com/hBzN8Kr4
ulimit -a
[root@]# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 256907
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 65535
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 256907
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
df -h
[root@]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/root 438G 130G 286G 32% /
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 0 32G 0% /dev/shm
tmpfs 32G 2.7G 29G 9% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/sda1 510M 2.6M 508M 1% /boot/efi
tmpfs 6.3G 0 6.3G 0% /run/user/0
tmpfs 6.3G 0 6.3G 0% /run/user/1000
update: mysql was crashing/restarting every few hours so i added swap space and changed swappiness from 10 to default 60.
here is latest report by mysqltuner.pl
https://pastebin.com/3xMXvvZv
I added these in my.cnf as per suggested by Wilson Hauck
key_cache_age_threshold=64800 # from 300 second discard, only to RD again
innodb_lru_scan_depth=100 # from 1024 to reduce CPU load see v8 refman
innodb_log_buffer_size=1G # from 16M to better support 3G per hour
innodb_log_file_size=4G # from 50M before rotation to next logfile.
innodb_io_capacity=1000 # from 200 to 'let it run'
innodb_change_buffer_max_size=10 # from 25 to reduce BP set-aside RAM
sort_buffer_size=2M # from 256K to reduce sort_merge_passes of 1m+
max_write_lock_count=16 # from a HUGE number to allow RD after nn lcks
and restarted mysql.
now
top
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
10957 mysql 20 0 63.1g 42.8g 17472 S 768.8 68.1 3560:16 mysqld
free -h
total used free shared buff/cache available
Mem: 62G 54G 592M 27M 7.9G 7.6G
Swap: 34G 3.4G 30G
df -h
Filesystem Size Used Avail Use% Mounted on
/dev/root 438G 132G 284G 32% /
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 0 32G 0% /dev/shm
tmpfs 32G 3.2G 29G 11% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/sda1 510M 2.6M 508M 1% /boot/efi
tmpfs 6.3G 0 6.3G 0% /run/user/0
tmpfs 6.3G 0 6.3G 0% /run/user/1000
cat /proc/meminfo
https://pastebin.com/WshJiLP3
iostat -x
Linux 4.9.58-xxxx-std-ipv6-64 (ns540545) 06/12/2018 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
18.50 0.08 1.03 4.37 0.00 76.02
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 2.20 79.15 31.77 238.97 1285.23 5032.09 46.67 0.22 0.82 1.46 0.74 0.38
mysql status
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
Connection id: 1650513
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.22 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 7 hours 39 min 28 sec
Threads: 102 Questions: 16743958 Slow queries: 0 Opens: 495 Flush tables: 1 Open tables: 486 Queries per second avg: 607.369
