<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=utf-8">
<META content="MSHTML 6.00.6000.16705" name=GENERATOR></HEAD>
<BODY style="MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>Hi Alan,</DIV>
<DIV> </DIV>
<DIV>Up to yesterday I had a similar problem with our NDO db on the same spec box as yours.</DIV>
<DIV> </DIV>
<DIV>Are your tables primarily type innodb like mine ?</DIV>
<DIV>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.<BR></DIV>
<DIV>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.</DIV>
<DIV>Second is innodb_flush_log_at_trx_commit which defaults to 1, which is safe, but slow, it commits after every transaction.</DIV>
<DIV> </DIV>
<DIV>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.</DIV>
<DIV> </DIV>
<DIV>These are the settings I am using:</DIV>
<DIV> </DIV>
<DIV>key_buffer_size=64M<BR>thread_cache_size=32<BR>innodb_buffer_pool_size=512M<BR>innodb_flush_log_at_trx_commit=0<BR>innodb_log_file_size=32M<BR>read_buffer_size=256k<BR>tmp_table_size=64M<BR>join_buffer_size=8M<BR>query_cache_size=32M<BR>table_cache=1024<BR></DIV>
<DIV>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.</DIV>
<DIV> </DIV>
<DIV>Thanks and regards,</DIV>
<DIV> </DIV>
<DIV>Simon Finch</DIV>
<DIV>ESM Team Leader</DIV>
<DIV>St George Bank</DIV>
<DIV>Australia</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><BR>>>> Alan Cooper <ajcooper80@googlemail.com> 26/08/2008 4:39 am >>><BR>Mikael Fridh wrote:<BR>> On Wed, Aug 20, 2008 at 8:46 PM, Alan Cooper <ajcooper80@googlemail.com> wrote:<BR>> <BR>>> Thanks for the replies,<BR>>><BR>>> Unfortunately, all the tables are already indexed so I'm back to the<BR>>> underlying problem is that there are so many joins on big tables so<BR>>> MySQL has to copy to temp tables each time - I'd really like to<BR>>> eliminate the need for this if possible.<BR>>> <BR>><BR>> Show the EXPLAIN output as well as SHOW CREATE TABLE for all tables in<BR>> your query.<BR>><BR>> <BR><BR>Hi Mikael,<BR><BR>Please find the details requested attached.<BR><BR>Thanks again for your help.<BR><BR>Regards,<BR>Alan<BR><BR><BR>*** 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 <A href="https://www.mailcontrol.com/sr/zM1O!OPwutLTndxI!oX7UmGwwysDArWcDrzRWHDQJ5mU7sNPLDw9NuS3S+!tH0c3S!CJwybpcpidye7!a4hOmw==">https://www.mailcontrol.com/sr/zM1O!OPwutLTndxI!oX7UmGwwysDArWcDrzRWHDQJ5mU7sNPLDw9NuS3S+!tH0c3S!CJwybpcpidye7!a4hOmw==</A> . ***<BR></DIV>
<p><span style="font-family:'Arial';font-size:8pt;">**********************************************************************</span></p>
<p><span style="font-family:'Arial';font-size:8pt;">***** IMPORTANT INFORMATION *****</span></p>
<p><span style="font-family:'Arial';font-size:8pt;">This document should be read only by those persons to whom it is </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">addressed and its content is not intended for use by any other </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">persons. If you have received this message in error, please notify </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">us immediately. Please also destroy and delete the message from </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">your computer. Any unauthorised form of reproduction of this message </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">is strictly prohibited.</span></p>
<p><span style="font-family:'Arial';font-size:8pt;"></span></p>
<p><span style="font-family:'Arial';font-size:8pt;"> </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">St George Bank Limited AFSL 240997, Advance Asset Management Limited </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">AFSL 240902, St George Life Limited AFSL 240900, ASGARD Capital Management Limited </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">AFSL 240695 and Securitor Financial Group Limited AFSL 240687 is not liable for </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">the proper and complete transmission of the information contained in </span></p>
<p><span style="font-family:'Arial';font-size:8pt;">this communication, nor for any delay in its receipt.</span></p>
<p><span style="font-family:'Arial';font-size:8pt;">**********************************************************************</span></p>
<p><span style="font-family:'Arial';font-size:8pt;"> </span></p></BODY></HTML>