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

[bagas bagas]

Peter Brawley Wrote:
-------------------------------------------------------
> You've reverted to 5.7.33 so we can't investigate.
> If you have another machine available, consider
> installing 5.7.34 there so what's going on can be
> studied.
>
> Table_open_cache is high, what is Opened_files /
> Uptime_since_flush_status ?
>
> Consider reading the 5.7.34 release notes for
> anything that looks relevant to your setup.




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


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.