Database insanely (and weirdly inconsistently) slow on 8.0

Question

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.

Answer 1

Welcome to the growing query planner insanity heisenbug. It started in 5.7 and got worse in 8.0. I suspect what you will find is that EXPLAIN for that query looks strikingly different between 5.7 and 8.0.

Your options are:

1) See if it regains some sanity if you change optimizer_% variables to the same values as on 5.7

2) Run ANALYZE TABLE accounting; repeately and hope it'll change it's mind. Note that this is subject to change as statistics estimation runs periodically in the background.

3) Use index hinting (USE/FORCE INDEX(indexname)). This is stable, but you'll be whack-a-mole-ing queries throughout your code base.

4) Switch to MariaDB which doesn't seem to suffer from this kind of query plan instability.

Don't use MyISAM unless you have an overwhelmingly good reason for doing so.

Answer 2

When writes are occurring on a MyISAM table, reads may be blocked. I suspect a query (or combination of queries) was locking the table for the 4 seconds. That is, the SELECT was probably blocked for 4 seconds, then quickly ran. The slowlog may have given an indication of this; what it turned on?

INDEX(type, status) (in either order) should help with the current query. However, this does not prevent the blockage.

Since MyISAM is the real problem, and the real cause is some unknown the query (or set of queries), the problem is likely to recur, regardless of what version of MySQL or MariaDB you are running.

If you stick with MyISAM (which I don't recommend), then sticking with MariaDB is probably wise. MySQL is likely to remove MyISAM, while MariaDB is likely to continue to support it longer.