I am tracing down slow MySQL performance on the production server and encountered a strange issue - the same MySQL query takes much longer to execute on the production CentOS 7.4.1708 3.14.32-xxxx-grs-ipv6-64 with 125GB of memory, when the same query, being run on the local instance on the same dump of the database executes much faster, specifically, it takes around 10-20s to finish on the local environment (osX box 16 GB of ram) and the 3-5mins or so on the production machine. Both machines used default MariaDB configs, and the query was the only one being run at the moment of testing.
Mysql data directory is mounted on /home/mysql_data may that be a reason?
The same MySQL dump was used, the query was run from console MySQL dbname < test.sql so the bottleneck is apparently is the database, the mariadb was installed from ports on macos and from repo on linux (I've tried custom compiled version as well) with no difference.
I started to be suspicious about the hardware, any hint or suggestion would be much appreciated, attaching pt-summary output
# 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 memory settings:
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
innodb_buffer_pool_sizeon each machine? Do you have only 14.5GB on each machine? Let's see the query; it may give some clues.