Then I do not understand what is the matter.i have 5.7.34 on one of my (idle) vps
it sits idle, no unusual cpu load
same 12.2-R P7
Have you tried checking the databases? With mysqlcheck(1) for example?Then I do not understand what is the matter.
Not understood.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.
What will this command give?mysqladmin -u root -p proc
At the time of high base load.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 |
+-----+--------------+------------------+--------------+---------+------+--------------------+------------------------------------------------------------------------------------------------------+
mysql config2021-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
At the time of high load of the base, sites open with slowdown.[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
query_cache_size= 16M
query_cache_type = 1
i use FreeBSD 12.2-RELEASE, apache24-2.4.48, php74-7.4.21Mysql for which web engine?
The mysql settings can be seen?
malloc-lib=/usr/local/lib/libtcmalloc_minimal.so
# $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
The query cache is being deprecated: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.
I collect mysql57-server from ports.Are you using the package or building yourself from ports?
optimizer_switch=index_condition_pushdown=off,materialization=off,use_index_extensions=off,condition_fanout_filter=off,duplicateweedout=off