Copy visits into temp table non locking #14788
Conversation
|
BTW the only other solution I see to make this non-locking is selecting all results in PHP, then doing a batchInsert. Of course this can have memory and performance implications. Could try doing it for MyISAM users though |
|
Added a workaround for MyIsam users to actually fetch the idVisits into PHP and insert them all at once to not have it locking for them. This way we don't need to disable it for them as this behaviour should be used eventually for all users |
| @@ -68,6 +68,7 @@ public function __construct(Parameters $params, ArchiveWriter $archiveWriter = n | |||
| $this->archiveWriter->initNewArchive(); | |||
|
|
|||
| $this->logAggregator = new LogAggregator($params); | |||
| $this->logAggregator->allowUsageSegmentCache(); | |||
There was a problem hiding this comment.
We only do it during archiving... if other usages want to make use of it, they need to enable it as well... prevents performing the create temporary table logic for some live requests like Heatmaps where it otherwise would possibly apply the segment on a yearly range etc.
There was a problem hiding this comment.
Also prevents that someone maybe doesn't call the cleanup command etc
There was a problem hiding this comment.
Also prevents that we possibly create heaps of temporary tables depending on the ui/api request...
|
FYI: Was reading this morning a new blog the read-uncommitted status might be even fine for our regular archiving queries but this would be a different issue https://federico-razzoli.com/read-only-transactions-in-mysql |
|
@sgiehl can you please review this PR and check it all makes sense? |
|
sounds good @mattab most of the code we're actually running on production for a while |
@mattab from what I've been reading this should avoid the issue re a long running
create temporary table select ...possibly locking some tracking requests.create temporary tableandinsert into ...to avoid a metadata lockread-uncommittedto prevent locks on the source and target DB. Target DB doesn't matter anyway since we know nobody can read from it until it's been created as it's a temporary tableread-uncommittedonly wheninnodbis configured in the config as we can't really know ifinnodbis available and it wouldn't make sense withmyisam.Myisammight be pretty bad as it locks the entire table... I wonder if we should disable this newtemp tablebehaviour if someone is still usingmyisamonlog_visit(and assume it is the same for other log tables)? Could also add a system check if not already done if myisam is still used?This issue also fixes: #14818 making archiving of segments again faster