Reports expire failed

Suresh_P · March 6, 2015, 10:04am

Hi,

I'm trying to delete old reports from foreman. But i'm getting following
error. Any clue please.

foreman-rake reports:expire days=30
rake aborted!
Mysql2::Error: The total number of locks exceeds the lock table size:
DELETE FROM logs WHERE logs.report_id IN (SELECT reports.id FROM
reports WHERE (reports.created_at < '2015-02-04 10:00:52'))

Tasks: TOP => reports:expire
(See full trace by running task with --trace)

Regards,
Suresh.P

Suresh_P · March 6, 2015, 11:39am

I'm running foreman in 4core CPU with 16G RAM, and around 750 nodes
reporting to this foreman.
I have changed the innodb_buffer_pool_size to 2000M and restarted mysql.
After that i didn't get the error but foreman-rake running for long time.

Regards,
Suresh.

···

On Friday, 6 March 2015 15:34:56 UTC+5:30, Suresh P wrote: > > Hi, > > I'm trying to delete old reports from foreman. But i'm getting following > error. Any clue please. > > foreman-rake reports:expire days=30 > rake aborted! > Mysql2::Error: The total number of locks exceeds the lock table size: > DELETE FROM `logs` WHERE `logs`.`report_id` IN (SELECT `reports`.`id` FROM > `reports` WHERE (reports.created_at < '2015-02-04 10:00:52')) > > Tasks: TOP => reports:expire > (See full trace by running task with --trace) > > > Regards, > Suresh.P >

Dominic_Cleal · March 9, 2015, 4:23pm

Foreman 1.7.3 has a fix in bug #8565 that should significantly reduce
the size of the query during the reports expiry, I'd recommend trying to
upgrade it or apply the patch from that ticket.

···

-- Dominic Cleal Red Hat Engineering

On 06/03/15 11:39, Suresh P wrote:

I’m running foreman in 4core CPU with 16G RAM, and around 750 nodes
reporting to this foreman.
I have changed the innodb_buffer_pool_size to 2000M and restarted
mysql. After that i didn’t get the error but foreman-rake running for
long time.

Regards,
Suresh.

On Friday, 6 March 2015 15:34:56 UTC+5:30, Suresh P wrote:
Hi,

I'm trying to delete old reports from foreman.   But i'm getting
following error.  Any clue please.

    foreman-rake reports:expire days=30
    rake aborted!
    Mysql2::Error: The total number of locks exceeds the lock table
    size: DELETE FROM `logs` WHERE `logs`.`report_id` IN (SELECT
    `reports`.`id` FROM `reports`  WHERE (reports.created_at <
    '2015-02-04 10:00:52'))

    Tasks: TOP => reports:expire
    (See full trace by running task with --trace)


Regards,
Suresh.P
–
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
mailto:foreman-users+unsubscribe@googlegroups.com.
To post to this group, send email to foreman-users@googlegroups.com
mailto: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.

Suresh_P · March 9, 2015, 4:41pm

Thanks Dominic, i'll try and get back to you.
One more question, As of now we are managing 2500+ nodes in foreman.
Servers keep on increasing may be in next month we may crossed 3000+
servers. How to stabilize the foreman to manage higher no. of servers.

Hardware details:
We have 3 puppetmaster(8 core cpu, 32 GB RAM) in load balancing.
1 foreman ( 8 core cpu, 32 GB RAM)

Regards,
Suresh.P

···

