MySQL на виртуальной машине, ожидание высокого IO, но низкий 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
обработка потока в одном потоке
нить стека 262144
ПОКАЗАТЬ ГЛОБАЛЬНОЕ СОСТОЯНИЕ, КАК '% thread%'
Delayed_insert_threads 0
Performance_schema_thread_classes_lost 0
Performance_schema_thread_instances_lost 0
Slow_launch_threads 2
Threads_cached 61
Threads_connected 561
Threads_created 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%'
);
идентификатор 1
выберите тип SIMPLE
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
введите ВСЕ
возможные_ключи \N
ключ \N
key_len \N
ref \N
строки 28431345
отфильтрованный 3,76
Extra Используя где
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%'
);
идентификатор 1
выберите тип SIMPLE
стол 30-10-2018
разделы \N
введите ВСЕ
возможных_ключей index_msgsubmitid
ключ \N
key_len \N
ref \N
строки 28431345
отфильтрованный 0,19
Extra Используя где
Создать таблицу
"СОЗДАТЬ СТОЛ 2018-10-30 (MsgId bigint (20) НЕ NULL, UserId int (11) NOT NULL, Status бит (1) DEFAULT NULL, Priority int (11) NOT NULL, MsgStatus int (11) DEFAULT '1111', DestinationNumber varchar (50) NOT NULL, OrginatorName varchar (11) DEFAULT NULL, OrginatorNumber varchar (20) DEFAULT NULL, MsgSubmitID varchar (100) DEFAULT NULL, MsgStatusMsg varchar (1000) DEFAULT NULL, MsgDeliveryDateTime varchar (50) DEFAULT NULL, Message varchar (500) НЕ NULL, IPaddress varchar (15) НЕ NULL, TransDate datetime НЕ NULL, SubmitDateTime datetime DEFAULT NULL, SMSType int (11) DEFAULT NULL, DateTimeToSend datetime DEFAULT NULL, Subject varchar (100) DEFAULT NULL, ISDFlag tinyint (4) DEFAULT NULL, GatewayID int (11) DEFAULT NULL, SmscSubmitDateTime datetime DEFAULT NULL, ClientMsgId varchar (100) DEFAULT NULL, Source int (10) DEFAULT '0', CreatedDateTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, UpdatedDateTime datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
ОСНОВНОЙ КЛЮЧ (MsgId),
KEY index_msgsubmitid (MsgSubmitID),
KEY index_gatewayid (GatewayID),
KEY index_TransDate (TransDate),
KEY index_dstn_no (DestinationNumber),
KEY index_UserId (UserId),
KEY index_MsgStatus (MsgStatus)
) ENGINE = InnoDB 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
RAM: 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
Д.Ф.
Любая идея о том, где искать, будет принята с благодарностью.
4 ответа
Проблема в том, что ваш message поле является varchar() и вы выполняете полнотекстовый поиск по неиндексированному столбцу - с вашим запросом, ведущим % вызывает полнотекстовое сканирование всего столбца данных, поскольку MySQL может использовать только префикс данных перед первым подстановочным знаком для традиционных индексов BTREE.
Выполнение полнотекстового поиска с MySQL не является хорошим вариантом использования, но если вы решили использовать MySQL, взгляните на FULLTEXT index - это позволит MySQL эффективно запрашивать данные без полного сканирования таблицы.
https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html
Никакая настройка InnoDB не устранит проблему схемы или дизайна запроса.
Предложения для вашего примера, чтобы улучшить 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 часа, подождите три часа, чтобы проанализировать влияние, пожалуйста.
Ты только делаешь 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(*),
И ведущий джокер, и 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. Когда он заполняется, происходит I/O, выкидывают некоторые блоки и вводят другие блоки. Если основной стол действительно большой, вы можете ожидать ввода-вывода. Если он помещается в buffer_pool, вы можете не увидеть ввода-вывода, даже для сканирования таблицы.
UNHEX - Похоже, message все гекс. Если это так, используйте UNHEX() а также HEX() и объявить столбец VARBINARY(250) быть вдвое меньше. Опять же, меньше -> меньше ввода / вывода (когда таблица слишком велика для кэширования).
innodb_buffer_pool_size - какова ценность этого? С 96 ГБ ОЗУ должно быть около 75 ГБ. Насколько большой стол? Много ли таких таблиц? Бьюсь об заклад, есть, на основе неуклюжего имени таблицы.
Что произойдет, если одно и то же сообщение появится в два разных дня?
Анализ ПЕРЕМЕННЫХ и СОСТОЯНИЯ
Замечания:
- Версия: 5.7.20-log
- 94,2 ГБ ОЗУ
- Uptime = 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
Очень мало ВЫБИРАЕТ? Так это в основном "только для записи"? Вставлены ли пакеты?
Хотя innodb_log_file_size меньше, чем это должно быть для интенсивной записи, вероятно, это не стоит усилий, чтобы изменить сейчас. (5G будет лучше, чем 2G.)
Если вы используете SSD, вы можете также отключить innodb_flush_neighbors,
Ваш I/O, кажется, обрабатывает больше, чем 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 - ROLLBACKs в InnoDB.
- Чрезмерная частота откатов может указывать на неэффективную логику приложения.
- (OTOH, Com_rollback очень низкий по сравнению с Com_commit.)
( Innodb_dblwr_writes ) = 29,374,160 / 2602600 = 11 /sec - "Doublewrite buffer" записывает на диск. "Двойные надписи" являются признаком надежности. Некоторым более новым версиям / конфигурациям они не нужны. - (Симптом других проблем)
( innodb_flush_neighbors ) = 1 - Незначительная оптимизация при записи блоков на диск. - Используйте 0 для SSD накопителей; 1 для жесткого диска.
( ( 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 для твердотельных накопителей; умножить на коэффициент RAID.
( sync_binlog ) = 0 - Используйте 1 для дополнительной безопасности, при некоторой стоимости I/O =1 может привести к большому количеству "конца запроса"; =0 может привести к "binlog в невозможной позиции" и потерять транзакции в случае сбоя, но это быстрее.
( 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 - write /sec - 50 write /sec + log-flush, вероятно, максимально увеличат емкость записи ввода-вывода обычных дисков
( 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
Rate Per Second=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
Отказ от ответственности: я являюсь автором веб-контента для сайта, указанного в моем профиле, в сетевом профиле, который включает контактную информацию.
Вы можете использовать SHOW GLOBAL STATUS LIKE '%dirty%'; ежечасно анализировать уменьшение количества записей innodb_buffer_pool_dirty_pages, что окажет положительное влияние на снижение READS RPS для таблиц данных innodb.