[Maria-discuss] Problem with mariabackup
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
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 <riccardo.brunetti@host.it> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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 <riccardo.brunetti@host.it> 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
What version of MariaDB are you using? There was a release about 6 months ago that caused spurious phantom locks intermittently under load. I observed it on both 10.3 and 10.4. It was fixed about 2 releases ago, I think it was a regression in a release at the beginning of the year. Update to the latest point release on the branch you are using and you might find the problem disappears. On Tue, Jun 15, 2021 at 6:32 PM Riccardo Brunetti <riccardo.brunetti@host.it> wrote:
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 < riccardo.brunetti@host.it> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hello.
Server version: 10.3.28-MariaDB-log MariaDB Server
Cheers Riccardo 15/06/2021, 17:38 Gordan Bobic ha scritto:
What version of MariaDB are you using? There was a release about 6 months ago that caused spurious phantom locks intermittently under load. I observed it on both 10.3 and 10.4. It was fixed about 2 releases ago, I think it was a regression in a release at the beginning of the year.
Update to the latest point release on the branch you are using and you might find the problem disappears.
On Tue, Jun 15, 2021 at 6:32 PM Riccardo Brunetti <riccardo.brunetti@host.it> wrote:
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 <riccardo.brunetti@host.it> 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
That's recent enough to have the fix in it. In which case, I'm fresh out of ideas. On Tue, Jun 15, 2021 at 6:40 PM Riccardo Brunetti <riccardo.brunetti@host.it> wrote:
Hello.
Server version: 10.3.28-MariaDB-log MariaDB Server
Cheers Riccardo
15/06/2021, 17:38 Gordan Bobic ha scritto:
What version of MariaDB are you using? There was a release about 6 months ago that caused spurious phantom locks intermittently under load. I observed it on both 10.3 and 10.4. It was fixed about 2 releases ago, I think it was a regression in a release at the beginning of the year. Update to the latest point release on the branch you are using and you might find the problem disappears.
On Tue, Jun 15, 2021 at 6:32 PM Riccardo Brunetti < riccardo.brunetti@host.it> wrote:
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 < riccardo.brunetti@host.it> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@host.it> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@host.it> 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
participants (3)
-
Gordan Bobic -
Karl Levik -
Riccardo Brunetti