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



## bagas (May 31, 2021)

Hello.
Update mysql 5.7.34 does not work correctly, it creates a heavy load on the server.
Be careful.
I had to roll back to the previous version mysql 5.7.33.
My system FreeBSD 12.2-RELEASE-p7 amd64.


----------



## bagas (Jun 13, 2021)

After 2 weeks, I tried to update mysql again.
As before, after the update, the load is 100% of the processor from the mysql process.
At the time of the load of the processor is not in the log mysql error.
How to be?
After returning to the version mysql 5.7.33, there is no load on the processor.
Something is wrong with mysql version 5.7.34.


----------



## covacat (Jun 13, 2021)

i have 5.7.34 on one of my (idle) vps
it sits idle, no unusual cpu load
same 12.2-R P7


----------



## bagas (Jun 13, 2021)

covacat said:


> i have 5.7.34 on one of my (idle) vps
> it sits idle, no unusual cpu load
> same 12.2-R P7


Then I do not understand what is the matter.


----------



## Denis Shaposhnikov (Jun 13, 2021)

May be you can try to replace mysql by mariadb? May be it'll work better. On my system the latest mysql 5.7 worked fine. But I configured it to use malloc from google-perftools. Anyway I replaced it by mariadb and mariadb works fine without external malloc lib.


----------



## tingo (Jun 13, 2021)

bagas said:


> Then I do not understand what is the matter.


Have you tried checking the databases? With mysqlcheck(1) for example?


----------



## richardtoohey2 (Jun 13, 2021)

Not seeing this (phew!) on any of the amd64 machines I have upgraded - a mix of 11.4, 12.2, and 13.0.  Not using tcmalloc on any of them, just a built-from-ports MySQL 5.7.34, using ports OpenSSL.


----------



## Alain De Vos (Jun 14, 2021)

I'll use mariadb


----------



## bagas (Jun 14, 2021)

richardtoohey2 said:


> Not seeing this (phew!) on any of the amd64 machines I have upgraded - a mix of 11.4, 12.2, and 13.0.  Not using tcmalloc on any of them, just a built-from-ports MySQL 5.7.34, using ports OpenSSL.


Not understood.
Can you tell us more?


----------



## richardtoohey2 (Jun 14, 2021)

You said you have issues with MySQL 5.7.34 on 12.2 amd64.

I don’t see any issues on 12.2 (or 11.4 or 13.0)


----------



## CyberCr33p (Jun 14, 2021)

Do you use a specific CMS?


----------



## bagas (Jun 26, 2021)

Hello.
cms bitrix.
Today I tried again to update to 5.7.34.
Still 100% CPU load from mysqld after upgrade.
There are no errors in the mysql log.
There are no errors in the messages system log.
What to do, what to do ?!


----------



## covacat (Jun 26, 2021)

mysqladmin -u root -p proc


----------



## bagas (Jun 27, 2021)

covacat said:


> mysqladmin -u root -p proc


What will this command give?


----------



## richardtoohey2 (Jun 27, 2021)

Is it working? I.e. showing 100% but appears to be fine? The website or whatever is responsive?

Or it shows 100% and the website doesn’t work or is unusably slow?

If you login to MySQL prompt and do 

show full processlist

what do you see?  Just searched and that’s almost the same as what covacat suggested.


----------



## bagas (Jul 10, 2021)

> mysqladmin -u root -p proc
> Enter password:
> +-----+--------------+------------------+--------------+---------+------+--------------------+------------------------------------------------------------------------------------------------------+
> | Id  | User         | Host             | db           | Command | Time | State              | Info                                                                                                 |
> ...


At the time of high base load.
Mysql-server startup log


> 2021-07-10T16:25:23.007063Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
> 2021-07-10T16:25:23.008090Z 0 [Note] /usr/local/libexec/mysqld (mysqld 5.7.34) starting as process 37660 ...
> 2021-07-10T16:25:23.010146Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
> 2021-07-10T16:25:23.010157Z 0 [Note] InnoDB: Uses event mutexes
> ...


mysql config
/usr/local/etc/mysql/my.cnf


> [client]
> port=3306
> socket=/tmp/mysql.sock
> 
> ...


At the time of high load of the base, sites open with slowdown.
As soon as I reinstall mysql server to 5.7.33, the load drops to almost a minimum.


----------



## richardtoohey2 (Jul 10, 2021)

