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: | |
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 |
[31 May 2019 18:31]
Tal Weksler
[6 Jun 2019 12:58]
MySQL Verification Team
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.
[6 Jun 2019 13:10]
Tal Weksler
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
[6 Jun 2019 13:20]
MySQL Verification Team
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.
[16 May 2020 11:57]
Billy Sullivan
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?
[18 May 2020 12:13]
MySQL Verification Team
All that is needed is in our Reference Manual for the version 8.0.