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?

5
  • Hardware specs unchanged. Same cpu’s? Same io cards? Same disks? Same sector sizes? Same core speed? Dedicated hardware? Database on own server? Dedicated storage? With the same execution plan such a change is extraordinary. Almost like a disc brake intervention.
    – user953
    Jun 27, 2021 at 8:46
  • Only difference is minor change in underlying HD specs but disk IO performance tests similar. And the problem queries do not appear to be reading from disk or writing temporary tables to disk anyway so disk performance shouldn't make a difference. If anything it appears to be CPU bound but same vCPU specs as previous install.
    – frEEk
    Jun 28, 2021 at 3:02
  • Does it run on dedicated cpu’s or thin provisioned?
    – user953
    Jun 30, 2021 at 17:01
  • Additional information request from new server RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Jul 1, 2021 at 0:17
  • Memory is unchanged and only spinners for storage. Servers are virtually identical including OS and software config. Difference is only newer OS and MySQL version. Hence the confusion about a large performance differential to begin with.
    – frEEk
    Jul 3, 2021 at 16:06

1 Answer 1

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.

7
  • EXPLAIN shows the identical plan on both instances, other than of course the new partition and filtered fields. Seems expected given how simple a query I'm dealing with (no joins even).
    – frEEk
    Jun 24, 2021 at 7:42
  • What happens if you just read the database files using dd and write to dev null? (and the same directly from the device?) do io sizes matter(count and bs variations)
    – user953
    Jun 24, 2021 at 8:27
  • Did a dd copy test early on to confirm FS performance. Definitely ballpark similar. While it is on a different physical machine the virtual server has the same specs and the underlying hardware is very similar (both same RPM spinners, no SSDs). Also worth noting that the example problem query above does not write a temp table to disk (per STATUS reporing).
    – frEEk
    Jun 24, 2021 at 20:44
  • Can you run the same version mariadb on the new system?
    – user953
    Jun 24, 2021 at 21:32
  • Not easily, given it is a production machine and would need ram added to allow it the overhead to run 2 instances of the DB. Besides which since the specs of the old and new VPS are so similar, it doesn't seem that it could explain a 20 to 1 (or worse) performance difference.
    – frEEk
    Jun 25, 2021 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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