On Monday, 9 March 2015 21:53:40 UTC+5:30, Dominic Cleal wrote: > > Foreman 1.7.3 has a fix in bug #8565 that should significantly reduce > the size of the query during the reports expiry, I'd recommend trying to > upgrade it or apply the patch from that ticket. > > -- > Dominic Cleal > Red Hat Engineering > > On 06/03/15 11:39, Suresh P wrote: > > I'm running foreman in 4core CPU with 16G RAM, and around 750 nodes > > reporting to this foreman. > > I have changed the innodb_buffer_pool_size to 2000M and restarted > > mysql. After that i didn't get the error but foreman-rake running for > > long time. > > > > Regards, > > Suresh. > > > > On Friday, 6 March 2015 15:34:56 UTC+5:30, Suresh P wrote: > > > > Hi, > > > > I'm trying to delete old reports from foreman. But i'm getting > > following error. Any clue please. > > > > foreman-rake reports:expire days=30 > > rake aborted! > > Mysql2::Error: The total number of locks exceeds the lock table > > size: DELETE FROM `logs` WHERE `logs`.`report_id` IN (SELECT > > `reports`.`id` FROM `reports` WHERE (reports.created_at < > > '2015-02-04 10:00:52')) > > > > Tasks: TOP => reports:expire > > (See full trace by running task with --trace) > > > > > > Regards, > > Suresh.P > > > > -- > > 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 > > <mailto:foreman-users+unsubscribe@googlegroups.com >. > > To post to this group, send email to forema...@googlegroups.com > > > <mailto:forema...@googlegroups.com >. > > Visit this group at http://groups.google.com/group/foreman-users. > > For more options, visit https://groups.google.com/d/optout. > >

Dominic_Cleal · March 9, 2015, 5:00pm

There are a few things you can do I guess:

