1

As part of a complete server move and upgrade (OS, Apache, and db) went from MariaDB 5.5 to MySQL 8.0.21. Hardware specs unchanged. Unfortunately despite many hours of reading and experimental tuning by both developer and sysadmin, performance is massively down. Some queries run OK on the new machine, but ANYTHING that requires any kind of table scan take seconds to many minutes to run (compared to always sub-1s on old install). A particularly bad example:

SELECT id,email FROM tickets WHERE key='md5stringkey'

tickets is MyISAM (on both installs) and some 700k rows long. key is not indexed. This query on 5.5 runs in 1s, but on 8.0 is ~20 minutes. Similarly a query like:

SELECT COUNT(*) FROM tickets WHERE type='service' AND key='md5stringkey'

where type is indexed, will take between nothing and 60s depending on how many records of type are found. Would seem to suggest that a temp table is being written with the results of the type criteria then slowly scanned for key. In the case of type='service' there are 17k rows: takes 15s on mysql8, 0.8s with maria5.5.

mysqltuner says nothing other than "add 20x your physical memory" (5.5 ran fine with the same ram), performance schema uselessly shows all the time is spent in executing stage with no breakdown beyond that.

Oddly created_tmp_files is fairly low and created_tmp_disk_tables shows zero. Had assumed we were just writing to disk (spinners, unfortunately) alot. key_reads miss rate at below 0.2% but the keys involved in tickets are called constantly so doesn't seem likely that it would ever leave memory.

Any ideas?

frEEk
  • 11
  • 1

1 Answers1

0

There are a few things that pop up.

  1. the optimizer became smarter
  2. object statistics are wrong

To take the guessing out, compare the execution plans. To understand the differences compare the optimizer statistics and see how you can make them more equal.

Maybe you must limit some of the newer optimizer choices.