So basically, the other solution for users that want to keep fulltext searching and MyISAM performance is to use the round-robin replication feature of MyBB/MySQL. InnoDB is certainly slower in many areas, but if table-locking is your problem then obviously row-locking will work out better for performance at the overall cost of running a somewhat slower DB engine.
The solution labrocca used will not help out anyone unless you have a forum as big as labrocca's, so I don't want to see users changing their databases just because people think "this will fix their performance problems." That issue is still and always will be because of overcrowded shared hosting environments or unoptimized plugins.
Reading up on innodb vs myisam it does ultimately boil down to data integrity, locks, and query quantity. MyISAM is typically faster for small databases which is the majority of databases. However as your database grows in size and queries increase it's innodb that can be the better performer. I am seriously having like 1000% increase in performance because of this change. I could probably be on one server again because of this.
Maybe you can try my changes at ncaabbs and see if you experience the same. It's easy enough to undo the changes.
Quote:MyISAM performance is to use the round-robin replication feature of MyBB/MySQL
Is there any documentation on how to implement this feature for MyBB?
btw...this is very noteworthy: Previously on table lock waits I had 1:34 ratio. That's one in every 34 queries had to wait for a lock. With 250 queries per second it's a tremendous problem. Now it's 221:1. That's significant. Also because of boost in performance queries per second has increased to 280. Probably because users don't have to wait as long for page loads.
I am so happy right now.
(2010-01-12, 11:17 PM)labrocca Wrote: [ -> ]Maybe you can try my changes at ncaabbs and see if you experience the same. It's easy enough to undo the changes.
There is no need, we are running fine and we do not want the performance decreases or loss of fulltext capabilities at NCAAbbs.
(2010-01-12, 11:17 PM)labrocca Wrote: [ -> ]Quote:MyISAM performance is to use the round-robin replication feature of MyBB/MySQL
Is there any documentation on how to implement this feature for MyBB?
Yes, it was fully outlined here:
http://blog.mybboard.net/2008/03/18/mybb...ew-part-3/
I'm not sure if anyone from the community contributed that documentation to the Wiki yet.
Yes that should be in the wiki. I actually was looking for that info a few weeks ago.
Just share..
Oke, I think We have few same problem.
But, labrocca expert in PHP/SQL.
And, I am not.
I choose different option.
With current MyBB performance and Ryan tweaking (such pre-parse and sphinx).
I've seen, the problem not came from Search. May affected only on huge Handler_read_rnd_next (PHPMyAdmin).
This week, I tune may server parameters such Lighttpd or Apache and SQL and being examined right now. Currently, I have 1.2GB data and 500-600 indexing assign as Key_Buffer.
Lighttpd quite stable together with SQL and running well. But Apache much faster, with memory hog. Combine Xcache+Zend, I have fast performance and less 30 concurrent connections at peak visit (600-700 members online at a time). Last two days, my server back normal like past (I hope)
But, problem happen. If any "Locked" table. Even, it will release in few second. Too bad, if this happen at peak and concurrent connections increase suddenly twice, just few secons!!
I could not monitor Query everytime, because I have to works (outside)
Well, let see until this weekend. I hope, my forum got high hits (usually on tuesday/friday noon).
I will report it.
[
attachment=16970]
If you can run innodb then do it on the users table at least. It's a very easy change and shouldn't produce negative performance results. Your screenshot shows the users table locking up. Innodb row-level locking would help you immensely.
If it doesn't work out it's an easy change back to myisam. You won't have to make adjustments to the indexes either on the users table.
Just an update.
![[Image: 6r1p3obcyhkrtfj1kn9.png]](https://camo.mybb.com/5f0d2177c556b2ca0545f7cedc47fae30b8152e1/687474703a2f2f696d6167652d686f73742e636f6d2f696d616765732f36723170336f626379686b7274666a316b6e392e706e67)
MySQL server has run smooth now for over six days. You can see in screenshot just how busy the server really is.