Ensure you don't run a Puppet master on your Foreman server. You
could use it as a CA, as the traffic will be low, but make sure you're
not compiling catalogs - use your other servers.
Move the database to another server with better disk I/O and memory
for caching. Keep Foreman running on something with more cores and
memory so it can spin up more processes.
Look at "passenger-status" and the number of Apache workers (apache
status page?) to ensure it's not queueing unnecessarily, or running too
many workers. The Apache and Passenger pooling defaults aren't great,
do look at tuning them for performance.
If your Foreman host is overloaded (CPU wise) you can set up a second
server pointing to the same database and load balance. I recommend
installing the memcached plugin so they share sessions and a cache,
which may prevent problems (https://github.com/theforeman/foreman_memcache).

There are threads in the archives from people doing one or more of these
things, so do have a search and you should find some useful advice from
people who've done it before.

Cheers,
Dominic

···

On 09/03/15 16:41, Suresh P wrote: > Thanks Dominic, i'll try and get back to you. > One more question, As of now we are managing 2500+ nodes in foreman. > Servers keep on increasing may be in next month we may crossed 3000+ > servers. How to stabilize the foreman to manage higher no. of servers. > > Hardware details: > We have 3 puppetmaster(8 core cpu, 32 GB RAM) in load balancing. > 1 foreman ( 8 core cpu, 32 GB RAM) > > Regards, > Suresh.P > > > On Monday, 9 March 2015 21:53:40 UTC+5:30, Dominic Cleal wrote: > > Foreman 1.7.3 has a fix in bug #8565 that should significantly reduce > the size of the query during the reports expiry, I'd recommend > trying to > upgrade it or apply the patch from that ticket. > > -- > Dominic Cleal > Red Hat Engineering > > On 06/03/15 11:39, Suresh P wrote: > > I'm running foreman in 4core CPU with 16G RAM, and around 750 nodes > > reporting to this foreman. > > I have changed the innodb_buffer_pool_size to 2000M and restarted > > mysql. After that i didn't get the error but foreman-rake > running for > > long time. > > > > Regards, > > Suresh. > > > > On Friday, 6 March 2015 15:34:56 UTC+5:30, Suresh P wrote: > > > > Hi, > > > > I'm trying to delete old reports from foreman. But i'm getting > > following error. Any clue please. > > > > foreman-rake reports:expire days=30 > > rake aborted! > > Mysql2::Error: The total number of locks exceeds the lock > table > > size: DELETE FROM `logs` WHERE `logs`.`report_id` IN (SELECT > > `reports`.`id` FROM `reports` WHERE (reports.created_at < > > '2015-02-04 10:00:52')) > > > > Tasks: TOP => reports:expire > > (See full trace by running task with --trace) > > > > > > Regards, > > Suresh.P > > > > -- > > 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 > > <mailto:foreman-users+unsubscribe@googlegroups.com >. > > To post to this group, send email to forema...@googlegroups.com > > > <mailto:forema...@googlegroups.com >. > > Visit this group at http://groups.google.com/group/foreman-users > . > > For more options, visit https://groups.google.com/d/optout > . > > -- > 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.

–
Dominic Cleal
Red Hat Engineering

Suresh_P · March 10, 2015, 2:45pm

Hi Dominic,

Thank you for your detailed and very useful inputs. Will also check the
user's advice and then implement for my setup.

Thanks,
Suresh .P

···

On Monday, 9 March 2015 22:30:23 UTC+5:30, Dominic Cleal wrote: > > There are a few things you can do I guess: > > 1. Ensure you don't run a Puppet master on your Foreman server. You > could use it as a CA, as the traffic will be low, but make sure you're > not compiling catalogs - use your other servers. > > 2. Move the database to another server with better disk I/O and memory > for caching. Keep Foreman running on something with more cores and > memory so it can spin up more processes. > > 3. Look at "passenger-status" and the number of Apache workers (apache > status page?) to ensure it's not queueing unnecessarily, or running too > many workers. The Apache and Passenger pooling defaults aren't great, > do look at tuning them for performance. > > 4. If your Foreman host is overloaded (CPU wise) you can set up a second > server pointing to the same database and load balance. I recommend > installing the memcached plugin so they share sessions and a cache, > which may prevent problems (https://github.com/theforeman/foreman_memcache). > > > There are threads in the archives from people doing one or more of these > things, so do have a search and you should find some useful advice from > people who've done it before. > > Cheers, > Dominic > > On 09/03/15 16:41, Suresh P wrote: > > Thanks Dominic, i'll try and get back to you. > > One more question, As of now we are managing 2500+ nodes in foreman. > > Servers keep on increasing may be in next month we may crossed 3000+ > > servers. How to stabilize the foreman to manage higher no. of servers. > > > > Hardware details: > > We have 3 puppetmaster(8 core cpu, 32 GB RAM) in load balancing. > > 1 foreman ( 8 core cpu, 32 GB RAM) > > > > Regards, > > Suresh.P > > > > > > On Monday, 9 March 2015 21:53:40 UTC+5:30, Dominic Cleal wrote: > > > > Foreman 1.7.3 has a fix in bug #8565 that should significantly > reduce > > the size of the query during the reports expiry, I'd recommend > > trying to > > upgrade it or apply the patch from that ticket. > > > > -- > > Dominic Cleal > > Red Hat Engineering > > > > On 06/03/15 11:39, Suresh P wrote: > > > I'm running foreman in 4core CPU with 16G RAM, and around 750 > nodes > > > reporting to this foreman. > > > I have changed the innodb_buffer_pool_size to 2000M and restarted > > > mysql. After that i didn't get the error but foreman-rake > > running for > > > long time. > > > > > > Regards, > > > Suresh. > > > > > > On Friday, 6 March 2015 15:34:56 UTC+5:30, Suresh P wrote: > > > > > > Hi, > > > > > > I'm trying to delete old reports from foreman. But i'm > getting > > > following error. Any clue please. > > > > > > foreman-rake reports:expire days=30 > > > rake aborted! > > > Mysql2::Error: The total number of locks exceeds the lock > > table > > > size: DELETE FROM `logs` WHERE `logs`.`report_id` IN > (SELECT > > > `reports`.`id` FROM `reports` WHERE (reports.created_at < > > > '2015-02-04 10:00:52')) > > > > > > Tasks: TOP => reports:expire > > > (See full trace by running task with --trace) > > > > > > > > > Regards, > > > Suresh.P > > > > > > -- > > > 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 > > > <mailto:foreman-users+unsubscribe@googlegroups.com > >. > > > To post to this group, send email to forema...@googlegroups.com > > > > > <mailto:forema...@googlegroups.com >. > > > Visit this group at http://groups.google.com/group/foreman-users > > . > > > For more options, visit https://groups.google.com/d/optout > > . > > > > -- > > 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 > > <mailto:foreman-users+unsubscribe@googlegroups.com >. > > To post to this group, send email to forema...@googlegroups.com > > > <mailto:forema...@googlegroups.com >. > > Visit this group at http://groups.google.com/group/foreman-users. > > For more options, visit https://groups.google.com/d/optout. > > > -- > Dominic Cleal > Red Hat Engineering >