Nothing screamingly obvious in the process list - hardly anything in there and the times aren't excessive.

There doesn't seem to be much in the change logs for 5.7.34: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-34.html

But something about your workload obviously triggers an issue - you'll have to try the mysql slow query log, look at the lock counts, INNODB stats etc. and see if you can find out what the problem(s) are.

I run a few servers with MySQL 5.7.34 and there's been no issue moving from 5.7.33 to 5.7.34 so unfortunately it seems to be something specific to what you are doing.


----------



## bagas (Jul 12, 2021)

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


----------



## Bubrak (Jul 12, 2021)

same problem here, had to go back to 5.7.33 (again after new try after several weeks when i tried first with same issue), thanks to bagas  for helping me!


----------



## bagas (Jul 12, 2021)

Bubrak said:


> same problem here, had to go back to 5.7.33 (again after new try after several weeks when i tried first with same issue), thanks to bagas  for helping me!


Mysql for which web engine?
The mysql settings can be seen?


----------



## Bubrak (Jul 12, 2021)

bagas said:


> Mysql for which web engine?
> The mysql settings can be seen?


i use FreeBSD 12.2-RELEASE, apache24-2.4.48, php74-7.4.21


i have to use that



> malloc-lib=/usr/local/lib/libtcmalloc_minimal.so



since upgrade from FreeBSD 11 to 12, today tried to remove this line with mysql 5.7.34 but no effect, so using it again with 5.7.33

mysql config:


```
# $FreeBSD: head/databases/mysql57-server/files/my.cnf.sample.in 414707 2016-05-06 14:39:59Z riggs $

[client]
port                            = 3306
socket                          = /tmp/mysql.sock

[mysql]
prompt                          = \u@\h [\d]>\_

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock

#bind-address                    = 127.0.0.1

basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
expire_logs_days                = 30
default_password_lifetime       = 0
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
open_files_limit                = 32768
table_open_cache                = 16384
table_definition_cache          = 8192
net_retry_count                 = 16384
key_buffer_size                 = 512M
max_allowed_packet              = 64M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 2
innodb_buffer_pool_size         = 1G
innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend:max:3G
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 128M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
skip-symbolic-links



init-connect='SET NAMES utf8'
character-set-server = utf8
collation-server = utf8_czech_ci

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

default-storage-engine=myisam
default-tmp-storage-engine=myisam

#performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

# SSL
ssl-ca=/home/system/mysqlcerts/ca.pem
ssl-cert=/home/system/mysqlcerts/server-cert.pem
ssl-key=/home/system/mysqlcerts/server-key.pem


[mysqld_safe]
malloc-lib=/usr/local/lib/libtcmalloc_minimal.so



[mysqldump]
max_allowed_packet              = 256M
quote_names
quick
```


----------



## richardtoohey2 (Jul 12, 2021)

bagas said:


> 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.


The query cache is being deprecated:





__





						MySQL :: MySQL 5.7 Reference Manual :: 8.10.3.3 Query Cache Configuration
					






					dev.mysql.com
				




That doesn’t explain why it stopped working for you between 5.7.33 and 5.7.34 unless maybe your binaries were built with it disabled (if that’s possible).

Are you using the package or building yourself from ports?


----------



## bagas (Jul 13, 2021)

richardtoohey2 said:


> Are you using the package or building yourself from ports?


I collect mysql57-server from ports.


----------



## Alain De Vos (Jul 13, 2021)

Does mariadb show this problem ?
Just dump the DB.


----------



## covacat (Jul 13, 2021)

going from 5.1 to 5.7 i've seen various selects go one order of magnitude slower
what i had to do after investigating was

```
optimizer_switch=index_condition_pushdown=off,materialization=off,use_index_extensions=off,condition_fanout_filter=off,duplicateweedout=off
```
no idea if a minor version upgrade changes any of optimizer flags but you can test (the above line is in my.cnf)


----------



## bagas (Jul 16, 2021)

> 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 (Aug 3, 2021)

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 (Aug 3, 2021)

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 (Aug 3, 2021)

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 (Aug 3, 2021)

bagas said:


> 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.
> 
> ```
> ...


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 (Aug 3, 2021)

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


----------



## richardtoohey2 (Aug 4, 2021)

bagas said:


> 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 (Aug 7, 2021)

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 (Feb 16, 2022)

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.


----------

