VELIK505
@VELIK505
Руководитель департамента profitcentr.com

Оптимизация mysql 8.0.15 only innodb под 400млн запросов в сутки?

Переход осуществлялся с mysql 5.7.18 (90% myisam) на mysql 8.0.15 innodb (100%).
Покурив офф ману мускуля 8ого (стоящих статей вообще нету даже за бугром про него) и Немного погоняв инно и новый мускуль на локалке и решился на онлайн проекте сделать переходик. Прирост в скорости замечен даже на глаз!
Но я испытываю проблемы (так как моих знаний в innodb не хватает в связи с тем что первый раз юзаю only innodb на нагружаемой базе)

1*). mysql 8.0.15 как то странно себя ведёт в плане распределения нагрузки по всем ядрам/потокам из 16 потоков поначалу было вгружено 3-4 на 80-90% при 6000 онлайна и
ø за минуту: 243,575
ø в секунду: 4,059

немного потюнив my.cnf и установки irqbalance mysql стала задействовать ровно на половину 8 потоков из 16 остальные 8 всегда на 0%.
Немного покумекав я понял что у меня innodb_thread_concurrency = 18 перевыставление на 34-36 не дало результатов токо при выставление в 0 (default) mysql чёто начала выходить за рамки 8ми потоков,

но судя по htop работает как то странно: иногда 1-2 потока загружены на 70-90% а остальные 0%-1% (Да да я понимаю что тяжелый запрос который не может в многопоточность крон задание и тд и тп но slow log 2сек пусто и крон вырубал для проверки) ??
В целом примерно такое вижу в htop (это на 200к - + запросов в мин)

5c966fd19e5c4316692590.jpeg

При myisam mysql 5.7 такого не было mysql почти всегда хавала почти все потоки при такой нагрузке и 1-2-3 потока вгружались на 90% токо когда какойнить запрос с крона отрабатывал долгий.

Игрался с их самыми новыми последними внедрёнными фичами:
innodb_log_spin_cpu_abs_lwm
innodb_log_spin_cpu_pct_hwm
innodb_log_wait_for_flush_spin_hwm
innodb_parallel_read_threads
разницы + - не заметил на глаз (ни где инфы на рабочих проектах не нашёл про них). Возможно я не правильно их заюзал. в итоге оставил по умолчанию. По этим феничкам интересно услышать коментарий кто юзал в реале.

2*). Почему не сьедает весь выделеный буфер пул который я выдал в 32 гига ей а токо 11 гиг + - Когда на mysql 5.7 myisam через минуту после запуска базы жрал уже 22+ гига озу. ?

mysqltuner смогу заюзать токо завтра так как база совсем не очём пашет ещё по времени.

Железо:
i9-9900K, 64gb 2400mhz, 2 ssd по терабайту RAID 1
( [root@mysql ~]# hdparm -tT /dev/md2

/dev/md2:
Timing cached reads: 33580 MB in 1.99 seconds = 16873.98 MB/sec
Timing buffered disk reads: 7880 MB in 3.00 seconds = 2626.15 MB/sec )
------------------------
Дополнения снятые при 10к онлайна:
Средние показания в пики cpu в течении 15 мин:
%Cpu(s): 9.7 us, 0.6 sy, 0.0 ni, 89.5 id, 0.0 wa, 0.0 hi, 0.2 si, 0.0 st
%Cpu(s): 11.1 us, 0.7 sy, 0.0 ni, 87.9 id, 0.1 wa, 0.0 hi, 0.2 si, 0.0 st
%Cpu(s): 10.4 us, 0.5 sy, 0.0 ni, 88.9 id, 0.0 wa, 0.0 hi, 0.2 si, 0.0 st
%Cpu(s): 19.7 us, 0.3 sy, 0.0 ni, 79.8 id, 0.1 wa, 0.0 hi, 0.1 si, 0.0 st
%Cpu(s): 10.4 us, 0.5 sy, 0.0 ni, 88.8 id, 0.0 wa, 0.0 hi, 0.2 si, 0.0 st

