Whenever rebooting the DB instance which is in AWS EC2 the mysql service went to down state and getting crashed

[Sujay1046]

Error :
Feb 27 05:03:37 xxxx systemd[1]: Started Percona Server.
Feb 29 13:05:41 xxxxx systemd[1]: Stopping Percona Server…
Feb 29 13:05:50 xxxx systemd[1]: mysql.service: Succeeded.
Feb 29 13:05:50 xxxx systemd[1]: Stopped Percona Server.
Feb 29 14:40:54 xxxx systemd[1]: Dependency failed for Percona Server.
Feb 29 14:40:54 xxxx systemd[1]: mysql.service: Job mysql.service/start failed with result ‘dependency’.
Feb 29 14:48:16 xxxx systemd[1]: Dependency failed for Percona Server.
Feb 29 14:48:16 xxxx systemd[1]: mysql.service: Job mysql.service/start failed with result ‘dependency’.
Feb 29 15:21:04 ip-xxxx systemd[1]: Dependency failed for Percona Server.
Feb 29 15:21:04 ip-xxxx systemd[1]: mysql.service: Job mysql.service/start failed with result ‘dependency’.

OS version : Ubuntu 20.0
MySQL : 8.0.30-22

Error as follows:
2024-02-27T05:03:13.152076Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.30-22).
2024-02-27T05:03:15.152866Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046328 user: ‘xtwo’.
2024-02-27T05:03:15.152956Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046416 user: ‘xtwo’.
2024-02-27T05:03:15.152990Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046304 user: ‘xtwo’.
2024-02-27T05:03:15.153028Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046320 user: ‘xtwo’.
2024-02-27T05:03:15.153060Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046360 user: ‘xtwo’.
2024-02-27T05:03:15.153094Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046408 user: ‘xtwo’.
2024-02-27T05:03:15.153125Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046088 user: ‘xtwo’.
2024-02-27T05:03:15.153166Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046392 user: ‘xtwo’.
2024-02-27T05:03:15.153196Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046240 user: ‘xtwo’.
2024-02-27T05:03:15.153225Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046200 user: ‘xtwo’.
2024-02-27T05:03:15.153262Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046312 user: ‘xtwo’.
2024-02-27T05:03:15.153295Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045872 user: ‘xtwo’.
2024-02-27T05:03:15.153332Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046368 user: ‘xtwo’.
2024-02-27T05:03:15.153362Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046336 user: ‘xtwo’.
2024-02-27T05:03:15.153412Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046128 user: ‘xtwo’.
2024-02-27T05:03:15.153482Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045368 user: ‘xtwo’.
2024-02-27T05:03:15.153514Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046256 user: ‘xtwo’.
2024-02-27T05:03:15.153543Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046384 user: ‘xtwo’.
2024-02-27T05:03:15.153571Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045608 user: ‘xtwo’.
2024-02-27T05:03:15.153611Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046377 user: ‘xtwo’.
2024-02-27T05:03:15.153641Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046233 user: ‘xtwo’.
2024-02-27T05:03:15.153667Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046345 user: ‘xtwo’.
2024-02-27T05:03:15.153703Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046353 user: ‘xtwo’.
2024-02-27T05:03:15.153734Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045953 user: ‘xtwo’.

These are the binaries installed

percona-server-client_8.0.30-22-1.focal_amd64.deb
percona-server-rocksdb_8.0.30-22-1.focal_amd64.deb
percona-server-server_8.0.30-22-1.focal_amd64.deb
percona-server-source_8.0.30-22-1.focal_amd64.deb
percona-server-common_8.0.30-22-1.focal_amd64.deb
percona-mysql-router_8.0.30-22-1.focal_amd64.deb
libperconaserverclient21_8.0.30-22-1.focal_amd64.deb
libperconaserverclient21-dev_8.0.30-22-1.focal_amd64.deb

Let me know anything we missed in installation part since the DB instance getting crashed every time after the reboot

[matthewb]

Please provide more logs from the mysql error log.

[Sujay1046]

