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

bagas

Well-Known Member

Reaction score: 7
Messages: 254

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

bagas

Well-Known Member

Reaction score: 7
Messages: 254

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

Well-Known Member

Reaction score: 170
Messages: 365

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

Denis Shaposhnikov

New Member

Reaction score: 2
Messages: 14

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.
 

richardtoohey2

Aspiring Daemon

Reaction score: 278
Messages: 554

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

bagas

Well-Known Member

Reaction score: 7
Messages: 254

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

Aspiring Daemon

Reaction score: 278
Messages: 554

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)
 
OP
bagas

bagas

Well-Known Member

Reaction score: 7
Messages: 254

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 ?!
 

richardtoohey2

Aspiring Daemon

Reaction score: 278
Messages: 554

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

bagas

Well-Known Member

Reaction score: 7
Messages: 254

mysqladmin -u root -p proc
Enter password:
+-----+--------------+------------------+--------------+---------+------+--------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------------+------------------+--------------+---------+------+--------------------+------------------------------------------------------------------------------------------------------+
| 185 | site_lot | 10.10.10.1:14424 | site_lot | Sleep | 64 | | |
| 301 | site_lot | 10.10.10.1:14537 | site_lot | Query | 1 | Sending data | SELECT BE.ID as ID
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_i |
| 303 | site_DIS | 10.10.10.1:14539 | site_DIS | Query | 1 | Sending data | SELECT BE.ID as ID
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_i |
| 304 | site_lot | 10.10.10.1:14540 | site_lot | Query | 0 | Sending data | SELECT BE.ID as ID
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_i |
| 305 | site_lot | 10.10.10.1:14541 | site_lot | Query | 3 | removing tmp table | SELECT BE.ID as ID,BE.IBLOCK_ID as IBLOCK_ID,BE.CODE as CODE,BE.XML_ID as XML_ID,BE.NAME as NAME,BE |
| 311 | site_DIS | 10.10.10.1:14548 | site_DIS | Query | 1 | Sending data | SELECT BE.ID as ID,BE.IBLOCK_ID as IBLOCK_ID,BE.CODE as CODE,BE.XML_ID as XML_ID,BE.NAME as NAME,BE |
| 314 | site_DIS | 10.10.10.1:14550 | site_DIS | Query | 1 | Sending data | SELECT BE.ID as ID,BE.IBLOCK_ID as IBLOCK_ID,BE.CODE as CODE,BE.XML_ID as XML_ID,BE.NAME as NAME,BE |
| 315 | site_lot | 10.10.10.1:14551 | site_lot | Query | 0 | Sending data | SELECT BE.ID as ID,BE.IBLOCK_ID as IBLOCK_ID,BE.CODE as CODE,BE.XML_ID as XML_ID,BE.NAME as NAME,BE |
| 319 | site_lot | 10.10.10.1:14553 | site_lot | Query | 0 | Sending to client | |
| 320 | root | localhost | | Query | 0 | starting | show processlist |
+-----+--------------+------------------+--------------+---------+------+--------------------+------------------------------------------------------------------------------------------------------+
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
2021-07-10T16:25:23.010160Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-07-10T16:25:23.010162Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-07-10T16:25:23.010396Z 0 [Note] InnoDB: Number of pools: 1
2021-07-10T16:25:23.010452Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-07-10T16:25:23.013019Z 0 [Note] InnoDB: Initializing buffer pool, total size = 22G, instances = 22, chunk size = 128M
2021-07-10T16:25:24.350025Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-07-10T16:25:24.507776Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-07-10T16:25:24.792878Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-07-10T16:25:24.792956Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-07-10T16:25:24.801952Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-07-10T16:25:24.802422Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-07-10T16:25:24.802428Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-07-10T16:25:24.802589Z 0 [Note] InnoDB: Waiting for purge to start
2021-07-10T16:25:24.856970Z 0 [Note] InnoDB: 5.7.34 started; log sequence number 1591002920031
2021-07-10T16:25:24.857051Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/db/base_mysql/mysql/ib_buffer_pool
2021-07-10T16:25:24.857214Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-07-10T16:25:24.861610Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-07-10T16:25:24.861719Z 0 [Note] Server hostname (bind-address): '10.10.10.2'; port: 3306
2021-07-10T16:25:24.861734Z 0 [Note] - '10.10.10.2' resolves to '10.10.10.2';
2021-07-10T16:25:24.861748Z 0 [Note] Server socket created on IP: '10.10.10.2'.
2021-07-10T16:25:24.864858Z 0 [Warning] 'proxies_priv' entry '@ root@base' ignored in --skip-name-resolve mode.
2021-07-10T16:25:24.876144Z 0 [Note] Event Scheduler: Loaded 0 events
2021-07-10T16:25:24.876242Z 0 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.7.34' socket: '/tmp/mysql.sock' port: 3306 Source distribution
2021-07-10T16:25:26.390949Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210710 19:25:26
mysql config
/usr/local/etc/mysql/my.cnf
[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
sql-mode=
datadir=/var/db/base_mysql/mysql
bind-address=10.10.10.2
port=3306
socket=/tmp/mysql.sock
skip-log-bin
skip_ssl
skip-name-resolve
skip-external-locking
symbolic-links=0
sync_binlog = 0
explicit_defaults_for_timestamp = 1
wait_timeout = 180
interactive_timeout = 180
max_connections = 550
key_buffer_size = 2M
myisam_sort_buffer_size = 1M
max_allowed_packet = 480M
sort_buffer_size = 2M
read_buffer_size = 1M
join_buffer_size = 2M
read_rnd_buffer_size = 8M
tmp_table_size=320M
max_heap_table_size=320M
table_open_cache = 247900
table_definition_cache = 3000
table_open_cache_instances = 16
query_cache_size= 16M
query_cache_type = 1
thread_cache_size = 130
default-storage-engine = innodb
innodb_buffer_pool_size = 22G
innodb_buffer_pool_instances = 22
innodb_log_file_size = 2G
innodb_write_io_threads = 64
innodb_read_io_threads = 64
innodb_lru_scan_depth = 256
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 18M
transaction-isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
innodb_io_capacity=15000
innodb_io_capacity_max=55000
general_log = 0
slow_query_log = 0
log_error = /mnt/backup/log/mysql/errors.log

[mysqldump]
quick

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
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

Aspiring Daemon

Reaction score: 278
Messages: 554

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

bagas

Well-Known Member

Reaction score: 7
Messages: 254

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

New Member

Reaction score: 11
Messages: 10

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!
 
OP
bagas

bagas

Well-Known Member

Reaction score: 7
Messages: 254

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

New Member

Reaction score: 11
Messages: 10

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:

Bash:
# $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

Aspiring Daemon

Reaction score: 278
Messages: 554

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:


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?
 

covacat

Well-Known Member

Reaction score: 170
Messages: 365

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
Code:
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)
 
Top