I currently need approximately 12 hours per night just to cleanup logs, sources, reports, etc. tables.
Until the reporting rework happens in 1.25 or later, is there any impact to just “truncating” the following tables:
logs
messages
reports
sources
I’m not worried about losing the data - but more worried about some foreign key or other constraint (admittedly I’m not super familiar with Databases…)
I’m looking for a “quick” way to restore DB performance. Right now if we get a few bad puppet code pushes in a single day our database “balloons” by like 20-30GB - no longer fits in Memory, and starts to perform like shit, leading to more errors, more logs, and a fun and lengthy cleanup process. I then have 10-12+ hours at least deleting/cleaning the above tables, via rake tasks, and then “optimizing them” (this may be mysql specific vs auto vacuum in postgres) to shrink them back down to fit in memory and run good again.
Current sizes (we had a few bad code pushes today cause lots of “errors” to stream in)
16G fact_names.ibd
2.2G fact_values.ibd 34G logs.ibd 1.6G messages.MYD 2.0G reports.ibd 3.6G sources.MYD
2.5G taxable_taxonomies.ibd
Foreman 1.19.1
MariaDB 10.2
Tagging @lzap since he always has the answers on these things - but if anyone else knows either…
Lucky you, we have a patch that is pending merge which we want to put into 1.25 and possibly backport it to Satellite versions which should vastly improve the expiration process until we come with the redesign. Before you try it:
Can you count records in these tables and report back here so I could correlate how much data is that for MySQL. My wild guess for logs table: 300k records?
Here is the patch, it’s small:
However there is one problem - as you can see it was only tested on PostgreSQL, can you modify it and do the same for MySQL? If you provide the correct (and tested) SQL statement I’d add it to the patch. The idea is simple - find the last database ID and delete all records with ID less than 100_000 (configurable option).
Heh, 300k. I wish. Last i checked we topped 250 million rows in the logs table. that’s using a custom cleanup job to purge records older than 5 days. We are currently on 1.19.1 and using the “date” value stored in the table, which, based on the reading of your PR - is removed sometime between 1.19 and 1.23 - so ill probably need to adopt this either way!
Once i get foreman online again (currently all puppet runs time-out waiting for the database queries which take like 20-30 seconds each…) I am totally going to try this out!
Since you mentioned you are still on 1.19 and using MySQL, I just wanted to make sure that you are aware that in Foreman 2.0 (formerly known as 1.25) support for MySQL is going to be removed - so you might want to start planning ahead for the migration. You can find further information regarding this change at Foreman :: Dropping support for MySQL.
Interesting, looks like MySQL stores the data more efficiently or there was something wrong with reports from my customers about table counts and sizes. I wonder - why “sources” is MyISAM table and all the others are InnoDB? I would be curious how much faster MyISAM is. Well, we will never find out I guess since we’ve dropped MySQL in 1.24.
