1

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.

  • Look at the explain from both server, and see that both uise the same index. or try a new one a combined for type and status. – nbk May 1 '20 at 22:30
  • Your SELECT required delivering all the rows to the client. That means that network I/O may have been the bottleneck. – Rick James May 2 '20 at 21:58
  • There was actually a LIMIT that phpmyadmin automatically added to the query. Plus I didn't have that issue with 5.7 or MariaDB which would have delivered the same content. – Talik Eichinger May 4 '20 at 21:25
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.

  • Yeah, I recently upgraded from 5.6 to 5.7, and it definitely slowed down, but is at least usable. 8.0 completely breaks the site. The explains actually look identical. I updated all optimizer settings to the same ones on 5.7 but no change. – Talik Eichinger May 1 '20 at 23:06
  • Analyze shows the table as OK. In this particular table I don't have a good reason to use myISAM, but I do have a couple other tables that do (huge reads, few writes). Do you know if MariaDB is more or less a safe swap? Or will it take code change to be compatible? – Talik Eichinger May 1 '20 at 23:09
  • MyISAM is slower even on read-only loads and has been for well over a decade. MariaDB is a reasonably safe swap, but you should always test extensively first. – Gordan Bobic May 1 '20 at 23:36
  • It is definitely not in my testing for read-heavy tables. I'll give MariaDB a try, seems like MySQL has been going downhill and I might as well get off of it. Thanks. – Talik Eichinger May 2 '20 at 0:17
  • 1
    Oracle is phasing out MyISAM. Virtually no effort has been put into it in a long time. – Rick James May 2 '20 at 22:05
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.