[MDEV-28704] mariadb server consumes 100% of the cpus running in raspberry 64 bits Created: 2022-05-31  Updated: 2022-08-30  Resolved: 2022-08-30

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.5.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Enrique Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None

Attachments: File lshw.out     File mariadb.gdb_trace     File mariadb.journal    

 Description   

I have been running since a week or so a mariadb server in a raspberry 64 bits but very often it starts using all available cores all the time and it does not stop.

The database is used by 4 different web services. The interesting thing is that I have been running the same database server, with the same load for years without problem using Raspbian 32 bits. I have now migrated to Debian 64 bits (arm64 architecture) and the problems have started. But it s actually the same mariadb version as well as the whole stack, since Raspbian OS is based on the same Debian 11 sources.

I have not been able to correlate the problem with a given query or a particular database. Sometimes the problem starts to appear within minutes of the database restart and sometimes after a few hours. A restart of the database seems to restore a normal behaviour.

The system logs do not show anything special. I am attaching the output of journalctl between two restarts where high CPU happened.

I have also tried to set the global log with SET GLOBAL general_log=1 but apart from a flurry of queries I cannot say anything obvious.

I have attached gdb and show the stack trace of a couple of threads that were showing high CPU usage in top. The trace does not tell me much, but I attach them here in case they bring some useful info.

BTW, during the high CPU times, some of the services seem to continue working properly, although at some point it seems that mariadb stops servicing requests, at least for some of the services.

 Comments   
Comment by Enrique [ 2022-05-31 ]

Some more context info:

# cat /etc/os-release 
PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
 
NAME="Debian GNU/Linux"
 
VERSION_ID="11"
 
VERSION="11 (bullseye)"
 
VERSION_CODENAME=bullseye


# dpkg -l | grep mariadb
 
ii  libdbd-mariadb-perl               1.21-3                                arm64        Perl5 database interface to the MariaDB/MySQL databases
 
ii  libmariadb3:arm64                 1:10.5.15-0+deb11u1                   arm64        MariaDB database client library
 
ii  mariadb-client-10.5               1:10.5.15-0+deb11u1                   arm64        MariaDB database client binaries
 
ii  mariadb-client-core-10.5          1:10.5.15-0+deb11u1                   arm64        MariaDB database core client binaries
 
ii  mariadb-common                    1:10.5.15-0+deb11u1                   all          MariaDB common configuration files
 
ii  mariadb-server-10.5               1:10.5.15-0+deb11u1                   arm64        MariaDB database server binaries
 
ii  mariadb-server-core-10.5          1:10.5.15-0+deb11u1                   arm64        MariaDB database core server files

The system is fully up to date, so the rest of the stack is what is provided by the Debian repos.

This is running in a Raspberry PI 4. I attach the output of lshw.

Comment by Marko Mäkelä [ 2022-05-31 ]

With this little information, it is really hard to say anything. If the ARMv6 or ARMv7 build of MariaDB that ran before upgrading to ARMv8 was before 10.5, then one of many possible causes could be that the innodb_log_file_size may have been effectively halved by MDEV-20907. A small log could cause huge increase in data file write activity.

Comment by Marko Mäkelä [ 2022-05-31 ]

Which MariaDB Server version was used before upgrading to the ARMv8 (Aarch64) ISA? What are the start-up parameters of the MariaDB server (in /etc/mysql or elsewhere)?

Comment by Enrique [ 2022-06-01 ]

The version of MariaDB Server used before was exactly the same one :

# dpkg -l | grep maria
 
ii  libmariadb3:armhf                1:10.5.15-0+deb11u1                   armhf        MariaDB database client library
 
ii  mariadb-client-10.5              1:10.5.15-0+deb11u1                   armhf        MariaDB database client binaries
 
ii  mariadb-client-core-10.5         1:10.5.15-0+deb11u1                   armhf        MariaDB database core client binaries
 
ii  mariadb-common                   1:10.5.15-0+deb11u1                   all          MariaDB common configuration files
 
ii  mariadb-server-10.5              1:10.5.15-0+deb11u1                   armhf        MariaDB database server binaries
 
ii  mariadb-server-core-10.5         1:10.5.15-0+deb11u1                   armhf        MariaDB database core server files

But that wasn´t indeed the version in which the tables were created. They were created several years ago, so now I cannot tell for sure which version was used at that time. My guess is that it was 10.1.48 for one of the databases and 10.3.34 for the other 3 databases.

Comment by Sergei Golubchik [ 2022-08-01 ]

The gdb trace shows that two threads are inside the SQL optimizer, looking for a best execution plan for your queries.'

What SQL queries do you run there?

Generated at Thu Feb 08 10:02:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.