Mysql – Database insanely (and weirdly inconsistently) slow on 8.0

MySQLmysql-8.0

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.

Best Answer

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.

Related Solutions

MySQL Table Creation Insanely Slow – Performance Tips

I would turn on profiling to get an idea of what takes so long. An example using mysql's CLI:

SET profiling = 1;
CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
SET profiling = 1;

You should get a response something like this:

mysql> SHOW PROFILES;
| Query_ID | Duration   | Query |
+----------+------------+-------------------------------------------------------------+
|        1 | 0.00913800 | CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY) |
+----------+------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000071 |
| checking permissions | 0.000007 |
| Opening tables       | 0.001698 |
| System lock          | 0.000043 |
| creating table       | 0.007260 |
| After create         | 0.000004 |
| query end            | 0.000004 |
| closing tables       | 0.000015 |
| freeing items        | 0.000031 |
| logging slow query   | 0.000002 |
| cleaning up          | 0.000003 |
+----------------------+----------+
11 rows in set (0.00 sec)

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.