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