Optimizing mysql 8.0.15 only innodb for 400 million requests per day?

Question

MySQL

The transition was from mysql 5.7.18 (90% myisam) to mysql 8.0.15 innodb (100%).
Having smoked the off-mana muscle of the 8th (there are no worthwhile articles even over the hill about him) and after a little driving inno and a new muscle on LAN, I decided to make a transition on an online project. The increase in speed is noticed even by eye!
But I'm experiencing problems (because my knowledge of innodb is not enough due to the fact that for the first time I use only innodb on a loaded database)
1*). mysql 8.0.15 somehow behaves strangely in terms of load distribution across all cores / threads out of 16 threads, at first 3-4 were loaded by 80-90% at 6000 online and
ø per minute: 243.575
ø per second: 4.059
after tweaking my.cnf a little and setting irqbalance mysql began to use exactly half of 8 threads out of 16, the remaining 8 are always at 0%.
After thinking a little, I realized that I have innodb_thread_concurrency = 18, re-setting to 34-36 did not give results, but when set to 0 (default), mysql even started to go beyond 8 threads,
but judging by htop it works somehow strange: sometimes 1-2 threads are loaded by 70-90% and the rest 0%-1% (Yes, yes, I understand that a heavy request that cannot multithread cron job, etc., etc. but slow log 2sec is empty and cron cut down to check) ??
In general, I see something like this in htop (this is 200k - + requests per minute)

With myisam mysql 5.7, this was not the case mysql almost always hawala almost all threads under such a load, and 1-2-3 threads were loaded at 90% current when a query from cron worked for a long time.
I played with their newest latest implemented features:
innodb_log_spin_cpu_abs_lwm
innodb_log_spin_cpu_pct_hwm
innodb_log_wait_for_flush_spin_hwm
innodb_parallel_read_threads
difference + - I didn't notice the difference by eye (I didn't find any information about them on working projects). Perhaps I did not use them correctly. ended up leaving the default. On these baubles it is interesting to hear a comment from someone who used it in real life.
2*).Why does not the pool eat up the entire allocated buffer, which I gave out to it in 32 gigabytes, but toko 11 gigabytes + - When on mysql 5.7 myisam, a minute after starting the database, it already ate 22+ gigabytes of RAM. ?
mysqltuner I will be able to use Toko tomorrow as the base does not plow at all in time.
Hardware:
i9-9900K, 64gb 2400mhz, 2 ssd over terabyte RAID 1 ( [[email protected] ~]# 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 )
-----------------------
Additions taken at 10k online:
Average reads per cpu peak within 15 min:
%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 0.0 hi, 0.2 si, 0.0
st
ni, 88.8 id, 0.0 wa, 0.0 hi, 0.2 si, 0.0 st
iotop average:

spoiler

But sometimes it jumps into iotop like this:

spoiler

iostat

spoiler

Output mysqltuner
Well, so-so stray. It checks the main thing. I was only interested in:
[!!] InnoDB buffer pool instances: 16 and [!!] InnoDB Write Log efficiency: 73.36% (42700256 hits/ 58205341 total) I don't know what he wants here.
------------------------------
centos 7.5
3gb database dump, ~7gb in progress.
3*). How normal is it to make a backup via mysqldump with quick and without locking to the muzzle server every 2 hours
ssh 192.168.1.1 mysqldump --single-transaction=1 -uroot dbname > /bks/bk_$(date +%d%m%y, %T).sql
?
4*). I will give everyone a + for possible edits / hints 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_unicode_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

Answer 1

Stop twisting the values ​​to incredible values ​​without understanding what it generally affects.

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...
In short, it says that you need to test your particular queries on your particular data with different sort_buffer values ​​in order to find optimal value. Suddenly it can be very small.
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... - here it is written under what conditions temporary tables on the disk will be created regardless of how much you unscrew tmp_table_size

max_user_connections = 6000
max_connections = 18000

It seems to me that this is a game and you need an order of magnitude fewer connections, but here you have to wait for the output of mysqltuner.
Since you have all innodb tables, you should look towards xtrabackup