2024-02-16T06:48:40.230147Z 0 [Warning] [MY-010918] [Server] ‘default_authentication_plugin’ is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-02-16T06:48:40.230154Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) initializing of server in progress as process 16153
2024-02-16T06:48:40.235693Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-16T06:48:40.767931Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-16T06:48:42.904696Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2024-02-16T06:48:47.152242Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-02-16T06:48:47.152291Z 0 [Warning] [MY-010918] [Server] ‘default_authentication_plugin’ is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-02-16T06:48:47.152308Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) starting as process 16205
2024-02-16T06:48:47.159001Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-16T06:48:47.303253Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-16T06:48:47.515679Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-02-16T06:48:47.515699Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-02-16T06:48:47.519255Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location ‘/tmp’ in the path is accessible to all OS users. Consider choosing a different directory.
2024-02-16T06:48:47.539715Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.30-22).
2024-02-16T06:48:47.541796Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-02-16T06:48:49.027940Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-22) Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-02-16T06:48:50.071537Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-02-16T06:48:50.071586Z 0 [Warning] [MY-010918] [Server] ‘default_authentication_plugin’ is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-02-16T06:48:50.071604Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) starting as process 16430
2024-02-16T06:48:50.075918Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-16T06:48:50.221581Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-16T06:48:50.387528Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-02-16T06:48:50.387549Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-02-16T06:48:50.400794Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-02-16T06:48:50.400842Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: ‘8.0.30-22’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-02-16T06:54:00.281972Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.30-22).
2024-02-16T06:54:01.139420Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-22) Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-02-16T06:54:15.344333Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 10000 (requested 404010)
2024-02-16T06:54:15.344336Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 2995 (requested 200000)
2024-02-16T06:54:15.583307Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-02-16T06:54:15.583357Z 0 [Warning] [MY-010918] [Server] ‘default_authentication_plugin’ is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-02-16T06:54:15.583375Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) starting as process 20095
2024-02-16T06:54:15.619187Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=8589934592. Please use innodb_redo_log_capacity instead.
2024-02-16T06:54:15.620438Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-16T06:54:24.012640Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-16T06:54:24.292994Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-02-16T06:54:24.293018Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-02-16T06:54:24.323714Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-02-16T06:54:24.323736Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: ‘8.0.30-22’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-02-23T10:45:13.808130Z 0 [Warning] [MY-013706] [InnoDB] Detected too long lock waiting around m2_2023.catalog_category_product_index_store16_replica.IDX_3FCA7BB482F2A772620280C9007DB7F4 stats_sample_pages. Retrying with the smaller value 9.
2024-02-27T05:03:13.152076Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.30-22).
2024-02-27T05:03:15.152866Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046328 user: ‘xtwo’.
2024-02-27T05:03:15.152956Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046416 user: ‘xtwo’.
2024-02-27T05:03:15.152990Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046304 user: ‘xtwo’.
2024-02-27T05:03:15.153028Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046320 user: ‘xtwo’.
2024-02-27T05:03:15.153060Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046360 user: ‘xtwo’.
2024-02-27T05:03:15.153094Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046408 user: ‘xtwo’.
2024-02-27T05:03:15.153125Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046088 user: ‘xtwo’.
2024-02-27T05:03:15.153166Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046392 user: ‘xtwo’.
2024-02-27T05:03:15.153196Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046240 user: ‘xtwo’.
2024-02-27T05:03:15.153225Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046200 user: ‘xtwo’.
2024-02-27T05:03:15.153262Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046312 user: ‘xtwo’.
2024-02-27T05:03:15.153295Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045872 user: ‘xtwo’.
2024-02-27T05:03:15.153332Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046368 user: ‘xtwo’.
thread 16046394 user: ‘xtwo’.
2024-02-27T05:03:15.154944Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046298 user: ‘xtwo’.
2024-02-27T05:03:15.154966Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046178 user: ‘xtwo’.
2024-02-27T05:03:15.154987Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046282 user: ‘xtwo’.
2024-02-27T05:03:15.155017Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046386 user: ‘xtwo’.
2024-02-27T05:03:15.155040Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16044818 user: ‘xtwo’.
2024-02-27T05:03:15.155065Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046306 user: ‘xtwo’.
2024-02-27T05:03:15.155095Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046187 user: ‘xtwo’.
2024-02-27T05:03:15.155117Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046395 user: ‘xtwo’.
2024-02-27T05:03:15.155150Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046235 user: ‘xtwo’.
2024-02-27T05:03:15.155178Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046115 user: ‘xtwo’.
2024-02-27T05:03:15.155201Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046371 user: ‘xtwo’.
2024-02-27T05:03:15.155223Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046331 user: ‘xtwo’.
2024-02-27T05:03:15.155246Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045923 user: ‘xtwo’.
2024-02-27T05:03:15.155274Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046307 user: ‘xtwo’.
2024-02-27T05:03:15.155298Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046243 user: ‘xtwo’.
2024-02-27T05:03:15.155324Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046299 user: ‘xtwo’.
2024-02-27T05:03:15.155348Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046411 user: ‘xtwo’.
2024-02-27T05:03:15.155383Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16044315 user: ‘xtwo’.
2024-02-27T05:03:15.155413Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 11876059 user: ‘root’.
2024-02-27T05:03:15.155436Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046091 user: ‘xtwo’.
2024-02-27T05:03:15.155469Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046419 user: ‘xtwo’.
2024-02-27T05:03:15.155505Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046387 user: ‘xtwo’.
2024-02-27T05:03:15.155533Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046195 user: ‘xtwo’.
2024-02-27T05:03:15.155554Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046347 user: ‘xtwo’.
2024-02-27T05:03:15.155580Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045851 user: ‘xtwo’.
2024-02-27T05:03:15.155604Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045563 user: ‘xtwo’.
2024-02-27T05:03:15.156119Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046012 user: ‘xtwo’.
2024-02-27T05:03:15.156145Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046436 user: ‘xtwo’.
2024-02-27T05:03:15.156170Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046332 user: ‘xtwo’.
2024-02-27T05:03:15.156202Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046220 user: ‘xtwo’.
2024-02-27T05:03:15.156228Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046132 user: ‘xtwo’.
2024-02-27T05:03:15.156252Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16044564 user: ‘xtwo’.
2024-02-27T05:03:15.156275Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046316 user: ‘xtwo’.
2024-02-27T05:03:15.156301Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046348 user: ‘xtwo’.
2024-02-27T05:03:15.156325Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046308 user: ‘xtwo’.
2024-02-27T05:03:15.156350Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045436 user: ‘xtwo’.
2024-02-27T05:03:15.156377Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046292 user: ‘xtwo’.
2024-02-27T05:03:15.156401Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046060 user: ‘xtwo’.
2024-02-27T05:03:15.156425Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045756 user: ‘xtwo’.
2024-02-27T05:03:15.156447Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046420 user: ‘xtwo’.
2024-02-27T05:03:15.156467Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046300 user: ‘xtwo’.
2024-02-27T05:03:15.156492Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046372 user: ‘xtwo’.
2024-02-27T05:03:15.156513Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046036 user: ‘xtwo’.
2024-02-27T05:03:15.156537Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046412 user: ‘xtwo’.
2024-02-27T05:03:15.156562Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045268 user: ‘xtwo’.
2024-02-27T05:03:15.156597Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045861 user: ‘xtwo’.
2024-02-27T05:03:15.156622Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046373 user: ‘xtwo’.
2024-02-27T05:03:15.156645Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046269 user: ‘xtwo’.
2024-02-27T05:03:15.156668Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045181 user: ‘xtwo’.
2024-02-27T05:03:15.156695Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045429 user: ‘xtwo’.
2024-02-27T05:03:15.156721Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046357 user: ‘xtwo’.
2024-02-27T05:03:15.156756Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046109 user: ‘xtwo’.
2024-02-27T05:03:15.156784Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046021 user: ‘xtwo’.
2024-02-27T05:03:15.156813Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046365 user: ‘xtwo’.
2024-02-27T05:03:15.156841Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046413 user: ‘xtwo’.
2024-02-27T05:03:15.156867Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046125 user: ‘xtwo’.
2024-02-27T05:03:15.156896Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046213 user: ‘xtwo’.
2024-02-27T05:03:15.156921Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046237 user: ‘xtwo’.
2024-02-27T05:03:15.158510Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046405 user: ‘xtwo’.
2024-02-27T05:03:15.158552Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046333 user: ‘xtwo’.
2024-02-27T05:03:15.158578Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046205 user: ‘xtwo’.
2024-02-27T05:03:15.158610Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046390 user: ‘xtwo’.
2024-02-27T05:03:15.158634Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046406 user: ‘xtwo’.
2024-02-27T05:03:15.158660Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045862 user: ‘xtwo’.
2024-02-27T05:03:15.158683Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045014 user: ‘xtwo’.
2024-02-27T05:03:15.158706Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045894 user: ‘xtwo’.
2024-02-27T05:03:15.158735Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045134 user: ‘xtwo’.
2024-02-27T05:03:15.158759Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046190 user: ‘xtwo’.
2024-02-27T05:03:15.158787Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046398 user: ‘xtwo’.
2024-02-27T05:03:15.158811Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045174 user: ‘xtwo’.
2024-02-27T05:03:15.158833Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046286 user: ‘xtwo’.
2024-02-27T05:03:15.158854Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046366 user: ‘xtwo’.
2024-02-27T05:03:15.158875Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046326 user: ‘xtwo’.
2024-02-27T05:03:15.158899Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046422 user: ‘xtwo’.
2024-02-27T05:03:15.158922Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046350 user: ‘xtwo’.
2024-02-27T05:03:15.158944Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046294 user: ‘xtwo’.
2024-02-27T05:03:15.158969Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046382 user: ‘xtwo’.
2024-02-27T05:03:15.159004Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046239 user: ‘xtwo’.
2024-02-27T05:03:15.159029Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046407 user: ‘xtwo’.
2024-02-27T05:03:15.159051Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045311 user: ‘xtwo’.
2024-02-27T05:03:15.159076Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046359 user: ‘xtwo’.
2024-02-27T05:03:15.159099Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046199 user: ‘xtwo’.
2024-02-27T05:03:15.159122Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046367 user: ‘xtwo’.
2024-02-27T05:03:15.159144Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045623 user: ‘xtwo’.
2024-02-27T05:03:15.159167Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046023 user: ‘xtwo’.
2024-02-27T05:03:15.159196Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046375 user: ‘xtwo’.
2024-02-27T05:03:15.159219Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046335 user: ‘xtwo’.
2024-02-27T05:03:15.159247Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046399 user: ‘xtwo’.
2024-02-27T05:03:15.159272Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046063 user: ‘xtwo’.
2024-02-27T05:03:15.159293Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16044295 user: ‘xtwo’.
2024-02-27T05:03:15.159681Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046383 user: ‘xtwo’.
2024-02-27T05:03:15.159709Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046191 user: ‘xtwo’.
2024-02-27T05:03:15.159732Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16045887 user: ‘xtwo’.
2024-02-27T05:03:15.159761Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046327 user: ‘xtwo’.
2024-02-27T05:03:15.159785Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 16046295 user: ‘xtwo’.
2024-02-27T05:03:25.653405Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-22) Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
“%%”“”“”“”“”“”“”“”“%%”“”“”“”“”“”“2024-02-27T05:03:26.800445Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 10000 (requested 404010)
2024-02-27T05:03:26.800447Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 2995 (requested 200000)
2024-02-27T05:03:27.040918Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-02-27T05:03:27.040970Z 0 [Warning] [MY-010918] [Server] ‘default_authentication_plugin’ is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-02-27T05:03:27.040989Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) starting as process 928502
2024-02-27T05:03:27.080181Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=8589934592. Please use innodb_redo_log_capacity instead.
2024-02-27T05:03:27.081491Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-27T05:03:36.895194Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-27T05:03:37.326709Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-02-27T05:03:37.326731Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-02-27T05:03:37.364488Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-02-27T05:03:37.364546Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: ‘8.0.30-22’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-02-29T13:05:41.700218Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.30-22).
2024-02-29T13:05:43.701528Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519496 user: ‘xtwo’.
2024-02-29T13:05:43.701594Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519448 user: ‘xtwo’.
2024-02-29T13:05:43.701634Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519480 user: ‘xtwo’.
2024-02-29T13:05:43.701665Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519493 user: ‘xtwo’.
2024-02-29T13:05:43.703034Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519205 user: ‘xtwo’.
2024-02-29T13:05:43.703071Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519478 user: ‘xtwo’.
2024-02-29T13:05:43.703105Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519390 user: ‘xtwo’.
2024-02-29T13:05:43.703128Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519566 user: ‘xtwo’.
2024-02-29T13:05:43.703720Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519462 user: ‘xtwo’.
2024-02-29T13:05:43.703753Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519270 user: ‘xtwo’.
2024-02-29T13:05:43.703776Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519534 user: ‘xtwo’.
2024-02-29T13:05:43.703798Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519494 user: ‘xtwo’.
2024-02-29T13:05:43.703824Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519543 user: ‘xtwo’.
2024-02-29T13:05:43.703854Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519391 user: ‘xtwo’.
2024-02-29T13:05:43.703877Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519503 user: ‘xtwo’.
2024-02-29T13:05:43.703903Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 6103 user: ‘root’.
2024-02-29T13:05:43.703919Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519551 user: ‘xtwo’.
2024-02-29T13:05:43.703943Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 3519407 user: ‘xtwo’.
2024-02-29T13:05:49.123250Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-22) Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
“””“”“”“”“%”“%%”“%%%”%%%%%%“%%%%%%”%%%%%%“%%%%%%”%%%%%%“%%%%%%”%%%%%%“%%%%%%%%%%”%%%%%%“%%%%”“”“”“”“”“”“”%%“”%“%%%”%%%%%%“%%%%%%”%%%%%%“%%%%%%”%%%%“”“”“”“”“”“”%%“”“”%“”%“%%%”%%%%%%“%%%%%%”%%“%%%%%%”%%%%%%“%%%%%%”%%%%%%“%%%%%%”%%%%%%“%%%%%%%%”“”“”“”“”“”“”%“%%%”%%%%%%“%%%%%%”%%%%%%“%%%%%%%%”%%%%%%%%""2024-02-29T15:23:34.533084Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-02-29T15:23:34.533137Z 0 [Warning] [MY-010918] [Server] ‘default_authentication_plugin’ is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-02-29T15:23:34.533157Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) starting as process 4046323
2024-02-29T15:23:34.572754Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=8589934592. Please use innodb_redo_log_capacity instead.
2024-02-29T15:23:34.574065Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-29T15:23:43.963987Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-29T15:23:44.418247Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-02-29T15:23:44.418270Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-02-29T15:23:44.432724Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2024-02-29T15:23:44.432744Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: ‘8.0.30-22’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 0 Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-02-29T18:20:01.012696Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.30-22).
2024-02-29T18:20:07.703940Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-22) Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-03-01T10:34:28.046484Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 10000 (requested 404010)
2024-03-01T10:34:28.046488Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 2995 (requested 200000)
2024-03-01T10:34:28.281590Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-03-01T10:34:28.283029Z 0 [Warning] [MY-010918] [Server] ‘default_authentication_plugin’ is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-03-01T10:34:28.283045Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) starting as process 1156
2024-03-01T10:34:28.345553Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=8589934592. Please use innodb_redo_log_capacity instead.
2024-03-01T10:34:28.362786Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-01T10:34:37.200071Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-01T10:34:37.642686Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-01T10:34:37.642711Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-01T10:34:37.677465Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-03-01T10:34:37.677523Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: ‘8.0.30-22’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-03-01T10:39:52.705098Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.30-22).
2024-03-01T10:39:57.208267Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-22) Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-03-01T11:11:27.588099Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 10000 (requested 404010)
2024-03-01T11:11:27.588103Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 2995 (requested 200000)
2024-03-01T11:11:27.826235Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-03-01T11:11:27.827752Z 0 [Warning] [MY-010918] [Server] ‘default_authentication_plugin’ is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-03-01T11:11:27.827771Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22) starting as process 1146
2024-03-01T11:11:27.889980Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=8589934592. Please use innodb_redo_log_capacity instead.
2024-03-01T11:11:27.906291Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-01T11:11:36.999238Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-01T11:11:37.470295Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-01T11:11:37.470321Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-01T11:11:37.510819Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/run/mysqld/mysqlx.sock
2024-03-01T11:11:37.510864Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: ‘8.0.30-22’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.

