MySQL performance

[gfdsa]

Hello all!

I need help in finding a solution, i will really appreciate any help from ppl...

Im hosting a board at http://www.hwzone.co.il/community
Everything is cool except MySQL which makes all the buisness really slow, a hit on index.php makes MySQL jump to 80%-90% of cpu usage, this issue makes the index page to opened ~30-40 sec when there are 150-200 users on board, in such hours system load gets to 1000% on the box.

IMHO the board is kinda big, about 7000 topics, 70000 posts and 1800 members, database is about 25M
but still i dont see  any reason for this issue.

Can anyone give me a clue what i miss?
Thanks a lot.

[Greg Robson]

As far as I know it's more YaBB SE performance than MySQL. Although YaBB SE is fast, a lot of the code hasn't been optimised. The database could do with better indexes and queries - all I can suggest is splitting some of the larger boards into smaller ones so there isn't as many posts in each one.

[Jeff Lewis]

I have to agree, we haven't optimized it as much as possible yet.  So while you're seeing a big hit on the index, in some tests we've done - we've managed to knock something like 70% of the waste off of it.

So we are optimizing now but the versions out now, aren't

[Greg Robson]

If you want to find out more check out the documentation board - Groundup and I are working on tidying up the code: then we'll optimise it as much as possible (without having to re-write YaBB ).

[Purple Raine]

Awesome.... Great to know people are onto it!

I'm at 4700 topics, 50000 posts, 2500 users... and the queries are really sucking CPU... (and i'm on shared hosting)...

I look forward to the next one =)

[gfdsa]

Well,
First of all, thank you for your replies, but i must inform you that yabb is not so bad! i optimized tables (what i was sure webmaster does everyday :-), and now with >250 users index.php opens in 6-7 sec, load average on box less than 300%, (sure i would like this number to get to 100% at least). Then after 5-7 minutes it starts to slow down, and aprr. in 30 min 30 sec delays come back. I'm not sure i know what is this issue, is it because of table sizes?
And should i really do optimizing every 15 minutes? i was sure it must be done once in a 24 hours or something.
May be i should try it running in not safe mode?
Does MySQL locks the table when it does insert?
Should i try to lower priority of inserts?
Sorry guys for this amount of questions, i need an advice.....
Thank you again