maria-discuss team mailing list archive

[Riccardo Brunetti <riccardo.brunetti@xxxxxxx>]

Hello Karl.

Thanks for your help.

Unfortunately, we have many databases with many MyISAM tables....

I guess we can't use the --no-lock option.



Kind Regards

Riccardo 



15/06/2021, 18:43 Karl Levik ha scritto:

> 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
> 
> 
> 
> 
> 
> 
> _______________________________________________
> 
> 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