Last modified: 2014-08-06 14:01:09 UTC
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?
(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.
(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.
*tools
(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.
See also https://bugzilla.wikimedia.org/show_bug.cgi?id=69182
