phpBB + mysql optimizations

[robbat2]

for optimizing this box for the forums:
1. run mysql 4 - the query cache helps a LOT. Also increase the cache memory by tweaking the my.cnf settings. (look at the my-huge.cnf that ships with mysql...).
2. look at your mysql database permissions, and try to remove any column+table permissions, moving them instead to database permissions. mysql suffers if you used any table or column on any databases.
3a. have a long look at the indexes on the tables in mysql.
3b. if you have a batch of common queries for each activity, use 'explain' on them in mysql and make sure that fields used in a lot of where clauses are indexed.
4. if you want a rough guide to timings, use those common queries in a recent CVS version of phpMyAdmin and look at the query timing results.
5. try and compare performance runnign different table types in MySQL. I think you'll probabably come out best with BerkDB or MyISAM, but YMMV.
6. (drastic) move the db to another server and convert the existing server to a readonly slave.

{shameless plug}
I'm a core developer on phpMyAdmin.
{/shameless plug}

[BartVB]

MySQL is nice but IIRC still not very suitable for a production box. It's still alpha/beta, isn't it

The indices are all OK with phpBB

By default MySQL uses MyISAM which is OK for medium sized boards. InnoDB will give a nice performance boost for large sites, especially because of it's row locking (on average it's slower than MyISAM).

More hardware is always better

One of the largest DB related problems with phpBB is (ofcourse) the search system. The search system contains one large and one huge table which can slow the system down quite a bit when those need to be updated. Especially editing a post posted quite a burden on the DB.

Would be nice if we could integrate a separate (specialized, non-SQL-based) fulltext search tool with phpBB...

[robbat2]

MySQL 4 is gamma status now. I've been running it since late beta (4.0.5) in a few production sites, slowly ramping up usage to more servers. I've got about 3gb of data spread across my servers in MySQL 4 now, with no problems at all seen.

I haven't had any admin work with phpBB for quite a while now, last time I had to deal with it was quite some time ago. The last one I dealt with didn't have much in the way of indices, hence my suggestion.

More hardware never hurts a problem I would agree.

Editing a post shouldn't be so much of a burden, at least in concept. There should be a unique id for the post, and then it should become a matter of a few update statements.

[BartVB]

Editing the post is no problem at all. Updating the search index is

There is a table with (almost) all words that occur in all posts and there is a table with two INTs that tells what words occur in which posts. So if you want to edit a posting you either have to check what has been changed or you have to delete everything in the 'link table' and then add it again. With a larger forum this link table is several million rows long, on my board it's 27M rows It takes some time before that's updated, even with the proper indices...

BTW do you know when MySQL 4 is going out of gamma status? It does have some nice features/improvements (the query cache being the most important one).

[robbat2]

Initial gamma release was 4.0.6 on 14 December 2002.

here is a list of the major changes in MySQL 4:
http://www.mysql.com/doc/en/News-4.0.x.html
amongst the things there that should interest you:
"A query cache, offering vastly increased performance for many applications."
"Improved full-text indexing with boolean mode, truncation, and phrase searching"

Ouch, a word frequency table like that has gotta hurt bigtime. It's a a few 100mb of data in there at least with your 27m rows. I wonder if there is not a better way to get that functionality, without having that expliciting linking data. It sounds like a custom implemented fulltext index. I have a feeling that the MySQL FULLTEXT indices might be a better way of going for that, but i'm not 100% certain.