maria-discuss team mailing list archive

[Karl Levik <karl.levik@xxxxxxxxx>]

Hi Riccardo,

Are you able to use the --no-lock option?

According to "mariabackup --help":

  --no-lock           Use this option to disable table lock with "FLUSH
TABLES
                      WITH READ LOCK". Use it only if ALL your tables are
                      InnoDB and you DO NOT CARE about the binary log
position
                      of the backup. This option shouldn't be used if there
are
                      any DDL statements being executed or if any updates
are
                      happening on non-InnoDB tables (this includes the
system
                      MyISAM tables in the mysql database), otherwise it
could
                      lead to an inconsistent backup. If you are
considering to
                      use --no-lock because your backups are failing to
acquire
                      the lock, this could be because of incoming
replication
                      events preventing the lock from succeeding. Please try
                      using --safe-slave-backup to momentarily stop the
                      replication slave thread, this may help the backup to
                      succeed and you then don't need to resort to using
this
                      option.

Cheers,
Karl

On Tue, 15 Jun 2021 at 14:00, Riccardo Brunetti <riccardo.brunetti@xxxxxxx>
wrote:

> Hello.
> We are using the mariabackup tool to stream to a remote server the backup
> of our databases.
> We are having some issue because in the middle of the operation the db
> server stops responding for many minutes until we ultimately kill the
> mariabackup process.
> During the lock, we can see the following:
>
> MariaDB [(none)]> show processlist;
>
> +--------+----------------------------+-----------------+--------------------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+
> | Id | User | Host | db | Command | Time | State | Info | Progress |
>
> +--------+----------------------------+-----------------+--------------------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+
> | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL |
> 0.000 |
> | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL |
> 0.000 |
> | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator |
> NULL | 0.000 |
> | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL |
> 0.000 |
> | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler |
> NULL | 0.000 |
> | 274355 | root | localhost:42512 | NULL | Sleep | 407 | | NULL | 0.000 |
> | 297631 | udaww4uw_gsut | localhost:39784 | udaww4uw_gsprod | Query | 17
> | Waiting for global read lock | UPDATE `wp_litespeed_crawler` SET reason =
> CONCAT( SUBSTRING_INDEX( reason, ',', 0 ), '200', SUBSTRI | 0.000 |
> | 302083 | ud4k9xsm_pres633 | localhost | ud4k9xsm_pres633 | Query | 47 |
> Waiting for global read lock | DELETE FROM `ps4d_marketplace_product_action`
> WHERE `id_product` = 7356 AND `id_lan | 0.000 |
> | 302391 | udaww4uw_gsut | localhost:50748 | udaww4uw_gsprod | Query | 46
> | Waiting for global read lock | INSERT INTO wp_post_views (id, type,
> period, count)
> VALUES (62075, 3, '2021', 1)
> ON DUPLIC | 0.000 |
> | 302395 | uqlugtoj_enedina.it | localhost | uqlugtoj_enedina | Query |
> 44 | Waiting for global read lock | UPDATE `wp_options` SET `option_value`
> = '1623829414' WHERE `option_name` = '_transient_timeout_wc_r | 0.000 |
> | 302404 | udlah5r1_remigio | localhost:50782 | udlah5r1_vtiger71 | Query
> | 44 | Waiting for global read lock | insert into
> vtiger_pbxmanager(pbxmanagerid,direction,callstatus,starttime,endtime,totalduration,bill
> | 0.000 |
> | 302431 | utlmb6ko_6M0oD0 | localhost | utlmb6ko_6M0oD0 | Query | 38 |
> Waiting for global read lock | UPDATE `wp_options` SET `option_value` =
> '1623743080' WHERE `option_name` = 'action_scheduler_lock_a | 0.000 |
> | 302432 | uml1gkxr_58IJ3 | localhost:50836 | uml1gkxr_prod | Query | 38
> | Waiting for global read lock | UPDATE `wp_options` SET `option_value` =
> 'a:13835:{i:1226;s:32:\"ab7050de2954be1e369e358a15a17c48\"; | 0.000 |
> | 302433 | uml1gkxr_58IJ3 | localhost:50838 | uml1gkxr_prod | Query | 38
> | Waiting for global read lock | UPDATE `wp_options` SET `option_value` =
> 'a:13835:{i:1226;s:32:\"ab7050de2954be1e369e358a15a17c48\"; | 0.000 |
> | 302440 | uqtx0c94_giorgio | localhost:50848 | uqtx0c94_due | Query | 38
> | Waiting for global read lock | UPDATE `wp_options` SET `option_value` =
> '[]' WHERE `option_name` = '_transient_adsforwp_transient_a | 0.000 |
> | 302473 | uw6vnwff_helpdesk | localhost:50932 | uw6vnwff_helpassist |
> Query | 30 | Waiting for global read lock | INSERT INTO ost_syslog SET
> created=NOW(), updated=NOW() ,title='DB Error #1205',log_type='Error',log |
> 0.000 |
>
> At the same time, we see messages like these in the system log:
>
> Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
> Executing FLUSH TABLES WITH READ LOCK...
>
> This is the command we launch to backup data:
>
> mariabackup --backup --stream=xbstream --user=root
> --extra-lsndir=${BCKDBDIR} -u${MYSQLUSER} -p${MYSQLPW} -H ${MYSQLHOST}"
>
> Is it somehow an expected behavior (some of the db are quite heavily used)?
> How can we avoid those locks?
>
> Thanks
> Riccardo
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>