iotop в среднем:
spoiler
5c97616da041f690331943.jpeg

Но иногда в iotop проскакивает так:
spoiler
5c976194a8b49071163955.jpeg5c97621613cbf056192437.jpeg5c97629d9423d466093298.jpeg

iostat
spoiler
5c97655f4e34b047630481.jpeg
Вывод mysqltuner
Ну так себе приблуда. Чекает токо основное самое. Заинтересовало только:
[!!] InnoDB buffer pool instances: 16 и [!!] InnoDB Write Log efficiency: 73.36% (42700256 hits/ 58205341 total) тут не знаю чё хочет.
------------------------------

centos 7.5
Дамп бд 3gb, в работе ~7gb.

3*). На сколько нормальным решением делать бэкап через mysqldump с quick и без блокировки на сервак морды каждые 2 часа
ssh 192.168.1.1 mysqldump --single-transaction=1 -uroot dbname > /bks/bk_$(date +%d%m%y,%T).sql
?

4*). Всем поставлю + за возможные правки/подсказки my.cnf
spoiler
[client]
port = 3306
socket = /var/run/mysql/mysql.sock
default-character-set = utf8mb4

[mysqld_safe]
socket = /var/run/mysql/mysql.sock
nice = 0

[mysqld]
sql-mode="NO_ENGINE_SUBSTITUTION"
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysql/mysql.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
port = 3306
tmpdir = /dev/shm
basedir = /usr
user = mysql
skip-external-locking
skip-log-bin
skip_name_resolve
connect_timeout = 70
interactive_timeout = 70
wait_timeout = 70
join_buffer_size = 32M
max_connect_errors = 30
max_allowed_packet = 64M
table_open_cache = 4096
thread_stack = 384K
thread_cache_size = 500
max_user_connections = 6000
max_connections = 18000
net_buffer_length = 96K
max_heap_table_size = 1024M
tmp_table_size = 16384M
read_buffer_size = 196M
sort_buffer_size = 196M
read_rnd_buffer_size = 196M
default_authentication_plugin = mysql_native_password
validate_password.check_user_name=OFF
validate_password.dictionary_file=0
validate_password.length=0
validate_password.mixed_case_count=0
validate_password.number_count=0
validate_password.policy=LOW
validate_password.special_char_count=0
net_read_timeout=500
max_binlog_size = 100M
log_error = /var/log/mysql/mysql_errornew.log
log_slave_updates = OFF
innodb_log_compressed_pages = OFF
sync_binlog = 0
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 32768M
innodb_log_file_size = 4096M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 80
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 256M
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_stats_on_metadata = 0
innodb_log_spin_cpu_abs_lwm = 80
innodb_log_spin_cpu_pct_hwm = 50
innodb_log_wait_for_flush_spin_hwm = 400
innodb_parallel_read_threads = 4
innodb_open_files = 65536
innodb_sort_buffer_size = 64M
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 1000
innodb_doublewrite = 0
innodb_page_cleaners = 16
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
init-connect="SET NAMES utf8mb4"
skip-character-set-client-handshake
[mysqldump]
quick
quote-names
max_allowed_packet = 64M
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[isamchk]
key_buffer=512M
sort_buffer=512M
read_buffer=512M
write_buffer=512M
  • Вопрос задан
  • 2365 просмотров
Решения вопроса 3
@neol
Перестаньте выкручивать значения до неимоверных величин не разбираясь, на что это вообще влияет.

read_buffer_size = 196M
read_rnd_buffer_size = 196M

https://www.percona.com/blog/2007/09/17/mysql-what...

sort_buffer_size = 196M
innodb_sort_buffer_size = 64M