Can you please check this is live PROD environment and if each month when we do mysql restart for DB maintenance we are getting the DB server crash.

Thanks,
Sujay

[matthewb]

Check the package status: dpkg -l mysql-server-8.0

MySQL shutting down unexpectedly every 24 hours on Ubuntu

[Sujay1046]

Hi Matthew,

The below is the current settings of my.cnf file

The Percona Server 8.0 configuration file.

For explanations see

http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
disable_log_bin

bind-address = 0.0.0.0

key_buffer_size = 512M
max_allowed_packet = 700M
thread_stack = 512K

max_connections = 4000
table_open_cache = 800

table_open_cache = 200000

table_open_cache_instances=64

back_log=3500

join_buffer_size=2048K

sort_buffer_size=3072K

innodb_file_per_table

innodb_log_files_in_group=2

innodb_open_files=4000

innodb_buffer_pool_instances=8

innodb_page_cleaners=8

default_storage_engine=InnoDB

innodb_flush_log_at_trx_commit = 0

innodb_doublewrite= 0

innodb_flush_method= O_DIRECT

innodb_file_per_table= 1

innodb_io_capacity=12000

innodb_io_capacity_max=14000

innodb_flush_neighbors=0

max_prepared_stmt_count=1000000

innodb_buffer_pool_size = 142G
innodb_log_buffer_size = 4G
innodb_log_file_size = 4G
innodb_write_io_threads = 38

