Project

General

Profile

Bug #22068

Audits table queries cause high load in large deployments

Added by Amy Walker almost 3 years ago. Updated over 2 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Database
Target version:
Difficulty:
Triaged:
Bugzilla link:
Fixed in Releases:
Found in Releases:

Description

Regularly in our foreman + puppet environment, we see this type of query hitting mysql:

SELECT MAX(`audits`.`version`) AS max_id FROM `audits`  WHERE `audits`.`auditable_id` = 7555 AND `audits`.`auditable_type` = 'LookupKey';

With the default indexes that ship with foreman, these queries can take upwards of 13 seconds to process, and under periods of heavy load (i.e. when foreman is cleaning up its reports/logs table), this can take even longer:

+--------+
| max_id |
+--------+
| 117292 |
+--------+
1 row in set (13.44 sec)

Our environment has quite a few audits from 2064 hosts:

select count(*) from audits;
+----------+
| count(*) |
+----------+
| 29872974 |
+----------+
1 row in set (13.18 sec)

After analyzing the query, I realized that the index that ships by default with foreman is not optimized for the query that is being performed; for a max() where a = x and b = y, you'd expect a perfect index to be (a, b, max_column). So I added that to our foreman db and saw these results:

+--------+
| max_id |
+--------+
| 117292 |
+--------+
1 row in set (0.00 sec)

And as can be seen from the query analysis, it no longer has to hit the table at all to get the single result this query delivers:

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.02 sec)

Fixing this should just be a case of updating the index to be:

`auditable_index` (`auditable_id`,`auditable_type`,`version`)

Associated revisions

Revision 565621cf (diff)
Added by Amy Walker almost 3 years ago

Fixes #22068 - Add version column to auditable_index

Audits table queries cause high load in large deployments.

Foreman regularly does queries like this:
SELECT MAX AS max_id FROM `audits` WHERE `audits`.`auditable_id` = 7555 AND `audits`.`auditable_type` = 'LookupKey';

On large deployments with huge audits tables, this looks like this and results in everything taking a very long time:
------ | max_id |
------ | 117292 |
------
1 row in set (13.44 sec)

A simple solution to this, is to include the version in the compound index, which stops mysql from having to go to the table at all, and turns the same query into this:

------ | max_id |
------ | 117292 |
------
1 row in set (0.00 sec)

History

#1 Updated by Amy Walker almost 3 years ago

  • Subject changed from Audits table queries cause high load and lag in large deployments to Audits table queries cause high load in large deployments

#2 Updated by Amy Walker almost 3 years ago

  • Pull request https://github.com/theforeman/foreman/pull/5111 added

#3 Updated by Tomer Brisker almost 3 years ago

  • Legacy Backlogs Release (now unused) set to 296
  • Assignee set to Amy Walker
  • Status changed from New to Closed

#4 Updated by Tomer Brisker almost 3 years ago

  • Bugzilla link set to 1528988

Also available in: Atom PDF