maria-discuss team mailing list archive

[Gordan Bobic <gordan.bobic@xxxxxxxxx>]

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