MySQL is using too much CPU

Question

I have i7 3770 dedicated server with 32GB ram, and Centos7 is installed, below are CPU details

[root@server ~]# lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                8
On-line CPU(s) list:   0-7
Thread(s) per core:    2
Core(s) per socket:    4
Socket(s):             1
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 58
Model name:            Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz
Stepping:              9
CPU MHz:               1712.218
BogoMIPS:              6799.29
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              8192K
NUMA node0 CPU(s):     0-7

and mysql is using too much CPU on it. I am using it for web hosting and there is no so demanding account with a lot of visits. Databases are mixed, some are InnoDB and other are MyISAM, and I can't make them all to InnoDB or MyISAM, it needs to stay like this.

This is my mysql configuration

[mysqld]
default-storage-engine=InnoDB
performance-schema=0
max_allowed_packet=268435456
join_buffer_size = 24M
explicit_defaults_for_timestamp = 1
read_rnd_buffer_size=1024K
read_buffer_size=4M
sort_buffer_size=4M
table_open_cache=8000
key_buffer_size=2G
local-infile=0
tmp_table_size=32M
max_heap_table_size=32M
slow_query_log=1
slow_query_log_file="/var/log/mysql-slow-queries.log"
long_query_time=10

innodb_buffer_pool_size=10G
innodb_buffer_pool_instances = 10
innodb_log_file_size=1024M
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_autoinc_lock_mode=0
innodb_read_io_threads=4
innodb_write_io_threads=8

Mysql tuner results are this

 -------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 911M (Tables: 2990)