sql_mode=“”

Timeout value changed from 8 hours to 4 - 30Jan2024

wait_timeout = 8000
interactive_timeout = 8000
connect_timeout = 600

[client]
default-character-set=utf8mb4

In addition to this when I run the dpkg -l mysql-server-8.0

root@ip-xxxx:~# dpkg -l mysql-server-8.0
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name Version Architecture Description
++±================-============-============-=================================
un mysql-server-8.0 (no description available)

In the blog that you shared is related to version 5.7, please let me know which parameter to be changed in the mysql end

mysql> show variables like ‘%segments%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| innodb_rollback_segments | 128 |
±-------------------------±------+
1 row in set (0.00 sec)

mysql> show variables like ‘%undo%’;
±-------------------------±-----------+
| Variable_name | Value |
±-------------------------±-----------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
±-------------------------±-----------+
5 rows in set (0.00 sec)

Let me know do we know to do the fresh install again from scratch.

[Sujay1046]

In addition to the above the below ones are the packages installed for MySQL 8.0.30-22 In Ubuntu version

root@ip-xxxx:~# apt list --installed | grep percona -

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

libperconaserverclient21-dev/now 8.0.30-22-1.focal amd64 [installed,local]
libperconaserverclient21/now 8.0.30-22-1.focal amd64 [installed,local]
percona-mysql-router/now 8.0.30-22-1.focal amd64 [installed,local]
percona-server-client/now 8.0.30-22-1.focal amd64 [installed,local]
percona-server-common/now 8.0.30-22-1.focal amd64 [installed,local]
percona-server-rocksdb/now 8.0.30-22-1.focal amd64 [installed,local]
percona-server-server/now 8.0.30-22-1.focal amd64 [installed,local]
percona-server-source/now 8.0.30-22-1.focal amd64 [installed,local]
root@ip-xxxx:~#

