Originally Posted by technical

Some, but not all. Best case example is Apache. Many Java technologies fall into that category as well e.g. Struts.

Originally Posted by Brains

CF does a lot of archiving, which moves things out of the main post and thread tables. Its kind of a pain in the butt to search because of it, and is the main reason we've been hesitant to follow suit. With me converting all the queries to be SQL92 compliant, it opens us up to run any database server that PHP offers support for (in one form or another, be it a library module or ODBC). There's no abstraction layer in the code however, so the conversion is MUCH more involved than simply changing MySQL-specific language/functions in the queries. I'm having to alter a *LOT* of the source because they coded directly against MySQL and its unique way of doing things. For instance, getting the last inserted auto_increment'ed column's value for a record -- MySQL has a function last_insert_id(), that will pull the last ID without having to specify which table you want the value from. Nobody else is that "lazy" about it, so the code should be changed to specify -- and there's a LOT of locations to do so.

Then there's performance tuning differences between database engines. One of the reasons why switching over to even InnoDB on MySQL doesn't pan out well, is the use of the ever-so-common SELECT COUNT(*). With the MyISAM storage engine, this isn't a costly call at all -- it executes instantaneously because with table locking the number of records is always the total number of rows in the table. With a storage engine that supports multi-level concurrency, you could have any number of records in flux -- inserts, deletes, etc. So, a COUNT(*) is very costly - especially on large tables. vBulletin makes heavy use of COUNT(*) as well, almost every page in fact. That will have to be rewritten as well.

The only "easy" solution I've come up with, and its one I may very well do first, is to segment the site. Basically turn the forums into a group of forums. vBulletin stores all the posts in one table, and all the threads in one table. That means we have over 3 million rows in the post table. It also means the whole thing is locked on updates, which is why the entire site freezes up when locks are held. By segmenting, I can reduce that to one pair of post/thread tables per group of forums, or possibly per forum. That would reduce the number of rows considerably, and severe lock times would only affect one or a small set of forums.

Originally Posted by Brains

I'm actually not using the postindex table at all -- zero rows I've switched over to using FULLTEXT indexes on the post and thread tables which helps immensely.

I am continually trimming the MySQL caches and buffers, there's not much else there I believe. I'm running a PHP cache/accelerator, there's no way this site would even run without one

Originally Posted by ArcticZ28

BTW - any good tutorials on getting a struts app up and running? You've gotta use Ant to compile them into a .war file before deploying them in the webapps folder right? I'm using Apache Tomcat server.

Originally Posted by Brains

I've thought long and hard about doing it that way, or by simply storing each forum in its own pair of thread/post tables, but there's a LOT of code that would also need to be written to keep the site functioning as "one." Instead of put the effort into "bandaids" for MySQL, I decided to put my time into re-coding vBulletin to use a better database backend completely. I currently have a fairly complete implementation of vB 3.5.0 running on a PostgreSQL backend, just going through and cleaning up some final bugs