maria-discuss team mailing list archive

[Riccardo Brunetti <riccardo.brunetti@xxxxxxx>]

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@xxxxxxx> 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@xxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
> > > > 
> > > > Unsubscribe : https://launchpad.net/~maria-discuss
> > > > 
> > > > More help   : https://help.launchpad.net/ListHelp