ndoutils Query optimisation

[Simon Finch]

Hi Alan,
 
Up to yesterday I had a similar problem with our NDO db on the same spec box as yours.
 
Are your tables primarily type innodb like mine ?
I am asking as my NDO db was created by Centreon's install script and not the normal way, if so then the following may help.
Your initial post showed no setting for innodb_buffer_pool_size if you are using lots of innodb tables then this needs to be as big as you can afford.
Second is innodb_flush_log_at_trx_commit which defaults to 1, which is safe, but slow, it commits after every transaction.
 
I have 9 Nagios servers feeding data from 2500 hosts into 1 NDO db, when I started Mysql was reported by top as using 300 - 350% of cpu and the whole system was virtually unusable, now cpu usage is 10 - 50% with occasional peaks of 90% and all is good.
 
These are the settings I am using:
 
key_buffer_size=64M
thread_cache_size=32
innodb_buffer_pool_size=512M
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=32M
read_buffer_size=256k
tmp_table_size=64M
join_buffer_size=8M
query_cache_size=32M
table_cache=1024
I am still fiddling with settings, but these seem good for now. I am not a dba, only a sysadmin who had to make it work. Hopefully I can help others with similar problems.
 
Thanks and regards,
 
Simon Finch
ESM Team Leader
St George Bank
Australia
 
 

>>> Alan Cooper <ajcooper80 at googlemail.com> 26/08/2008 4:39 am >>>
Mikael Fridh wrote:
> On Wed, Aug 20, 2008 at 8:46 PM, Alan Cooper <ajcooper80 at googlemail.com> wrote:
>   
>> Thanks for the replies,
>>
>> Unfortunately, all the tables are already indexed so I'm back to the
>> underlying problem is that there are so many joins on big tables so
>> MySQL has to copy to temp tables each time - I'd really like to
>> eliminate the need for this if possible.
>>     
>
> Show the EXPLAIN output as well as SHOW CREATE TABLE for all tables in
> your query.
>
>   

Hi Mikael,

Please find the details requested attached.

Thanks again for your help.

Regards,
Alan


*** This is an automatically appended message from St. George ITP.  If this email is SPAM please register it with WebSense MailControl by clicking this link https://www.mailcontrol.com/sr/zM1O!OPwutLTndxI!oX7UmGwwysDArWcDrzRWHDQJ5mU7sNPLDw9NuS3S+!tH0c3S!CJwybpcpidye7!a4hOmw== . ***

**********************************************************************
*****   IMPORTANT INFORMATION    *****
This document should be read only by those persons to whom it is 
addressed and its content is not intended for use by any other 
persons.  If you have received this message in error, please notify 
us immediately.  Please also destroy and delete the message from 
your computer.  Any unauthorised form of reproduction of this message 
is strictly prohibited.

St George Bank Limited AFSL 240997, Advance Asset Management Limited 
AFSL 240902,  St George Life Limited AFSL 240900, ASGARD Capital Management Limited 
AFSL 240695 and Securitor Financial Group Limited AFSL 240687 is not liable for 
the proper and complete transmission of the information contained in 
this communication, nor for any delay in its receipt.
**********************************************************************

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-lists.org/archive/users/attachments/20080826/b7c901d1/attachment.html>
-------------- next part --------------
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
-------------- next part --------------
_______________________________________________
Nagios-users mailing list
Nagios-users at 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