Hello. Thank you very much for your answer.
What it is not clear to me is that, if I look at one of the tables that appears to be locked: > | 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 | it's not a MyISAM table, but it's InnoDB.. > | wp_litespeed_crawler | CREATE TABLE `wp_litespeed_crawler` ( > > `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, > > `url` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', > > `res` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT > '-=not crawl, H=hit, M=miss, B=blacklist', > > `reason` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'response code, > comma separated', > > `mtime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE > current_timestamp(), > > PRIMARY KEY (`id`), > > KEY `url` (`url`(191)), > > KEY `res` (`res`) > > ) ENGINE=InnoDB AUTO_INCREMENT=15414 DEFAULT CHARSET=utf8mb4 > COLLATE=utf8mb4_unicode_ci | Cheers Riccardo 15/06/2021, 17:02 Gordan Bobic ha scritto: > Purely guessing here, but it sounds like you have MyISAM tables. Those have > to be locked for the duration of the transfer to ensure data consistency. > > Convert everything except the system databases to InnoDB and there is a good > chance the problem will disappear. > > > > On Tue, Jun 15, 2021 at 4:00 PM 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