[yunus_shaikh]

Hi Sujay,
Do you see anything related to percona server in
/var/log/dpkg.log
or in the system logs?
/var/log/syslog

[Sujay1046]

dpkglog.txt (39.8 KB)
syslogfile.txt (1.2 MB)

Attached the log files as requested. The below steps are the ones executed for installing the Percona packages.

sudo apt update
sudo apt-get install debsums
apt --fix-broken install
sudo apt-get install debsums
dpkg -i libperconaserverclient21_8.0.30-22-1.focal_amd64.deb libperconaserverclient21-dev_8.0.30-22-1.focal_amd64.deb percona-mysql-router_8.0.30-22-1.focal_amd64.deb percona-server-client_8.0.30-22-1.focal_amd64.deb percona-server-common_8.0.30-22-1.focal_amd64.deb percona-server-rocksdb_8.0.30-22-1.focal_amd64.deb percona-server-server_8.0.30-22-1.focal_amd64.deb percona-server-source_8.0.30-22-1.focal_amd64.deb
dpkg -i libperconaserverclient21_8.0.30-22-1.focal_amd64.deb libperconaserverclient21-dev_8.0.30-22-1.focal_amd64.deb percona-mysql-router_8.0.30-22-1.focal_amd64.deb percona-server-client_8.0.30-22-1.focal_amd64.deb percona-server-common_8.0.30-22-1.focal_amd64.deb percona-server-rocksdb_8.0.30-22-1.focal_amd64.deb percona-server-server_8.0.30-22-1.focal_amd64.deb percona-server-source_8.0.30-22-1.focal_amd64.deb
dpkg -i libperconaserverclient21_8.0.30-22-1.focal_amd64.deb libperconaserverclient21-dev_8.0.30-22-1.focal_amd64.deb percona-mysql-router_8.0.30-22-1.focal_amd64.deb percona-server-client_8.0.30-22-1.focal_amd64.deb percona-server-common_8.0.30-22-1.focal_amd64.deb percona-server-rocksdb_8.0.30-22-1.focal_amd64.deb percona-server-server_8.0.30-22-1.focal_amd64.deb percona-server-source_8.0.30-22-1.focal_amd64.deb
sudo apt-get install zlib1g-dev
apt --fix-broken install
systemctl status mysql.service

