[MDEV-15921] mariabackup hangs at FLUSH TABLES WITH READ LOCK when Node is wsrep_desync Created: 2018-04-18  Updated: 2018-05-15  Resolved: 2018-04-23

Status: Closed
Project: MariaDB Server
Component/s: Backup, Galera
Affects Version/s: 10.3.6
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Zdravelina Sokolovska (Inactive) Assignee: Vladislav Vaintroub
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 7.4

Attachments: Text File mariabackup_log.txt     Text File processlist.txt    
Issue Links:
Relates
relates to MDEV-15067 Node fails to rejoin mdb cluster afte... Closed

 Description   

mariabackup hangs at FLUSH TABLES WITH READ LOCK when Node is wsrep_desync

on Galera Node set wsrep_desync and FTWRL
get mariabackup . mariabackup hangs.

attached full mariabackup log file and sql processlist ;

MariaDB [(none)]> set global wsrep_desync=1;
 
Query OK, 0 rows affected (0.001 sec)
 
MariaDB [(none)]> flush tables with read lock;
 
Query OK, 0 rows affected (0.000 sec)

2. Perform mariabackup from other session

mariabackup --backup -u root -p1 --target-dir=/var/mariadb/backup/B1
 
180418 14:01:52 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
 
Using server version 10.3.6-MariaDB
 
mariabackup based on MariaDB server 10.3.6-MariaDB Linux (x86_64)
 
mariabackup: uses posix_fadvise().
 
mariabackup: cd to /var/lib/mysql/
 
mariabackup: open files limit requested 0, set to 1024
 
mariabackup: using the following InnoDB configuration:
 
mariabackup:   innodb_data_home_dir = .
 
mariabackup:   innodb_data_file_path = ibdata1:12M:autoextend
 
mariabackup:   innodb_log_group_home_dir = ./
 
2018-04-18 14:01:52 0 [Note] InnoDB: Number of pools: 1
 
mariabackup: Generating a list of tablespaces
 
2018-04-18 14:01:52 0 [Warning] InnoDB: Allocated tablespace ID 154 for fi/people, old maximum was 0
 
2018-04-18 14:01:52 0 [Warning] InnoDB: Open files 60 exceeds the limit 18446744073709551615
 
180418 14:01:52 >> log scanned up to (48213433987)
 
180418 14:01:52 [01] Copying ./ibdata1 to /var/mariadb/backup/B1/ibdata1
 
2018-04-18 14:01:53 0 [Warning] InnoDB: Open files 61 exceeds the limit 18446744073709551615
 
180418 14:01:53 >> log scanned up to (48213433987)
 
180418 14:01:54 [01]        ...done
 
180418 14:01:54 [01] Copying ./fi/people.ibd to /var/mariadb/backup/B1/fi/people.ibd
 
180418 14:01:54 [01]        ...done
 
 
 
~~~~~
 
180418 14:44:08 [01] Copying ./tpcc/ORDER_LINE.ibd to /var/mariadb/backup/B1/tpcc/ORDER_LINE.ibd
 
180418 14:44:09 [01]        ...done
 
180418 14:44:09 [01] Copying ./tpcc/STOCK.ibd to /var/mariadb/backup/B1/tpcc/STOCK.ibd
 
180418 14:44:11 [01]        ...done
 
180418 14:44:11 [01] Copying ./tpcc/WAREHOUSE.ibd to /var/mariadb/backup/B1/tpcc/WAREHOUSE.ibd
 
180418 14:44:11 [01]        ...done
 
180418 14:44:11 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
 
180418 14:44:11 Executing FLUSH TABLES WITH READ LOCK...

mariabackup hangs

MariaDB [(none)]> show processlist ;
 
 
 
|  88 | root        | localhost | NULL | Query   |     0 | Init                     | show processlist            |                                              0.000 |
 
| 120 | root        | localhost | NULL | Sleep   |  2386 |                          | NULL                        |                                              0.000 |
 
| 125 | root        | localhost | NULL | Query   |  1345 | Init                     | FLUSH TABLES WITH READ LOCK |                                              0.000 |
 
+-----+-------------+-----------+------+---------+-------+--------------------------+-----------------------------+                                          ----------+
 
73 rows in set (0.000 sec)



 Comments   
Comment by Vladislav Vaintroub [ 2018-04-18 ]

