Percona mysql 8.0.25-15 crash restarts for count(*)

MySQL & MariaDB
1

Hello,
I’m having issue with mysql server when doing a select count() from table. The mysql server crash restart when doing the count()

error message: Lost connection to mysql

My RAM on the machine is 16GB and my innodb_buffer_pool_size is 13GB

Info from error.log

2022-07-06T21:23:35.316227Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.25-15) starting as process 27393
2022-07-06T21:23:35.426026Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-07-06T21:23:36.740294Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-07-06T21:23:38.197831Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060, socket: /var/lib/mysql/mysqlx.sock
Wed Jul 6 21:23:38 2022 PerconaFT recovery starting in env /data/mysql/
Wed Jul 6 21:23:38 2022 PerconaFT recovery scanning backward from 843222
Wed Jul 6 21:23:38 2022 PerconaFT recovery bw_end_checkpoint at 843222 timestamp 1657142590908817 xid 843218 (bw_newer)
Wed Jul 6 21:23:38 2022 PerconaFT recovery bw_begin_checkpoint at 843218 timestamp 1657142590508642 (bw_between)
Wed Jul 6 21:23:38 2022 PerconaFT recovery turning around at begin checkpoint 843218 time 400175
Wed Jul 6 21:23:38 2022 PerconaFT recovery starts scanning forward to 843222 from 843218 left 4 (fw_between)
Wed Jul 6 21:23:38 2022 PerconaFT recovery closing 2 dictionaries
Wed Jul 6 21:23:38 2022 PerconaFT recovery making a checkpoint
Wed Jul 6 21:23:38 2022 PerconaFT recovery done
2022-07-06T21:23:38.285525Z 0 [System] [MY-010229] [Server] Starting XA crash recovery…
2022-07-06T21:23:38.302393Z 0 [System] [MY-010232] [Server] XA crash recovery finished.

2

Your system is running out of memory. Check dmesg and system logs to look for “OOM”. Most likely the kernel killed MySQL because it was using too much memory. You need to decrease buffer pool to 8 or 10GB, or add more memory to the server.

1 Like
3

Thank you @matthewb, I’ll reduce the buffer pool size. Just out of curiosity wouldn’t mysql use max memory of 13Gib since buffer pool size is set to 13Gib.

1 Like
4

No. InnoDB buffer pool size only restricts the size of the innodb buffer pool. That’s why it is named like that. It is not called ‘mysql_buffer_pool_size’. InnoDB is 1 engine running within MySQL. There are many other memory allocations that take place within MySQL during normal operations and even more when running queries that examine lots of data.

2 Likes
5

Thank you. @matthewb

1 Like