我有一台 i7 3770 专用服务器，配备 32GB 内存，并安装了 Centos7，以下是 CPU 详细信息
[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
mysql 占用了太多 CPU。我用它来托管网站，没有访问量很大的高要求帐户。数据库是混合的，一些是 InnoDB，其他是 MyISAM，我无法将它们全部改为 InnoDB 或 MyISAM，它需要保持这种状态。
这是我的 mysql 配置
[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 的结果如下
-------- 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)
顶级结果
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
这是 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几乎一直在40%以上，最高可达100%
昨天我添加禁用的 mysql 服务 5 分钟，负载低于 1。
请告知如何改进
答案1
正如我所见，你有核心i7如果 mysql 的 CPU 使用率为 100%，则它仅使用一个核心，您的使用率可能会高达 700%
还请检查是否有读写延迟等待资源的时间如果启用了任何从属复制，请尝试禁用它，并检查向 mysql 发送查询的 Web 服务器，它可能是错误或坏查询，也请检查慢查询和僵尸进程
查看以下链接了解详情
答案2
答案3
稍微扩展一下维杰·穆杜说：
输出中的 100% 是指一个核心的 100% - 而您的系统有 8 个核心，因此我认为 CPU 水平本身并不是“太高” - 它肯定会占用系统资源，似乎会影响其他事物。
top查看标题可能会有所帮助，例如以下几行：
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
值得注意的是，我会检查你的 (WAIT) 值wa是多少 - 如果它很重要，请检查 iostat 的输出。