Ubuntu OS version : 20.04-amd64-server-20231025 AWS EC2 instance.

[Sujay1046]

Hi,

Can anyone please reply with the findings with the logs that was shared yesterday since the current DB instance is utilizing High CPU and memory and if we restart the service or instance it is getting crashed. Since it is PROD env we dont want to take risks by restarting. If we get a solution for the logs that was shared we will work this weekend to fix the same in PROD.

[matthewb]

@Sujay1046,
You’re entire config is not optimal. You have many parameter out of best practices range.

innodb_io_capacity=12000
innodb_io_capacity_max=14000

Please read, https://www.percona.com/blog/give-love-to-your-ssds-reduce-innodb_io_capacity_max/

innodb_log_buffer_size = 4G

This should never be above 16M (the default) unless you are inserting BLOBs larger than 16M.

innodb_doublewrite= 0

You have disabled a critical safety feature of MySQL. Disabling the doublewrite buffer does not protect you when you get crashes.

innodb_file_per_table

You have this twice in your config

join_buffer_size=2048K
sort_buffer_size=3072K

This tells me you have crazy query problems and you are trying to compensate badly written queries with larger buffers. These buffers are allocate per-connection and you can quickly consume all memory in the system.

You should reduce these back to defaults and focus all efforts on optimizing your queries. Learn how to use EXPLAIN to identify which queries are performing full-table-scans and which queries are using excessively large temporary tables.

