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.
Innodb status:
https://pastebin.com/XDgyNbk0
Mysql configuration:
[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
Sysctl fs:
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
iostat snapshot:
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.
Update:
Variables and global status: https://pastebin.com/pzhXV7hq
https://pastebin.com/jBTYLbY6
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.
ulimit
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
Big update
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:
innodb_lru_scan_depth=100
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
innodb_buffer_pool_instances=12
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.
Update
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.
Latest status:
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
