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 <[email protected]> > 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 : [email protected] > > > > Unsubscribe : https://launchpad.net/~maria-discuss > > > > More help : https://help.launchpad.net/ListHelp > > > > > > > _______________________________________________ > > Mailing list: https://launchpad.net/~maria-discuss > > Post to : [email protected] > > Unsubscribe : https://launchpad.net/~maria-discuss > > More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
