我正在由 Planet 托管的专用虚拟 24GB RAM CentOS 服务器上运行高流量网站。
服务器经常因为 CPU 使用率过高 (%1500) 而死机
当我使用“top”命令检查正在运行的进程时，我发现平均负载总是超过 100，而 mysql 的 CPU 经常超过 1500%
我尝试调整 mysql、调整 apache、my.cnf、httpd.cnf，删除我不使用的 Apache 模块，但这并不能解决问题。
请告诉我您需要知道我的服务器配置中的哪些值以帮助我诊断问题。
提前致谢。
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Thread(s) per core: 2
Core(s) per socket: 4
Socket(s): 2
NUMA node(s): 2
Vendor ID: GenuineIntel
CPU family: 6
Model: 44
Stepping: 2
CPU MHz: 2400.093
BogoMIPS: 4799.88
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 12288K
NUMA node0 CPU(s): 0-3,8-11
NUMA node1 CPU(s): 4-7,12-15
top - 05:37:00 up 4 days, 22:04, 2 users, load average: 136.61, 145.42, 139.03
Tasks: 565 total, 8 running, 554 sleeping, 0 stopped, 3 zombie
Cpu(s): 28.6%us, 70.6%sy, 0.1%ni, 0.2%id, 0.0%wa, 0.0%hi, 0.5%si, 0.0%st
Mem: 24596732k total, 13172200k used, 11424532k free, 630632k buffers
Swap: 2097144k total, 0k used, 2097144k free, 7648348k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4850 mysql 15 -5 12.4g 1.1g 6036 S 1545.1 4.7 12992:15 mysqld
15846 root 20 0 162m 22m 916 R 4.2 0.1 0:00.62 lfd
15509 apache 20 0 632m 129m 5460 S 3.0 0.5 0:01.59 httpd
15276 apache 20 0 632m 128m 5504 R 2.7 0.5 0:01.85 httpd
15505 apache 20 0 634m 130m 5460 R 2.1 0.5 0:01.49 httpd
15792 apache 20 0 630m 125m 4816 S 2.1 0.5 0:00.43 httpd
3058 nginx 20 0 76576 34m 2268 S 1.8 0.1 107:48.32 nginx
15585 apache 20 0 632m 128m 5268 S 1.8 0.5 0:01.25 httpd
15827 apache 20 0 629m 124m 3752 S 1.8 0.5 0:00.10 httpd
cat /proc/meminfo
MemTotal: 24596732 kB
MemFree: 13807688 kB
Buffers: 1108524 kB
Cached: 4888972 kB
SwapCached: 0 kB
Active: 4929640 kB
Inactive: 3225108 kB
Active(anon): 1523440 kB
Inactive(anon): 635484 kB
Active(file): 3406200 kB
Inactive(file): 2589624 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 2097144 kB
SwapFree: 2097144 kB
Dirty: 16032 kB
Writeback: 0 kB
AnonPages: 2114752 kB
Mapped: 52792 kB
Shmem: 1648 kB
Slab: 2155004 kB
SReclaimable: 2028484 kB
SUnreclaim: 126520 kB
KernelStack: 5184 kB
PageTables: 105004 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 14395508 kB
Committed_AS: 16484584 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 463816 kB
VmallocChunk: 34345823536 kB
HardwareCorrupted: 0 kB
AnonHugePages: 800768 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 5696 kB
DirectMap2M: 2082816 kB
DirectMap1G: 23068672 kB
my.cnf
[mysqld]
tmpdir=/var/tmpfs
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#general_log=1
slow_query_log= 0
long_query_time =9
log_queries_not_using_indexes =0
max_connections = 400
key_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
max_heap_table_size = 600M
tmp_table_size = 600M
read_buffer_size = 320M
sort_buffer_size = 320M
join_buffer_size = 3G
table_definition_cache = 8000
table_open_cache = 220000
thread_cache_size = 384
wait_timeout = 50
interactive_timeout = 30
connect_timeout = 10
max_allowed_packet = 265M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 256M
query_cache_size = 200M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
open_files_limit = 3G
max_write_lock_count = 8
innodb_open_files = 500
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 60M
innodb_additional_mem_pool_size = 32M
innodb_log_files_in_group = 2
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
trafic shown by phpmyadmin for just one hour and half
Trafic réseau depuis le démarrage : 123 Gio
Ce serveur MySQL fonctionne depuis 0 jours, 1 heures, 33 minutes et 58 secondes. Il a démarré le Jeu 28 Janvier 2016 à 13:21.
Trafic ø par heure
Reçu 445 Mio 284,2 Mio
Envoyé 122,6 Gio 78,3 Gio
Total 123 Gio 78,6 Gio
Connexions ø par heure %
max. de connexions simultanées 170 --- ---
Tentatives échouées 3 253 2 077,12 0,55%
Arrêts prématurés 306 195,39 0,05%
Total 591 k 377,53 k 100,00%
答案1
谢谢，将 myisam 表更改为 innodb 解决了问题，现在 mysql CPU 最多为 300%，正常负载平均峰值为 5%