maria-discuss team mailing list archive

[Gordan Bobic <gordan.bobic@xxxxxxxxx>]

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
>>
>
>