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
