during my research on reports performance, I came with two small patches which can both dramatically improve performance of report upload and expiration. While these patches are still to be reviewed, I would like to hear from experience from production systems.
Foreman renders reports back after creation, however most (if not all) clients (puppet, ansible, openscap) do only check for the HTTP code. The rendering of huge JSON is slow (the culprit is likely ActiveRecord), but I am proposing to drop the biggest offender from the response - all the log lines. To test this, apply this small template change and restart the server to see if report processing is faster: https://github.com/theforeman/foreman/pull/7987/files
Reports are broken up into lines (messages, sources) and stored as individual records. While we would like to rewrite this completely, it is possible to do a small change in postgres to boost performance quite a bit. Dropping btree indices on
sources.digestcolumns replacing them with hash indices makes everything much faster - specifically on instances with many reports:
# su postgres -c "psql foreman" foreman=# drop index index_messages_on_digest; DROP INDEX foreman=# create index index_messages_on_digest on messages using hash (digest); CREATE INDEX foreman=# drop index index_sources_on_digest; DROP INDEX foreman=# create index index_sources_on_digest on sources using hash (digest); CREATE INDEX
Index creation was pretty fast on my testing VM: 18 million records per minute. Also all the changes are safe - RABL change will be overwritten during upgrade (no matter how we end up fixing this issue) and if we choose to convert indices this will happen during database migration as well (dropping existing and recreating them once again).
Please get back to us with numbers if you apply those changes:
- How fast was report upload before and after the RABL patch (1)?
- How fast was report upload before and after the hash index (2)?
- How big were sources/messages tables before and after the indices creation (2)?
- How quick was the index creation and how many records were in the two tables (2)?
- Any observations during report expiration rake (cron) job?