0

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
3
  • What is the value of innodb_buffer_pool_size on each machine? Do you have only 14.5GB on each machine? Let's see the query; it may give some clues.
    – Rick James
    Dec 9, 2019 at 16:52
  • Please post the pt-summary and sysctl memory settings from your local machine for reference and comparison. Usually, Hyperthreading is recommended to be OFF when using MySQL. Dec 10, 2019 at 0:30
  • Additional information requests from your production server. Any SSD or NVME for data storage? Post on pastebin.com and share the links. From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Jan 4, 2020 at 23:23

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.