Ok I can see there is a subselect, these are sometimes painful
particularly for MySQL. We fixed that already, see in fact_importer.rb
(this is develop branch):
def delete_removed_facts
ActiveSupport::Notifications.instrument
"fact_importer_deleted.foreman", :host_id => host.id, :host_name =>
host.name, : +facts => facts, :deleted => [] do |payload|
delete_query = FactValue.joins(:fact_name).where(:host => host,
'fact_names.type' => fact_name_class.name).where.
+not('fact_names.name' => facts.keys)
if ActiveRecord::Base.connection.adapter_name.downcase.starts_with?
'mysql'
# MySQL does not handle delete with inner query correctly
(slow) so we will do two queries on purpose
payload[:count] = @counters[:deleted] = FactValue.where(:id =>
delete_query.pluck(:id)).delete_all
else
# deletes all facts using a single SQL query with inner query otherwise
payload[:count] = @counters[:deleted] = delete_query.delete_all
end
end
end
See the comment there, do you have this in your instance? If not git
blame the commit and apply it. You have some older version I assume.
On Wed, Oct 4, 2017 at 1:38 AM, 'Konstantin Orekhov' via Foreman users
<[email protected]> wrote:
>
>> One more idea - we have seen similar (but different tables) deadlocks
>> when a background (cron) job we ship by default attempts to delete old
>> reports. Can you check if there is any cronjob or any other process
>> doing some management of facts? Even deleting lot of data can block
>> all updates for a long time (minutes to hours). Perhaps try to disable
>> all foreman jobs and re-test.
>
>
> I have tried this to no avail. However, I think the culprit of a problem is
> in very slow DELETE MySQL query, which is apparently happens even for
> absolutely new and freshly-discovered systems as well already discovered
> ones.
>
> 2017-09-28 13:09:49 c75f5c40 [sql] [D] SQL (50843.2ms) DELETE FROM
> `fact_values` WHERE `fact_values`.`id` IN
>
> Please see these gists I've recorded with SQL debug enabled. I have a ton of
> hosts doing exactly the same thing - try to register, mysql delete expires
> (it takes up to 50 sec as you can see), some rollback happens and expires
> again. And so on and so forth until systems register one by one. This
> results in many empty or duplicate entries even for a small batch of systems
> coming online at the same time.
>
> https://gist.github.com/anonymous/a721e220d82f5160450e483b8776489d
>
> The above examples are taken from a single Foreman instance running against
> a regular (non-Galera) MySQL DB, so at least I can say that the fact that I
> had several Foreman instances behind a load-balancer talking to
> Galera-replicated MySQL has nothing to do with this behavior. The only
> difference is that in Galera-enabled DB, expiration errors are replaced with
> deadlock error, which makes total sense - if delete operation takes almost a
> minute, no wonder it results in some rows being locked. As load increases
> (more systems register at the same time), more and more such errors are
> happening, so I believe a proper way to deal with this is optimize MySQL
> query first and the go from there. Would you agree?
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Foreman users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/foreman-users.
> For more options, visit https://groups.google.com/d/optout.
--
Later,
Lukas @lzap Zapletal
--
You received this message because you are subscribed to the Google Groups
"Foreman users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/foreman-users.
For more options, visit https://groups.google.com/d/optout.
