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
Threads_cached 61
Подключено к потокам 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
select_type 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
введите ВСЕ
possible_keys \ N
ключ \ N
key_len \ N
ref \ N
строки 28431345
отфильтровано 3,76
Дополнительно Использование where
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
ref \ N
строки 28431345
отфильтровано 0,19
Дополнительно Использование where
Create Table
"СОЗДАТЬ ТАБЛИЦУ 2018-10-30 (
MsgId bigint (20) NOT NULL,
UserId int (11 ) NOT NULL,
Status bit (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 ( ) DEFAULT NULL,
MsgStatusMsg varchar (1000) DEFAULT NULL,
MsgDeliveryDateTime varchar (50) DEFAULT NULL,
Сообщение varchar (500) NOT NULL IP, [ varchar (15) NOT NULL,
TransDate datetime NOT NULL,
SubmitDateTime datetime DEFAULT NULL,
SMSType int (11) DEFAULT NULL,
DateTimeTimeTime datetime DEFAULT NULL,
Subject varchar (100) DEFAULT NULL,
ISDFlag tinyint (4) DEFAULT NULL,
GatewayID int (11) DEFAULT NULL,
SmscSubmitDateTime datetime DEFAULT NULL,
ClientMsgId varchar NULL,
Источник int (10) DEFAULT '0',
CreatedDateTime datetime NOT NULL CURRENT_TIMESTAMP ПО УМОЛЧАНИЮ,
UpdatedDateTime datetime ПО УМОЛЧАНИЮ 11120903] datetime ПО УМОЛЧ. ]
ПЕРВИЧНЫЙ КЛЮЧ ( 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 ГБ
Статистика операций ввода-вывода
В ходе исследования мы обнаружили следующую статистику операций ввода-вывода, которая показывает, что некоторые эхо-запросы достигают 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 - это позволит MySQL эффективно запрашивать данные без полное сканирование таблицы.
https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html
Никакая настройка InnoDB не исправит проблему со схемой или запросом.
Скорость в секунду = 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 СТАТУС, КАК "% dirty%"; ежечасно, чтобы пересмотреть сокращение счетчика innodb_buffer_pool_dirty_pages, которое положительно повлияет на сокращение запросов READS RPS для таблиц данных 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 часа, подождите три часа, чтобы оценить влияние, пожалуйста.
Вы делаете только COUNT ? Если нет, покажите нам настоящие запросы. Есть ярлыки для ускорения 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 может немного ускорить его. В настоящее время столбец сообщение сканируется до 4 раз. При следующем: все делается «сразу»:
AND message REGEXP '596f7572204f6e652054696d652050494e20|4f545020666f7220|4f545020616e642072656620|4f545020746f20'
приоритет теперь занимает 4 байта; не будет ли TINYINT достаточно?
Еще одно возможное ускорение - иметь индекс покрытия (и использовать `COUNT (*)):
INDEX(priority, message, ISDFlag, msgsubmitid)
Тогда любой из ваших запросов представленный будет меньше материала для сканирования. Они будут сканировать BTree индекса, а не более широкое BTree данных.
IOPS
Блоки таблицы (таблиц) кэшируются в buffer_pool. Когда он заполняется, идет ввод-вывод, выбрасывайте некоторые блоки и вводите другие блоки. Если основная таблица действительно велика, можно ожидать ввода-вывода. Если он помещается в buffer_pool, вы можете не видеть ввода-вывода даже при сканировании таблицы.
UNHEX - Похоже, сообщение шестнадцатеричное. Если это так, используйте UNHEX () и HEX () и объявите столбец как VARBINARY (250) равным половине размера. Опять же, меньше -> меньше операций ввода-вывода (когда таблица слишком велика для кэширования).
innodb_buffer_pool_size - каково это значение? С 96 ГБ ОЗУ это должно быть около 75 ГБ. Насколько велик стол? Много ли таких таблиц? Готов поспорить, что есть, основываясь на неудобном названии таблицы.
Что произойдет, если одно и то же сообщение появится в два разных дня?
Анализ ПЕРЕМЕННЫХ и СОСТОЯНИЕ
Наблюдения:
- Версия: 5.7.20 -log
- 94,2 ГБ ОЗУ
- Время работы = 30d 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.)
Если вы используете твердотельные накопители, вы также можете отключить innodb_flush_neighbors .
Кажется, ваш ввод-вывод обрабатывает больше, чем innodb_io_capacity = 200 указывает; поднять его. Предложите 500.
Репликация задействована? Является ли машина рабом? Некоторые значения, относящиеся к репликации, кажутся странными, в частности:
slave_skip_errors установлено странное значение. Вы «скрываете проблемы под ковриком»?
Почти половина SELECT выполняет сканирование таблицы. Это довольно много, и, возможно, необходимо изучить. OTOH, количество выбранных вариантов довольно низкое.
Замена сохраненной процедуры примерно 4 раза в день довольно высока.
ПОКАЗАТЬ ТАБЛИЦЫ происходит 2-3 раза в секунду -- разве этого нельзя избежать?
Подробности и другие наблюдения:
(Innodb_buffer_pool_reads) = 1,006,346,347 / 2602600 = 386 / сек - Скорость чтения ввода-вывода InnoDB buffer_pool
- проверьте innodb_buffer_pool_size
(Innodb_buffer_pool_pages_flasted) = 513 074 244/2602600 = 197 / сек - Записывает (сбрасывает)
- проверьте 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 [1162420_page_page_page_
- Рекомендуем установить 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_flasted)) = ((1006346347 + 513074244)) / 2602600 = 583 / сек. InnoDB ввода / вывода
- Увеличить innodb_buffer_pool_size?
(Innodb_os_log_written) = 7,973,352,303,616 / 2602600 = 3063610 / сек - Это индикатор того, насколько загружен InnoDB.
- Очень простаивающая или очень загруженная InnoDB.
(Innodb_log_writes) = 1,662,275,231 / 2602600 = 638 / сек
(Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_3,3016,900 / размер) = 2,616,900 / размер 2048M = 2,57 - Соотношение
- (см. минуты)
(Время работы / 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 / сек - ОТКАТ в InnoDB.
- Чрезмерная частота откатов может указывать на неэффективную логику приложения.
- (OTOH, Com_rollback очень низкий по сравнению с Com_commit.)
(Innodb_dblwr_writes) = 29,374,160 / 2602600 = 11 / сек - «Буфер двойной записи» записывает на диск. «Двойная запись» - признак надежности. Некоторым более новым версиям / конфигурациям они не нужны.
- (Признак других проблем)
(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 для SSD; умножьте на коэффициент RAID.
(sync_binlog) = 0 - Используйте 1 для дополнительной безопасности, при некоторой стоимости ввода-вывода = 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 и ЗАГРУЗКИ ДАННЫХ
- Слишком большой размер может угрожать размеру оперативной памяти. Слишком маленький размер может помешать выполнению таких операций.
((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 / сек -полное сканирование таблицы
- Добавить индексы / оптимизировать запросы (если они не являются крошечными таблицами)
(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 / сек - пишет сек
- 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