I recently tried moving a large DB to 8.0, and am seeing insanely bad speeds (100x slower in some cases). I've spent the last 2 days googling this and feel like I've tried everything and have not had any luck.
Some info: I spun up a new VM for the new instance. It has more ram, same SSD drives and should be generally faster than the old one, but I still have that running so I can compare the two.
I pulled one of the queries I found acting slow, and did some testing: Table accounting has ~100k rows. Here's where things get a little odd. I have a production and dev database on the new server and I am getting drastically different speeds. I have copied this particular table recently, so I know the table, structure and keys are identical in all 3 places. (Note cash and status are both ENUM)
SELECT * FROM `accounting` WHERE type='cash' AND status='pending' Response on 5.7: (Query took 0.0427 seconds.) Response on 8.0 dev db: (Query took 0.7911 seconds.) Response on 8.0 prod db: (Query took 4.1671 seconds.)
The first digit on those response times are consistent every time. I've speed tested the underlying hard drive and it is testing faster than on the old server.
I've tried every config option that came up while googling and none of them help significantly. Here is what I have. I've tried larger and smaller versions of most of these options. This server has nothing else happening on it, and this site isn't using it in production right now so I can experiment and restart as much as I need.
join_buffer_size=512K sort_buffer_size = 4M innodb_buffer_pool_size = 5G innodb_buffer_pool_instances=5 innodb_io_capacity = 2000 innodb_read_io_threads = 64 innodb_thread_concurrency = 16 innodb_write_io_threads = 64 innodb_log_file_size = 3G innodb_log_buffer_size = 64M innodb_lru_scan_depth=100 #optimizer_switch='derived_merge=off' #this significantly hurts performance join_buffer_size=8M tmp_table_size = 256M max_heap_table_size=256M table_open_cache=4000 performance_schema=OFF #roughly 25% speed increase skip-log-bin sync_binlog=0 innodb_flush_log_at_trx_commit=0
I'm really at a loss here. Any help would be appreciated.
Edit: I tried adding a type+status index on prod-8.0 db, and the speeds were blazing. When I removed the index, I am now getting speeds consistent with dev-8.0 db. Not sure what this tells us.
Edit 2: Table I'm testing with is MyISAM.
Update (and solution):
I ended up installing mariadb, and the queries are blazingly fast. I guess it's time to hop off the MySQL train.