Re: [Nagios-users] Ndoutils MySQL performance problem?
2009/3/3 Livio Zanol Puppim : > Hello everyone! > > I'm having huge troubles with Mysql using 100% of CPU at peculiar times. I > don't know when this problem starts and I don't know the reason, but when it > happends I kill nagios, ndoutils and Mysql, and try to restart Mysql, > ndoutils and Nagios at this order. When I start Nagios, the CPU usage of > Mysql goes to 100% and everything just freezes with the initials > INSERT/UPDATES made by ndoutils. > > Has anyone had the same problem? I've changed my.cnf to hold slow-queries > logging, an have seen at intervals of 1 minute a query runned by ndo that > tries to delete service_checks results. It takes about 4 seconds too run. Is > this expected? Can anyone help me? > > Running Nagios 3.0.3, Debian etch, NDO2DB 1.4b7 and mysql Ver 14.12 Distrib > 5.0.45. > > > Slow-Queries log: > # Time: 090303 14:29:25 > # u...@host: ndoutils[ndoutils] @ localhost [] > # Query_time: 4 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 > DELETE FROM nagios_servicechecks WHERE instance_id='1' AND > start_time # Time: 090303 14:30:26 > # u...@host: ndoutils[ndoutils] @ localhost [] > # Query_time: 4 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 > DELETE FROM nagios_servicechecks WHERE instance_id='1' AND > start_time > Thank you. > -- > []'s > > Lívio Zanol Puppim > Try creating a index for that table - create index nagios_servicechecks_idx1 on nagios_servicechecks (start_time, instance_id) - I'm not sure if the order would change the result though (start_time, instance_id) OR (instance_id, start_time) Regards, -- Ciro Iriarte http://cyruspy.wordpress.com -- -- Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H ___ Nagios-users mailing list Nagios-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/nagios-users ::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. ::: Messages without supporting info will risk being sent to /dev/null
Re: [Nagios-users] Ndoutils MySQL performance problem?
Livio Zanol Puppim wrote: > I'm having huge troubles with Mysql using 100% of CPU at peculiar times. > I don't know when this problem starts and I don't know the reason, but > when it happends I kill nagios, ndoutils and Mysql, and try to restart > Mysql, ndoutils and Nagios at this order. When I start Nagios, the CPU > usage of Mysql goes to 100% and everything just freezes with the > initials INSERT/UPDATES made by ndoutils. You can read this thread too: [Nagios-users] NDOutils and Nagios startup speed http://www.mail-archive.com/nagios-users@lists.sourceforge.net/msg23943.html There's a list of indexes to create at the end. -- Mathieu -- Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H ___ Nagios-users mailing list Nagios-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/nagios-users ::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. ::: Messages without supporting info will risk being sent to /dev/null
Re: [Nagios-users] Ndoutils MySQL performance problem?
2009/3/3 Livio Zanol Puppim : > > > > I'm having huge troubles with Mysql using 100% of CPU at peculiar times. I > > don't know when this problem starts and I don't know the reason, but when it > > happends I kill nagios, ndoutils and Mysql, and try to restart Mysql, > > ndoutils and Nagios at this order. When I start Nagios, the CPU usage of > > Mysql goes to 100% and everything just freezes with the initials > > INSERT/UPDATES made by ndoutils. > > > > Has anyone had the same problem? I've changed my.cnf to hold slow-queries > > logging, an have seen at intervals of 1 minute a query runned by ndo that I'm no expert at MySQL performance, but we found a couple of things that helped speed up NDO significantly: -We are using a my.cnf based on the "my-innodb-heavy-4G.cnf" example that comes with MySQL. -The innodb_flush_log_at_trx_commit setting in that file is "2" (instead of 1..the difference in performance is huge, and the trade off in data integrity is negligible for us). If your process gets heavy over time, you may have to reduce things in ndo2db.cfg like max_servicechecks_age (to keep the size of these historical tables manageable). Marc Gridshield, S.A. -- Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H ___ Nagios-users mailing list Nagios-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/nagios-users ::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. ::: Messages without supporting info will risk being sent to /dev/null
Re: [Nagios-users] Ndoutils MySQL performance problem?
On Mar 4, 2009, at 5:19 PM, Marc DeTrano wrote: > 2009/3/3 Livio Zanol Puppim : > > -We are using a my.cnf based on the "my-innodb-heavy-4G.cnf" example > that comes with MySQL. > -The innodb_flush_log_at_trx_commit setting in that file is > "2" (instead > of 1..the difference in performance is huge, and the trade off in data > integrity is negligible for us) Thanks for this info... > If your process gets heavy over time, you may have to reduce things in > ndo2db.cfg like max_servicechecks_age (to keep the size of these > historical tables manageable). I find that these are my two largest. They grow *huge* I had to drop them and recreate just last week as they were ~20G and ~35G respectively... 1.5Gnagios_host_contactgroups.ibd 3.2Gnagios_service_contactgroups.ibd They're not particularly relevant to me but I don't see that there is that kind of granularity to exclude them... -- Marc -- Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H ___ Nagios-users mailing list Nagios-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/nagios-users ::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. ::: Messages without supporting info will risk being sent to /dev/null
Re: [Nagios-users] Ndoutils MySQL performance problem?
I've tweaked the ndo configuration... It doesn't saves logs at database, and I'm still having this problem. I'll give a try at everybody suggestion tomorow. Thanks very much! :) 2009/3/4 Marc Powell > > On Mar 4, 2009, at 5:19 PM, Marc DeTrano wrote: > > > 2009/3/3 Livio Zanol Puppim : > > > > > -We are using a my.cnf based on the "my-innodb-heavy-4G.cnf" example > > that comes with MySQL. > > -The innodb_flush_log_at_trx_commit setting in that file is > > "2" (instead > > of 1..the difference in performance is huge, and the trade off in data > > integrity is negligible for us) > > Thanks for this info... > > > If your process gets heavy over time, you may have to reduce things in > > ndo2db.cfg like max_servicechecks_age (to keep the size of these > > historical tables manageable). > > I find that these are my two largest. They grow *huge* I had to drop > them and recreate just last week as they were ~20G and ~35G > respectively... > > 1.5Gnagios_host_contactgroups.ibd > 3.2Gnagios_service_contactgroups.ibd > > They're not particularly relevant to me but I don't see that there is > that kind of granularity to exclude them... > > -- > Marc > > > > -- > Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, > CA > -OSBC tackles the biggest issue in open source: Open Sourcing the > Enterprise > -Strategies to boost innovation and cut costs with open source > participation > -Receive a $600 discount off the registration fee with the source code: > SFAD > http://p.sf.net/sfu/XcvMzF8H > ___ > Nagios-users mailing list > Nagios-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/nagios-users > ::: Please include Nagios version, plugin version (-v) and OS when > reporting any issue. > ::: Messages without supporting info will risk being sent to /dev/null > -- []'s Lívio Zanol Puppim -- Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H___ Nagios-users mailing list Nagios-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/nagios-users ::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. ::: Messages without supporting info will risk being sent to /dev/null
Re: [Nagios-users] Ndoutils MySQL performance problem?
2009/3/4 Marc DeTrano : > 2009/3/3 Livio Zanol Puppim : > I'm no expert at MySQL performance, but we found a couple of things that > helped speed up NDO significantly: > > -We are using a my.cnf based on the "my-innodb-heavy-4G.cnf" example > that comes with MySQL. > -The innodb_flush_log_at_trx_commit setting in that file is "2" (instead > of 1..the difference in performance is huge, and the trade off in data > integrity is negligible for us). > > If your process gets heavy over time, you may have to reduce things in > ndo2db.cfg like max_servicechecks_age (to keep the size of these > historical tables manageable). > > Marc > Gridshield, S.A. Wow! That made a huge difference! I simply added a file "nagios.cnf" under /etc/mysql/conf.d like so: [mysqld] innodb_flush_log_at_trx_commit = 2 and it reduced my Nagios startup time from more than 3 minutes to only 30 seconds! I already had quite a few of the data_processing_options disabled in ndomod.cfg and had reduced all of the max_*_age parameters in ndo2db.cfg to 24 hours (before those my startup time was more than 5 minutes). Many thanks indeed! Cheers, Jim -- Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H ___ Nagios-users mailing list Nagios-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/nagios-users ::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. ::: Messages without supporting info will risk being sent to /dev/null