2024-03-01T10:34:37.677523Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: ‘8.0.30-22’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Percona Server (GPL), Release ‘22’, Revision ‘7e301439b65’.
2024-03-01T10:39:52.705098Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.30-22).

Look at the time in these two log entries. MySQL started at 10:34 and 5m later, someone/something told MySQL to shutdown.

I would enable the general_log and watch for any connection issuing the SHUTDOWN command. You will be able to see the user account which executed this command which will help you determine the root cause.

[Sujay1046]

Thanks @matthewb for the detailed explaination of the parameters in the my.cnf, since it is PROD env we cannot change the parameters as such so will spin up a test env and will do the necessary changes and will test the app along with DB. So this is the only cause of DB crash after the restart of the instance or do we need to remove any additional packages that was installed in the server. Please let me know we need to update before we implement in PROD.

Regards,
Sujay

[matthewb]

Have you looked at dmesg for any OOM messages?

[Sujay1046]

Yes, now the DB restart crash is resolved, but after changing the my.cnf parameter values as per the suggestions that you gave now we could see sleep queries and CPU is good, but for memory we could see usage of Inactive memory is huge. I have attached our output snippet of the tool that we monitor the DB metrics.

Let me know any suggestions to make the memory stable.
But after restarting the mysql service the memory usage is less and after some days again the usage goes high. Attached the screenshot of the memory usage for last 8 days.

Thanks,
Sujay

[matthewb]

@Sujay1046

But after restarting the mysql service the memory usage is less and after some days again the usage goes high.

Yes, when you restart MySQL you empty out InnoDB’s buffer pool. As the days go by and queries are ran, the buffer pool is slowly populated with data. The amount of memory used by the buffer pool will not increase beyond what you have configured as the max buffer pool size.

[Sujay1046]

Thanks @matthewb.
So what value can be set as an average in buffer pool size were we have set 75% of total memory and it is around 142Gb size were we could avoid the inactive memory usage.
innodb_buffer_pool_size = 142G

[matthewb]

Most MySQL DBAs will recommend starting at 80% of system RAM. However, you can use monitoring tools like Percona Monitoring and Management (PMM) to watch InnoDB’s buffer hit/miss ratio. If you have a 100% hit rate at 30GB, then that means you don’t need the extra memory and should downsize your instance to save costs.