Project

General

Profile

Actions

Bug #18561

closed

Slow exec of delete_removed_facts on MySQL 5.6

Added by Yakir Gibraltar over 7 years ago. Updated over 6 years ago.

Status:
Closed
Priority:
Normal
Category:
Importers
Target version:
Difficulty:
Triaged:
Fixed in Releases:
Found in Releases:

Description

Hi,
I'm using MySQL 5.6 and 2657 hosts, after upgrade foreman to 1.14.1 i see bad performance from DB side on delete_removed_facts in app/models/fact_value.rb one delete take 10 sec instead 1 sec (maximum).
For example:
2017-02-19 15:16:25 04030062 [audit] [I] [461790d9.d462ad46.com] deleted 0 (9844.4ms)
2017-02-19 15:16:25 04030062 [audit] [I] [461790d9.d462ad46.com] updated 10 (126.8ms)
2017-02-19 15:16:25 04030062 [audit] [I] [461790d9.d462ad46.com] added 0 (2.1ms)

Base on https://github.com/theforeman/foreman/commit/46e1ea9f704f41afbd0373e4b8fc54313ac6b397, i reverted:
@ def delete_removed_facts
ActiveSupport::Notifications.instrument "fact_importer_deleted.foreman", :host_id => host.id, :host_name => host.name, :facts => facts, :deleted => [] do |payload| # deletes all facts using a single SQL query (with inner query)
payload[:count] = counters[:deleted] = FactValue.joins(:fact_name).where(:host => host, 'fact_names.type' => fact_name_class).where.not('fact_names.name' => facts.keys).delete_all
end
end

To:
@def delete_removed_facts
to_delete = host.fact_values.eager_load(:fact_name).where("fact_names.type = '#{fact_name_class}' AND fact_names.name NOT IN (?)", facts.keys) # N+1 DELETE SQL, but this would allow us to use callbacks (e.g. auditing) when deleting.
deleted = to_delete.destroy_all
counters[:deleted] = deleted.size
@db_facts = nil
logger.debug("Merging facts for '#{host}': deleted #{counters[:deleted]} facts")
end

After revert this change i see high performance from DB side.
Any option that someone else will test it on MySQL or improve delete_removed_facts?

Thanks!
Yakir.


Related issues 1 (0 open1 closed)

Related to Foreman - Bug #9016: Fact import code consumes lot of memoryClosedLukas Zapletal01/18/2015Actions
Actions

Also available in: Atom PDF