Been sitting on this for 12 hours straight (it's noon now, so I will read/respond when I woke up again).
I have made the big fault of recommending a database upgrade of our productive environment to increase performance.
We can NOT go back, it's nearly 6 terrabytes of storage and downgrades are not possible with mysql 8.0.
Also using a pre upgrade snapshot backup is no solution (days of work).
I would like to solve the horrible performance that resulted from switching server to latest version
A few details:
Environment: Linux Stretch on AWS i3.8xlarge (32 cpus, 240gb ram)
Server: Mysql 8.0 in mysql/ Docker container using bind mounts and host networking
Storage: Amazon AWS EBS IO1 storage (6 TB) with 20,000 IOPS reserved.
The storage delivers 500mb/sec-600mb/sec in fio tests.
CPU: Usually the 32 cores are 50-60% used but since mysql 8.0 it's idle (10-15% used)
RAM: 200GB are dedicated to mysql, before 8.0 it took not long and it was used. Now it takes many hours until mysql is able to fill the buffer.
The core problem:
The speed dropped about 20 times in comparison to 5.7
Innodb/mysql is not using the disk efficiently.
Using the original configuration it was reading with 15 mb/sec (simple counts took minutes to run because the IBD file was not read properly)
I have since disabled performance schema, which helped to at least operate the server again at 10% load.
I disabled bin logging, maybe that helped a bit more, not sure.
I tried increasing read/write threads to 64 (that completely stalled the server)
I invested good 8 hours in trying to tune the mysql configuration, I managed to "push" mysql from using only 5mb/sec to now up to 50mb/sec.
To make sure:
Of course I did test disk IO from inside docker, it's exactly similar to outside.
The main disk is mostly idle, the other disks completely idle.
The system is 90% idle when it should be occupied.
[mysqld] user=mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql tmpdir = /mysql_tmp # compatibility default_authentication_plugin=mysql_native_password character_set_server=latin1 collation_server=latin1_swedish_ci log-error = /var/log/mysql_error.log bind-address = 0.0.0.0 sql_mode= "NO_ENGINE_SUBSTITUTION" interactive_timeout = 3600 wait_timeout = 900 max_allowed_packet = 64M thread_stack = 256K thread_cache_size = 192 max_connections = 1600 max_user_connections = 1500 #query_cache_limit = 3M #query_cache_size = 200M #query_cache_type = 1 table_open_cache = 2500 key_buffer_size = 64M # index in memory for myisam innodb_buffer_pool_size = 190G innodb_log_file_size = 256M tmp_table_size = 250M max_heap_table_size = 250M join_buffer_size = 2M #pagecleaners - those were uncommented on 5.7 #innodb_buffer_pool_instances=8 #innodb_page_cleaners=2 innodb_io_capacity=5000 innodb_io_capacity_max=20000 # tried 64, that totally stalled the database innodb_read_io_threads = 8 innodb_write_io_threads = 8 #in pre 5.7 times I had consistent 300mb/sec writes, now it's useless innodb_lru_scan_depth=256 skip-name-resolve secure_file_priv="" #innodb_checksum_algorithm = crc32 #binlog_checksum = CRC32 # this one at least made it possible so I can go to bed, with performance_schema the database was unuseable performance_schema=OFF skip-log-bin
fs.aio-max-nr = 1048576 fs.aio-nr = 139264 fs.binfmt_misc.status = enabled fs.dentry-state = 355223 335269 45 0 0 0 fs.dir-notify-enable = 1 fs.epoll.max_user_watches = 51660308 fs.file-max = 25224638 fs.file-nr = 19136 0 25224638 fs.inode-nr = 70145 5686 fs.inode-state = 70145 5686 0 0 0 0 0 fs.inotify.max_queued_events = 16384 fs.inotify.max_user_instances = 128 fs.inotify.max_user_watches = 8192 fs.lease-break-time = 45 fs.leases-enable = 1 fs.mqueue.msg_default = 10 fs.mqueue.msg_max = 10 fs.mqueue.msgsize_default = 8192 fs.mqueue.msgsize_max = 8192 fs.mqueue.queues_max = 256 fs.nr_open = 1048576 fs.overflowgid = 65534 fs.overflowuid = 65534 fs.pipe-max-size = 1048576 fs.protected_hardlinks = 1 fs.protected_symlinks = 1 fs.quota.allocated_dquots = 0 fs.quota.cache_hits = 0 fs.quota.drops = 0 fs.quota.free_dquots = 0 fs.quota.lookups = 0 fs.quota.reads = 0 fs.quota.syncs = 62 fs.quota.warnings = 1 fs.quota.writes = 0 fs.suid_dumpable = 0
avg-cpu: %user %nice %system %iowait %steal %idle 13.86 0.71 10.84 3.23 0.11 71.26 Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn xvdh 1795.31 13923.89 26159.95 256609017 482112504
As you can see it's just reading at 14mb/sec, writing at 26mb/sec ..
With mysql 5.7 it was doing up to 200mb reading and writing.
The disk is basically idle. It can deliver 10 times the performance but for an unknown reason innodb/mysql are not doing it anymore.
Another change I was forced to make:
I have a hundred users connecting per second through apache/php, every connection usually triggered a "SELECT count(*) FROM information_schema.processlist" The new mysql wasn't able to do that anymore, it queued 50+ of those selects so I instead made an asynchronous task that inserts the processlist into an innodb table every 5 seconds.
It's just another sign how less performant the new mysql is reacting, it's even choked by processlists.
core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 985342 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 65536 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 985342 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
Spent half a day reading into internas, taking your suggestions in consideration and what I was told on IRC.
I did the opposite of the professional: I made 10 changes at once, I can't afford so many restarts in a gradual change process:
1) Giving the system lots of parallel write opportunity without choking it
innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_thread_concurrency=64 # cpus*2
2) Speeding up the background sync:
3) Disabling highest reliability settings that have a hard performance impact
performance_schema=OFF skip-log-bin sync_binlog=0 innodb_flush_log_at_trx_commit=0 # not crash safe, 0 is crash safe
4) More multithreading in backend memory
5) Increasing logfiles significantly, increasing logfile buffer moderatly
innodb_log_file_size = 3G # innodb_log_buffer_size = 64M
What happened: About 10 times increase in read performance,1.5x times write performance, I am not where I want to be but it's 15 times faster than before!
IOPS usage doubled from ~5-6k to 9k-12k, so I am at 60% IO usage
CPU usage increased from 7% to 50%
My goal would be 80% IO and CPU usage by the database, I think other variables are bottlenecking.
In real time use: I have a huge insert running before and after change (next to the usual load).
Before change the speed was about 3000 rows per second, after the above changes it's 8000 rows per second.
Thought I share this as the change in performance is extreme and I've only reached 50% of what should be possible.
I think the problem can be considered half solved, I've made another update after the previous successful one and the performance is acceptable now.
The last changes involved write/read threads. I've put them to 32 each.
write buffer increased to 128M (for my heavy workload higher might be better)
logfiles increased to 8GB
buffer_pool_instances increased to 64 (max) for better memory fragmentation
page_cleaners increased to 64 (max) to have one for each buffer instance.
write performance increased by another ~20%, read performance increased by another ~30%.
It's been a 24 hour ride to get mysql perform acceptable, definitely not a simple upgrade.
Current configuration: https://pastebin.com/9vsbEQxt show engine status innodb: https://pastebin.com/kCjnmtze show global variables: https://pastebin.com/aMdQxWcA global status: https://pastebin.com/VbG1yzHX