Hi Kane,
The sessions table is used to track a users permissions etc.... so that we build their permission set once and don't need to rebuild it on every page view to see what they can do, view, etc...
For anonymous users, the session record will track their permissions as well as things like flood control. ie if Guests can search or post only once every 5 minutes, you need a session record for each guest so that you have something to check against. This also tracks any of the customizable aspects (collapsing categories, altering the default view date, templates, referral info etc....) which would be unique per person browsing and need to be retained throughout their visit.
The session length is used for guests - I would set this as short as possible. The shortest allowed setting is 3 and the maximum is 24. 4 is the default and this should be a pretty safe setting. The higher the number the bigger the session table.
The online activity setting is the length for users to remain in the session table (and thus in the online page). The higher the number, the bigger the sessions table and the longer the online list. I would set this as short as possible on a busy site 10 minutes should be pretty good. For a small or average site, 30 minutes is just fine.
As per display names in the topics table - this is still the case. We store the display names, status flags and name colors in the topics table to avoid having to join the topics and posts table on the listing of posts - which is a huge performance hit. So we're choosing to store this info in multiple places, sacrifice the ability to update it easily, for the sake of general speedy performance in this area. For this reason, on large sites, I do NOT recommend allowing users to change their display names. It's a performance killer. For this reason we have this ability permission based. Removing the names from the topics table would make changing names easy, but kill the showforum.php script, which is the most intense and most heavily used script. This is a feature limitation done by design.
As per performance killers - things you can do to keep things speedy. I'll list them by area and notes about each.
In Edit->Configuration Settings
1) Database Search Server - I think this was implimented in 2.1 or 2.2 - but you can define a different server for searches - this can take the searching load off your primary mysql server. This setting will be in the "database settings" section of the configuration settings.
2) Forum Settins -> Topics Per Page, Replies Per Page
I would leave these settings as low as possible. This is the setting that guests will see, so the fewer posts per page the less intense the query. Registered users will be able to increase this if they wish, but the vast majority will keep the default settings. Recommend 10 or 20 for each.
3) General Settings
If your site is busy, make sure you are using Real Cron. If you don't setup a real cron job, and check the real cron box, then every time a script loads for a user, it checks if a cron tasks needs to be run, and then runs it in the background. So the browsing user who triggers the cron, essentially waits for it to complete - thus slowing them down. Real cron jobs trigger the cron events on the server level, without the user having to be the trigger.
4) Zlib compression - will make the page size smaller and faster for the end user, but increase load on your webserver (since the pages are compressed before being sent to the user).
5) User Settings
Max Search Results - I wouldn't set this too high - 250 or 500 should probably be the max.
6) Maximum posts displayed
This pertains to having an "all" link when viewing posts. I wouldn't set this too high either. Letting users (or bots) display "all" posts in a topic, just ups the heft of the query needed. The default is 100. For busy sites, I'd lower this even more. Maybe to 25 or 50.
In Permissions -> Site Permissions
You may wish to limit the following from the masses, since they are high impact.
1) Can Mark All Read Anytime
2) Can search All Forums - maybe guests can only search one forum at a time. Makes the searching more limited one forum at a time and thus an easier task for MySQL
3) Can see full post tracking on Index
This is a big performance killer This is the setting that controls the little number on the forum list, indicating the number of new posts since their last visit. This is an intense query if you have alot of users, or alot of forums. Busy or large sites should disable this for the masses for sure!!!! With this setting disabled, the atoms still "light up" they just don't get the count of new topics/posts inside. The counting of them is high demand.
4) Can see full post tracking Inside of Forum
This is not
quite as bad as the one above as it is more efficient. But it's an area that you can limit to reduce MySQL load. Again the folders still "light up" this setting just nixes the count of new replies inside the topic. Again, the counting of them is where the load comes into play.
5) Can change display name - I wouldn't allow members on a busy or large site to have this permission. Or if you MUST, make it require approval. As updating all topics will be the killer query. For the sake of performance, this is a feature for small sites.
You may wish to make sure the following have times associated with them to limit the flooding of this activity. ie can only post or search every X seconds.
1) Can Search Forums - search is intense, so you don't want, particularly guests to be able to search repeatedly. Set the Guests time limit high - recommend 300 seconds (5 minutes) or higher. Users might not need to be limited quit this much, maybe 2 or 3 minutes.
2) Can subscribe to topics
Unfortunately, the topic subscription system is high demand. When a post is made, if it's an instant subscription, it needs to be checked and emailed. Daily or Weekly subs are handled by a cron, but an instant is done when the post is made. For this reason, limit the number of topics that a user can subscribe to, to a reasonable number.
Forum Permissions
1) Can create new topics -
set the time limit high enough to prevent someone from being able to "flood" you with new topics rapid fire. For registered users maybe 180 seconds. For guests maybe 600 seconds.
2) Can reply to topics - I'd use the same settings as above.
Forums -> Manage Forums- > Edit Forum
1) Default Topic View Age
I would set this setting to as low as reasonable for your forum. If you have alot of posts, I would
NEVER Never never ever never in a million years - set this to All Dates. If you set it to all dates, and you have a million posts, on every load of showforum.php - even though you may just be displaying 20 topics at a time, it's going to take all million of those topics, wade through and sort them. If you limit this by date, you immediately allow MySQL to make use of an index on the timestamp. So if the forum is set for 7 days, then immediately, before any topics are dealt with, MySQL will knock any out of the mix that are more than 7 days old. Again, if you have alot of posts, this makes a much smaller set to be sorted, limited and displayed.
This is the single biggest setting to help improve performance if your post table is large.
Users will be able to override this setting for the length of their session, so they do have the ability to go back and see older posts. But the vast majority will be look for and at recent stuff initially.
Other general suggestions
Make sure the fbb_page_cache table isn't getting too big. The page cache time can be set shorter, and make sure the expire page cache cron job is running to clear this out.
Make sure the fbb_logs table isn't getting too big. This is a table that we haven't been optimizing until 2.2.1 comes out. And earlier versions didn't have a way to clear it. So if it's large, feel free to empty it and optimize it.
Other suggestions to scrapbook.com - are you using phpAdsNew? Is that on the same server as Fusion? phpAdsNew is a GREAT free MySQL/PHP based banner ad management solution. It has GREAT reporting features. But because of the great reporting features - it's a MySQL killer as it's tables can get HUGE. I don't know of a better solution though. But have seen it really add alot of size to the database. If you've got the ability to run this from a different box, that may help it not compete with fusion for MySQL power.
Feel Better, Feel Younger, Sleep Better - Trans-D Tropin
Visit www.BuyTransD.com - use coupon code 1004 to save $20 |
(I tend to be picky when it comes to tweaking and tweaking until something looks the way I'd like it to. This topic is close, but not there yet LOL )
