MySQL на виртуальной машине, ожидание высокого ввода-вывода, но низкий TPS
Мы пытаемся разобраться в странной ситуации с нашей установкой MySQL на ВМ. Мы используем Mysql 5.7 и RHEL 7 с XFS.
Мы наблюдаем, когда выполняем несколько выборочных запросов, IOWait достигает 40-50%, в то время как мы видим, что скорость чтения с диска не превышает 25-30 Мбит / с.
Мы проверили на уровне ОС, но мы легко получаем 500-600 Мбит / с, когда пытаемся выполнить копирование файла и другие тесты скорости чтения и записи с диска (поэтому мы предполагаем, что это не узкое место ввода-вывода диска)
IOPS показывает более 20 000, в среднем 9–10 000.
Мы пытаемся выяснить, что может вызвать дисковый ввод-вывод. Подождите, пока у нас достаточно быстрых дисков и достаточно свободного ЦП. Некоторые из ключевых переменных MySQL перечислены ниже:
ПОКАЗАТЬ ГЛОБАЛЬНЫЕ ПЕРЕМЕННЫЕ, КАК innodb_io%
innodb_io_capacity 200
innodb_io_capacity_max 2000
ПОКАЗАТЬ ГЛОБАЛЬНЫЕ ПЕРЕМЕННЫЕ, КАК '% thread%'
innodb_purge_threads 4
innodb_read_io_threads 4
innodb_thread_concurrency 0
innodb_thread_sleep_delay 10000
innodb_write_io_threads 4
max_delayed_threads 20
max_insert_delayed_threads 20
myisam_repair_threads 1
performance_schema_max_thread_classes 50
performance_schema_max_thread_instances -1
thread_cache_size 100
thread_handling один поток на соединение
thread_stack 262144
ПОКАЗАТЬ ГЛОБАЛЬНЫЙ СТАТУС КАК "% thread%"
Delayed_insert_threads 0
Performance_schema_thread_classes_lost 0
Performance_schema_thread_instances_lost 0
Slow_launch_threads 2
Кэшированные темы 61
Связанные темы 561
Создано_потоков 44399
Threads_running 2
Выберите @@ Max_connections: 1200
1. Описание медленного запроса приведено ниже:
EXPLAIN
SELECT COUNT(msgid)
FROM `2018-10-30`
WHERE priority=1
AND (message LIKE '%596f7572204f6e652054696d652050494e20%'
OR message LIKE '%4f545020666f7220%'
OR message LIKE '%4f545020616e642072656620%'
OR message LIKE '%4f545020746f20%'
);
id 1
select_type ПРОСТОЙ
EXPLAIN
SELECT COUNT(msgid)
FROM 2018-10-30
WHERE priority=1
AND (message LIKE '%596f7572204f6e652054696d652050494e20%'
OR message LIKE '%4f545020666f7220%'
OR message LIKE '%4f545020616e642072656620%'
OR message LIKE '%4f545020746f20%'
);
стол 30-10-2018
разделы \ N
введите ВСЕ
possible_keys \ N
ключ \ N
key_len \ N
ссылка \ N
ряды 28431345
фильтровано 3.76
Дополнительно Использование где
2. Описание медленного запроса приведено ниже:
EXPLAIN
SELECT COUNT(msgid)
FROM `2018-10-30`
WHERE priority=1
AND ISDFlag=0
AND msgsubmitid IS NOT NULL
AND (message LIKE '%596f7572204f6e652054696d652050494e20%'
OR message LIKE '%4f545020666f7220%'
OR message LIKE '%4f545020616e642072656620%'
OR message LIKE '%4f545020746f20%'
);
id 1
select_type ПРОСТОЙ
стол 30-10-2018
разделы \ N
введите ВСЕ
possible_keys index_msgsubmitid
ключ \ N
key_len \ N
ссылка \ N
ряды 28431345
фильтровано 0,19
Дополнительно Использование где
Создать таблицу
"СОЗДАТЬ ТАБЛИЦУ 2018-10-30 (
MsgId bigint (20) НЕ NULL,
UserId int (11) НЕ NULL,
Status бит (1) ПО УМОЛЧАНИЮ NULL,
Priority int (11) НЕ NULL,
MsgStatus int (11) ПО УМОЛЧАНИЮ '1111',
DestinationNumber varchar (50) НЕ NULL,
OrginatorName varchar (11) ПО УМОЛЧАНИЮ NULL,
OrginatorNumber varchar (20) ПО УМОЛЧАНИЮ NULL,
MsgSubmitID varchar (100) ПО УМОЛЧАНИЮ NULL,
MsgStatusMsg varchar (1000) ПО УМОЛЧАНИЮ NULL,
MsgDeliveryDateTime varchar (50) ПО УМОЛЧАНИЮ NULL,
Message varchar (500) НЕ NULL,
IPaddress varchar (15) НЕ NULL,
TransDate datetime NOT NULL,
SubmitDateTime datetime DEFAULT NULL,
SMSType int (11) ПО УМОЛЧАНИЮ NULL,
DateTimeToSend datetime DEFAULT NULL,
Subject varchar (100) ПО УМОЛЧАНИЮ NULL,
ISDFlag tinyint (4) ПО УМОЛЧАНИЮ NULL,
GatewayID int (11) ПО УМОЛЧАНИЮ NULL,
SmscSubmitDateTime datetime DEFAULT NULL,
ClientMsgId varchar (100) ПО УМОЛЧАНИЮ NULL,
Source int (10) ПО УМОЛЧАНИЮ '0',
CreatedDateTime datetime NOT NULL CURRENT_TIMESTAMP по умолчанию,
UpdatedDateTime datetime ПО УМОЛЧАНИЮ CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
ОСНОВНОЙ КЛЮЧ (MsgId),
КЛЮЧ index_msgsubmitid (MsgSubmitID),
КЛЮЧ index_gatewayid (GatewayID),
КЛЮЧ index_TransDate (TransDate),
КЛЮЧ index_dstn_no (DestinationNumber),
КЛЮЧ index_UserId (UserId),
КЛЮЧ index_MsgStatus (MsgStatus)
) ENGINE = InnoDB DEFAULT CHARSET = latin1 "
Индекс таблицы 
iostat во время загрузки
[ ~]$ iostat -xm 5 3
Linux 3.10.0-957.5.1.el7.x86_64 (...) 04/09/2019 _x86_64_ (24 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
4.13 0.00 1.54 1.56 0.00 92.77
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
fd0 0.00 0.00 0.00 0.00 0.00 0.00 8.00 0.00 31.50 31.50 0.00 31.50 0.00
sde 0.00 0.03 45.51 54.98 0.73 2.30 61.65 0.17 1.64 0.95 2.22 0.49 4.89
sdi 0.00 0.00 0.46 0.18 0.23 0.09 1016.01 0.02 31.79 6.59 95.41 1.15 0.07
sdh 0.00 1.77 112.94 27.89 1.77 0.69 35.77 0.13 0.96 0.79 1.65 0.48 6.83
sdc 0.00 0.18 114.18 144.24 2.11 7.36 75.07 0.33 1.26 0.95 1.50 0.45 11.62
sda 0.00 0.01 0.01 0.04 0.00 0.00 245.47 0.00 9.96 4.75 11.82 0.84 0.00
sdj 0.00 0.01 65.86 4.17 1.04 0.10 33.41 0.06 0.87 0.80 1.92 0.54 3.77
sdd 0.57 0.91 0.12 0.18 0.00 0.00 64.37 0.00 4.88 1.43 7.12 1.90 0.06
sdb 0.00 0.05 12.34 7.21 0.31 0.37 71.69 0.03 1.30 0.88 2.03 0.57 1.11
sdf 0.00 0.00 33.24 9.79 0.52 0.33 40.69 0.04 1.01 0.82 1.67 0.53 2.27
sdg 0.00 0.00 71.83 6.64 1.12 0.26 35.98 0.07 0.84 0.72 2.13 0.51 3.97
dm-0 0.00 0.00 2.73 1.44 0.15 0.01 76.44 0.00 1.17 1.31 0.90 0.60 0.25
dm-1 0.00 0.00 0.68 1.09 0.00 0.00 8.01 0.02 10.23 1.22 15.91 0.31 0.06
dm-2 0.00 0.00 453.14 249.19 7.43 11.24 54.44 0.81 1.15 0.84 1.72 0.30 21.30
dm-3 0.00 0.00 0.00 0.04 0.00 0.00 105.64 0.00 10.38 2.45 10.40 0.56 0.00
dm-4 0.00 0.00 0.49 0.50 0.24 0.23 981.47 0.03 25.41 6.51 44.16 1.00 0.10
dm-5 0.00 0.00 0.01 6.02 0.00 0.03 9.42 0.01 1.07 4.12 1.07 0.46 0.28
avg-cpu: %user %nice %system %iowait %steal %idle
4.40 0.00 1.44 15.94 0.00 78.22
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
fd0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sde 0.00 0.00 941.00 4.40 14.70 1.94 36.05 0.90 0.96 0.92 9.68 0.81 76.62
sdi 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdh 0.00 2.00 0.00 3.80 0.00 0.03 13.47 0.00 0.58 0.00 0.58 0.32 0.12
sdc 0.00 0.40 863.40 247.00 13.49 11.70 46.46 1.12 1.01 0.78 1.80 0.63 69.84
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdj 0.00 0.00 0.00 0.20 0.00 0.00 16.00 0.00 0.00 0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 62.40 0.00 0.97 31.80 0.08 1.25 0.00 1.25 0.14 0.88
sdf 0.00 0.00 1818.40 0.00 28.41 0.00 32.00 2.10 1.15 1.15 0.00 0.54 98.94
sdg 0.00 0.00 131.40 0.20 2.05 0.00 32.00 0.18 1.34 1.34 1.00 1.33 17.50
dm-0 0.00 0.00 0.00 0.60 0.00 0.00 11.33 0.00 0.67 0.00 0.67 0.33 0.02
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 3754.60 314.00 58.67 14.61 36.88 4.38 1.08 1.02 1.79 0.25 99.90
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-5 0.00 0.00 0.00 5.80 0.00 0.03 8.83 0.00 0.38 0.00 0.38 0.24 0.14
avg-cpu: %user %nice %system %iowait %steal %idle
4.30 0.00 1.36 15.71 0.00 78.62
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
fd0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sde 0.00 0.00 1004.60 3.60 15.70 1.70 35.34 0.89 0.89 0.86 9.11 0.78 78.80
sdi 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdh 0.00 2.00 0.00 3.80 0.00 0.02 13.05 0.00 0.74 0.00 0.74 0.63 0.24
sdc 0.00 0.00 883.40 135.60 13.80 7.78 43.37 0.87 0.86 0.78 1.35 0.69 70.42
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdj 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 28.00 0.00 0.44 32.00 0.03 1.15 0.00 1.15 0.19 0.54
sdf 0.00 0.00 1762.60 0.00 27.54 0.00 32.00 2.09 1.19 1.19 0.00 0.56 98.88
sdg 0.00 0.00 126.00 0.00 1.97 0.00 32.00 0.18 1.39 1.39 0.00 1.39 17.54
dm-0 0.00 0.00 0.00 0.20 0.00 0.00 32.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 3776.20 167.00 59.00 9.91 35.79 4.07 1.03 1.01 1.49 0.25 99.96
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-5 0.00 0.00 0.00 5.80 0.00 0.02 8.55 0.00 0.83 0.00 0.83 0.41 0.24
Монтировать выход
$ mount
sysfs on /sys type sysfs (rw,nosuid,nodev,noexec,relatime)
proc on /proc type proc (rw,nosuid,nodev,noexec,relatime)
devtmpfs on /dev type devtmpfs (rw,nosuid,size=49397072k,nr_inodes=12349268,mode=755)
securityfs on /sys/kernel/security type securityfs (rw,nosuid,nodev,noexec,relatime)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev)
devpts on /dev/pts type devpts (rw,nosuid,noexec,relatime,gid=5,mode=620,ptmxmode=000)
tmpfs on /run type tmpfs (rw,nosuid,nodev,mode=755)
tmpfs on /sys/fs/cgroup type tmpfs (ro,nosuid,nodev,noexec,mode=755)
cgroup on /sys/fs/cgroup/systemd type cgroup (rw,nosuid,nodev,noexec,relatime,xattr,release_agent=/usr/lib/systemd/systemd-cgroups-agent,name=systemd)
pstore on /sys/fs/pstore type pstore (rw,nosuid,nodev,noexec,relatime)
cgroup on /sys/fs/cgroup/devices type cgroup (rw,nosuid,nodev,noexec,relatime,devices)
cgroup on /sys/fs/cgroup/cpu,cpuacct type cgroup (rw,nosuid,nodev,noexec,relatime,cpuacct,cpu)
cgroup on /sys/fs/cgroup/pids type cgroup (rw,nosuid,nodev,noexec,relatime,pids)
cgroup on /sys/fs/cgroup/net_cls,net_prio type cgroup (rw,nosuid,nodev,noexec,relatime,net_prio,net_cls)
cgroup on /sys/fs/cgroup/perf_event type cgroup (rw,nosuid,nodev,noexec,relatime,perf_event)
cgroup on /sys/fs/cgroup/blkio type cgroup (rw,nosuid,nodev,noexec,relatime,blkio)
cgroup on /sys/fs/cgroup/memory type cgroup (rw,nosuid,nodev,noexec,relatime,memory)
cgroup on /sys/fs/cgroup/cpuset type cgroup (rw,nosuid,nodev,noexec,relatime,cpuset)
cgroup on /sys/fs/cgroup/freezer type cgroup (rw,nosuid,nodev,noexec,relatime,freezer)
cgroup on /sys/fs/cgroup/hugetlb type cgroup (rw,nosuid,nodev,noexec,relatime,hugetlb)
configfs on /sys/kernel/config type configfs (rw,relatime)
/dev/mapper/rhel-root on / type xfs (rw,relatime,attr2,inode64,noquota)
mqueue on /dev/mqueue type mqueue (rw,relatime)
debugfs on /sys/kernel/debug type debugfs (rw,relatime)
hugetlbfs on /dev/hugepages type hugetlbfs (rw,relatime)
/dev/mapper/rhel-var on /var type xfs (rw,relatime,attr2,inode64,noquota)
/dev/mapper/rhel-home on /home type xfs (rw,nosuid,nodev,noexec,relatime,attr2,inode64,noquota)
/dev/mapper/rhel-tmp on /tmp type xfs (rw,relatime,attr2,inode64,noquota)
/dev/mapper/rhel-lv_dam on /dam_agent type ext4 (rw,relatime,data=ordered)
/dev/sda1 on /boot type xfs (rw,relatime,attr2,inode64,noquota)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw,relatime)
tmpfs on /run/user/42 type tmpfs (rw,nosuid,nodev,relatime,size=9882784k,mode=700,uid=42,gid=42)
tmpfs on /run/user/987 type tmpfs (rw,nosuid,nodev,relatime,size=9882784k,mode=700,uid=987,gid=981)
tmpfs on /run/user/1012 type tmpfs (rw,nosuid,nodev,relatime,size=9882784k,mode=700,uid=1012,gid=1012)
tmpfs on /run/user/1005 type tmpfs (rw,nosuid,nodev,relatime,size=9882784k,mode=700,uid=1005,gid=1005)
systemd-1 on /proc/sys/fs/binfmt_misc type autofs (rw,relatime,fd=51,pgrp=1,timeout=0,minproto=5,maxproto=5,direct,pipe_ino=19059084)
binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,relatime)
tmpfs on /run/user/0 type tmpfs (rw,nosuid,nodev,relatime,size=9882784k,mode=700)
Статистика сервера
Процессор: 24
Ядра: 24
Розетки: 12
Оперативная память: 96 ГБ
Статистика ioping
В ходе расследования мы обнаружили следующую статистику операций ввода-вывода, которая показывает, что некоторые запросы достигают 5 мсек, и все еще исследуют, может ли это повлиять на пропускную способность диска ввода-вывода:
# ioping /var/
4 KiB <<< /var/ (xfs /dev/dm-2): request=1 time=6.12 ms (warmup)
4 KiB <<< /var/ (xfs /dev/dm-2): request=2 time=569.9 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=3 time=618.6 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=4 time=505.7 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=5 time=534.8 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=6 time=744.6 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=7 time=1.10 ms (slow)
4 KiB <<< /var/ (xfs /dev/dm-2): request=8 time=447.6 us (fast)
4 KiB <<< /var/ (xfs /dev/dm-2): request=9 time=578.0 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=10 time=1.11 ms (slow)
4 KiB <<< /var/ (xfs /dev/dm-2): request=11 time=586.4 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=12 time=449.4 us (fast)
4 KiB <<< /var/ (xfs /dev/dm-2): request=13 time=402.0 us (fast)
4 KiB <<< /var/ (xfs /dev/dm-2): request=14 time=650.4 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=15 time=497.9 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=16 time=4.78 ms (slow)
4 KiB <<< /var/ (xfs /dev/dm-2): request=17 time=534.5 us (fast)
4 KiB <<< /var/ (xfs /dev/dm-2): request=18 time=8.27 ms (slow)
4 KiB <<< /var/ (xfs /dev/dm-2): request=19 time=876.8 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=20 time=3.99 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=21 time=1.04 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=22 time=1.20 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=23 time=980.6 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=24 time=2.26 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=25 time=794.6 us (fast)
4 KiB <<< /var/ (xfs /dev/dm-2): request=26 time=963.0 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=27 time=1.91 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=28 time=1.04 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=29 time=643.9 us (fast)
4 KiB <<< /var/ (xfs /dev/dm-2): request=30 time=1.40 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=31 time=837.2 us
4 KiB <<< /var/ (xfs /dev/dm-2): request=32 time=1.54 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=33 time=5.13 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=34 time=381.3 us (fast)
4 KiB <<< /var/ (xfs /dev/dm-2): request=35 time=1.03 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=36 time=1.27 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=37 time=1.99 ms
4 KiB <<< /var/ (xfs /dev/dm-2): request=38 time=827.9 us
^C
--- /var/ (xfs /dev/dm-2) ioping statistics ---
37 requests completed in 52.5 ms, 148 KiB read, 705 iops, 2.75 MiB/s
generated 38 requests in 37.5 s, 152 KiB, 1 iops, 4.06 KiB/s
min/avg/max/mdev = 381.3 us / 1.42 ms / 8.27 ms / 1.59 ms
Выход MySQLTuner находится по этой ссылке: https://pastebin.com/H4pxRttg
MySQL my.cnf: https://pastebin.com/CEcjvBRS
Показать глобальный статус: https://pastebin.com/c54xPmtT
Показать глобальные переменные: https://pastebin.com/9edrGmaL
показать список процессов: https://pastebin.com/gNwF0KpG
верхний
Ulimit
iostat
iostat2
df
Мы будем очень благодарны за любую информацию о том, где искать.
Проблема в том, что ваш message поле это varchar() и вы выполняете полнотекстовый поиск по неиндексированному столбцу - с вашим запросом ведущий % вызывает полнотекстовое сканирование всего столбца данных, поскольку MySQL может использовать только префикс данных перед первым подстановочным знаком для традиционных индексов BTREE.
Полнотекстовый поиск с помощью MySQL - не лучший вариант использования, но если вы твердо намерены использовать MySQL, взгляните на FULLTEXT index - это позволит MySQL эффективно запрашивать данные без полного сканирования таблицы.
https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html
Никакая настройка InnoDB не исправит проблему со схемой или запросом.
Ты только делаешь COUNTs? Если нет, покажите нам настоящий запросы. Есть ярлыки для создания COUNT быстро; мы не должны обсуждать их, если это не настоящая цель.
SELECT COUNT(msgid)
FROM `2018-10-30`
WHERE priority=1
AND (message LIKE '%596f7572204f6e652054696d652050494e20%'
OR message LIKE '%4f545020666f7220%'
OR message LIKE '%4f545020616e642072656620%'
OR message LIKE '%4f545020746f20%'
);
Не говори COUNT(msgid), что подразумевает тестирование msgid для того, чтобы быть NOT NULL. Просто скажи COUNT(*).
И ведущая wild card, и OR убийцы производительности. Однако переход на REGEXP мощь ускорить это немного. В настоящее время message столбец просматривается до 4 раз. При следующем: все делается «сразу»:
AND message REGEXP '596f7572204f6e652054696d652050494e20|4f545020666f7220|4f545020616e642072656620|4f545020746f20'
priority теперь занимает 4 байта; не стал бы TINYINT хватит?
Еще одно возможное ускорение - это индекс покрытия (и используйте `COUNT (*)):
INDEX(priority, message, ISDFlag, msgsubmitid)
Тогда по любому из представленных вами запросов будет меньше материала для сканирования. Они будут сканировать BTree индекса вместо более широкого BTree данных.
IOPS
Блоки таблицы (таблиц) кэшируются в buffer_pool. Когда он заполняется, идет ввод-вывод, выбрасывайте некоторые блоки и вводите другие блоки. Если основная таблица действительно велика, можно ожидать ввода-вывода. Если он помещается в buffer_pool, вы мощь не вижу ввода / вывода даже при просмотре таблицы.
UNHEX -- Это выглядит как message все шестнадцатеричные. Если это так, используйте UNHEX() и HEX() и объявить столбец VARBINARY(250) быть вдвое меньше. Опять же, меньше -> меньше операций ввода-вывода (когда таблица слишком велика для кеширования).
innodb_buffer_pool_size - в чем ценность этого? С 96 ГБ ОЗУ это должно быть около 75 ГБ. Насколько велик стол? Много ли таких таблиц? Держу пари, что есть, судя по неудобному названию стола.
Что произойдет, если одно и то же сообщение появится в два разных дня?
Анализ ПЕРЕМЕННЫХ и СТАТУСА
Наблюдения:
- Версия: 5.7.20-log
- 94,2 ГБ оперативной памяти
- Время работы = 30д 02:56:40
- Вы не работаете в Windows.
- Работает 64-битная версия
- Кажется, вы работаете полностью (или почти полностью) с InnoDB.
Более важные вопросы:
Некоторые предлагали ПЕРЕМЕННЫЕ изменения:
innodb_page_cleaners = 16
innodb_buffer_pool_instances = 16
innodb_lru_scan_depth = 256
innodb_read_io_threads = 8
innodb_write_io_threads = 8
long_query_time = 2
Очень мало SELECT? Так это в основном «только для записи»? Сгруппированы ли вставки?
Хотя innodb_log_file_size меньше, чем должно быть для интенсивной записи, вероятно, сейчас не стоит прилагать усилия для его изменения. (5G лучше, чем 2G.)
Если вы используете SSD, вы также можете выключить innodb_flush_neighbors.
Ваш ввод-вывод, похоже, обрабатывает больше, чем innodb_io_capacity = 200 указывает; поднять его. Предложите 500.
Речь идет о репликации? Является ли машина рабом? Некоторые значения, относящиеся к репликации, кажутся странными, в частности: slave_skip_errors установлено странное значение. Вы «скрываете проблемы под ковриком»?
Почти половина SELECT выполняет сканирование таблицы. Это довольно много, и, возможно, необходимо изучить. OTOH, количество выборок довольно мало.
Замена сохраненной процедуры примерно 4 раза в день - это довольно дорого.
SHOW TABLES происходит 2-3 раза в секунду - разве этого нельзя избежать?
Детали и другие наблюдения:
( Innodb_buffer_pool_reads ) = 1,006,346,347 / 2602600 = 386 /sec - Скорость чтения ввода-вывода InnoDB buffer_pool - проверьте innodb_buffer_pool_size
( Innodb_buffer_pool_pages_flushed ) = 513,074,244 / 2602600 = 197 /sec - Пишет (сбрасывает) - проверяем innodb_buffer_pool_size
( Key_blocks_used * 1024 / key_buffer_size ) = 19 * 1024 / 1024M = 0.00% - Процент использования key_buffer. Самая высокая отметка. - Уменьшите key_buffer_size, чтобы избежать ненужного использования памяти.
( table_open_cache ) = 32,163 - Число дескрипторов таблиц для кеширования - Обычно достаточно нескольких сотен.
( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 71680M / 8 = 8960MB - Размер каждого экземпляра buffer_pool. - Экземпляр должен быть не менее 1 ГБ. В очень большой оперативной памяти есть 16 экземпляров.
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 - Объем работы очистителей страниц каждую секунду. - «InnoDB: page_cleaner: предполагаемый цикл 1000 мс занял ...» можно исправить, снизив lru_scan_depth: рассмотрите 1000 / innodb_page_cleaners
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 - innodb_page_cleaners - Рекомендовать установить innodb_page_cleaners на innodb_buffer_pool_instances
( innodb_lru_scan_depth ) = 1,024 - «InnoDB: page_cleaner: намеченный цикл 1000 мс занял ...» можно исправить, уменьшив lru_scan_depth
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((1006346347 + 513074244) ) / 2602600 = 583 /sec - InnoDB I / O - Увеличить innodb_buffer_pool_size?
( Innodb_os_log_written ) = 7,973,352,303,616 / 2602600 = 3063610 /sec - Это показатель того, насколько загружен InnoDB. - Очень простаивает или очень занято InnoDB.
( Innodb_log_writes ) = 1,662,275,231 / 2602600 = 638 /sec
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 7,973,352,303,616 / (2602600 / 3600) / 2 / 2048M = 2.57 - Коэффициент - (см. Минуты)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 2,602,600 / 60 * 2048M / 7973352303616 = 11.7 - Минуты между ротациями журнала InnoDB. Начиная с 5.6.8, это можно изменять динамически; не забудьте также изменить my.cnf. - (Рекомендация 60 минут между ротациями несколько произвольна.) Отрегулируйте innodb_log_file_size. (Нельзя изменить в AWS.)
( Com_rollback ) = 760,459 / 2602600 = 0.29 /sec - ОТКАТЫ в InnoDB. - Чрезмерная частота откатов может указывать на неэффективную логику приложения.
- (OTOH, Com_rollback очень низкий по сравнению с Com_commit.)
( Innodb_dblwr_writes ) = 29,374,160 / 2602600 = 11 /sec - «Буфер двойной записи» записывает на диск. «Двойные записи» - признак надежности. Некоторым более новым версиям / конфигурациям они не нужны. - (Признак других проблем)
( innodb_flush_neighbors ) = 1 - Небольшая оптимизация при записи блоков на диск. - Используйте 0 для SSD-накопителей; 1 для HDD.
( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 1021090541 + 513083786 ) / 2602600 / 200 = 294.7% - Если> 100%, нужно больше io_capacity. - Увеличьте innodb_io_capacity, если диски могут с этим справиться.
( innodb_io_capacity ) = 200 - Число операций ввода-вывода на диске в секунду. 100 для медленных дисков; 200 для прядильных приводов; 1000-2000 для SSD; умножить на коэффициент RAID.
( sync_binlog ) = 0 - Используйте 1 для дополнительной безопасности, при некоторых затратах на ввод-вывод = 1 может привести к большому количеству "завершения запроса"; = 0 может привести к "бинлогу в невозможной позиции" и потере транзакций в случае сбоя, но это быстрее.
( innodb_thread_concurrency ) = 0 - 0 = позволить InnoDB выбрать лучший вариант для concurrency_tickets. - Установите 0 или 64. Это может снизить нагрузку на ЦП.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF - Регистрировать ли все взаимоблокировки. - Если вас беспокоят тупиковые ситуации, включите это. Внимание: если у вас много тупиковых ситуаций, это может привести к большой записи на диск.
( local_infile ) = local_infile = ON - local_infile = ON - потенциальная проблема безопасности
( bulk_insert_buffer_size / _ram ) = 8M / 101146479820.8 = 0.01% - Буфер для многострочных INSERT и LOAD DATA - Слишком большой может угрожать размеру ОЗУ. Слишком маленький размер может помешать таким операциям.
( (Queries-Questions)/Queries ) = (5936481203-59444814)/5936481203 = 99.0% - Доля запросов, находящихся внутри сохраненных подпрограмм. - (Неплохо, если высокое; но это влияет на обоснованность некоторых других выводов.)
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (2193033569 + 372855165 + 1643709242 + 0) / 1660237104 = 2.54 - Количество запросов на фиксацию (при условии, что все InnoDB) - Низкое: может помочь сгруппировать запросы в транзакции; Высокий: длинные транзакции напрягают разные вещи.
( Select_scan ) = 7,124,788 / 2602600 = 2.7 /sec - полное сканирование таблиц - добавление индексов / оптимизация запросов (если они не являются крошечными таблицами)
( Select_scan / Com_select ) = 7,124,788 / 15138927 = 47.1% -% выборок, выполняющих полное сканирование таблицы. (Может быть обманут сохраненными подпрограммами.) - Добавить индексы / оптимизировать запросы
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (2193033569 + 1643709242 + 0 + 0 + 372855165 + 571) / 2602600 = 1617 /sec - записей / сек - 50 записей / сек + сброс журнала, вероятно, максимизируют емкость записи ввода-вывода обычных дисков
( expire_logs_days ) = 3 - Как скоро автоматически очистить binlog (по прошествии этого количества дней) - Слишком большой (или ноль) = занимает дисковое пространство; слишком маленький = необходимо быстро реагировать на сбой сети / машины. (Не актуально, если log_bin = OFF)
( slave_pending_jobs_size_max / max_allowed_packet ) = 16M / 4M = 4 - Для параллельных подчиненных потоков - slave_pending_jobs_size_max не должно быть меньше max_allowed_packet
( slave_skip_errors ) = slave_skip_errors = 1 03 21 05 41 062 - Какие случаи ошибок игнорировать. - Лучше изменить код, чем скрыть проблемы.
( long_query_time ) = 10 - Отсечка (секунды) для определения «медленного» запроса. - Предложить 2
Аномально маленький:
(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.0036
Аномально большой:
( Innodb_pages_read + Innodb_pages_written ) / Uptime = 589
Binlog_cache_use = 635 /sec
Com_begin = 633 /sec
Com_commit = 637 /sec
Com_commit + Com_rollback = 638 /sec
Com_create_procedure = 0.15 /HR
Com_dealloc_sql = 39 /HR
Com_delete = 631 /sec
Com_do = 2.3 /HR
Com_drop_procedure = 0.15 /HR
Com_execute_sql = 42 /HR
Com_insert = 842 /sec
Com_load = 0.79 /HR
Com_prepare_sql = 42 /HR
Com_rename_table = 0.05 /HR
Com_show_create_proc = 2.9 /sec
Com_show_tables = 2.6 /sec
Com_show_warnings = 92 /HR
Com_signal = 4.6 /HR
Com_slave_start = 0.0014 /HR
Com_slave_stop = 0.0014 /HR
Com_update = 143 /sec
Handler_commit = 4507 /sec
Handler_delete = 635 /sec
Handler_prepare = 4503 /sec
Handler_update = 2978 /sec
Innodb_buffer_pool_pages_data = 4.51e+6
Innodb_buffer_pool_pages_total = 4.59e+6
Innodb_buffer_pool_write_requests = 27091 /sec
Innodb_data_read = 6426970 /sec
Innodb_data_reads = 392 /sec
Innodb_data_writes = 848 /sec
Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 198 /sec
Innodb_data_written = 9506808 /sec
Innodb_dblwr_pages_written = 196 /sec
Innodb_log_write_requests = 4670 /sec
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 5,259.0MB
Innodb_pages_created = 30 /sec
Innodb_pages_read = 392 /sec
Innodb_pages_read + Innodb_pages_written = 1.53e+9
Innodb_pages_written = 197 /sec
Innodb_rows_deleted = 635 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 1945 /sec
Innodb_rows_inserted = 1310 /sec
Innodb_rows_updated = 663 /sec
Max_execution_time_set = 16
Ongoing_anonymous_transaction_count = 1
Open_tables = 4,129
Performance_schema_digest_lost = 2.23e+7
Select_range / Com_select = 49.2%
Threads_cached = 315
auto_increment_offset = 2
innodb_open_files = 32,163
port = 3317
report_port = 3317
Аномальные строки:
Ssl_session_cache_mode = Unknown
event_scheduler = ON
have_ssl = YES
have_symlink = DISABLED
innodb_data_home_dir = /var/lib/mysql
innodb_fast_shutdown = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
session_track_system_variables = time_zone, autocommit, character_set_client, character_set_results, character_set_connection
slave_compressed_protocol = ON
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
Предложения для вашего экземпляра по улучшению MySQL IOPS,
SET GLOBAL innodb_io_capacity_max=18000 # from 2000
SET GLOBAL innodb_io_capacity=9000 # from 200
для тестирования и для применения в разделе my.cnf [mysqld] перед следующей остановкой / запуском служб.
Заявление об ограничении ответственности: я являюсь автором содержания веб-сайта, указанного в моем профиле, сетевом профиле и могу предоставить дополнительные предложения.
18 апреля 2019 г., поскольку значение SET GLOBAL variable_name = value применяется к «новым соединениям», подождите минимум 1 час, чтобы проверить влияние на время ожидания io. Если ваши процессы обычно занимают 3 часа, подождите три часа, чтобы оценить влияние, пожалуйста.
Скорость в секунду = RPS - предложения для рассмотрения в разделе my.cnf [mysqld]. Все это динамические переменные, и их можно установить с помощью SET GLOBAL global_name = Value;
read_rnd_buffer_size=262144 # from 8M to reduce handler_read_rnd_next RPS of 22,915
read_buffer_size=262144 # from 2M to reduce handler_read_next RPS of 52,015
innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for function
innodb_flushing_avg_loops=5 # from 30 to reduce innodb_buffer_pool_pages_dirty count of 17,452
innodb_change_buffer_max_size=15 # from 25 percent set aside from innodb_buffer_pool_size
Отказ от ответственности: я являюсь автором веб-контента для сайта, указанного в моем профиле, сетевого профиля, который включает контактную информацию.
Вы можете использовать ПОКАЗАТЬ ГЛОБАЛЬНЫЙ СТАТУС КАК '% dirty%'; ежечасно, чтобы пересмотреть сокращение счетчика innodb_buffer_pool_dirty_pages, которое положительно повлияет на сокращение запросов READS RPS для таблиц данных innodb.