It does not hang, it waits for the current holder of global read lock to release the lock (unlock tables). Holding that lock for long time is an excellent way to stall many queries, and not just mariabackup.

If you do not want mariabackup to do "flush tables with read lock", there is an option for it. there are also options for controlling "flush table with read lock" timeouts, and even more (like, forcefully killing sessions that take too long time)

Comment by Vladislav Vaintroub [ 2018-04-19 ]

You are missing
UNLOCK TABLES
in your mysql client session.

FTWRL locks all tables. do not hold it for long. do UNLOCK TABLES, when you're ready with whatever you did under lock.

Comment by Vladislav Vaintroub [ 2018-04-19 ]

Alternatively, if you in any reason have to FTWRL in the session, and hold it, you can do backup with --no-locks

Comment by Zdravelina Sokolovska (Inactive) [ 2018-04-20 ]

the same occurs when do backup with --no-locks

mariabackup --backup -u root -p1 --galera-info --no-locks --target-dir=/var/mariadb/backup/B1 

[root@t4w5 ~]# mariabackup --backup -u roo                                                                                                                   t -p1  --galera-info --no-locks --target-d                                                                                                                   ir=/var/mariadb/backup/B0
 
180420 17:43:23 Connecting to MySQL server                                                                                                                    host: localhost, user: root, password: se                                                                                                                   t, port: not set, socket: not set
 
Using server version 10.3.6-MariaDB
 
mariabackup based on MariaDB server 10.3.6                                                                                                                   -MariaDB Linux (x86_64)
 
mariabackup: uses posix_fadvise().
 
mariabackup: cd to /var/lib/mysql/
 
mariabackup: open files limit requested 0,                                                                                                                    set to 1024
 
mariabackup: using the following InnoDB co                                                                                                                   nfiguration:
 
mariabackup:   innodb_data_home_dir = .
 
mariabackup:   innodb_data_file_path = ibd                                                                                                                   ata1:12M:autoextend
 
mariabackup:   innodb_log_group_home_dir =                                                                                                                    ./
 
2018-04-20 17:43:23 0 [Note] InnoDB: Numbe                                                                                                                   r of pools: 1
 
mariabackup: Generating a list of tablespa                                                                                                                   ces
 
2018-04-20 17:43:23 0 [Warning] InnoDB: Al                                                                                                                   located tablespace ID 158 for vd/t, old ma                                                                                                                   ximum was 0
 
2018-04-20 17:43:23 0 [Warning] InnoDB: Op                                                                                                                   en files 61 exceeds the limit 184467440737                                                                                                                   09551615
 
180420 17:43:23 >> log scanned up to (4821                                                                                                                   3480709)
 
180420 17:43:23 [01] Copying ./ibdata1 to                                                                                                                    /var/mariadb/backup/B0/ibdata1
 
2018-04-20 17:43:24 0 [Warning] InnoDB: Op                                                                                                                   en files 62 exceeds the limit 184467440737                                                                                                                   09551615
 
180420 17:43:24 >> log scanned up to (4821                                                                                                                   3480709)
 
180420 17:43:25 [01]        ...done
 
180420 17:43:25 [01] Copying ./vd/t.ibd to                                                                                                                    /var/mariadb/backup/B0/vd/t.ibd
 
180420 17:43:25 [01]        ...done
 
 
 
~~~~~~~~~
 
180420 17:43:48 [01] Copying ./tpcc/STOCK.                                                                                                                   ibd to /var/mariadb/backup/B0/tpcc/STOCK.i                                                                                                                   bd
 
180420 17:43:49 [01]        ...done
 
180420 17:43:49 [01] Copying ./tpcc/WAREHO                                                                                                                   USE.ibd to /var/mariadb/backup/B0/tpcc/WAR                                                                                                                   EHOUSE.ibd
 
180420 17:43:49 [01]        ...done
 
180420 17:43:49 Executing FLUSH NO_WRITE_T                                                                                                                   O_BINLOG TABLES...
 
180420 17:43:49 Executing FLUSH TABLES WIT                                                                                                                   H READ LOCK...

.
mariabackup hangs

Comment by Vladislav Vaintroub [ 2018-04-20 ]

it does not hang, it waits, for lock holder to release the lock.

and it does not work, because the option was mistyped (also in our documentation). it is called correctly --no-lock , not --no-locks

Generated at Thu Feb 08 08:25:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.