Page MenuHomePhabricator

Database upgrade MariaDB 10: Lock wait timeouts / deadlocks in a row
Closed, InvalidPublic

Description

Author: metatron

Description:
Since start of upgrade process, I'm experiencing lots of Lock wait timeouts / deadlocks.

Usecase:
Xtools creates (temporary) tables in schema s51187__xtools_tmp (newly created in this new "combined" instance) to analyze data. Currently this breaks in ~ 9 out of 10 calls due to lock wait timeouts/deadlocks.
This only happens on the new "combined" instance. eg. accessing it via s[45].labsdb or dewiki.labsdb or similar.

While perfoming the identical analysis on an "old" shard eg. s1.labsdb/enwiki.labsdb everything works fine. There is also an s51187__xtools_tmp schema - but an "old" one.

Samples:
(enwiki ~25,000 edits)
https://tools.wmflabs.org/xtools/ec/index.php?user=TParis&lang=en&wiki=wikipedia&uselang=en&purge=1

(dewiki ~24,000 edits)
https://tools.wmflabs.org/xtools/ec/index.php?user=Anton-kurt&lang=de&wiki=wikipedia&uselang=de&purge=1

Questions:

  • Is this behaviour transient? If not, please make it go away.
  • Is new user-schema-storage slower than the older one? And is this realated?
  • Is there some process that interferes with user schemas?

Version: unspecified
Severity: critical

Details

Reference
bz68753

Event Timeline

bzimport raised the priority of this task from to Needs Triage.Nov 22 2014, 3:28 AM
bzimport added a project: Cloud-VPS.
bzimport set Reference to bz68753.
bzimport added a subscriber: Unknown Object (MLST).
bzimport created this task.Jul 28 2014, 5:23 PM

(In reply to metatron from comment #0)

  • Is this behaviour transient? If not, please make it go away.

Hopefully transient, yes.

We are using the TokuDB storage engine on the new instance in order to handle the write load from replicating multiple shards. TokuDB has a shorter lock-wait setting by default, just 4s. Immediately after upgrade that default was in use for around 24h, but has since been increased to 50s to match InnoDB config.

Today we are trialing READ-COMMITTED isolation level which was initially REPEATABLE-READ. This has to be watched carefully; while toolserver was RC, the labsdb replicas were RR and tools may rely on one or the other.

  • Is new user-schema-storage slower than the older one? And is this realated?

The new instance is on a RAID 10 array during migration. It will be migrated back onto SSD this week after the old instances are removed. Yes, it may slower until then.

Also worth noting that TokuDB is likely to be a little slower than InnoDB in cases when the dataset fits entirely in memory, however it should be faster when the dataset exceeds memory and/or data is cold. The difference should always be well within the same order of magnitude. Anything running drastically slower is a bug.

  • Is there some process that interferes with user schemas?

Do you mean user schemas or user traffic? If queries are running for many hours, or doing table scans on large replicated tables that block replication, they may be killed.

Some tools use very slow processes that only *seemed* ok on the old instances, but in fact caused issues behind the scenes up to and including instance OOM crashes. We need to reach out to certain labs users to help redesign some queries to be a little friendlier.

In most cases it will be a matter of breaking up large, slow transactions into batches.

scfc added a comment.Jul 29 2014, 3:30 PM

(In reply to Sean Pringle from comment #1)

[...]

Today we are trialing READ-COMMITTED isolation level which was initially
REPEATABLE-READ. This has to be watched carefully; while toolserver was RC,
the labsdb replicas were RR and tools may rely on one or the other.

[...]

Asher wrote in http://permalink.gmane.org/gmane.org.wikimedia.labs/1308:

> [...]
> Is this limited to the replicated tables or does it affect
> the user tables as well? Does this mean REPEATABLE READ is
> not available at all on the labsdb servers?
Essentially. While tx_isolation is set to REPEATABLE READ, only READ
COMMITTED is actually guaranteed. This applies to all labsdb systems.

So Tools shouldn't have been able to rely on RR.

metatron wrote:

(In reply to Sean Pringle from comment #1)

Though I didn't change anything, the massive lock wait timeouts or deadlocks disappeared. Right now I'm trying:
ENGINE=Aria PAGE_CHECKSUM=0 TABLE_CHECKSUM=0 ROW_FORMAT=FIXED for this special purpose tables.

Another issue, but related to blocks:
From time to time statistics collector appears and blocks all current requests for ~10-20 sec.

1903088 s51187 10.68.16.29:48497 zhwiki_p Query 37 Opening tables
1903141 s51187 10.68.17.123:45670 ptwiki_p Query 33 Sending data
1903172 s51187 10.68.16.29:48546 dewiki_p Query 29 statistics
1903179 s51187 10.68.17.123:45770 dewiki_p Query 25 statistics
1903183 s51187 10.68.16.29:48562 wikidatawiki_p Query 24 statistics
1903184 s51187 10.68.16.29:48566 wikidatawiki_p Query 24 statistics
1903185 s51187 10.68.17.123:45790 ptwiki_p Query 24 statistics
...

(In reply to Tim Landscheidt from comment #2)

(In reply to Sean Pringle from comment #1)

[...]
Today we are trialing READ-COMMITTED isolation level which was initially
REPEATABLE-READ. This has to be watched carefully; while toolserver was RC,
the labsdb replicas were RR and tools may rely on one or the other.
[...]

Asher wrote in http://permalink.gmane.org/gmane.org.wikimedia.labs/1308:

> [...]
> Is this limited to the replicated tables or does it affect
> the user tables as well? Does this mean REPEATABLE READ is
> not available at all on the labsdb servers?
Essentially. While tx_isolation is set to REPEATABLE READ, only READ
COMMITTED is actually guaranteed. This applies to all labsdb systems.

So Tools shouldn't have been able to rely on RR.

More complicated than that, unfortunately. Using innodb_locks_unsafe_for_binlog has the effect of only guaranteeing READ COMMMITTED for INSERT/UPDATE/DELETE queries, but not for SELECT which respects the REPEATABLE READ isolation level. This would only be a potential behavior change for certain multi-statement transactions.

(In reply to metatron from comment #4)

Though I didn't change anything, the massive lock wait timeouts or deadlocks
disappeared. Right now I'm trying:
ENGINE=Aria PAGE_CHECKSUM=0 TABLE_CHECKSUM=0 ROW_FORMAT=FIXED for this
special purpose tables.

Ok, good. Let's watch it for a day or two before declaring the locking problem solved.

Technical13 added a subscriber: Technical13.
chasemp triaged this task as Low priority.Nov 30 2015, 4:19 PM
chasemp added a subscriber: chasemp.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 30 2015, 4:19 PM
Matthewrbowker moved this task from Inbox to Other on the XTools board.Nov 24 2016, 12:05 AM
Restricted Application added a subscriber: Matthewrbowker. · View Herald TranscriptNov 24 2016, 12:05 AM
Matthewrbowker closed this task as Invalid.Jul 13 2017, 8:51 PM
Matthewrbowker claimed this task.
Matthewrbowker moved this task from Other to Complete on the XTools board.

I am closing this, work on T153112: Epic: Rewriting XTools has removed temporary tables. T167217: Planning for Xtools beta is the plan for rolling that out.

Restricted Application removed a subscriber: Liuxinyu970226. · View Herald TranscriptJul 13 2017, 8:51 PM