MySQL Bugs: #95590: Upgrade from 5.7 to 8.0.13 breaks performance

Bug #95590	Upgrade from 5.7 to 8.0.13 breaks performance
Submitted:	31 May 2019 18:31	Modified:	6 Jun 2019 13:10
Reporter:	Tal Weksler	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	internal_tmp_mem_storage_engine, space going down, temp table

Description:
Version 8.0.2 introduced the internal_tmp_mem_storage_engine parameter which was set on MEMORY on previous versions. The default was set to TempTable meaning writing to disk the temp tables. When upgrading from 5.7 this cause for a major performance issue since you don't even expect it to happen. I also don't understand why the default is not MEMORY.

We are working on AWS RDS. We spent the past month and a half seeing decreasing performance because of that without understanding what changed. Since this is a new param, we could not even compare to previous settings.

In addition, and this seems like another bug, we experienced constant reset of the DB every couple of hours.
We have a lot of joins and heavy queries which trigger automatic temp tables.
Instead of being done in memory, they used disk space but since we are using pool and persistent connections they were never dropped (our regular temp table has a drop in the end of each stored procedure). This cause massive disk space leak and every couple of hours the DB just reset automatically to clear the space.

How to repeat:
upgrade from 5.7 to 8.0.2 or higher

Suggested fix:
the default value for internal_tmp_mem_storage_engine should be MEMORY

Hi Mr. Weksler,

Thank you for your bug report.

However, this is not a bug. First of all, the default value of the system variable internal_tmp_mem_storage_engine will not change in 8.0.

Second, this variable governs whether MEMORY storage engine (SE) will be used, or whether a new in-memory SE will be used. This does not mean that the other is always slower. 

Third, new SE requires some tuning, and their tuning is described in our Reference Manual. It is different from the ways that Memory SE is tuned.

Fourth, nobody, to my knowledge, has yet published high-concurrency benchmarks that proves that Memory SE is much faster then new in-memory SE, when properly tuned.

Fifth, what you have observed is probably a consequence of the well known slowdown of the single-thread performance in 8.0. High concurrency performance, however, is much better in 8.0.

Sixth, and last, do remember that there are queries that can be resolved only by on-disk temporary tables. Hence, you should check whether your queries can be resolved by memory-only temporary tables.

Not a bug.

Hi,

There are two issues we encounter:

The first one I understand you don't see as a bug. As a user, I can tell you that we did the whole tuning and only switch back to Memory helped. It was also very unexpected that the default value is not as it was in 5.7. We know about some joins must be on disk - however this was not the case. I think this is a weird behavior when upgrading. Default should be as in 5.7.

The second one is that when using the new temp table on disk, the tables were not dropped once the query finished. It caused space to run out and the DB reset.
Please see the attached image of our storage:
https://drive.google.com/file/d/15wWRX0BC9QkLnNkKnm7EPKhwIttIxYde/view?usp=sharing
Every time the storage ran out, the DB reset itself.

Thanks,
Tal

Hi,

Well, first of all, I already wrote that tuning of the new SE for internal in-memory temporary tables is completely different then tuning for Memory SE.

Hence, all your tuning for Memory SE does not apply here. You can try again by tuning with the new parameters that are described in our Reference Manual for 8.0.

Regarding your other problem, that is something that is tuneable as well. Particularly if you are using LInux or similar OS which uses pool of temporary files. This is also described in our Reference Manual for 8.0.

I also remember that there was one bug related to that problem, which was fixed in 8.0.16.

Closely related: https://bugs.mysql.com/bug.php?id=99593

Sinisa Milivojevic, can you please provide a link to the tuning resource you mentioned above?

All that is needed is in our Reference Manual for the version 8.0.