?
最近mysql主库不正常，发现VIRT 占用45.2G，是RES 20G的2倍
PID USER????? PR? NI? VIRT? RES? SHR S %CPU %MEM??? TIME+? COMMAND
1749 admin???? 15?? 0 47.1g? 21g 4124 S 277.2 67.0? 33362:19 mysqld
但是从库正常的：
PID USER????? PR? NI? VIRT? RES? SHR S %CPU %MEM??? TIME+? COMMAND
4887 admin???? 15?? 0 23.3g? 21g 6052 S 115.6 69.9? 14040:45 mysqld
?
通过vmstat 1观察,发现swap s1在不停的进行
[admin@server23 ~]$ vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
?r? b?? swpd?? free?? buff? cache?? si?? so??? bi??? bo?? in?? cs us sy id wa st
?6? 0 16367452 1488060 124448 8715404??? 4??? 4?? 559?? 157??? 1??? 0 10? 9 80? 1? 0
?6? 0 16367452 1400536 124464 8724192?? 16??? 0? 9288? 4292 7660 8572 30 16 51? 3? 0
?1? 0 16367452 1726756 124488 8732264?? 24??? 0? 6972?? 284 6553 7077 22 17 59? 2? 0
?3? 0 16367452 1478972 124488 8736252?? 28??? 0? 4468?? 108 6209 6128 13 19 67? 2? 0
?9? 0 16367452 345228 124500 8740192?? 16??? 0? 3900?? 152 5815 6053 19 43 37? 1? 0
?5? 1 16367452 1269216 124528 8747000?? 88??? 0? 6828? 1028 7381 9250 19 40 36? 4? 0
?7? 0 16367452 1374012 124536 8751320??? 0??? 0? 4180?? 172 7222 7640 15 20 63? 1? 0
?1? 0 16367452 1322812 124544 8758732?? 44??? 0? 7232?? 144 7777 9032 22 45 30? 2? 0
?3? 0 16367452 1714968 124564 8765264?? 28??? 0? 6632?? 184 7333 7871 15 22 61? 2? 0
?
而从库没有swap
[admin@server24 ~]$ vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
?r? b?? swpd?? free?? buff? cache?? si?? so??? bi??? bo?? in?? cs us sy id wa st
?1? 1??? 184? 90464 122116 9251568??? 0??? 0?? 521?? 102??? 2??? 5 10? 3 87? 1? 0
?8? 2??? 184? 91428 122100 9250384??? 0??? 0? 6052?? 132 10121 10937 13? 4 82? 1? 0
?4? 0??? 184? 93196 122084 9248304??? 0??? 0? 5384?? 320 8936 9720 11? 3 84? 1? 0
?3? 1??? 184? 95680 122060 9245556??? 0??? 0? 4920?? 212 9340 10316 13? 3 83? 1? 0
?0? 0??? 184? 96840 122092 9244148??? 0??? 0? 5660? 1308 8676 9913 13? 3 83? 1? 0
?1? 0??? 184? 91836 122104 9250092??? 0??? 0? 5192?? 252 9328 10331 13? 3 83? 1? 0
?3? 0??? 184? 93952 122116 9248052??? 0??? 0? 5724? 4220 9909 10810 11? 4 83? 2? 0
?1? 0??? 184? 96468 122112 9244600??? 0??? 0? 3788?? 116 8408 8960 10? 2 87? 1? 0
?4? 0??? 184? 93152 122120 9248648??? 0??? 0? 3444?? 244 8713 9409 11? 2 85? 1? 0
?3? 0??? 184? 95248 122120 9245860??? 0??? 0? 5248?? 240 10407 11483 13? 4 82? 1? 0
?1? 0??? 184? 96660 122100 9244204??? 0??? 0? 6060?? 116 10993 12116 14? 3 81? 1? 0
?0? 0??? 184? 92560 122128 9249160??? 0??? 0? 4216? 4352 9067 9881 11? 3 85? 1? 0
?1? 0??? 184? 94348 122116 9247356??? 0??? 0? 5316?? 188 9545 10377 11? 3 84? 1? 0
?3? 0??? 184? 91232 122116 9251068??? 0??? 0? 2996?? 140 9781 10505 11? 4 85? 1? 0
?1? 1??? 184? 93168 122108 9248340??? 0??? 0? 5380? 1160 9258 10225 11? 3 85? 1? 0
?0? 0??? 184? 94048 122084 9247452??? 0??? 0? 6064?? 300 9889 10977 12? 3 83? 1? 0
?4? 1??? 184? 90324 122084 9251024??? 0??? 0? 3748?? 192 8821 9281 10? 3 86? 1? 0
?1? 0??? 184? 90496 122084 9251004??? 0??? 0? 6768?? 372 9418 10403 12? 3 83? 1? 0
?2? 0??? 184? 96116 122012 9244708??? 0??? 0? 8648?? 168 11239 12589 15? 4 80? 2? 0
?0? 0??? 184? 97124 122008 9244048??? 0??? 0? 6104? 4452 10512 11754 13? 4 82? 2? 0
?0? 0??? 184? 92872 122008 9248628??? 0??? 0? 4616?? 232 8550 9282 10? 3 86? 1? 0
?0? 0??? 184? 94056 121996 9248124??? 0??? 0? 6212?? 108 9501 10352 12? 3 84? 1? 0
?
最后发现是几个配置有问题：
sort_buffer_size??????? = 512M
join_buffer_size??????? = 512M
innodb_flush_log_at_trx_commit? = 1
这几个参数有问题
?
sort_buffer_size??????? = 2M
join_buffer_size??????? = 2M
innodb_flush_log_at_trx_commit? = 2
我改了这几个参数
sort_buffer_size??????? = 2M
join_buffer_size??????? = 2M
这俩是线程独享的参数
?
更多参考：
<