My system's very small - approx 20 users and very little content at present. It's for a small collaborative research group which may grew to ~100 members. I expect quite a few largish attachments and have already configured filesystem attachments.
Sounds quite reasonable. Limited memory?
I'm using PostgreSQL for various reasons, so can't add all the suggested indexes (names the string prefix ones that are specific to MySQL), but I'd be surprised to be hitting limits so soon.
The database difference might be something to explore. I've seen rather often that things are more battle-tested for MySQL. But I fully agree you have reasons to prefer PostgreSQL.
The slowdown occurred soon after I opened the wiki to the other members, who probably did some exploring and perhaps triggered something painful.
You definitely need to explore when it happens. - something such as show full processlist (that's a MySQL thing) should be available to show if there's an SQL query taking long and if others are slow (typically, big LIKE queries tend to block all writes) - invoke a thread-dump (kill -HUP or use JMX or http://extensions.xwiki.org/xwiki/bin/view/Extension/JMX+Access#HExample5:ge...): the threads generally carry the path of the http requests - check memory - make sure you enable -verbose:gc as a JVM option to indicate when memory limits are reached paul