[MDEV-31047] MariaDB(10.4) is sometimes very slow Created: 2023-04-13  Updated: 2023-06-27  Resolved: 2023-06-27

Status: Closed
Project: MariaDB Server
Component/s: Replication, Storage Engine - InnoDB
Affects Version/s: 10.4.26
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Hwang Euijung Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: innodb, replication
Environment:

OS : Rocky Linux release 8.6 (Green Obsidian), 4.18.0-372.9.1.el8.x86_64
CPU : 2 x 48 AMD EPYC 7643 48-Core Processor
Memory : 1TB
Disk : 7.3TB ssd

Attachments: Text File db_variables_27.txt     Text File innodb_status_20230412111150.txt     Text File innodb_status_normal.txt    
Issue Links:
Duplicate
duplicates MDEV-31044 MariaDB(10.4) is sometimes very slow ... Closed

 Description   

I have a super production DB server as above environment.
DB servers consist of 1 master DB server, 2 replication servers.
And there are 3 WAS server and 1 RP server.

Generally, DB server used 10~30% cpu rate.(active db sessions are 30~40)

But DB server is sometimes very slow.( 1~3 times a week)
So actvive db session increase to 3000(max), and cpu rate up to 30%~60%.
And our service have been stopped.
This problem occur at non-peak time.
This is about master DB server.

I've googling for a month, but I didn't get any reason and solution unfortunately.
I doubt innodb_log_buffer_size, but I'm not sure.

Currently, our only solution is that WAS servers httpd deamon stop, and restart after active DB session is 0 from WAS servers.

I attach mariadb variables and innodb status of problem's initial time and innodb status of normal.

What can I check?
I hope this nightmare is ended ASAP.

Thanks in advance.

 Comments   
Comment by Sergei Golubchik [ 2023-05-26 ]

what is slow? queries use inefficient execution plan? Innodb is slow? network? CPU is overloaded? Disk IO?

Try to find out where your slow query spends the time — there are many tools that can tell you that, you can show SHOW PROFILE, PERFORMANCE_SCHEMA, poor man profiler (or a proper profiler), etc.

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