mysql57-(server/client) 5.7.34 does not work correctly

[bagas]

mysql> SELECT @@optimizer_switch;
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on |

optimizer_switch is the same in versions 5.7.33 / .5.7.34.

 

[Bubrak]

hi, just to inform all - tried upgrade to 5.7.35 and having the same issue with overloaded CPUs as on 5.7.34

so again back on 5.7.33

 

[richardtoohey2]

I've moved all my servers (including a couple of fairly busy ones) to MySQL 5.7.35 and not seeing any issues compared to earlier 5.7.x releases.

Something about your workloads or configurations seems to be causing you issues, but there doesn't seem to be a general problem otherwise more people would be reporting it.

Sorry I can't be more helpful - but it seems specific to certain set-ups.

 

[bagas]

I think the problem is in the port itself!
Or the problem is specific to the level of the FreeBSD system itself.
I'm thinking of transferring everything to Linux.

 

[SirDice]

It seems to me that this is the problem.
These parameters either do not work completely, or they do not work correctly in version 5.7.34.

query_cache_size= 16M
query_cache_type = 1

There is no query cache on multi-core systems. There never was.

I suggest you run something like databases/mysqltuner and see what it suggest. Looking at your my.cnf I have a feeling you've tweaked a lot of variables and many of those shouldn't have been modified. So I would start by removing all the "tuning" you've done so far, and start with a relatively clean config.

 

[CyberCr33p]

Maybe you can ask here: https://forums.mysql.com/list.php?24

 

[richardtoohey2]

I think the problem is in the port itself!
Or the problem is specific to the level of the FreeBSD system itself.
I'm thinking of transferring everything to Linux.

You gotta to do what you gotta do.

It's a pity you can't figure out why this particular workload is now having issues - and there's every chance that you'll have the same issues on Linux and you'll need the same skills/techniques to figure out the issue on Linux. Or maybe it will be better - this time - but you'll still hit a performance issue with MySQL on Linux in the future. And you'll need those same skills to figure out why MySQL is performing sub-optimally for the workload at that point.

Plenty of people reporting MySQL performance issues (and RAM issues and swap issues etc) on Linux as well, so I think it's inevitable you'll have some MySQL issues to investigate in the future, whatever OS you choose.

You are obviously seeing something but it doesn't appear to be common otherwise I would have thought more people would be raising it on these forums and the mailing lists.

I had a few things to work through with the jump from 5.6 to 5.7, but nothing between 5.7 revisions. And some MySQL workloads (for me) tickle a bad pattern with FreeBSD's virtual memory and swapping (seems to be better in 13.0).

Will you be using the same hardware on Linux? It will be interesting to see how much better performance you get from the same hardware and same programs and same workload, so please let us know how it goes.

 

[bagas]

I decided to try mysql 5.7.35, the same problem, the load on the server immediately appears.
I tried the standard mysql configuration, it didn't help.
It's either mysql itself or the FreeBSD system.
I can’t figure it out for a long time because the sites do not work.
I'll try to translate the base to Linux, then we'll see.

 

[Bubrak]

Hi,

have big news - tried to install 5.7.37 yesterday evening and today under full load it looks OK.

But about a month ago I also upgraded FreeBSD from 12.2 to 12.3, so there might be also the cause.

Haven't tried 5.7.36 (34 and 35 were not working).

In changelog for 5.7.37 there is stated "Partitioning: A query using an index other than the primary key of a partitioned table sometimes resulted in excessive CPU load. (Bug #104576, Bug #33238010)" so I gave it a try (even i dont use partitioned tables) and it is now working fine.

So just to let you all know who had the same problem. Not sure what exactly was the cause tho.