Db:migrate very slow upgrading from 1.8 to 1.9

Support
1

I'm attempting to upgrade from Foreman from 1.8 to 1.9. After doing a yum
upgrade ruby* foreman*, the packages install, but the yum command
freezes while running the postscripts, during which time, I'm assuming the
db:migrate rake job is running. MySQL usage goes to 100%. The job never
completes (I've let it go for 4 hours), and yum never returns to a prompt.

I ran into some database issues last month when trying to use the rake jobs
to clean the Foreman reports table. There were a number of tables I was
able to truncate without (noticeably) breaking anything. So I went back
and truncated the following tables:

logs
sessions
messages
reports
hpcbackup

After, I reran the rake job, but it didn't seem to help. So I went back
and looked at other tables with large numbers of records (excluding hosts,
which is at 8000+). Here's what I found:

fact_values: 916546
param_values: 6176300
trend_counters: 5601070
resource_tags: 7757645
resources: 1472945

Is it safe to truncate any of these tables? A "show processlist" in MySQL
seems to show a lot of time being spent querying the trend_counters table
(there's also a long running process that shows its command as "NULL").

Thanks.

2

> I'm attempting to upgrade from Foreman from 1.8 to 1.9. After doing a yum
> upgrade ruby* foreman*, the packages install, but the yum command
> freezes while running the postscripts, during which time, I'm assuming the
> db:migrate rake job is running. MySQL usage goes to 100%. The job never
> completes (I've let it go for 4 hours), and yum never returns to a prompt.
>

I would assume its trends, between 1.8 and 1.9 the way trends are stored in
the database changed dramatic and offer a huge performance benefit.

>
> I ran into some database issues last month when trying to use the rake
> jobs to clean the Foreman reports table. There were a number of tables I
> was able to truncate without (noticeably) breaking anything. So I went
> back and truncated the following tables:
>
> logs
> sessions
> messages
> reports
> hpcbackup
>
> After, I reran the rake job, but it didn't seem to help. So I went back
> and looked at other tables with large numbers of records (excluding hosts,
> which is at 8000+). Here's what I found:
>
> fact_values: 916546
> param_values: 6176300
> trend_counters: 5601070
> resource_tags: 7757645
> resources: 1472945
>
> Is it safe to truncate any of these tables? A "show processlist" in MySQL
> seems to show a lot of time being spent querying the trend_counters table
> (there's also a long running process that shows its command as "NULL").
>

You can delete the trends if you don't need them… if you run the migration
manually (e.g. foreman-rake db:migrate) you would see more information
about the migration process etc.

Ohad

··· On Wed, Dec 2, 2015 at 7:53 AM, Kyle Flavin wrote:

Thanks.

–
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 foreman-users+unsubscribe@googlegroups.com.
To post to this group, send email to foreman-users@googlegroups.com.
Visit this group at http://groups.google.com/group/foreman-users.
For more options, visit https://groups.google.com/d/optout.

3

Thanks Ohad, that's what I ended up doing, and it seems to have worked. I
truncated the trends_counter table, and ran the rake jobs manually. The
job went from running for over 12 hours without completing, to finishing in
11 minutes.

··· On Wednesday, December 2, 2015 at 11:54:20 PM UTC-8, ohad wrote: > > > > On Wed, Dec 2, 2015 at 7:53 AM, Kyle Flavin > wrote: > >> I'm attempting to upgrade from Foreman from 1.8 to 1.9. After doing a *yum >> upgrade ruby\* foreman\**, the packages install, but the yum command >> freezes while running the postscripts, during which time, I'm assuming the >> db:migrate rake job is running. MySQL usage goes to 100%. The job never >> completes (I've let it go for 4 hours), and yum never returns to a prompt. >> > > I would assume its trends, between 1.8 and 1.9 the way trends are stored > in the database changed dramatic and offer a huge performance benefit. > > >> >> I ran into some database issues last month when trying to use the rake >> jobs to clean the Foreman reports table. There were a number of tables I >> was able to truncate without (noticeably) breaking anything. So I went >> back and truncated the following tables: >> >> logs >> sessions >> messages >> reports >> hpcbackup >> >> After, I reran the rake job, but it didn't seem to help. So I went back >> and looked at other tables with large numbers of records (excluding hosts, >> which is at 8000+). Here's what I found: >> >> fact_values: 916546 >> param_values: 6176300 >> trend_counters: 5601070 >> resource_tags: 7757645 >> resources: 1472945 >> >> Is it safe to truncate any of these tables? A "show processlist" in >> MySQL seems to show a lot of time being spent querying the trend_counters >> table (there's also a long running process that shows its command as >> "NULL"). >> > > You can delete the trends if you don't need them.. if you run the > migration manually (e.g. foreman-rake db:migrate) you would see more > information about the migration process etc. > > Ohad > >> >> Thanks. >> >> -- >> 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 foreman-user...@googlegroups.com . >> To post to this group, send email to forema...@googlegroups.com >> . >> Visit this group at http://groups.google.com/group/foreman-users. >> For more options, visit https://groups.google.com/d/optout. >> > >