[MDEV-32377] mariadb 10.6.8 hang Created: 2023-10-09  Updated: 2023-12-04  Resolved: 2023-12-04

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

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

centos 7.4


Attachments: PNG File 23-10-24-2.png     PNG File 23-10-24.png     PNG File df.png     Text File innodb_status.log     PNG File log.png     PNG File mariadb.png     PNG File processlist-1.png     PNG File processlist.png     Text File show_global_status.log     PNG File trx.png    

 Description   

Importing a 170M sql file will take several hours.

mysql < a.sql

Enter the database to view:

show processlist;

It shows that the import operation has been stuck at the INSERT INTO operation.

Check that there are no lock records in the information_schema.INNODB_LOCKS table

Executing SHOW STATUS and how engine innodb status will hang.

Execute show create table td_framework_monitor_history and Waiting for table flush will be displayed in show processlist. However, show create table and other tables are normal

Other tables can be selected, but insert will also hang. Show processlist shows State: update status.

It has appeared many times, sometimes not when data is imported, but also when inserting normal business logic. But no pattern found

 Comments   
Comment by Sergei Golubchik [ 2023-10-12 ]

can you show the table structure?

Comment by ls [ 2023-10-13 ] 

show create table td_framework_monitor_history

The table being inserted by show will hang, and the state is waiting for metadata lock.

show other tables can operate normally

Comment by ls [ 2023-10-13 ]

MDEV-30884

Here is a question similar to mine. But not sure if it is. I plan to upgrade to mariadb 10.6.15 and see if it happens again.

Comment by ls [ 2023-10-18 ]

Encountered this problem again after upgrading to 10.6.15. These are the hangs when executing INSERT IGNORE INTO TD_DM_USER to insert 1000 records in batches. But this time, the `show engine innodb status\G` command can be executed normally. `show create table TD_DM_USER\G` can also return table structure information normally.

show engine innodb status\G You can see that there is a big gap between Log flushed up to and Log sequence number. I am not sure whether it is because of the hangs statement that causes redo not to flush the disk. Or is it because redo does not flush the disk, causing the insert statement to hang.

There is still no lock information in information_schema.innodb_lock_waits

error log no information

The operating system disk has space left and the disk is readable and writable

Comment by ls [ 2023-10-24 ]

Today is October 24th. The instance hangs again.
As shown in the picture.
Version is 10.6.15

Comment by Sergei Golubchik [ 2023-11-03 ]

these screenshots don't really help much. could you paste, please, a text output of show full processlist and show create table td_framework_monitor_history

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