![相同的 mysql 查询在特定 Linux 机器上的裸 MariaDB 上执行时间更长](https://linux22.com/image/746052/%E7%9B%B8%E5%90%8C%E7%9A%84%20mysql%20%E6%9F%A5%E8%AF%A2%E5%9C%A8%E7%89%B9%E5%AE%9A%20Linux%20%E6%9C%BA%E5%99%A8%E4%B8%8A%E7%9A%84%E8%A3%B8%20MariaDB%20%E4%B8%8A%E6%89%A7%E8%A1%8C%E6%97%B6%E9%97%B4%E6%9B%B4%E9%95%BF.png)
我正在跟踪生产服务器上 MySQL 性能缓慢的问题,并遇到了一个奇怪的问题 - 相同的 MySQL 查询在CentOS 7.4.1708 3.14.32-xxxx-grs-ipv6-64
具有 125GB 内存的生产服务器上执行时间更长,而在同一数据库转储上的本地实例上运行的相同查询执行速度要快得多,具体来说,在本地环境(osX box 16 GB 内存)上完成大约需要 10-20 秒,在生产机器上完成大约需要 3-5 分钟。两台机器都使用默认的 MariaDB 配置,并且查询是测试时唯一正在运行的查询。
Mysql数据目录挂载在/home/mysql_data上这可能是原因吗?
使用了相同的 MySQL 转储,查询是从控制台运行的,MySQL dbname < test.sql
因此瓶颈显然是数据库,mariadb 是从 macos 上的端口和 linux 上的 repo 安装的(我也尝试过自定义编译版本),没有区别。
我开始对硬件产生怀疑,任何提示或建议都将不胜感激,附上pt-summary
输出
# Percona Toolkit System Summary Report ######################
Date | 2019-12-09 09:35:10 UTC (local TZ: EST -0500)
Hostname | ...
Uptime | 727 days, 23:51, 3 users, load average: 1.29, 1.48, 1.75
System | Supermicro; X10DRH; v123456789 (Rack Mount Chassis)
Service Tag | 123456789
Platform | Linux
Release | CentOS Linux release 7.4.1708 (Core)
Kernel | 3.14.32-xxxx-grs-ipv6-64
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.17
Compiler | GNU CC version 4.8.5 20150623 (Red Hat 4.8.5-16).
SELinux | Disabled
Virtualized | VMWare
# Processor ##################################################
Processors | physical = 2, cores = 16, virtual = 32, hyperthreading = yes
Speeds | 1x2596.593, 1x2597.156, 1x2600.437, 1x2601.093, 1x2605.312, 1x2607.093, 1x2610.375, 1x2613.937, 1x2619.562, 1x2655.750, 1x2752.312, 1x2759.343, 1x2769.937, 1x2780.906, 1x2835.000, 1x2841.281, 1x2851.593, 1x2855.437, 1x2880.000, 1x2882.812, 1x2896.968, 1x2913.187, 1x2923.500, 1x2945.625, 1x2951.062, 1x2974.312, 1x3004.500, 1x3029.343, 1x3062.625, 1x3076.218, 1x3166.593, 1x3191.250
Models | 32xIntel(R) Xeon(R) CPU E5-2630 v3 @ 2.40GHz
Caches | 32x20480 KB
# Memory #####################################################
Total | 125.9G
Free | 2.3G
Used | physical = 14.5G, swap allocated = 0.0, swap used = 0.0, virtual = 14.5G
Shared | 4.1G
Buffers | 109.1G
Caches | 106.7G
Dirty | 636 kB
UsedRSS | 15.4G
Swappiness | 1
DirtyPolicy | 30, 10
DirtyStatus | 0, 0
Locator Size Speed Form Factor Type Type Detail
========= ======== ================= ============= ============= ===========
P1_DIMMA1 16384 MB 2133 MHz DIMM DDR4 Synchronous
P1_DIMMB1 16384 MB 2133 MHz DIMM DDR4 Synchronous
P1_DIMMC1 16384 MB 2133 MHz DIMM DDR4 Synchronous
P1_DIMMD1 16384 MB 2133 MHz DIMM DDR4 Synchronous
P2_DIMME1 16384 MB 2133 MHz DIMM DDR4 Synchronous
P2_DIMMF1 16384 MB 2133 MHz DIMM DDR4 Synchronous
P2_DIMMG1 16384 MB 2133 MHz DIMM DDR4 Synchronous
P2_DIMMH1 16384 MB 2133 MHz DIMM DDR4 Synchronous
P1_DIMMA2 {EMPTY} Unknown DIMM DDR4 Synchronous
P1_DIMMB2 {EMPTY} Unknown DIMM DDR4 Synchronous
P1_DIMMC2 {EMPTY} Unknown DIMM DDR4 Synchronous
P1_DIMMD2 {EMPTY} Unknown DIMM DDR4 Synchronous
P2_DIMME2 {EMPTY} Unknown DIMM DDR4 Synchronous
P2_DIMMF2 {EMPTY} Unknown DIMM DDR4 Synchronous
P2_DIMMG2 {EMPTY} Unknown DIMM DDR4 Synchronous
P2_DIMMH2 {EMPTY} Unknown DIMM DDR4 Synchronous
# Mounted Filesystems ########################################
Filesystem Size Used Type Opts Mountpoint
/dev/md2 421G 62% ext4 rw,relatime,quota,usrquota,grpquota,data=ordered /R
/dev/md2 421G 62% ext4 rw,relatime,quota,usrquota,grpquota,data=ordered /R
devtmpfs 63G 0% devtmpfs rw,relatime,size=65953940k,nr_inodes=16488485,mode=755 /dev
tmpfs 13G 0% tmpfs rw /run/user/0
tmpfs 13G 0% tmpfs rw,nosuid,nodev,mode=755 /run/user/0
tmpfs 13G 0% tmpfs rw,nosuid,nodev,relatime,size=13197492k,mode=700 /run/user/0
tmpfs 13G 0% tmpfs ro,nosuid,nodev,noexec,mode=755 /run/user/0
tmpfs 63G 0% tmpfs rw /dev/shm
tmpfs 63G 0% tmpfs rw,nosuid,nodev,mode=755 /dev/shm
tmpfs 63G 0% tmpfs rw,nosuid,nodev,relatime,size=13197492k,mode=700 /dev/shm
tmpfs 63G 0% tmpfs ro,nosuid,nodev,noexec,mode=755 /dev/shm
tmpfs 63G 0% tmpfs rw /sys/fs/cgroup
tmpfs 63G 0% tmpfs rw,nosuid,nodev,mode=755 /sys/fs/cgroup
tmpfs 63G 0% tmpfs rw,nosuid,nodev,relatime,size=13197492k,mode=700 /sys/fs/cgroup
tmpfs 63G 0% tmpfs ro,nosuid,nodev,noexec,mode=755 /sys/fs/cgroup
tmpfs 63G 7% tmpfs rw /run
tmpfs 63G 7% tmpfs rw,nosuid,nodev,mode=755 /run
tmpfs 63G 7% tmpfs rw,nosuid,nodev,relatime,size=13197492k,mode=700 /run
tmpfs 63G 7% tmpfs ro,nosuid,nodev,noexec,mode=755 /run
# Disk Schedulers And Queue Size #############################
dm-0 | 128
md0 | 128
md1 | 128
md2 | 128
nbd0 | [deadline] 128
nbd1 | [deadline] 128
nbd10 | [deadline] 128
nbd11 | [deadline] 128
nbd12 | [deadline] 128
nbd13 | [deadline] 128
nbd14 | [deadline] 128
nbd15 | [deadline] 128
nbd2 | [deadline] 128
nbd3 | [deadline] 128
nbd4 | [deadline] 128
nbd5 | [deadline] 128
nbd6 | [deadline] 128
nbd7 | [deadline] 128
nbd8 | [deadline] 128
nbd9 | [deadline] 128
sda | [deadline] 128
sdb | [deadline] 128
# Disk Partioning ############################################
Device Type Start End Size
============ ==== ========== ========== ==================
/dev/dm-0 Disk 107374182400
/dev/md1 Disk 20970405888
/dev/md2 Disk 458591502336
/dev/sda Disk 480103981056
/dev/sda1 Part 4096 40962047 20970470912
/dev/sda2 Part 40962048 936648703 458591567360
/dev/sda3 Part 936648704 937695231 535821824
/dev/sdb Disk 480103981056
/dev/sdb1 Part 4096 40962047 20970470912
/dev/sdb2 Part 40962048 936648703 458591567360
/dev/sdb3 Part 936648704 937695231 535821824
# Kernel Inode State #########################################
dentry-state | 19102026 19047745 45 0 0 0
file-nr | 3328 0 512000
inode-nr | 1262883 278591
# LVM Volumes ################################################
Unable to collect information
# LVM Volume Groups ##########################################
Unable to collect information
# RAID Controller ############################################
Controller | Fusion-MPT SAS
# Network Config #############################################
Controller | Intel Corporation Ethernet Controller 10-Gigabit X540-AT2 (rev 01)
Controller | Intel Corporation Ethernet Controller 10-Gigabit X540-AT2 (rev 01)
FIN Timeout | 60
Port Range | 61000
# Interface Statistics #######################################
interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
========= ========= ========== ========== ========== ========== ==========
lo 12500000000000 8000000000 0 12500000000000 8000000000 0
bond0 0 0 0 0 0 0
dummy0 0 0 0 0 0 0
ifb0 0 0 0 0 0 0
ifb1 0 0 0 0 0 0
eth0 1000000000000 7000000000 0 5000000000000 4500000000 0
eth1 0 0 0 0 0 0
teql0 0 0 0 0 0 0
tunl0@NONE 0 0 0 0 0 0
sit0@NONE 0 0 0 0 0 0
ip6tnl0@NONE 0 0 0 0 0 0
docker0 175000000 450000 0 250000000 450000 0
# Network Devices ############################################
Device Speed Duplex
========= ========= =========
bond0 Unknown! Unknown!
eth0 1000Mb/s Full
eth1 Unknown! Unknown!
docker0
# Network Connections ########################################
Connections from remote IP addresses
35.158.140.87 2
45.94.25.205 1
46.38.144.17 4
46.38.144.32 2
46.38.144.57 3
46.38.144.146 2
46.38.144.179 3
46.38.144.202 1
46.229.168.150 1
54.36.148.89 1
54.36.148.155 1
54.36.148.199 1
54.36.149.27 1
54.36.150.48 1
54.36.150.58 1
54.36.150.85 1
54.36.150.138 1
63.135.176.20 2
64.250.124.70 2
66.249.64.104 1
66.249.64.106 1
66.249.64.234 1
66.249.64.236 1
74.121.199.234 2
92.118.38.38 4
121.66.252.155 1
127.0.0.1 80
136.145.228.221 2
160.10.5.26 2
162.247.242.5 3
178.62.77.163 6
188.163.83.193 3
203.125.15.242 1
Connections to local IP addresses
54.39.202.16 35
127.0.0.1 80
158.69.185.152 1
167.114.208.91 25
Connections to top 10 local ports
22 4
25 4
443 20
51554 1
52622 1
587 15
59914 1
80 2
8891 2
9000 60
States of connections
CLOSE_WAIT 1
ESTABLISHED 25
LISTEN 35
TIME_WAIT 125
# Top Processes ##############################################
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12492 mysql 20 0 61.652g 0.011t 15876 S 93.8 8.6 312:39.96 mysqld
30259 root 20 0 1023576 28600 4676 S 12.5 0.0 38262:50 newrelic-i+
1 root 20 0 191620 3876 1664 S 0.0 0.0 1429:05 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:32.51 kthreadd
3 root 20 0 0 0 0 S 0.0 0.0 107:31.39 ksoftirqd/0
5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:+
8 root 20 0 0 0 0 S 0.0 0.0 1407:32 rcu_sched
9 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
10 root rt 0 0 0 0 S 0.0 0.0 0:37.04 migration/0
# Notable Processes ##########################################
PID OOM COMMAND
29337 -17 sshd
26454 -17 systemd-udevd
27610 -17 postgres
# Simplified and fuzzy rounded vmstat (wait please) ##########
procs ---swap-- -----io---- ---system---- --------cpu--------
r b si so bi bo ir cs us sy il wa st
2 0 0 0 2 80 0 0 5 0 95 0 0
2 0 0 0 0 900 2500 4000 5 1 94 0 0
3 0 0 0 0 1000 2250 4500 10 0 89 0 0
1 0 0 0 0 600 1500 2000 6 0 94 0 0
7 0 0 0 0 250 2000 2000 9 1 90 0 0
# Memory mamagement ##########################################
Unable to get Transparent huge pages status.
# The End ####################################################
sysctl 内存设置:
vm.admin_reserve_kbytes = 8192
vm.block_dump = 0
vm.dirty_background_bytes = 0
vm.dirty_background_ratio = 10
vm.dirty_bytes = 0
vm.dirty_expire_centisecs = 3000
vm.dirty_ratio = 30
vm.dirty_writeback_centisecs = 500
vm.drop_caches = 0
vm.heap_stack_gap = 65536
vm.laptop_mode = 0
vm.legacy_va_layout = 0
vm.lowmem_reserve_ratio = 256 256 32
vm.max_map_count = 65530
vm.memory_failure_early_kill = 0
vm.memory_failure_recovery = 1
vm.min_free_kbytes = 45951
vm.min_slab_ratio = 5
vm.min_unmapped_ratio = 1
vm.mmap_min_addr = 4096
vm.nr_pdflush_threads = 0
vm.numa_zonelist_order = default
vm.oom_dump_tasks = 1
vm.oom_kill_allocating_task = 0
vm.overcommit_kbytes = 0
vm.overcommit_memory = 0
vm.overcommit_ratio = 50
vm.page-cluster = 3
vm.panic_on_oom = 0
vm.percpu_pagelist_fraction = 0
vm.scan_unevictable_pages = 0
vm.stat_interval = 1
vm.swappiness = 1
vm.user_reserve_kbytes = 131072
vm.vfs_cache_pressure = 100
vm.zone_reclaim_mode = 0