[--] Data in InnoDB tables: 3G (Tables: 2349)
[--] Data in MEMORY tables: 0B (Tables: 38)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'darssopt_mi@%' hasn't specific host restriction.
[!!] User 'darssoptions@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 6h 22m 27s (1M q [79.246 qps], 264K conn, TX: 6G, RX: 190M)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory     : 31.0G
[--] Max MySQL memory    : 17.0G
[--] Other process memory: 1.4G
[--] Total buffers: 12.0G global + 33.2M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 12.8G (41.33% of installed RAM)
[OK] Maximum possible memory usage: 17.0G (54.76% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (21/1M)
[OK] Highest usage of available connections: 15% (23/151)
[OK] Aborted connections: 0.08%  (219/264034)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 3% (8K temp sorts / 235K sorts)
[!!] Joins performed without indexes: 3128
[OK] Temporary tables created on disk: 19% (22K on disk / 115K total)
[OK] Thread cache hit rate: 99% (775 created / 264K connections)
[OK] Table cache hit rate: 99% (5K open / 6K opened)
[OK] Open file limit used: 15% (6K/40K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.6% (399M used / 2B cache)
[OK] Key buffer size / total MyISAM indexes: 2.0G/181.8M
[OK] Read Key buffer hit rate: 100.0% (79M cached / 8K reads)
[!!] Write Key buffer hit rate: 93.5% (460K cached / 29K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 10.0G/3.4G
[OK] InnoDB buffer pool instances: 10
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (4991812913 hits/ 4991885443 total)
[!!] InnoDB Write Log efficiency: 41.29% (38836 hits/ 94048 total)
[OK] InnoDB log waits: 0.00% (0 waits / 55212 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB 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:
    Restrict Host for user@% to user@SpecificDNSorIp
    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
    Adjust your join queries to always utilize indexes. Please note this
    calculation is made by adding Select_full_join + Select_range_check
    status values and triggered when the total >250
Variables to adjust:
    join_buffer_size (> 24.0M, or always use indexes with joins)

results of top

  top - 14:51:35 up 4 days, 22:36,  3 users,  load average: 5.88, 5.85, 6.11
Tasks: 275 total,   3 running, 271 sleeping,   0 stopped,   1 zombie
%Cpu(s):  9.1 us,  0.8 sy,  0.1 ni, 67.1 id, 22.9 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 32460092 total,  1248560 free,  5231404 used, 25980128 buff/cache
KiB Swap: 16760828 total, 16506348 free,   254480 used. 26023012 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 670307 mysql     20   0 15.638g 3.728g   8680 S  62.1 12.0 103:40.03 mysqld
 792649 adv+      20   0  235404  29852   8320 R   3.3  0.1   0:00.10 php-cgi
 792670 bdd       20   0  226492  20820   8292 R   1.3  0.1   0:00.04 php-cgi
   3359 root      30  10  277760  30508   2720 D   0.7  0.1  30:35.10 python2.7
     17 root      20   0       0      0      0 S   0.3  0.0  13:27.67 rcu_sched
     19 root      20   0       0      0      0 S   0.3  0.0  12:00.05 rcuos/1
     21 root      20   0       0      0      0 S   0.3  0.0   3:48.60 rcuos/3
    469 root       0 -20       0      0      0 S   0.3  0.0   8:35.86 kworker/+
    545 root      20   0       0      0      0 D   0.3  0.0   3:40.02 jbd2/md2+
   3336 root      30  10  417280   9084   1244 S   0.3  0.0  16:16.79 python2.7
 599324 root      20   0  107804  28732   2080 S   0.3  0.1   0:06.44 tailwatc+
 670290 root      20   0  186500  18572   3344 S   0.3  0.1   0:02.42 cPhulkd +
 792376 nobody    20   0  216284  24376   2428 S   0.3  0.1   0:00.02 httpd
 792449 superst   20   0   19972   2852   2116 S   0.3  0.0   0:00.05 pop3
 792498 root      20   0  155924   2356   1544 R   0.3  0.0   0:00.07 top
      1 root      20   0  338544   3812   2176 S   0.0  0.0   2:43.82 systemd
      2 root      20   0       0      0      0 S   0.0  0.0   0:00.18 kthreadd

This is result of iostat

[root@server ~]# iostat
Linux 3.10.0-427.36.1.lve1.4.40.el7.x86_64 (server.connect.rs)  03/23/2017     _x86_64_ (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.12    0.42    0.68   16.87    0.00   77.92

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sdb              65.24      2957.95       981.59 1264085940  419483991
sda              68.70      2749.45       981.59 1174981406  419483991
md1               0.00         0.05         0.00      23171         38
md0               0.70         0.87         1.92     370128     820580
md3              42.31       496.39       318.45  212134125  136090304
md2              68.18       104.71       650.46   44746045  277975140

Mysql is almost always above 40%, up to 100%

Yesterday I added disabled mysql services for 5 minutes and load was below 1.

please advise how to improve it

That's half of one core in CPU utilization... you have 4 cores. — jordanm, Mar 17 '17 at 21:56
Why is that too much - it looks like you have plenty of CPU available ? — user9517, Mar 17 '17 at 21:57
my uptime is: 22:58:19 up 4:30, 4 users, load average: 4.93, 4.76, 4.75. I am receiving mails with alert about high average cpu load 6 often. When I check processes mysql is only one with high numbers. All others are below 4%. Mysql goes up to 100% — emir, Mar 17 '17 at 22:00
Can you share your findings? How did you manage to lower the CPU usage from 90% to 40%? — Martin Dimitrov, Jan 16 '18 at 23:17
I was changing parameters in MySQL, I am not sure now what was the correct, but problems were repeating after. At the end, I used Munin to analyse the system and saw that disk2 was very slow. I have software raid so that all was linked. After replacing disk I had better results. — emir, Jan 17 '18 at 09:19

Vijay Muddu · Accepted Answer · 2017-03-23T09:49:59.203

2

As i can see you have core i7 cpu if mysql is using 100% also then its using only one core, your usage can go up to 700%

Also check if there is read-write delay wait time for resources If any slave replication is enabled try disabling it and check you web server which is sending queries to mysql, it can be bug or bad query check also for slow queries and zombie process

check below link for details

https://bobcares.com/blog/fix-mysql-high-cpu-usage/2/

edited Mar 23 '17 at 09:49

answered Mar 18 '17 at 11:39

Vijay Muddu

436
2
9

I am now checking whether there is some problem with hard disks, after I finish examining I will check what you provided. Thanks – emir Mar 18 '17 at 15:16
Second hard disk didn't show any problem, but after replacing that disk it was fine. – emir Jan 17 '18 at 09:16

score 1 · Answer 2 · edited Apr 13 '17 at 12:14

1

You need to

Install monitoring.
Gather data.
Analyse data
Make changes
Monitor changes for effectiveness
...

Scientific method is you friend.

edited Apr 13 '17 at 12:14

Community

1

answered Mar 17 '17 at 22:06

user9517

114,104
20
206
289

I am doing that, but it is going too slow. I managed to lower MySQL CPU usage from constantly >90% to about 40% and average CPU usage of server is lower, but I think that it can be still more optimized. – emir Mar 17 '17 at 22:11

score 0 · Answer 3 · answered Mar 23 '17 at 10:11

To expand a litte on what Vijay Muddu said:

100% in your output is 100% of one core - and your system has 8 of those, so I don't think the CPU level is in and of itself 'too high' - it certainly is hogging system resources in a way to would seem to impact other things.
it might help to see the top's header, e.g. these lines:

top - 10:09:45 up 17:47, 10 users, load average: 1.23, 0.93, 0.64 Tasks: 319 total, 1 running, 318 sleeping, 0 stopped, 0 zombie %Cpu(s): 5.8 us, 1.2 sy, 0.0 ni, 92.9 id, 0.1 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 7867484 total, 1611600 free, 3221660 used, 3034224 buff/cache KiB Swap: 524284 total, 524284 free, 0 used. 3419288 avail Mem

Notably, I would check what your value for wa (WAIT) is - and if it is significant, check the output of iostat.

I have updated mysql configuration, output from tuner and added iostat output. — emir, Mar 23 '17 at 13:59

MySQL is using too much CPU

3 Answers3