https://www.percona.com/blog/2007/08/18/how-fast-c...
Если коротко, то там написано, что нужно тестировать конкретно ваши запросы на конкретно ваших данных с разными значениями sort_buffer, чтобы найти оптимальное значение. Внезапно оно может оказаться очень небольшим.

join_buffer_size = 32M

On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload
https://dev.mysql.com/doc/refman/8.0/en/server-sys...

max_heap_table_size = 1024M
tmp_table_size = 16384M

https://dev.mysql.com/doc/refman/8.0/en/server-sys...
https://dev.mysql.com/doc/refman/8.0/en/internal-t... - тут написано при каких условиях временные таблицы на диске будут создаваться вне зависимости от того, на сколько вы выкрутите tmp_table_size

max_user_connections = 6000
max_connections = 18000

Мне кажется это дичь и вам надо на порядок меньше подключений, но тут надо подождать вывода mysqltuner.

На сколько нормальным решением делать бэкап через mysqldump с quick и без блокировки на сервак морды каждые 2 часа

Раз уж у вас все таблицы innodb, то стоит посмотреть в сторону xtrabackup
Ответ написан
@yaBliznyk
Попробуй лучше percona server. Там администрирование в ноль сведено и все процессоры будут задействованы сразу из коробки. У тебя появятся отличные утилиты по мониторингу, как бонус.
Ответ написан
VELIK505
@VELIK505 Автор вопроса
Руководитель департамента profitcentr.com
всё таки удалось добить как надо и заставить хавать все потоки и всю ОЗУ.
5ca0ae18d6c3e628848416.jpeg
Рабочий действущий конфиг:
spoiler
[client]
port		= 3306
socket		= /var/run/mysqld/mysql.sock
default-character-set = utf8mb4

[mysqld_safe]
socket		= /var/run/mysqld/mysql.sock
nice		= 0

[mysqld]
sql-mode="NO_ENGINE_SUBSTITUTION"
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysql.sock
datadir		= /var/lib/mysql
log-error	= /var/log/mysql/error.log
port		= 3306
tmpdir      = /dev/shm
basedir		= /usr
user		= mysql
skip-external-locking
skip-log-bin
skip_name_resolve
connect_timeout = 80
interactive_timeout = 80
wait_timeout = 80
net_write_timeout = 80
join_buffer_size = 32M
max_connect_errors = 30
max_allowed_packet = 64M
table_open_cache = 8192
thread_stack = 384K
thread_cache_size = 500
max_connections = 3000
net_buffer_length = 96K
max_heap_table_size = 1024M
tmp_table_size = 16384M
read_buffer_size = 196M
sort_buffer_size = 196M
read_rnd_buffer_size = 196M
#default_authentication_plugin = mysql_native_password
#validate_password.check_user_name=OFF
#validate_password.dictionary_file=0
#validate_password.length=0
#validate_password.mixed_case_count=0
#validate_password.number_count=0
#validate_password.policy=LOW
#validate_password.special_char_count=0
net_read_timeout=500
max_binlog_size         = 100M
log_error = /var/log/mysql/mysql_errornew.log
log_slave_updates = OFF
innodb_log_compressed_pages = OFF
sync_binlog = 0
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 32768M
innodb_log_file_size = 4096M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 256M
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_stats_on_metadata = 0
innodb_log_spin_cpu_abs_lwm = 80
innodb_log_spin_cpu_pct_hwm = 50
innodb_log_wait_for_flush_spin_hwm = 400
innodb_parallel_read_threads = 4
innodb_open_files =	65536
innodb_sort_buffer_size = 64M
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 1000
innodb_doublewrite = 0
innodb_page_cleaners = 16
innodb_lock_wait_timeout = 80
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
init-connect="SET NAMES utf8mb4"
skip-character-set-client-handshake
[mysqldump]
quick
quote-names
max_allowed_packet	= 64M
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[isamchk]
key_buffer=512M
sort_buffer=512M
read_buffer=512M
write_buffer=512M

Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы