Simple Machines Community Forum

The following are twenty-four improvements that you should be able to make to SMF regardless of your host, though only the first set will be possible anywhere. If the second set is not possible (outside of say, using Postgres over MySQL, in which case you probably don't need the second set), then I seriously suggest finding a new host. Hosts should provide you with shell access, access to crontab, php5 and InnoDB. An inability to do these things securely does not speak well of confidence in them.

Not all of these ideas are mine, but I did tweak some of them. I give credit to other places in the form of a link - I wanted everything in one place for people to easily reference, as opposed to being forced to check several lists - some of which are quite old. I believe this covers the overwhelming majority of what you would want to do without significantly altering the schema (TinyText needs to die a slow, horrible death), smf's code (as does the @ operator), or your server configuration. A lot can be done in these areas, but the first two are probably best dealt with by pressuring the SMF team directly, while the last deserves a far more comprehensive discussion.

If anyone has additional comments or optimizations, feel free to mention them. If appropriate I will edit them in here.

---

Just about anyone can make use of these optimizations, regardless of how much control you have over your server. The first few options here can have an extremely dramatic effect on your server's performance, I've sorted them roughly in order of potential impact.

1: Move your uploaded avatars directory. (http://www.simplemachines.org/community/index.php?topic=197938.0) In the administration panel, go to Attachments and Avatars, and then go to Avatar Settings.

For Upload avatars to... select: Specific directory...
For Upload directory: put in the raw, publicly accessible server directory you want them added to ie /var/www/yoursite/docs/forums/avs

So assuming /docs was your webroot, you would put /forums/avs for your upload URL. Don't do what I did and make it a subdirectory of your main avatars directory, SMF still handles it like an attachment directory for some reason so if someone uploads a .png avatar, someone else decides to use it and the first person replaces it with a .jpg avatar, the first will be deleted.

The benefit of this borders on the ridiculous. Every single avatar load is  another php call, which loads a session, the user's settings and permissions, the board and its theme. In addition, two additional calls to the database are made to load the avatar itself, and to increment the view counter. So not only are you reducing the processor load on your server, but you are also reducing your database load. If five different avatars are displayed on a page, that's five additional php calls and dozens of database queries getting made.

After you do this access index.php?action=manageattachments;sa=moveAvatars to use the hidden feature to move your avatars over.

SMF 2.0 RC3 Update (June 16th) the url is apparently now index.php?action=admin;area=manageattachments;sa=moveAvatars - thank you Spuds : )

2: Disable hostname lookups. This is located in Features and Options -> Layout and Options or Server Settings -> General in RC1 and on. If you leave hostname lookups on, every single page access carries with it a reverse DNS query. If your host's RDNS is having issues or goes down, you are going to hear about the lag from your members.

Note (May 26th, 2011): If you insist on using the reverse hostname lookup, you can alleviate the lag penalty imposed by reducing the amount of time for timeout. For example, I have

options timeout:1

in my /etc/resolv.conf file. This causes the timeout to be 1 second, rather than 5 as per default. A three-second delay in loading your page is much better than 15!

If you run your own server or VPS, you can add this yourself. Your host should be able to make this adjustment, if not.
[/color]

3: Enable (or disable) gzip compression. This is located in Server Settings -> Feature Configuration. This has the greatest benefit when combined with #1, particularly for dialup users.

Note: Your webserver configuration may render this irrelevant. If your webserver compresses the type of data you use to serve your webpages, you should specifically disable this, as the webserver can better handle it.

4: Tune topics, members, and posts per page. In Posts and Topics -> Topic Settings. Setting this low can have a detrimental affect on your forum's raw activity, however, at least it did on mine. If people click more, they make more requests, after all, and get more frustrated. I use fifty topics per page and twenty-five posts per page, which seems to be close to ideal. Disabling participation icons will also reduce load a slight bit, but some may find this annoying.

Note (May 26th, 2011): Participation icon queries are one of my 'culprit queries' and I've been forced to disable them for now. To be specific, you can turn this off in Admin -> Forums -> Posts and Topics -> Topic Settings

5: If you are going to enable search, and are not or cannot use Sphinx, use a Large Custom Index. SMF's custom index works by taking a hash of every available word, and then building an index of which words point to which messages. 'Small' means this is a 16 bit hash, 'Moderate' is 24 and 'Large' is a 32 bit hash. 'Large' is thus only two bytes larger per record than small is, plus an additional number of records based on the average number of unique words per post you have - which is not going to be a significant increase. The speed improvement you get for the additional ~40% space usage is well worth it.

6: Don't use post moderation on an active forum. Where active is over a thousand posts a day or so. Apparently the query is rather slow, but it is not difficult to have a self policing forum.

7: Disable the recent posts feature on the board index. Let people click a link if they want to see more than the last post they have access to, compiling that list is expensive especially if you have a lot of groups set up. Also I find it rather ugly, myself. This can be found under your Theme's settings, Number of recent posts to display on board index: - set to zero.

8: Run the mark read for inactive users (http://www.simplemachines.org/community/index.php?topic=212330.msg1667071#msg1667071) script periodically. The log_topics, log_boards, and log_mark_read tables are not the best designs, and for some unlucky folks end up dominating their database. Pruning them can help.

9: Do you really need that many boards? I tend to see this rather often and the answer is almost always no. Too many boards actually reduces your forum's activity, for one, but in the case of optimization here, each and every board takes up resources on the relatively common index load, as well as space for the log_boards and log_mark_read tables, with an amount that grows for each new member you gain.

10: Do you really need the calendar? When enabled, SMF looks for an event on each and every thread load - there are ways to make it more efficient but in general it's not an often-used feature.

11: Ben has a post about disabling features according to server load here. (http://www.simplemachines.org/community/index.php?topic=76249.0) - in the GUI for SMF 2.0 RC2 Don't enable this on a shared server or VPS unless you know what you are doing or at least have some guidance, but this can be pretty dramatic. My members use 'show unread posts' or 'show unread replies' roughly thirty thousand times per day. During high loads, even if they're caused by someone else, those sorts of requests are not helping.

12: Consider using database-driven sessions and offloading all attachments to Mediawiki. There is an awesome MediaWiki extension here (http://www.mediawiki.org/wiki/Extension:SMF_Auth_Integration) though some minor tweaks are needed for 1.13 - see the talk page. Combine with hotlinking protection and you can have a slightly more efficient file handling system. It is likely more difficult for your members to use, however.

Addition #1 (March 5th): Prune zero-post members If they really want in, they can post next time. They just take up more and more space in your database, which will be especially notable if you follow tip #8. A simple way to do this is to make one postcount group have zero posts, another at one post, go to members -> search for members, sort by user ID (Ascending), check and start purging until you get to the last month or whatever is comfortable. You can go to Features & Options -> Layout and Options, set members per page to 100 to make it faster (just don't go overboard and start deleting recent or genuinely active members).

Addition #2 (May 3rd): Perform only necessary tasks, and spread them out (SMF 2.0 only) I just had this murder my forum, twice. While these do not necessarily slow down php, they can have a pretty dramatic effect on your forum if there are DNS issues, or simply the volume becomes so great that your server stops to send out thousands of emails.

The tasks can be found directly under Scheduled Tasks, in 'maintenance'. Be sure that approval notifications are disabled unless you really want them, and that optimize database is disabled if you are doing it with your own script (see mine below). The default execution time is 6 PM which, for a large forum, is really not the best.  If you have a smaller (but still reasonably active) forum, you may want to spread them out by an hour or so - remember it's not enough for php to fire off emails to your mailserver, your mailserver also handles the emails, so don't have them running in immediate succession.

Addition #3 (July 1st): Cap the length of your threads This is one of those things that ought to be a setting - even vBulletin is known for choking on large threads, look up rpg.net's motivational poster history. Large threads mean there is a large result set for further instructions in the query to prune from, and if a thread is too disproportionally large, even browsing the thread will result in using table scans instead of the index - a very bad situation indeed.

30k is what I use for Elliquiy, and Ben uses 25k for Red and White Kop. Keep in mind, though, that we are both running finely tuned dedicated servers with two hard disks, two cores, and 4gb of RAM. You may want to cap thread length closer to 1k posts.

This also has the effect of making individual threads less overwhelming.

Addition #4 (November 1st): Don't go crazy on on-line time In Features and Options -> General, the online time threshold defaults to 15 minutes, as in vBulletin. Some people like to set this rather high to give the illusion of an extremely active forum, but people are already wising to this. In addition, you get diminishing returns, and the larger it is, the more time your forum will spend processing this extremely active table. It is also quite annoying - I had to modify the Users Online Today mod, simply because looking through hundreds of names is annoying.

Addition #5 (November 1st): If you really want, disable tracking daily page views in the same section as #4 there. My tepid enthusiasm is shared by others with highly tuned, CPU intensive forums - you do not need to do this to have a solid-performing forum.

It is an additional query on each page load, however, and if it becomes an issue and like most of us, you want to keep it, be sure to convert the underlying table (log_actions) to InnoDB as described below.

---

These steps are going to require some support from your host, or for you to actually be the host, as in running off of a VPS or dedicated hosting solution. The total improvement in speed can be extremely dramatic, however - you can easily cut your forum's response time in half. These will be possible with any and every vps/dedicated host, of course, and pretty much any shared host you ought to be doing business with.

1: Converting Tables to InnoDB and MEMORY (http://www.simplemachines.org/community/index.php?topic=50217.0) Download and run status.php from the checklist post here (http://www.simplemachines.org/community/index.php?topic=49999.0). Click on "Show more information" and look for the have_innodb: setting. Does it say YES? Good. If it says no, and you are on a shared host, and after all of the above you are still having issues, it's time to get a VPS or at least a better host. Make sure innodb_buffer_pool_size is sufficient 8M is only going to work for a tiny forum. 64M will do you fine up to low-end Big Boards levels, but if you have a lot of members and topics you may find that you need more, though you are clearly pushing "Needs a VPS" territory at that point.

First, clear out your error logs from smf's error page, and make a backup of your database.

You may also want to wipe out the read message indicators if you don't want your forum to be unresponsive for too long when you do some of this:


DELETE FROM smf_log_topics;
DELETE FROM smf_log_boards;
DELETE FROM smf_log_mark_read;


This removes all message read indicators. It will annoy your users. You'll want to weigh it against the amount of time converting these effectively freezes your forum. If you don't do this these three are going to take their sweet sweet time.


ALTER TABLE smf_attachments ENGINE=InnoDB;
ALTER TABLE smf_collapsed_categories ENGINE=InnoDB;
ALTER TABLE smf_log_actions ENGINE=InnoDB;
ALTER TABLE smf_log_boards ENGINE=InnoDB;
ALTER TABLE smf_log_errors ENGINE=InnoDB;
ALTER TABLE smf_log_karma ENGINE=InnoDB;
ALTER TABLE smf_log_mark_read ENGINE=InnoDB;
ALTER TABLE smf_log_online ENGINE=InnoDB;
ALTER TABLE smf_log_topics ENGINE=InnoDB;
ALTER TABLE smf_members ENGINE=InnoDB;
ALTER TABLE smf_sessions ENGINE=InnoDB;
ALTER TABLE smf_settings ENGINE=InnoDB;
ALTER TABLE smf_topics ENGINE=InnoDB;
ALTER TABLE smf_log_activity ENGINE=InnoDB;


The following command will only work for SMF 1.1. SMF 2.0 commands are next.

You will also want flood control to be memory/heap:

DROP TABLE smf_log_floodcontrol;
CREATE TABLE smf_log_floodcontrol (
  ip char(16) NOT NULL,
  logTime int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (ip),
  KEY logTime (logTime)
) ENGINE=MEMORY;


For SMF 2.0 the create statements for this is a bit different:


CREATE TABLE `smf_log_floodcontrol` (
  `ip` char(16) NOT NULL default '',
  `log_time` int(10) unsigned NOT NULL default '0',
  `log_type` varchar(8) NOT NULL default 'post',
  PRIMARY KEY  (`ip`,`log_type`)
) ENGINE=MEMORY;


Finally, on a forum with a lot of personal messages this is going to take awhile. So warn people, etc. etc.


ALTER TABLE smf_pm_recipients ENGINE=InnoDB;


SMF 2.0 has a few additional tables that you may wish to convert to InnoDB:


ALTER TABLE smf_pm_rules ENGINE=InnoDB;
ALTER TABLE smf_log_spider_stats ENGINE=InnoDB;


Unless you have installed mods, this should be a fairly complete list as of SMF 1.1 and 2.0 (I think >_>). Your mileage will vary regarding the amount of benefit this will provide - I switched my own forum after ~1M posts and response roughly doubled.

Some suggest converting -all- tables except floodcontrol to InnoDB. For most tables this is perfectly reasonable, but for smf_personal_messages and smf_messages, as well as similar "Post and forget" tables like a Shoutbox, this can actually be counterproductive to performance, particularly with a good-sized key_buffer and concurrent_insert = 2 in your my.cnf config. A warning, however, is that smf_boards currently depends on MyISAM's ordering in order to display boards properly - if you convert it to InnoDB you will want to make adjustments to the key and/or SMF accordingly:

SMF 1.1:
ALTER TABLE `smf_boards` ADD INDEX (`ID_CAT`), ADD UNIQUE KEY (`ID_BOARD`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `boardOrder`, `ID_BOARD` ), ENGINE=InnoDB;

SMF 2.0 RC 1:
ALTER TABLE `smf_boards` ADD UNIQUE KEY (`id_board`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `board_order`, `id_board` ), ENGINE=InnoDB;

2: Run PHP 5. If your host does not at least give you the option, get a new host.

3: If possible, keep SMF in its own database. If you do something like I do and have SMF, Drupal, MediaWiki, and other sites all on the same physical server, it's a good idea to give each such application and even every -instance- of each such application its own database. The primary purpose of this is not quite speed, but rather organization and faster backups.

4: Backup scripts make the world go round. You will need shell access for this, and I am making the possibly erroneous assumption that your server uses bash. There are several steps involved here:

A: Disable SMF's table optimization. This is in Server Settings -> Feature Configuration, set Optimize tables every how many days to 0 to disable it.

B: Make two subdirectories from your home directory. Call one 'backups' and the other 'scripts', without the quotes. chmod o-rx the both of them.

C: We're going to want to let your members know that your forum is getting backed up, because once you get into the million post range this is going to bring it down for awhile. Make a file in the scripts directory called Settings.lock.php - obviously, you're going to want to customize this appropriately.

SMF 2.0 Note They decided to move theme loading, etc. before Settings.php. So rather than this, you just have to swap out index.php : / Annoying.


$maintenance = 2;
$mtitle = 'Backup in progress';
$mmessage = 'We are in the midst of the nightly optimization and backup script. This currently takes a few minutes. Thank you for your patience. : )';

$mbname = 'Your Forum Name';
$language = 'english';
$boardurl = 'http://yourdomain.com/forums';
$webmaster_email = '[email protected]';
$cookiename = 'CookieName';

$boarddir = '/var/www/forums/docs/forums';
$sourcedir = '/var/www/forums/docs/forums/Sources';


D: Let's make a script in your scripts directory. Call it backup.smf and be sure to chmod 700 it. You are certainly going to have to edit the directories, database names, and password accordingly. Use whereis mysqlcheck/mysqldump/nice/bzip2 to make sure the directories calling them are accurate. You may also wish to remove --auto-repair from the mysqlcheck line, though fortunately, as you will note, you have already made a backup immediately beforehand in case something goes wrong. : )


#!/bin/bash
cp /var/www/webdir/docs/forums/Settings.php /home/userdir/scripts/Settings.orig.php
cp /home/userdir/scripts/Settings.lock.php /var/www/webdir/docs/forums/Settings.php
/usr/bin/nice /usr/bin/mysqldump -q -u dbusername -p"dbpassword" --ignore-table=smf_forums.smf_log_search_words smf_forums > /home/userdir/backups/smf_forums.`date +%a`.sql
/usr/bin/nice /usr/bin/mysqlcheck -ao -–auto-repair -u dbusername -p"dbpassword" --databases smf_forums
/bin/chmod o-r /home/userdir/backups/smf_forums.`date +%a`.sql
cp /home/userdir/scripts/Settings.orig.php /var/www/webdir/docs/forums/Settings.php
/usr/bin/nice /bin/bzip2 -f9 /home/userdir/backups/smf_forums.`date +%a`.sql


Line by line, this script:
1: Calls the interpreter
2: Makes a backup of our Settings.php file
3: Copies the locked Settings.php file over our forum Settings.php, shutting down the forum
4: Calls nice (this is optional, I use it because it's not the only aspect of the site) to make a backup via mysqldump. If you are not using a custom search index (Sphinx or search is just disabled) then you can omit the ignored table. The `date %a` in the filename returns the three-letter weekday. Omit if you just want one backup. `date +%b-%d` would let you keep a year's worth of backups (god why...) etc. The weekly log rotation seems to be a good compromise between space and having more than one backup to try and recover from IMO.
5: Optimizes our database and checks it for errors, again using nice.
6: I'm paranoid, even though I run my server on my own, don't want any random chance of someone breaking into an account and reading it.
7: Since we've got our database backed up, we can freely restore our original Settings.php before we compress the file.
8: nice is less optional here. Now that we're up and running again, compress the ginormous backup we just made. Do not pipe mysqldump to bzip2. You will end up taking your forum down for far longer than it needs to be unless you have a tiny forum.

E: run crontab -e and add this line:
35  4  *   *   *     /home/userdir/scripts/backup.smf

Change the directory and time as needed. This runs the backup at 4:35 AM my time, each morning, which is when my forums have the least amount of activity.

F: I use a WinSCP script to synchronize these with my machine at 5 AM each morning. You can set something up similar for your own machine or operating system.

Additional Item #1 (March 7th): Get rid of Text tables.  - note, this is included in SMF 2.0 RC2

Frequently, when doing sorts, joins and other operations, MySQL needs to create a temporary table. MySQL usually tries to create these as MEMORY tables, however, if the resulting table would have any TEXT or BLOB columns, it is incapable of doing this, instead creating it as a MyISAM table on-disk. This can murder performance if you don't have /tmp mounted in memory or if you have a tiny key buffer.

Converting TINYTEXT columns to VARCHAR(255) is fairly safe.

The following is for SMF 2.0 (pre-RC2)

ALTER TABLE `smf_admin_info_files` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_admin_info_files` CHANGE `path` `path` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_admin_info_files` CHANGE `parameters` `parameters` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_admin_info_files` CHANGE `filetype` `filetype` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_attachments` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_ban_groups` CHANGE `reason` `reason` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_ban_items` CHANGE `hostname` `hostname` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_items` CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_boards` CHANGE `name` `name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_categories` CHANGE `name` `name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_custom_fields` CHANGE `field_desc` `field_desc` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_custom_fields` CHANGE `field_options` `field_options` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_custom_fields` CHANGE `mask` `mask` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_custom_fields` CHANGE `default_value` `default_value` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_banned` CHANGE `email` `email` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_comments` CHANGE `recipient_name` `recipient_name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_errors` CHANGE `file` `file` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_member_notices` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_packages` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `package_id` `package_id` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `version` `version` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `member_installed` `member_installed` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `member_removed` `member_removed` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `themes_installed` `themes_installed` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_reported` CHANGE `membername` `membername` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_reported` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_reported_comments` CHANGE `membername` `membername` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_reported_comments` CHANGE `comment` `comment` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_spider_hits` CHANGE `url` `url` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_subscribed` CHANGE `vendor_ref` `vendor_ref` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_mail_queue` CHANGE `recipient` `recipient` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_mail_queue` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_membergroups` CHANGE `stars` `stars` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_openid_assoc` CHANGE `handle` `handle` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_package_servers` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_package_servers` CHANGE `url` `url` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_permission_profiles` CHANGE `profile_name` `profile_name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_poll_choices` CHANGE `label` `label` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_polls` CHANGE `question` `question` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_polls` CHANGE `poster_name` `poster_name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_settings` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_spiders` CHANGE `spider_name` `spider_name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_spiders` CHANGE `user_agent` `user_agent` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_spiders` CHANGE `ip_info` `ip_info` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_subscriptions` CHANGE `description` `description` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_themes` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_personal_messages` CHANGE `from_name` `from_name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_personal_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_members` CHANGE `lngfile` `lngfile` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `real_name` `real_name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `personal_text` `personal_text` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `website_title` `website_title` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `website_url` `website_url` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `location` `location` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `icq` `icq` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `msn` `msn` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `avatar` `avatar` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `usertitle` `usertitle` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `member_ip` `member_ip` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `secret_question` `secret_question` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `additional_groups` `additional_groups` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `member_ip2` `member_ip2` VARCHAR(255) NOT NULL;


There are also a few of these in the messages table. While the above may not be very disruptive, the below will prevent posting on a large forum. Each operation took nearly a minute for my forums:


ALTER TABLE `smf_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `poster_name` `poster_name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `poster_email` `poster_email` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `poster_ip` `poster_ip` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `modified_name` `modified_name` VARCHAR(255) NOT NULL;


Thanks to Keeper for converting this mess to SMF 1.1 (http://www.simplemachines.org/community/index.php?topic=336278.msg2256722#msg2256722):


ALTER TABLE `smf_attachments` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_groups` CHANGE `reason` `reason` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_items` CHANGE `hostname` `hostname` VARCHAR(255) NOT NULL, CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_boards` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_categories` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_banned` CHANGE `email` `email` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_membergroups` CHANGE `stars` `stars` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_package_servers` CHANGE `name` `name` VARCHAR(255) NOT NULL, CHANGE `url` `url` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_polls` CHANGE `question` `question` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_poll_choices` CHANGE `label` `label` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_settings` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_themes` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `lngfile` `lngfile` VARCHAR(255) NOT NULL, CHANGE `realName` `realName` VARCHAR(255) NOT NULL, CHANGE `emailAddress` `emailAddress` VARCHAR(255) NOT NULL, CHANGE `personalText` `personalText` VARCHAR(255) NOT NULL, CHANGE `websiteTitle` `websiteTitle` VARCHAR(255) NOT NULL, CHANGE `websiteUrl` `websiteUrl` VARCHAR(255) NOT NULL, CHANGE `location` `location` VARCHAR(255) NOT NULL, CHANGE `icq` `icq` VARCHAR(255) NOT NULL, CHANGE `msn` `msn` VARCHAR(255) NOT NULL, CHANGE `avatar` `avatar` VARCHAR(255) NOT NULL, CHANGE `usertitle` `usertitle` VARCHAR(255) NOT NULL, CHANGE `memberIP` `memberIP` VARCHAR(255) NOT NULL, CHANGE `secretQuestion` `secretQuestion` VARCHAR(255) NOT NULL, CHANGE `additionalGroups` `additionalGroups` VARCHAR(255) NOT NULL, CHANGE `memberIP2` `memberIP2` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL, CHANGE `posterEmail` `posterEmail` VARCHAR(255) NOT NULL, CHANGE `posterIP` `posterIP` VARCHAR(255) NOT NULL, CHANGE `modifiedName` `modifiedName` VARCHAR(255) NOT NULL, CHANGE `fromName` `fromName` VARCHAR(255) NOT NULL, CHANGE `subject` `subject` VARCHAR(255) NOT NULL;


You may want to file down your board descriptions so they can fit inside a VARCHAR(255):

SELECT LENGTH(description), id_board FROM smf_boards WHERE LENGTH(description) > 250;

Trim the offenders and then apply.

ALTER TABLE `smf_boards` CHANGE `description` `description` VARCHAR(255) NOT NULL;

If you don't care to do that, see the following.

Some TEXT columns can also be set to VARCHAR safely. This is, however, slightly riskier and there are some logistics involved, so if you're squeamish feel free to skip this step.


ALTER TABLE `smf_ban_groups` CHANGE `notes` `notes` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_actions` CHANGE `extra` `extra` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_comments` CHANGE `body` `body` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_errors` CHANGE `url` `url` VARCHAR(8000) NOT NULL;
ALTER TABLE `smf_log_errors` CHANGE `message` `message` VARCHAR(8000) NOT NULL;
ALTER TABLE `smf_log_group_requests` CHANGE `reason` `reason` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_member_notices` CHANGE `body` `body` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_online` CHANGE `url` `url` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_reported` CHANGE `body` `body` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_subscribed` CHANGE `pending_details` `pending_details` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_membergroups` CHANGE `description` `description` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_sessions` CHANGE `data` `data` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_settings` CHANGE `value` `value` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_themes` CHANGE `value` `value` VARCHAR(16000) NOT NULL;


May 26th, 2010 Note - I recently made an attempt at converting my main forum's personal messages, shoutbox messages, and messages tables to InnoDB. This was a mistake. I've kept the messages table converted - it reduces locking incidents - but *nix in general does a pretty good job of caching disk reads for MyISAM tables. If all that happens is insert and delete, don't fret over it.

Addition #2 (May 3rd): Control your Expires Header - This is often done in server configurations but can also be done through .htaccess. Note that your provider needs to have mod expires on in order for this to function. This can drastically cut your forum's bandwidth needs.

# Turn it on
ExpiresActive On

# Set a default expiry time. One hour is fine, but a day or longer may be appropriate.
ExpiresDefault A3600

# Turn expiry off for dynamic content (or potentially dynamic content).
ExpiresByType application/x-httpd-php A0
ExpiresByType application/x-python-code A0
ExpiresByType text/html A0

<FilesMatch "\.(php|py|pyc|pyo)$">
  ExpiresActive Off
</FilesMatch>

Addition #3 (July 1st): If you have an opcode cache enabled, disable template eval (http://www.simplemachines.org/community/index.php?topic=50227.0) If you install phpBB and SMF side by side, and install an opcode cache, you will notice that SMF goes from winning the horserace by about 20% to losing by about 25%. The culprit here is eval () - in order to assist coders in modifying their templates, SMF first evals the template to make sure everything is okay.

If you're at the point where you are using a VPS or dedicated server to host your site, however, you should be keen enough to be using a test template before pushing it live. All the eval is doing once you install APC or XCache is slowing you down.

Just execute the following code:


REPLACE INTO smf_settings
   (variable, value)
VALUES ('disableTemplateEval', '1');


And you're back up to speed : )

Excellent post!

Quote from: Vekseid on February 16, 2009, 06:29:50 AM
Some suggest converting -all- tables except floodcontrol to InnoDB. For most tables this is perfectly reasonable, but for smf_personal_messages and smf_messages, as well as similar "Post and forget" tables like a Shoutbox, this can actually be counterproductive to performance, particularly with a good-sized key_buffer and concurrent_insert = 2 in your my.cnf config. A warning, however, is that smf_boards currently depends on MyISAM's ordering in order to display boards properly - if you convert it to InnoDB you will want to make adjustments to the key and/or SMF accordingly.

Three things.

1. Might as well tell them the change needed to smf_boards:

ALTER TABLE `smf_boards` ADD INDEX ( `ID_CAT` ), DROP PRIMARY KEY, ADD PRIMARY KEY ( `boardOrder` , `ID_BOARD` ), ENGINE=InnoDB;


2. Important! Make sure innodb_buffer_pool_size is set to a reasonable amount in my.cnf or performance will suffer horribly. A reasonable rule of thumb would be a eighth (using MyISAM) to a quarter (InnoDB only) of the available RAM in a mixed web/PHP/MySQL server. The default 8M is absolutely too low.

innodb_buffer_pool_size = 256M


3. If you're only running SMF on the machine, consider setting innodb_flush_log_at_trx_commit=0 in my.cnf. This dramatically speeds up queries that update the database, making InnoDB as fast as MyISAM.

Ah right, thanks. Again though, this is mostly about tuning things from SMF's end, rather than the server, that's a much larger and more generic topic, really.

as result of tip 8

Run the mark read for inactive users script periodically. The log_topics, log_boards, and log_mark_read tables are not the best designs, and for some unlucky folks end up dominating their database. Pruning them can help.


i have a bad-result.

see here

http://www.simplemachines.org/community/index.php?topic=212330.msg1936124#msg1936124


someone knows what has happened?

Added Addition #1: Pruning zero-post members.

Edit: Also, you have a lot of boards - I don't speak your language so I can't make suggestions, but basically the size of those two tables is going to be governed by members * boards. Merging boards and pruning zero post members will take care of that nicely.

Quote from: Vekseid on February 16, 2009, 04:39:48 PM
Added Addition #1: Pruning zero-post members.

Edit: Also, you have a lot of boards - I don't speak your language so I can't make suggestions, but basically the size of those two tables is going to be governed by members * boards. Merging boards and pruning zero post members will take care of that nicely.

thanks vekseid!!
big words!

Thanks for the backup script. Very nice.

Great tips. Nice having them all in one place

I think this should be stickied

Quote from: metallica48423 on February 17, 2009, 02:30:08 AM
I think this should be stickied

Good idea. Better PM a mod.




:D

Stickied now. :D 

smf_log_online can also be converted to type MEMORY in MySQL 5.0.3 and later (needed to use a long varchar for the url field -- some sites will have long urls). This can speed things up quite a bit if you have a lot of users online.


ALTER TABLE `smf_log_online` CHANGE `url` `url` VARCHAR( 1024 ) NOT NULL, ENGINE = MEMORY;


I've been running like that for over a year without issue.

edit: fixed wrong table type in code.

I think you mean MEMORY there?

Online is the current URL I believe, could probably get away with 255.

Quote from: Vekseid on February 17, 2009, 03:18:04 AM
I think you mean MEMORY there?

Fixed. Thanks!

Quote
Online is the current URL I believe, could probably get away with 255.

No. It tracks the area the action and the user agent to guard against session id hijacking via cookie theft. For instance, here's some examples:

a:1:{s:10:"USER_AGENT";s:48:"Opera/9.62 (X11; Linux i686; U; en) Presto/2.1.1";}
a:2:{s:5:"board";i:11;s:10:"USER_AGENT";s:90:"Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.6) Gecko/2009011913 Firefox/3.0.6";}
a:1:{s:10:"USER_AGENT";s:87:"Mozilla/5.0 (Windows; U; Windows NT 5.1; fi; rv:1.9.0.6) Gecko/2009011913 Firefox/3.0.6";}
a:3:{s:6:"action";s:7:"profile";s:1:"u";s:4:"3698";s:10:"USER_AGENT";s:127:"Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_5_6; en-us) AppleWebKit/525.27.1 (KHTML, like Gecko) Version/3.2.1 Safari/525.27.1";}


255 is way too short. 512 might be cutting it a bit close. The maximum length in my table is currently 322:

mysql> SELECT MAX( Length( url ) )  FROM `smf_log_online`;
+----------------------+
| MAX( Length( url ) ) |
+----------------------+
|                  322 |
+----------------------+
1 row in set (0.00 sec)


So you can only do the conversion to MEMORY with a varchar 512 or so, and you can only do varchars longer than 255 in MySQL 5.0.3 or later.

...did not know it was a serialized string -_-

Should probably instead roll that into a new item that goes over converting tinytext and some text columns to varchar in general.

Quote from: Vekseid on February 17, 2009, 03:58:31 AM
Should probably instead roll that into a new item that goes over converting tinytext and some text columns to varchar in general.

I suppose it belongs in both places. It's most useful here, but you need to do the conversion first, too.

I would like to try moving my attachments to a new folder but I don't really understand what you're telling me to do.
Would you be able to explain in "dummy" language?
Sheryl

Quote from: sheryltoo on February 17, 2009, 08:17:16 AM
I would like to try moving my attachments to a new folder but I don't really understand what you're telling me to do.
Would you be able to explain in "dummy" language?
Sheryl

To quote from Ben_S (http://www.simplemachines.org/community/index.php?topic=197938.msg1258761#msg1258761):

Quote
You can do this through Admin > Attachments and Avatars > Avatar Settings > Upload avatars to > Specific directory.

Naturally create the directory and make it writable.

To move existing avatars to the new directory, access index.php?action=manageattachments;sa=moveAvatars

Is that enough?

Quote from: Vekseid on February 16, 2009, 06:29:50 AM
4: Tune topics, members, and posts per page. In Posts and Topics -> Topic Settings. Setting this low can have a detrimental affect on your forum's raw activity, however, at least it did on mine. If people click more, they make more requests, after all, and get more frustrated. I use fifty topics per page and twenty-five posts per page, which seems to be close to ideal. Disabling participation icons will also reduce load a slight bit, but some may find this annoying.

In my experience the less you have per page, the less the resource usage, in fact in the past when I was having a lot of traffic, I used to reduce mine from 40 to 20 in order to cope with the influx.

Yes, you will have more page views to contend with with lower figures, but you will also be manipulating less data with each page view.

QuoteTo move existing avatars to the new directory, access index.php?action=manageattachments;sa=moveAvatars

This is what I don't understand.
Sheryl

Quote from: sheryltoo on February 17, 2009, 09:01:22 AM

QuoteTo move existing avatars to the new directory, access index.php?action=manageattachments;sa=moveAvatars

This is what I don't understand.
Sheryl

Click this: http://daysofourlives.sherylspage.com/index.php?action=manageattachments;sa=moveAvatars

I get this error when going there:

The attachments upload directory is not writable. Your attachment or avatar cannot be saved.

My attachment directory is set to 777 so I'm  not sure why I'd get that error.
Maybe it has something to do with the fact that my site seems to be one big mess at the moment.
Sheryl

The new directory that you created for the avatars has to be writable (777), too.

I think I give up. Either I'm not understanding what needs to be done or the problems I'm having with my site are effecting how this works.

Here's what I did.
I created a folder in my root directory for smf for the attachments, then changed the path to attachments to read:
/home/site/public_html/root/attach and set the permissions to all 7's.
Under Avatar settings I changed it to say "Upload avatars to specific directory" and put this new path on both the lines.
Then I went to the area that you linked me to but don't see anything that says I can move avatars, however, I did copy all the avatars that were in the folder on my domain into the new folder but that didn't help.
I clicked a maintenance button and it said it found 24 images that were no longer there so I clicked fix or something and it deleted them all.
Before I did this, I had 36 avatars in the orginial attachment folder on my domain.

And I signed into my site with a non management account and tried to upload an avatar. It seemed to upload ok. No errors, however all I see is a red x so something isn't right.
Either I goofed up or my site isn't working right.
Sheryl

When you moved the avatar files, it doesn't automatically update the database. That's what that link does :)

What did you set for the avatars URL? Something like http://daysofourlives.sherylspage.com/attach ?

There's an emergency power outage in my building. It's a transformer problem. No ETA on power return. UPS won't last long. I'll be back later.

Quote from: Ben_S on February 17, 2009, 08:58:53 AM
In my experience the less you have per page, the less the resource usage, in fact in the past when I was having a lot of traffic, I used to reduce mine from 40 to 20 in order to cope with the influx.

Yes, you will have more page views to contend with with lower figures, but you will also be manipulating less data with each page view.

Actually, my page views and forum activity went up when I raised the figures, is what I was referring to.

Quote from: sheryltoo on February 17, 2009, 09:22:36 AM
I get this error when going there:

The attachments upload directory is not writable. Your attachment or avatar cannot be saved.

My attachment directory is set to 777 so I'm  not sure why I'd get that error.
Maybe it has something to do with the fact that my site seems to be one big mess at the moment.
Sheryl

http://daysofourlives.sherylspage.com/attach/

There are no files here, so it wasn't able to upload. Are you sure -that- directory is set to 777?

Seeing how nothing seemed like it was working, I put the avatars back in the attachment folder and I think I've set everything back to the default settings as far as where to upload attachments.
One of the reasons I thought I'd do this is because no one's attachment was showing up  after I edited some files and no one could upload any more attachments even though the permission is set to 777.

Like I said, I think there's some issues going on with my site and I'm trying to get that straightened out. I think I need to do that first before I try to make any more changes but thanks for trying to help me.
Maybe I'll be back if I ever get my site working right again.
Sheryl

A VERY helpful guide this is. I made a few changes that were refered to here and have already noticed a 50% (approx) improvement, thanks :)

If you have a lot of attachments, this change can greatly lower SMF's CPU usage.

This modification disables theme loading for attachments. It's a simple tweak for a big boost.

In Load.php, copy this:

// The following determines the user agent (browser) as best it can.
$context['browser'] = array(
'is_opera' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera') !== false,
'is_opera6' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 6') !== false,
'is_opera7' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 7') !== false || strpos($_SERVER['HTTP_USER_AGENT'], 'Opera/7') !== false,
'is_opera8' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 8') !== false || strpos($_SERVER['HTTP_USER_AGENT'], 'Opera/8') !== false,
'is_ie4' => strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 4') !== false && strpos($_SERVER['HTTP_USER_AGENT'], 'WebTV') === false,
'is_safari' => strpos($_SERVER['HTTP_USER_AGENT'], 'Safari') !== false,
'is_mac_ie' => strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5.') !== false && strpos($_SERVER['HTTP_USER_AGENT'], 'Mac') !== false,
'is_web_tv' => strpos($_SERVER['HTTP_USER_AGENT'], 'WebTV') !== false,
'is_konqueror' => strpos($_SERVER['HTTP_USER_AGENT'], 'Konqueror') !== false,
'is_firefox' => strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox') !== false,
'is_firefox1' => strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox/1.') !== false,
'is_firefox2' => strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox/2.') !== false,
);

$context['browser']['is_gecko'] = strpos($_SERVER['HTTP_USER_AGENT'], 'Gecko') !== false && !$context['browser']['is_safari'] && !$context['browser']['is_konqueror'];

// Internet Explorer 5 and 6 are often "emulated".
$context['browser']['is_ie7'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 7') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];
$context['browser']['is_ie6'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 6') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];
$context['browser']['is_ie5.5'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5.5') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];
$context['browser']['is_ie5'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5.0') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];

$context['browser']['is_ie'] = $context['browser']['is_ie4'] || $context['browser']['is_ie5'] || $context['browser']['is_ie5.5'] || $context['browser']['is_ie6'] || $context['browser']['is_ie7'];
$context['browser']['needs_size_fix'] = ($context['browser']['is_ie5'] || $context['browser']['is_ie5.5'] || $context['browser']['is_ie4'] || $context['browser']['is_opera6']) && strpos($_SERVER['HTTP_USER_AGENT'], 'Mac') === false;

to a new function, detectBrowser(), in Load.php. Don't forget to include global $context; at the top.

I replaced this in index.php:

// Load the current theme.  (note that ?theme=1 will also work, may be used for guest theming.)
loadTheme();

with

// Load the current theme.  (note that ?theme=1 will also work, may be used for guest theming.)
if (! (isset($_REQUEST['action']) && $_REQUEST['action'] == 'dlattach'))
loadTheme();
else
detectBrowser();


On my system, it reduced my CPU usage by 14%. I do about 4 downloads for every 1 page view.

I do about 800,000 attachments & thumbnail downloads per day, so it really added up.

I want to move the avatar from attachements, but i have enabled the "Encrypt stored filenames" option, and I want to ask so i'll be sure:
If I will go to mysiteforumurl/index.php?action=manageattachments;sa=moveAvatars every avatar jpg will decrypt and everything will be allright, or this option is only available when "Encrypt stored filenames" is disabled?

It should, yes.

Very weird. I tried to go to myforumurl/index.php?action=manageattachments;sa=moveAvatars, and it got me on the first page without doing nothing :o I have created a folder, i specified in avatars settings to use that folder to put avatar, new avatars go there (so it is chmod correctly) but old one doesn't move when i specify that hidden function.

This hidden future is only in SMF 1 series? Because i have 2.0 RC1 :-?


Later edit:
From what I see sintax for actions/url in smf 2 is different froms smf 1. So maybe the function exists but it is a different URL for SMF 2.

For example, to see somebody's pots in SMF1 the link would be like this:
forum_url/index.php?action=profile;u=1422;sa=showPosts

but for SMF 2 the link is this:
forum_url/index.php?action=profile;u=1422;area=showposts;sa=messages

I'm not sure about that function in SMF 2. I haven't looked. It was never a documented feature ;)

I figured it out....
In manageattachements.php i searched for moveavatars and saw this:
void MoveAvatars()
      - move avatars from or to the attachment directory.
      - called from the maintenance screen by
       index.php?action=admin;area=manageattachments;sa=moveAvatars

So maybe you want to edit your first post so that SMF 2.0 Users can use it too. Everythink worked like a charm, it asked for my password and then went to file maintanance. I checked the folder in ftp and it was full with moved avatars.

Thank you for your support!


P.S. it is documented in smf function pages: http://support.simplemachines.org/function_db/index.php?action=view_function;id=89

  The myISAM > innoDB conversion is recommended for all forums, including small or medium?
I have a forum with 2500 members, 40 members online at the same time average. They seems to publish ~1post/5minutes, 25,000topics, 70,000posts, not big load there, but there is a lot of searching. Every member use the search heavily.
The forum is fast (after optimization, bandwidth lows from over 1 GB/day to 500MB/day), but I'm just looking to make it even faster. I'm on VPS, so I can modify mySQL characteristics. It would be recommended to go with innoDB on some tables? Thank you.

(https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fi40.tinypic.com%2Fkb8200.jpg&hash=167ec764033e9c0b37a3b2b884e471e4a8ce1064)

Quote from: exxocet on February 24, 2009, 12:47:06 AM
  The myISAM > innoDB conversion is recommended for all forums, including small or medium?
I have a forum with 2500 members, 40 members online at the same time average. They seems to publish ~1post/5minutes, 25,000topics, 70,000posts, not big load there, but there is a lot of searching. Every member use the search heavily.
The forum is fast (after optimization, bandwidth lows from over 1 GB/day to 500MB/day), but I'm just looking to make it even faster. I'm on VPS, so I can modify mySQL characteristics. It would be recommended to go with innoDB on some tables? Thank you.

Your forum is big enough that you would likely benefit from switching to InnoDB.

You may wish to use Sphinx for search, if you're getting a lot of search activity. Search around the forums here.

  Thank you Phalloidium,
Indeed, I forgot to tell that I consider about using Sphinx, but i'm not sure if it's fit for me, but your answer helped there too. Just one more question, Sphinx will change the search alghorithm? I like SMF kind of searching, per instance if you search for termA termB it would first display the results containg both terms. On Invision and phpBB it's a completely mess on search, even if you search two terms, it will dispaly results containg each of those terms, not both. I hate that. Will Sphinx stick with the SMF search?
  In conversion innoDB tutorial that's nothing about smf_log_search_words table (available only for index search)? It should be converted too? Thank you very much.

If you install Sphinx, and then don't like it, you can easily change your search mechanism back in the Admin area.

  Oh, it will have an instance in Admin panel, that's great! OK, I've done the innoDB conversion and increased innodb_buffer_pool_size to 64MB. Also convertsmf_log_floodcontrol to Memory. It was fast and smooth.
  I didn't touched the smf_log_search_words, I guess it's more fit for myISAM, but of course, my knoledge is limited here. Thank you very much.

You may also wish to change the settings of two variables in my.cnf:

innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT

The first will make InnoDB much faster under heavy writes. It's possible to lose the last couple seconds of updates in a crash, but it's no worse than using MyISAM. The second will bypass Linux's filesystem cache. It's faster, and especially useful in low-memory situations like on a VPS.

  That's great, thank you Phalloidium, I'll do it right now. Just one small issue, I guess you're aware of this: it's normal as all modifications to have only [Uninstall] option (no active/inactive or delete) after the database conversion or I've done something wrong?

Quote from: exxocet on February 24, 2009, 04:35:16 AM
Just one small issue, I guess you're aware of this: it's normal as all modifications to have only [Uninstall] option (no active/inactive or delete) after the database conversion or I've done something wrong?

I do believe that's normal.

Memory usage goes to 72.23% (284020k), it doubled! With myISAM it was ~40%... whao, pretty much...

Quote from: exxocet on February 24, 2009, 05:35:34 AM
Memory usage goes to 72.23% (284020k), it doubled! With myISAM it was ~40%... whao, pretty much...

Well you increased the InnoDB buffer pool size by 56 MB. InnoDB does use more RAM, but it's faster.

Oh yes, I forgot, you're right. It's OK then, I thought something wrong with the database. Thank you.

log_search_words is changed pretty often (SMF automatically update the index) , but some people seems to convert it to innoDB, some choosed to stick with myISAM. Moreover, SMF 2.0 log_search_words is innoDB by defualt (am I wrong?). Don't know, but won't get faster results (avoid table blocking while indexing) with innoDB?
Thank you .

I run InnoDB or MEMORY for everything. My forums run pretty fast ;)

thanks krab

I am having this error:

mysql> ALTER TABLE `smf_boards` ADD INDEX ( `ID_CAT` ), DROP PRIMARY KEY, ADD PRIMARY KEY ( `boardOrder` , `ID_BOARD` ), ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key


can you guys help me ?

Do it like this then:


ALTER TABLE `smf_boards` ADD INDEX ( `ID_CAT` );
ALTER TABLE `smf_boards` DROP PRIMARY KEY, ADD PRIMARY KEY ( `boardOrder` , `ID_BOARD` );
ALTER TABLE `smf_boards` ENGINE=InnoDB;


Should work. Note the column names are different in SMF 2. That could be your problem also.

mysql> ALTER TABLE `smf_boards` ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key



did the first two steps properly, but when tried the one aboved it failed. Using SMF 1.1.8 here

Hmm, odd. I have to run to work right now though :(

I've updated the change code for smf_boards, making it more normalized and fixing the key/autoincrement situation, and giving the code for 2.0 : )

Finally put together the bit about converting away from text/tinytext >_>

Quote from: Vekseid on March 07, 2009, 09:40:00 PM
Finally put together the bit about converting away from text/tinytext >_>

The changes would go much faster if you combined all the ALTER statements for the same tables together. Just append the CHANGE .. NOT NULL bits, separated by commas. Each time you add/delete/change a column, MySQL rewrites the entire table. Might as well do it just once.

Quote from: shumilica on February 23, 2009, 03:37:56 PM
I figured it out....
In manageattachements.php i searched for moveavatars and saw this:
void MoveAvatars()
      - move avatars from or to the attachment directory.
      - called from the maintenance screen by
       index.php?action=admin;area=manageattachments;sa=moveAvatars

So maybe you want to edit your first post so that SMF 2.0 Users can use it too.

Hey, thanks for finding that, shumilica  :) I wasn't sure how I was going to go about fixing them all, since the other didn't work, of course. Glad I decided to read through to see if it was just me. Really would be good to add the info to the main post for 2.0 users  ;D

And thanks Vekseid for the great info all in one spot

Were would one find these options to disable them?

Quote6: Don't use post moderation on an active forum. Where active is over a thousand posts a day or so. Apparently the query is rather slow, but it is not difficult to have a self policing forum.

7: Disable the recent posts feature on the board index. Let people click a link if they want to see more than the last post they have access to, compiling that list is expensive especially if you have a lot of groups set up. Also I find it rather ugly, myself.

perhaps im just an idiot...  explain it to me and assume Im an idiot  ;)

Quote from: amlucent on March 10, 2009, 05:56:26 PM
Were would one find these options to disable them?

Quote6: Don't use post moderation on an active forum. Where active is over a thousand posts a day or so. Apparently the query is rather slow, but it is not difficult to have a self policing forum.

6 is only in SMF 2.0.

7, I believe, is in the theme settings.
7: Disable the recent posts feature on the board index. Let people click a link if they want to see more than the last post they have access to, compiling that list is expensive especially if you have a lot of groups set up. Also I find it rather ugly, myself.

perhaps im just an idiot...  explain it to me and assume Im an idiot  ;)

Thanks Phalloidium!  at least I know where to look for these options now.

btw I am going to try iweb based on your recommendation.. it cant be worse than bluehost.

I'm very happy with them. They're excellent to deal with on all levels. I haven't tried their shared hosting, but their dedicated service is awesome.

My own experience, don't set to InnoDB your smf_log_search_words (if you're using Custom Index, of course). Indexing won't be done properly under InnoDB.

At least that's my case, don't know if it's just me, but I doubt.

Interesting. I gave up on a custom index and went to Sphinx.

Quote from: Vekseid on February 16, 2009, 06:29:50 AM
7: Disable the recent posts feature on the board index. Let people click a link if they want to see more than the last post they have access to, compiling that list is expensive especially if you have a lot of groups set up. Also I find it rather ugly, myself.

I have been looking for the place to change this and can't find it. Are you talking about the Forum Stats box? Is there a way to remove that?

Edited, it is in your theme's settings - just set it to zero to disable it : )

Quote from: mariafgp on March 30, 2009, 03:41:05 AM

Quote from: Vekseid on February 16, 2009, 06:29:50 AM
7: Disable the recent posts feature on the board index. Let people click a link if they want to see more than the last post they have access to, compiling that list is expensive especially if you have a lot of groups set up. Also I find it rather ugly, myself.

I have been looking for the place to change this and can't find it. Are you talking about the Forum Stats box? Is there a way to remove that?

If you do most of the other things in the list, the Recent Posts feature is very quick. I know a lot of my members use it all day long.

This isn't disabling the recent posts feature itself, but rather the part on the index.

On #1 Move your uploaded avatars directory

When I go to index.php?action=manageattachments;sa=moveAvatars, it keeps redirecting me to the action=manageattachments;sa=maintenance page.

When I look at the code in the ManageAttachments.php file, the last line of the funtion reads:

if (!empty($updatedAvatars))
db_query("
UPDATE {$db_prefix}attachments
SET attachmentType = 1
WHERE ID_ATTACH IN (" . implode(', ', $updatedAvatars) . ')', __FILE__, __LINE__);

redirectexit('action=manageattachments;sa=maintenance');


Other code in the function gives an error message if the new directory isn't writable.   I'm not getting any error.
Which implies to me, that the db query is not finding any avatars to move.  But the File Maintenance page lists 149 avatars in the attachments directory.

Any suggestions?

great tips, thanks... :)

Quote from: mweed on April 07, 2009, 12:57:56 AM
Other code in the function gives an error message if the new directory isn't writable.   I'm not getting any error.
Which implies to me, that the db query is not finding any avatars to move.  But the File Maintenance page lists 149 avatars in the attachments directory.

Any suggestions?

They're still considered to be attachments, even if they are only in your specified avatar upload directory.

Yes, they are still attachments, but when I access the page that is supposed to move them from the standard attachment directory to my new avatar directory, it does not move them, it jumps straight to the redirect page.  So, even though I have followed the directions in suggestion #1, I get no benefit because nothing is moved.

QuoteE: run crontab -e and add this line:
35  4  *   *   *     /home/userdir/scripts/backup.smf

now i need some clarification on this, i have attached how my cron job center looks,

now my Q is, should i just put in the url of the script  in the run area ?

and set run time below ?

Command to run would simply be the path to and name of your script - it would not be a url.

Just highlight the equivalent runtime - minute 35, hour 4, every day, every month, every weekday. Or whenever you wish to run it.

Quote from: mweed on April 07, 2009, 09:56:50 PM
Yes, they are still attachments, but when I access the page that is supposed to move them from the standard attachment directory to my new avatar directory, it does not move them, it jumps straight to the redirect page.  So, even though I have followed the directions in suggestion #1, I get no benefit because nothing is moved.

Err, sorry. I know this command basically only works once - I'd need to have/see more details : /

Quote from: mweed on April 07, 2009, 09:56:50 PM
Yes, they are still attachments, but when I access the page that is supposed to move them from the standard attachment directory to my new avatar directory, it does not move them, it jumps straight to the redirect page.  So, even though I have followed the directions in suggestion #1, I get no benefit because nothing is moved.

i used this step as well, and also got redirected, to att mgmt page , but it moved the files to the new folder properly, and no issues wid the function as well,

chk your dir if the movement has taken place( new one)

and thanks vek, i did it , it worked well thanks for the tips, i am hoping u ll give some more

Good Post  8)

Added a couple more, about controlling mod_expires and scheduled tasks in SMF 2.0

Don't truncate your log tables. All of your posts will appear to be unread. One of the best features of SMF is being able to see what you've read no matter how old. Next, not all of those tables will benefit from InnoDB. In fact, some might be worse of as it.

Some of these changes I have been pushing for since before SMF was released. Then there are those things which make a huge difference - turn on caching, enable an opcode cache, drop the mods that you installed because someone, at some point, might be interested in using them.

Also, note that for 99% of forums out there, most of these recommendations are moot. Either the performance gains are minimal (possibly even negative) or they won't affect the overall performance of their hosts' server.

If you are running on a shared host and you are trying to squeeze out fractional gains from SMF, you are looking in the wrong place. Your host probably has more cause to your site running slow than SMF. Don't get me wrong with that, SMF can be slow, but it is the highest level in a pyramid of performance concerns.

Here's a way to speed up stats in SMF 1.1: http://www.simplemachines.org/community/index.php?topic=289961.0

Quote from: Vekseid on May 03, 2009, 07:37:15 PM
Added a couple more, about controlling mod_expires and scheduled tasks in SMF 2.0

I copied and pasted your entire piece there just like it is into my httpd.conf file  to get started and when I tried to restart Apache I got this.

Failed to start apache :

Starting httpd2 (prefork) Syntax error on line 244 of /etc/apache2/httpd.conf:
'ExpiresDefault 3600': bad expires code, unrecognised <base> '3600'

The command line was:
/usr/sbin/httpd2-prefork -f /etc/apache2/httpd.conf

[1A..failed

It appears it doesn't like 3600 for the expires code.

Thanks

EDIT: I did some digging and changed it to ExpiresDefault A7200 for 2 hours and Apache starts now with that section in the conf file. I'll see how it goes.

I had to change the avatar's custom path to 777 in order for the move to be successful. Does it stay 777?

'doh, sorry. I guess that was an Apache 2.2 trick, fixed : /

Making the avatar directory world-writable - yes, if you are running mod-php you will have to.

Trimmed reference:

Quote from: Vekseid on February 16, 2009, 06:29:50 AM

Code Select Expand


ALTER TABLE `smf_members` CHANGE `member_ip` `member_ip` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_members` CHANGE `member_ip2` `member_ip2` VARCHAR(255) NOT NULL;


Code Select Expand


ALTER TABLE `smf_messages` CHANGE `poster_ip` `poster_ip` VARCHAR(255) NOT NULL;


Is there a particular reason these shouldn't be VARCHAR(15) instead, or possibly VARCHAR(39) to support IPv6?
My first thought was that maybe one or more of those is used to store multiple IPs, but I checked by ordering those tables like "LENGTH(`member_ip`) DESC" and there were only single IPs listed for each of them.

Nice tips... Your tip #1 (The thing about the avatars) inspired me to create a modification to increase the efficiency of avatar loading in SMF. :D Now to wait :P

Quote from: Brettflan on June 23, 2009, 06:55:52 AM
Trimmed reference:

Quote from: Vekseid on February 16, 2009, 06:29:50 AM

Code Select Expand


ALTER TABLE `smf_members` CHANGE `member_ip` `member_ip` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_members` CHANGE `member_ip2` `member_ip2` VARCHAR(255) NOT NULL;


Code Select Expand


ALTER TABLE `smf_messages` CHANGE `poster_ip` `poster_ip` VARCHAR(255) NOT NULL;


Is there a particular reason these shouldn't be VARCHAR(15) instead, or possibly VARCHAR(39) to support IPv6?
My first thought was that maybe one or more of those is used to store multiple IPs, but I checked by ordering those tables like "LENGTH(`member_ip`) DESC" and there were only single IPs listed for each of them.

It was a mass search and replace on the schema. >_>

Ideally, they would be BINARY(16) entries. I've actually written some basic code to handle strings coming in as IPv4 and IPv6, converting them to and from 128-bit addresses on the fly. There's no real efficiency savings, however, for VARCHAR(15) over VARCHAR(255).

Added disabling template evals for people who have opcode caches installed, as well as limiting thread replies.

I'm doubtless going to sound rather dim, but how exactly does one run the php script to reset inactive users last read pointers?  Do I copy it into my site somewhere and then run it from my browser?  Load it as a cron job?

Grazie

Cal

Quote from: Cal O'Shaw on July 02, 2009, 02:11:53 AM
copy it into my site somewhere and then run it from my browser

Yes :) Just place it in the same folder your forum installations root index.php is placed,
and point your browser to it.

Quote from: Vekseid on June 29, 2009, 04:42:48 AM

Quote from: Brettflan on June 23, 2009, 06:55:52 AM
Trimmed reference:

Quote from: Vekseid on February 16, 2009, 06:29:50 AM

Code Select Expand


ALTER TABLE `smf_members` CHANGE `member_ip` `member_ip` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_members` CHANGE `member_ip2` `member_ip2` VARCHAR(255) NOT NULL;


Code Select Expand


ALTER TABLE `smf_messages` CHANGE `poster_ip` `poster_ip` VARCHAR(255) NOT NULL;


Is there a particular reason these shouldn't be VARCHAR(15) instead, or possibly VARCHAR(39) to support IPv6?
My first thought was that maybe one or more of those is used to store multiple IPs, but I checked by ordering those tables like "LENGTH(`member_ip`) DESC" and there were only single IPs listed for each of them.

It was a mass search and replace on the schema. >_>

Ideally, they would be BINARY(16) entries. I've actually written some basic code to handle strings coming in as IPv4 and IPv6, converting them to and from 128-bit addresses on the fly. There's no real efficiency savings, however, for VARCHAR(15) over VARCHAR(255).

There is, but only if using MEMORY tables or temporary tables (not temporary disk tables). Because MySQL doesn't know the full width of the table, and tables in memory are stored with fixed row width, it must assign the maximum width for the VARCHAR column(s).

Thanks for your basic tips. Great Post!!

Random tip: you can add this mod (http://custom.simplemachines.org/mods/index.php?mod=2054) to simplify the disable template eval step.

Here's what should be a fairly simple question.
I run my own server.

Windows OS (https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fredneckfabrication.com%2FSmileys%2Fclassic%2Fuglystupid2.gif&hash=dabd826fd768a979406391fc2b2bcd3f8d32321e)
Apache
MySQL
PHP
SMF
hMailServer

I currently have two drives.

C:/ runs the OS, Apache, MySQL, PHP, hMailServer, and the Apache wwwroot folder.
D:/ runs the Page File and is my backup storage.

The question is, what should I put on each drive to get the best performance out of this machine?

Quote from: ∑£ℓ¢†®¡¢ §ợų¡đ on August 28, 2009, 10:39:42 PM
Here's what should be a fairly simple question.
I run my own server.

Windows OS (https://www.simplemachines.org/community/proxy.php?request=http%3A%2F%2Fredneckfabrication.com%2FSmileys%2Fclassic%2Fuglystupid2.gif&hash=dabd826fd768a979406391fc2b2bcd3f8d32321e)
Apache
MySQL
PHP
SMF
hMailServer

I currently have two drives.

C:/ runs the OS, Apache, MySQL, PHP, hMailServer, and the Apache wwwroot folder.
D:/ runs the Page File and is my backup storage.

The question is, what should I put on each drive to get the best performance out of this machine?

I'm going to assume a few things:
* MySQL is tuned appropriately -- in other words, it has enough RAM that most reads are cached.
* You have enough memory that you don't really use swap
* The server mostly runs just SMF

If this is the case, the major source of disk usage/seeking will be two things:
* Database writes
* HTTP log writes

Thus, you want those two on different drives.

MySQL delays is where you'll notice the most latency. And the web logs are almost always file-appends, so the impact of the web logs is much smaller than MySQL. Also, you shouldn't be swapping much. Keeping that in mind, I'd do it like this:
C:\ - Everything, including the page file, except
D:\ - MySQL

Quote from: Mark Rose on August 28, 2009, 10:48:36 PM

I'm going to assume a few things:
* MySQL is tuned appropriately -- in other words, it has enough RAM that most reads are cached.
* You have enough memory that you don't really use swap
* The server mostly runs just SMF

MySQL tuned
Somewhat. I'm still working on that, but because I'm not a MySQL expert, tuning it scares the $#!+ out of me. If I break it, I'm not skilled enough to know what I did wrong, yet.

I looked into the RAM last night and it has 178M for the cache. Out of 2G of available RAM, I probably should give it a little more.


System Memory
This server machine was never intended to be a server (from the manufacturer of the mobo).
It's more like a gaming machine that I made into a server with the best stuff I had in the house.

It has 2G of RAM. I would add more, but simply cannot afford to do so.


Page File
As far as what "process manager" says, most of the time, only 200MB of Page File is used.
I have pushed it up to almost 1G, but that was while watching Hulu through the server :P


Server Usage
You are correct. Running SMF is the primary function of this machine.

I do run SETI (BOINC) at a 15% load on RAM and CPU during slow times.
The primary reason for BOINC is in case the server is ever stolen, it will give me their IP address if they ever connect it to the internet.
I also support the collective use of CPU time for science projects, because I'm a geek at heart.

You can see my LAMP guide for a basic overview of optimizing MySQL.

Once MySQL is configured nicely, the biggest speed improvement would be a custom php compile.

Quote from: Vekseid on August 29, 2009, 03:02:10 PM
You can see my LAMP guide for a basic overview of optimizing MySQL.

Once MySQL is configured nicely, the biggest speed improvement would be a custom php compile.

@Vekseid
OK, that seems impressive.
How hard do you think it would be for me to implement your MySQL settings to my server?
I mean, beside the obvious need to change the file locations, is it as easy as a copy and paste of the my.ini? (or as it is on your machine, my.cnf)

Quote from: Mark Rose on August 28, 2009, 10:48:36 PM

I'd do it like this:
C:\ - Everything, including the page file, except
D:\ - MySQL

@Mark Rose
OK, so it will be done. Thanks for the tip!!

How would I achieve this?
Is it as easy as copying and pasting the entire MySQL folder over to drive D:\ and changing any reference to where MySQL resides from C:\ to D:\ ?
Or am I going to have to reinstall it completely to get it to hook up correctly?

Of course I would do a full backup first as a just in case.

EDIT - MySQLadministrator (http://dev.mysql.com/downloads/gui-tools/5.0.html) made it just way too easy.
All I had to do was copy the MySQL\data folder to drive D:\ and then get into MySQLadmin (http://dev.mysql.com/downloads/gui-tools/5.0.html) and tell it where the new data directory was. (simply change C:\ to D:\)

Quote from: ∑£ℓ¢†®¡¢ §ợų¡đ on August 30, 2009, 12:55:37 PM
@Vekseid
OK, that seems impressive.
How hard do you think it would be for me to implement your MySQL settings to my server?
I mean, beside the obvious need to change the file locations, is it as easy as a copy and paste of the my.ini? (or as it is on your machine, my.cnf)

I would seriously read through the settings and give a  thought as to which ones to implement. Your needs won't be the same as mine.

Okay, let me apologize for some very very newb'ish questions. I am trying to accomplish #1 (move your uploaded avatar directory).

My domain is http://y2chaos.com
My forum is located at http://y2chaos.com/forums/index.php

This is what I did so far.

Using the ftp address of my site, I went and created the following directory

http://y2chaos.com/docs/forums/avs/

I then went to my admin panel and to Attachments and Avatars and changed everything to following

Avatars directory:     /home/content/m/i/s/mistabinks02/html/forums/avatars
Avatars URL:            http://y2chaos.com/forums/avatars

Upload directory:     http://y2chaos.com/docs/forums/avs/
Upload URL:             /forums/avs

I clicked save and then "Upload directory:" turned bold and the font turned red.

I then typed the following address

http://y2chaos.com/forums/index.php?action=manageattachments;sa=moveAvatars

I received the following error message:

QuoteThe attachments upload directory is not writable. Your attachment or avatar cannot be saved.

That is where I am at right now. I am a complete smf newb. I tried to research this through the forums and docs but could not figure it out on my own. I know I may have to change some settings and/or permissions but I do not know how to do that. Any one care to walk a newb through this?

Set the permissions on the directory to 777 (read, write, execute for owner, group, everyone).

Quote from: Mark Rose on September 08, 2009, 08:45:00 PM
Set the permissions on the directory to 777 (read, write, execute for owner, group, everyone).

I know this sounds incredibly newb'ish but how do I do that? At the ftp site, I clicked properties, and checked permissions for owner, group, all users. Is there a different way to do this or what is the correct way to change the permissions? Also, do I just to do it to avs, or do I also have to do it forums, and docs?

Thanks in advance.

Quote from: Y2Chaos on September 08, 2009, 08:51:30 PM

Quote from: Mark Rose on September 08, 2009, 08:45:00 PM
Set the permissions on the directory to 777 (read, write, execute for owner, group, everyone).

I know this sounds incredibly newb'ish but how do I do that? At the ftp site, I clicked properties, and checked permissions for owner, group, all users. Is there a different way to do this or what is the correct way to change the permissions? Also, do I just to do it to avs, or do I also have to do it forums, and docs?

Thanks in advance.

Just make sure the read, write, and execute bit are checked for owner, group, and all users.  You only have to do that for avs.

Quote from: Mark Rose on September 08, 2009, 09:02:07 PM

Just make sure the read, write, and execute bit are checked for owner, group, and all users.  You only have to do that for avs.

I may be doing something wrong. At the ftp site, I right clicked avs, selected properties, and checked read, write, execute for owner, group, all users.

I still get the following error message

The attachments upload directory is not writable. Your attachment or avatar cannot be saved.

Quote from: Y2Chaos on September 08, 2009, 09:55:17 PM

Quote from: Mark Rose on September 08, 2009, 09:02:07 PM

Just make sure the read, write, and execute bit are checked for owner, group, and all users.  You only have to do that for avs.

I may be doing something wrong. At the ftp site, I right clicked avs, selected properties, and checked read, write, execute for owner, group, all users.

I still get the following error message

The attachments upload directory is not writable. Your attachment or avatar cannot be saved.

Make sure you have the directory correctly specified in smf -- if it doesn't exist, you'll get an error like that.

If you go back to the last post on page 5, you can see how I currently have my Avatar and Attachments settings. Did I do something wrong there?

Quote from: Y2Chaos on September 08, 2009, 08:42:38 PM
Upload directory:     http://y2chaos.com/docs/forums/avs/
Upload URL:             /forums/avs

These are backwards. :)

Upload directory:      /forums/avs
Upload URL:             http://y2chaos.com/docs/forums/avs/

Okay, I changed it to the above. Is that the way it should be? By chance, should it be docs/forums/avs in the upload directory? Sorry for being such a pest. I changed it to the above and still got that error message.

Try /home/content/m/i/s/mistabinks02/html/forums/avs
/forums/avs probably doesn't exist on your server ;)

Quote from: ☯ Mark Rose on February 21, 2009, 05:30:19 AM
If you have a lot of attachments, this change can greatly lower SMF's CPU usage.

This modification disables theme loading for attachments. It's a simple tweak for a big boost.

In Load.php, copy this:

Code Select Expand


// The following determines the user agent (browser) as best it can.
$context['browser'] = array(
'is_opera' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera') !== false,
'is_opera6' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 6') !== false,
'is_opera7' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 7') !== false || strpos($_SERVER['HTTP_USER_AGENT'], 'Opera/7') !== false,
'is_opera8' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 8') !== false || strpos($_SERVER['HTTP_USER_AGENT'], 'Opera/8') !== false,
'is_ie4' => strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 4') !== false && strpos($_SERVER['HTTP_USER_AGENT'], 'WebTV') === false,
'is_safari' => strpos($_SERVER['HTTP_USER_AGENT'], 'Safari') !== false,
'is_mac_ie' => strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5.') !== false && strpos($_SERVER['HTTP_USER_AGENT'], 'Mac') !== false,
'is_web_tv' => strpos($_SERVER['HTTP_USER_AGENT'], 'WebTV') !== false,
'is_konqueror' => strpos($_SERVER['HTTP_USER_AGENT'], 'Konqueror') !== false,
'is_firefox' => strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox') !== false,
'is_firefox1' => strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox/1.') !== false,
'is_firefox2' => strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox/2.') !== false,
);

$context['browser']['is_gecko'] = strpos($_SERVER['HTTP_USER_AGENT'], 'Gecko') !== false && !$context['browser']['is_safari'] && !$context['browser']['is_konqueror'];

// Internet Explorer 5 and 6 are often "emulated".
$context['browser']['is_ie7'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 7') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];
$context['browser']['is_ie6'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 6') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];
$context['browser']['is_ie5.5'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5.5') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];
$context['browser']['is_ie5'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5.0') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];

$context['browser']['is_ie'] = $context['browser']['is_ie4'] || $context['browser']['is_ie5'] || $context['browser']['is_ie5.5'] || $context['browser']['is_ie6'] || $context['browser']['is_ie7'];
$context['browser']['needs_size_fix'] = ($context['browser']['is_ie5'] || $context['browser']['is_ie5.5'] || $context['browser']['is_ie4'] || $context['browser']['is_opera6']) && strpos($_SERVER['HTTP_USER_AGENT'], 'Mac') === false;

to a new function, detectBrowser(), in Load.php. Don't forget to include global $context; at the top.

I replaced this in index.php:

Code Select Expand


// Load the current theme.  (note that ?theme=1 will also work, may be used for guest theming.)
loadTheme();

with

Code Select Expand


// Load the current theme.  (note that ?theme=1 will also work, may be used for guest theming.)
if (! (isset($_REQUEST['action']) && $_REQUEST['action'] == 'dlattach'))
loadTheme();
else
detectBrowser();


On my system, it reduced my CPU usage by 14%. I do about 4 downloads for every 1 page view.

I do about 800,000 attachments & thumbnail downloads per day, so it really added up.

Is this able to work with SMF 1.1.10?

Quote from: ∑£ℓ¢†®¡¢ §ợų¡đ on September 10, 2009, 10:46:04 PM

Quote from: ☯ Mark Rose on February 21, 2009, 05:30:19 AM
If you have a lot of attachments, this change can greatly lower SMF's CPU usage.

This modification disables theme loading for attachments. It's a simple tweak for a big boost.

In Load.php, copy this:

Code Select Expand


// The following determines the user agent (browser) as best it can.
$context['browser'] = array(
'is_opera' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera') !== false,
'is_opera6' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 6') !== false,
'is_opera7' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 7') !== false || strpos($_SERVER['HTTP_USER_AGENT'], 'Opera/7') !== false,
'is_opera8' => strpos($_SERVER['HTTP_USER_AGENT'], 'Opera 8') !== false || strpos($_SERVER['HTTP_USER_AGENT'], 'Opera/8') !== false,
'is_ie4' => strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 4') !== false && strpos($_SERVER['HTTP_USER_AGENT'], 'WebTV') === false,
'is_safari' => strpos($_SERVER['HTTP_USER_AGENT'], 'Safari') !== false,
'is_mac_ie' => strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5.') !== false && strpos($_SERVER['HTTP_USER_AGENT'], 'Mac') !== false,
'is_web_tv' => strpos($_SERVER['HTTP_USER_AGENT'], 'WebTV') !== false,
'is_konqueror' => strpos($_SERVER['HTTP_USER_AGENT'], 'Konqueror') !== false,
'is_firefox' => strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox') !== false,
'is_firefox1' => strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox/1.') !== false,
'is_firefox2' => strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox/2.') !== false,
);

$context['browser']['is_gecko'] = strpos($_SERVER['HTTP_USER_AGENT'], 'Gecko') !== false && !$context['browser']['is_safari'] && !$context['browser']['is_konqueror'];

// Internet Explorer 5 and 6 are often "emulated".
$context['browser']['is_ie7'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 7') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];
$context['browser']['is_ie6'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 6') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];
$context['browser']['is_ie5.5'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5.5') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];
$context['browser']['is_ie5'] = strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE 5.0') !== false && !$context['browser']['is_opera'] && !$context['browser']['is_gecko'] && !$context['browser']['is_web_tv'];

$context['browser']['is_ie'] = $context['browser']['is_ie4'] || $context['browser']['is_ie5'] || $context['browser']['is_ie5.5'] || $context['browser']['is_ie6'] || $context['browser']['is_ie7'];
$context['browser']['needs_size_fix'] = ($context['browser']['is_ie5'] || $context['browser']['is_ie5.5'] || $context['browser']['is_ie4'] || $context['browser']['is_opera6']) && strpos($_SERVER['HTTP_USER_AGENT'], 'Mac') === false;

to a new function, detectBrowser(), in Load.php. Don't forget to include global $context; at the top.

I replaced this in index.php:

Code Select Expand


// Load the current theme.  (note that ?theme=1 will also work, may be used for guest theming.)
loadTheme();

with

Code Select Expand


// Load the current theme.  (note that ?theme=1 will also work, may be used for guest theming.)
if (! (isset($_REQUEST['action']) && $_REQUEST['action'] == 'dlattach'))
loadTheme();
else
detectBrowser();


On my system, it reduced my CPU usage by 14%. I do about 4 downloads for every 1 page view.

I do about 800,000 attachments & thumbnail downloads per day, so it really added up.

Is this able to work with SMF 1.1.10?

It should.

Of course, only people who can figure out the code should try doing it... so make the change at your own risk :)

Quote
Of course, only people who can figure out the code should try doing it... so make the change at your own risk :)

Oh, thanks. Nothing easy here, eh?

I'll fumble my way through it. That's how I learn.
If I screw it up, there is always the backup file.
LOL, one advantage of running my own server is the backup is as fast as a copy and paste :P

------------------------------------------------------------------------

I've been going through the MySQL optimization stuff back on page 2. For some reason I cannot get MySQL to restart after adding innodb_flush_method=O_DIRECT to my.ini (Windows)
Is there any special reason this won't work?

Quote from: ∑£ℓ¢†®¡¢ §ợų¡đ on September 10, 2009, 11:35:18 PM

Quote
Of course, only people who can figure out the code should try doing it... so make the change at your own risk :)

Oh, thanks. Nothing easy here, eh?

It's just to scare people away from messing with things they don't know enough to fix if they screw up :)

Quote
I've been going through the MySQL optimization stuff back on page 2. For some reason I cannot get MySQL to restart after adding innodb_flush_method=O_DIRECT to my.ini (Windows)
Is there any special reason this won't work?

Yeah, Windows doesn't support that flush method. :)

Quote from: Mark Rose on September 10, 2009, 11:55:15 PM

It's just to scare people away from messing with things they don't know enough to fix if they screw up :)

OK, that was easy...
How do I tell if it's working?

... and does it matter where in Load I add the function, or should it be in a certain place?
Any idea if TinyPortal 0.9.8 will have an issue with it?

No, you can put the function anywhere AFAIK provided the call is correctly placed.

Quote from: ∑£ℓ¢†®¡¢ §ợų¡đ on September 11, 2009, 10:16:39 AM

Quote from: Mark Rose on September 10, 2009, 11:55:15 PM

It's just to scare people away from messing with things they don't know enough to fix if they screw up :)

OK, that was easy...
How do I tell if it's working?

Your CPU usage will lower significantly if:
a) You have a lot of attachments
b) You haven't specified a custom avatar directory

If your attachment thumbnails are loading correctly, it's working :)

Quote from: ∑£ℓ¢†®¡¢ §ợų¡đ on September 11, 2009, 10:24:11 AM
... and does it matter where in Load I add the function, or should it be in a certain place?
Any idea if TinyPortal 0.9.8 will have an issue with it?

TinyPortal shouldn't have any issues with it. It only matters when you are showing attachment thumbnails, and don't have a custom attachment directory.

Quote from: Arantor on September 11, 2009, 10:31:42 AM
No, you can put the function anywhere AFAIK provided the call is correctly placed.

Correct. As long as the function is loaded before it's called, you're good. :)

I'm not sure why, but with coded in, TPortal.php breaks.
With every attachment, I get undefined index errors for 'is_admin' and a few others in TPortal.php

If I remove this function detectBrowser(), all is well in the error log.

Creating the function was a snap, but fixing TP to play nice is outside of the scope of my abilities right now, so I may have to save this for another time.

I know that TP does do it's own browser detection, so I'm not sure if it's somehow in conflict.

Oh, and the attachment thumbs loaded properly.
LOL, if I don't get an error on the first try, it makes me wonder if it's even working :P

Yea, I tried to rename it detectBrowswer2() but that throws the same errors.

I wonder why it is using so much CPU on a bunch of simple strpos calls.

It's not.

What it's doing is saying is that if it isn't an attachment, load the theme, otherwise just load the browsers (which is part of the attachment download requirements)

Though I thought you still had to load browser type anyway for most themes?

Woops, that makes a lot more sense. Yeah, you need to add detectbrowser() somewhere

Quote from: groundup on September 11, 2009, 12:46:16 PM
I wonder why it is using so much CPU on a bunch of simple strpos calls.

SMF? It's likely the parse_bbc function. Each strpos call is small, but when you do 10,000 of them, it's a lot.

I thought groundup was referring to the strpos calls in detectBrowser().

I was wondering more about the detecting of the browser but then Arantor made me realize I wasn't reading it correctly.

Yeah, the detect browser function is pretty lightweight.

Loading the theme isn't. It parses the language files and does a bunch of other crap that have no use when sending out a thumbnail. That's where the savings comes in.

Any chance this can get packed up for people like me who can do a little php but aren't ready to risk a site by hacking away with a blunt cleaver?

We don't allow uploads of attachments or avatars, so I don't know if this will help us, but we've done most of the other steps here and have cut CPU much lower (only over on some days) but memory remains over every day.

Grazie

Quote from: Cal O'Shaw on September 11, 2009, 04:44:56 PM
Any chance this can get packed up for people like me who can do a little php but aren't ready to risk a site by hacking away with a blunt cleaver?

We don't allow uploads of attachments or avatars, so I don't know if this will help us, but we've done most of the other steps here and have cut CPU much lower (only over on some days) but memory remains over every day.

Grazie

It won't help you then.

The problem with packing it up as a mod is simple: not every step can be carried out by every site admin, due to host configuration or some other restriction. Nor is every step appropriate for every site admin, so unfortunately it has to be done as a serious of complex steps *sigh*

Plus if you do it by hand you will learn a little something about the process.

I was doubtful, but figured it didn't hurt to ask.

I have learned a lot already from you folks and really appreciate it.

Grazie!

Quote from: Arantor on September 11, 2009, 04:47:53 PM
The problem with packing it up as a mod is simple: not every step can be carried out by every site admin, due to host configuration or some other restriction. Nor is every step appropriate for every site admin, so unfortunately it has to be done as a serious of complex steps *sigh*

Plus if you do it by hand you will learn a little something about the process.

This could easily be packaged as a mod, but:
1) it might break other things, like TinyPortal (apparently)
2) it wasn't thoroughly tested.

I misread the question; I was thinking of all of the 22 things in this thread, not this one tweak.

So what if it breaks another mod? The mod is for SMF, not TP :P

A lot of users use TP and expect things not to break, especially when you'll have to manually uninstall it when it does break - because I've noticed that users won't generally read the mod pages for little things like "OH NOES DONT USE THIS MOD WITH [A.N.OTHER MOD]" even if it's in big letters.

Quote from: groundup on September 11, 2009, 05:16:51 PM
So what if it breaks another mod? The mod is for SMF, not TP :P

It's one thing to make it so another mod doesn't apply. It's another to change core functionality inside SMF. A patch could easily be written that doesn't change the functionality, but I didn't need it. Another reason why I didn't release a mod -- my solution is only half baked.

Question, if i try to convert tables to InnoDB, get rid of text tables etc, is it safe to go back if that doesnt work? Another thing, that doesnt make upgrades etc harder in future? And if that faster, why it isnt default?

edit: i really wanna try that in my site http://hoitajat.net . 213181 messages, 9889 topics, 3953 members.
innodb_buffer_pool_size is  134217728, so its enough? Its 128M, right?


http://hoitajat.net/status.php

Yes, that's enough.

Getting rid of tinytext will only make your forum faster. There's no other issues with it. You could convert it back, but I have no idea why you'd want to.

Converting to InnoDB is reversible.

Neither will make upgrades harder.

TinyTexts are gone in 2.0. Not all systems support InnoDB, so SMF goes with MyISAM by default. And MyISAM is usually faster for small, unbusy boards.

Also, make sure you have innodb_flush_log_at_trx_commit=0 set in my.cnf for best InnoDB performance. And if it's a linux box, set innodb_flush_method=O_DIRECT.

Quote from: Mark Rose on September 27, 2009, 01:16:14 PM
TinyTexts are gone in 2.0.

Well, im using it :)

QuoteAlso, make sure you have innodb_flush_log_at_trx_commit=0 set in my.cnf for best InnoDB performance. And if it's a linux box, set innodb_flush_method=O_DIRECT.

Hmmm, I dont have acces to that file, im in shared host.
Details
http://hoitajat.net/status.php

innodb_flush_method
innodb_flush_log_at_trx_commit:  1
Well, its "1". Is it wise to convert?

Tinytext is gone from RC2 onwards, and it's possible to convert to it readily enough.

Quote from: Owdy on September 27, 2009, 01:27:59 PM

Quote from: Mark Rose on September 27, 2009, 01:16:14 PM
TinyTexts are gone in 2.0.

Well, im using it :)

Well, they'll be gone in RC2 then :)

QuoteHmmm, I dont have acces to that file, im in shared host.
Details
http://hoitajat.net/status.php

Code Select Expand

innodb_flush_method
innodb_flush_log_at_trx_commit:  1
Well, its "1". Is it wise to convert?

Yes, I would still convert, but I wouldn't convert all your tables, just the suggested ones. You'll get a benefit still: some page loads will be slower, but you shouldn't get dramatic slowdowns that can happen with MyISAM.

Quote from: Mark Rose on September 27, 2009, 01:37:06 PM
Yes, I would still convert, but I wouldn't convert all your tables, just the suggested ones. You'll get a benefit still: some page loads will be slower, but you shouldn't get dramatic slowdowns that can happen with MyISAM.

Ok, thanks. I try with those in first post.

QuoteCREATE TABLE `smf_log_floodcontrol` (
  `ip` char(16) NOT NULL default '',
  `log_time` int(10) unsigned NOT NULL default '0',
  `log_type` varchar(8) NOT NULL default 'post',
  PRIMARY KEY  (`ip`,`log_type`)
) ENGINE=MEMORY;

That table allready exist in my database.


CREATE TABLE `log_floodcontrol` (
  `ip` char(16) NOT NULL default '',
  `log_time` int(10) unsigned NOT NULL default '0',
  `log_type` varchar(8) NOT NULL default 'post',
  PRIMARY KEY  (`ip`,`log_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

So i just delete that one and create new? Or change engine?

I don't believe you can change engine without dropping the table, but I'm not sure.

Quote from: Arantor on September 27, 2009, 02:01:40 PM
I don't believe you can change engine without dropping the table, but I'm not sure.

Yes i dropped.

Well, seems this makes my site run slower :( Load times are sometimes 1 second.

Quote from: Owdy on September 27, 2009, 02:18:02 PM
Well, seems this makes my site run slower :( Load times are sometimes 1 second.

It's the innodb_flush_log_at_trx_commit=0 setting.

I would convert back for now.

Ok, i restore backup.

edit: that tinytext convert should be okay, i do just that.

Hi I don't have enough time to read all the pages but can I ask what is the new trick that was added in these topic. As I remember it was only 21 before now its 22. :) is it the Disabling of Eval?

It might be.

There's even a handy mod for it now to make it a checkbox in your admin panel - Disable Template Eval (http://custom.simplemachines.org/mods/index.php?mod=2054)

Quote from: Arantor on September 28, 2009, 08:34:45 PM
It might be.

There's even a handy mod for it now to make it a checkbox in your admin panel - Disable Template Eval (http://custom.simplemachines.org/mods/index.php?mod=2054)

Thanks

QuoteAddition #2: Control your Expires Header - This is often done in server configurations but can also be done through .htaccess. Note that your provider needs to have mod expires on in order for this to function. This can drastically cut your forum's bandwidth needs.

# Turn it on
ExpiresActive On

# Set a default expiry time. One hour is fine, but a day or longer may be appropriate.
ExpiresDefault A3600

# Turn expiry off for dynamic content (or potentially dynamic content).
ExpiresByType application/x-httpd-php A0
ExpiresByType application/x-python-code A0
ExpiresByType text/html A0

<FilesMatch "\.(php|py|pyc|pyo)$">
  ExpiresActive Off
</FilesMatch>

How can i look if this on or off? Im shared host so i dont have acces to config files. And what that does actually do? Files are are downloaded less frecuently or something?

Browsers receive a piece of information as to when a page 'expires', i.e. when the browser would need to go back and get it again if it has changed.

That page says, if you've seen a file that isn't HTML, PHP or Python (like an image), it won't change for the next hour. Meaning that for the next hour the browser shouldn't be requesting the images again - less page loads.

thanks for that some awesome tips in there

Hello,

We have a forum of almost 400,000 posts. Just recently we started hitting an issue regarding long page load times. I ended up turning off a feature which has helped performance tremendously.

Admin -- > Features and OPtions --> Track daily statistics    ** Unchecked ***

Just thought I would mention how much it has helped us.

I have tried to join the Big Boards club but always get denied so I have to discover all these things by accident, hard research here or just trial and error.

Hi chep, has it been long that you last asked for access? Just so you know - you are not too far away from the limit of posts that is normally required for the BB board access.

I should probably include that, many of us just like our stats, a lot >_>

Just convert the log_* tables to InnoDB and the stats issue should go away, if you've done the other InnoDB optimizations.

Added a couple - not really as notable, but some may find them useful.

I'm thinking about making a server and security sections, but not sure if those should be their own topic given the size of the topic.

Btw, 2.0 RC2 has the tinytext -> varchar conversion in, and for the disable template eval step, there's also a handy GUI item added from Disable Template Eval (http://custom.simplemachines.org/mods/index.php?mod=2054).

Hi,

If you don't mind the suggestion, when you add or adjust suggestions in the lead post, could you indicate the date the changes or additions are made.  It would make it a lot easier to locate the new tips.

Grazie mille!

Cal

Good call, Cal.

Btw, point 11 - there is now a GUI for this in 2.0 RC2.

Quote from: Cal O'Shaw on November 10, 2009, 12:39:09 PM
Hi,

If you don't mind the suggestion, when you add or adjust suggestions in the lead post, could you indicate the date the changes or additions are made.  It would make it a lot easier to locate the new tips.

Grazie mille!

Cal

Sorry, went through and added addition dates.

Quote from: Vekseid on November 17, 2009, 02:15:34 PM

Quote from: Cal O'Shaw on November 10, 2009, 12:39:09 PM
Hi,

If you don't mind the suggestion, when you add or adjust suggestions in the lead post, could you indicate the date the changes or additions are made.  It would make it a lot easier to locate the new tips.

Grazie mille!

Cal

Sorry, went through and added addition dates.

Thanks now its easier to check which one is new :)

I did the INNODB conversion last night, as well as the flood control table to memory on a pretty large and active forum.  I was expecting the INNODB conversion to take MUCH longer to complete. Most were under 15 seconds though. 

The only adverse reaction could of been avoided by omitting
DELETE FROM smf_log_mark_read;     Considering the speed the other tables were converted, I'm thinking this would have gone pretty fast as well.

Either way, thanks for the info, it really has improved performance noticeably.

Quote from: Dewed on November 21, 2009, 01:25:53 PM
I did the INNODB conversion last night, as well as the flood control table to memory on a pretty large and active forum.  I was expecting the INNODB conversion to take MUCH longer to complete. Most were under 15 seconds though. 

The only adverse reaction could of been avoided by omitting
DELETE FROM smf_log_mark_read;     Considering the speed the other tables were converted, I'm thinking this would have gone pretty fast as well.

On a big forum, that table will be several gigabytes in size, and will take a significant amount of time to convert. All the other tables are small in comparison.

Personally, I don't think it's cool to run "DELETE FROM smf_log_mark_read;" and wouldn't put it in the instructions. It's a great annoyance to the users.

And do be sure to adjust your innodb_buffer_pool_size as mentioned above.

Vekseid,

Can you please add this post (http://www.simplemachines.org/community/index.php?topic=336278.0) to the first post? It contains all the queries needed to convert the TinyTexts in SMF 1.1.

Thanks to Keeper for doing the hard work.

Dones : )

Hey guys
I am trying to do #1
When I get to the part where I am supposed to access index.php?action=manageattachments;sa=moveAvatars , I get redirected to index.php?action=manageattachments;sa=maintenance . So how can I move the avatars over to the new directory?

Thanks!

It should have done it already when you did that.

This is a good writeup with the informative and experienced details. I should not take much time thinking about this and will tell my circle of friends to go through this ASAP. Thanks really to compile such valuable information in a sensible manner :)

I
Sorry for my english

I read with attention the first post of this page: Get rid of Text tables.

Actualy, I am on SMF 2.0 RC1.2. In my sql log, I can read many long querries and I wonder how I can make desapear this one :
(

# Query_time: 167 Lock_time: 0 Rows_sent: 20000 Rows_examined: 356822
SELECT t.id_topic, t.num_replies, t.num_views, t.id_board,
m.subject, IFNULL(mem.real_name, m.poster_name) as poster, b.name,
m.poster_time as first_time, mes.poster_time
FROM smf_topics as t
INNER JOIN smf_messages as m ON (m.id_msg = t.id_first_msg)
INNER JOIN smf_boards as b ON (b.id_board = t.id_board)
INNER JOIN smf_messages as mes ON (mes.id_msg = t.id_last_msg)
LEFT JOIN smf_members as mem ON (mem.id_member = t.id_member_started)
WHERE (FIND_IN_SET(-1, b.member_groups))
ORDER BY m.poster_time DESC
LIMIT 0, 20000;


I also have many errors (95% of all my errors) like this one. How can i do to make it desepear ?

# Query_time: 2  Lock_time: 0  Rows_sent: 20000  Rows_examined: 356862
SELECT t.id_topic, t.num_replies, t.num_views, t.id_board,
      m.subject, IFNULL(mem.real_name, m.poster_name) as poster, b.name,
      m.poster_time as first_time, mes.poster_time
      FROM smf_topics as t
         INNER JOIN smf_messages as m ON (m.id_msg = t.id_first_msg)
         INNER JOIN smf_boards as b ON (b.id_board = t.id_board)
         INNER JOIN smf_messages as mes ON (mes.id_msg = t.id_last_msg)
         LEFT JOIN smf_members as mem ON (mem.id_member = t.id_member_started)
      WHERE (FIND_IN_SET(-1, b.member_groups))
      ORDER BY m.poster_time DESC
      LIMIT 0, 20000;

Upgrade to RC2. A lot of slow queries were improved.

Not sure what these have to do with Staff mod probably best is make a new post in the SMF 2.0 forum.

Hi,

I juste fllowed some of your recommandations and it seems to work. My forum loads faster.

Thank you very much !

Quote from: Vekseid on February 16, 2009, 06:29:50 AMIf your webserver compresses the type of data you use to serve your webpages, you should specifically disable this, as the webserver can better handle it.

This seems to be a wording mistake is it? Problems understanding it anyway.

No, it's saying disable SMF's own compressed output if the webserver does it instead.

would it be an idea to use pbxt storage engine as innodb replacement (?)

After moving the Avatars to the New Avatars Upload Dir, the function attachDirStatus() which counts the files in the attachment dir doesnt make sense, because it gets the whole amount of files, (Attachments and avatars) and then it gives the status Files Missing in the Status Field from the manageattachment Section.

I described my experience here:
http://www.simplemachines.org/community/index.php?topic=373728.msg2562257#msg2562257

So the Function who gives attachDirStatus() the estimated files count should decrease the amount with the amount of the moved avatars.

PetB

Thanks. Bookmarked for later...

May 26th, 2010 Note - I recently made an attempt at converting my main forum's personal messages, shoutbox messages, and messages tables to InnoDB. This was a mistake. I've kept the messages table converted - it reduces locking incidents - but *nix in general does a pretty good job of caching disk reads for MyISAM tables. If all that happens is insert and delete, don't fret over it.

Thanks very much, many of your suggestions were missed by me and have now been implemented. Always happy to optimize in any way I can. Also looking forward to the intermediate suggestions when my forum's big enough to really need a vps or dedicated host.

Bookmarked.

thanks for sharing

thanz

Thank you, noted : )

An easy way to gain additional speed when users post a message is to install the mbstring extension for php. (compile flag --enable-mbstring) as this means SMF does not have to use PHP code to do UTF-8 translations but rather can do it with a C extension.

Profiling showed that 70 utf8_strtoupper() calls are have been replaced with mb_strtoupper() reducing the execution time from 41,268 microseconds to 529 microseconds and reducing memory usage from 535 KB memory usage to 1 KB memory usage.

In my test this was 16.6% of total execution time removed when posting. Quite possibly larger for you since I am using a networked server for MySQL so I have network overhead for queries which makes them take longer.

In your language files - remove $txt['lang_capitalize_dates'] or comment it out... the entire line.

I really think the best you can do is to use optimizations of your server environment:

- with Xcache/memcache (PHP modules),
- mod_deflate (Apache modules, reduce bandwidth)
- and of course increasing of cache lifespan for static resources.

Any other optimizations are weak compared to these 3 major things.

And I almost forgot the database side: remember to launch, after one or two weeks of load, the MySQL optimization script, found here:

http://www.joehayes.org/optimizing-mysql-for-dummies.html

Quote from: lastnico on August 18, 2010, 08:32:34 AM
Any other optimizations are weak compared to these 3 major things.

Those you mentioned are important and has already been mentioned but you'd be foolish to consider everything else weak. SMF caching isn't extremely good and while an opcode cache will provide faster PHP overall the code still has to run. A lot of these improvements such as specifying a custom avatar directory will help much more due to how SMF works logging views of uploaded files.

Something as simple as what I specified in my previous post removed 43 ms run time, that's a lot of time on busy sites, so don't discount every other method as irrelevant.

That mbstring is a great tip wish i could skip some of that uft8 stuff completed since i do not use it.

How to improve server speed (not related to the smf script):
if you have lots of small thumbs, avatars, format the data partition for your content  as EXT4 and use small blocks, 512 for example. This could speed up your hard disk performance

Quote from: qtime on September 19, 2010, 01:43:05 PM
How to improve server speed (not related to the smf script):
if you have lots of small thumbs, avatars, format the data partition for your content  as EXT4 and use small blocks, 512 for example. This could speed up your hard disk performance

That doesn't make sense to me. If you use 512 byte blocks, instead of the usual 4k, you'll end up doing far more seeking to find all the pieces. Not just for avatars and thumbs, but everything else, like your database files, server logs, and program files. Furthermore, Linux handles memory in 4k chunks (on x86/x86-64 at least) so you'll only decrease performance when paging files (swapping). Not only that, but by using blocks 1/8th the size, you'll 8-tuple your inode usage, making file lookups take more time, plus you'll consume more memory as EXT4 caches the disk structure in memory. In short, unless you have a tiny disk (like in an embedded system) this is a really bad idea for performance.

Did you measure an improvement when you did this?

Very good tips indeed. I like it.

Can anyone give me the right mysql query to convert all my smf_log_* to innoDB I can convert them one by one but :) I am feeling lazy right now :)

Quote from: S-M-F Labs on September 27, 2010, 10:10:03 PM
Can anyone give me the right mysql query to convert all my smf_log_* to innoDB I can convert them one by one but :) I am feeling lazy right now :)

See the first post of this thread.

Owwww..! This one's awesome..! I will try doing these on my forum as well...

Thank You So Much creator...

This is such a great recommendation. I can save a lot of money not to move to another hosting (more expensive one).

#5, how do I change from small to large?

Using SMF 1.1.11

I carefully followed the directions to change the directory for my several thousand avatars.

Not sure if this matters, but on the top I have:
Avatars directory:/home/myaccount/public_html/forum/avatars
Avatars url: http://mywebsite/form/avs


On the bottom:
Upload avatars to... Specific Directory
Upload Directory: /home/myaccount/public_html/forum/avs
Upload URL: /home/myaccount/public_html/forums/avs

The AVS directory is set to 777

I ran the update move attachements. All of my old Avatars are in the AVS folder.

But now uploading an avatar fails, and all the avatars on the site are no longer linked - every user has the dreaded red X. Many of them are very upset.

SO what did I do wrong?

When I look at what URL SMF is looking for to display a URL it shows: http://mywebsite.com/home/myaccount/public_html/forum/avs/avatar_3514.png

Which is wrong. It should be looking in: http://mywebsite.com/forum/avs/avatar_3514.png

the upload url should actually be a url not a local path, so it begins with http and points to the same folder - if the url to /.../public_html/forum/ is http://domain.com/forum/, the url to the avatars would be http://domain.com/forum/avs/

wouldn't having a unix server be an obvious way to make smf faster?

Not necessarily. PHP and SMF runs fine on Windows or Solaris, probably also Mac. The platform overhead is small in all cases, so Unix, Windows, Solaris, it's pretty even.

Quote from: Ensiferous on November 27, 2010, 10:47:15 AM
The platform overhead is small in all cases, so Unix, Windows, Solaris, it's pretty even.

I would argue that Windows adds more overhead because the GUI is always running and taking up memory. The same can be said of Solaris (which boots X by default), or even Linux if X is ran at boot. Usually, however, Linux servers don't run X. Also, Windows Server 2008 and beyond support Server Core, which loads minimal services. In practice, this mode is rarely used as it is more difficult to manage due to the way it works. Maybe in the future, it will become more *NIX-like. Also, Server Core still loads the GUI, just none of the userland things like windows explorer. I believe on login you just get a GUI cmd.exe window.

By default Windows servers are logged out so the GUI is minimal and only the required processes are running. That said, you are completely right, Windows does add more overhead relative to Linux and Solaris. My point is that seen in the grand scheme of things your OS is going to be far less than 1% of your total load, your actual application is going to take up the majority of time and switching to a different OS would not present a significant change in performance.

The primary reason for using *nix over Windows is, perhaps ironically, simplicity, rather than any significant performance advantage. Windows does tend to lag behind a bit in terms of 'state of the art', which can be a factor - e.g. nginx for Windows is only in beta.

Nginx for Windows is not really in Beta, it's just that Nginx is event based which means it uses the system calls: epoll() (Linux), kqueue() (FreeBSD) or select() (Global). Of these 3 Windows only supports select() - which is slow as hell.

Windows has it's own method of dealing with event based applications and Nginx does not have support for this, and as far as I know it's not on the immediate road map. That said, Nginx on Windows is stable enough, it's not going to randomly crash so it really cannot be considered beta.

Quote3: Enable (or disable) gzip compression. This is located in Server Settings -> Feature Configuration. This has the greatest benefit when combined with #1, particularly for dialup users.

Note: Your webserver configuration may render this irrelevant. If your webserver compresses the type of data you use to serve your webpages, you should specifically disable this, as the webserver can better handle it.

How could one check whether webserver compress the type of data or not?

Anyone :)

I could type all the things that this site (http://www.mydigitallife.info/2010/04/17/how-to-test-and-check-or-verify-gzip-page-compression-on-website/) says, but going there would be much easier :)

THANKS

hi, happy new year!

Using SMF 1.1.12; custom theme, many mods including tinyportal 1.rc1 and pretty url's rc5

I like Flavious on previous page, followed the directions to change the directory for our avatars.

on the top of avatar settings I have:
Avatars directory:/home/myaccount/htdocs/avatars
Avatars url: http://www.mywebsite.com/avs


On the bottom:
Upload avatars to... Specific Directory
Upload Directory: /home/myaccount/htdocs/avs
Upload URL: http://www.mywebsite.com/avs

The AVS directory is set to 777

I ran the url move attachments. All of my old Avatars are in the AVS folder.

But now  all the avatars on the site are no longer linked - every user has a blank
eek

SO what did I do wrong?

When I look at an avatar in the browse files section of the avatar section (avatars), i see it linking with a double slash as http://www.mywebsite.com//avatar_46.png

whereas http://www.mywebsite.com/avs/avatar_46.png shows the avatar

any ideas please

many thx

folks are out to lynch me!

several hours on i am wondering whether the double slash is caused by my running the "MoveAvatars" script twice, as what it does ? some 20 lines in,  is add a slash here
if (rename($filename, $modSettings['custom_avatar_dir'] . '/' . $row['filename']))

ref line 430 ManageAttachments.php

and if this is the case how could i correctly remove one of the slashes from the renamed dir path ?

any help appreciated

cheers

It looks like it's missing the "avs" in the path between the slashes rather than having an extra slash. Under Avatar Settings in the admin panel (Forum->Attachments and Avatars->Avatar Settings), at the bottom of the page should be the three options in question.
"Upload avatars to..." should of course be set to "Specific directory...". Make sure "Upload directory" and "Upload URL" both are correct, with the "avs" directory specified. It sounds like your "Upload URL" is incorrect.

thx brettflan,
just been back to check after a sleep and find that the upload url is blank, so i retyped and saved;   it accepts the url (which is the same as the avatar url at the top, and my admin avatar appears (so the url is correct), then i go to home page to check the members avatars - no joy  .. no avatars -- so i return to avatar setting, only to find the upload url field empty and admin avatar gone?

thx for your help

can i save the avatar_url directly via ftp (or in mysql with phpmyadmin)? and, if so, in which file would i look?
(looking in the tables:custom_avatar_url and avatar_url are both showing the path  correctly in smf settings)

the 'save bit of manageattachments.php i think is around here

// Only update these settings if they are not disabled by JavaScript.
if (empty($_POST['avatar_download_external']))
updateSettings(array(
'avatar_max_width_external' => empty($_POST['avatar_max_width_external']) ? 0 : (int) $_POST['avatar_max_width_external'],
'avatar_max_height_external' => empty($_POST['avatar_max_height_external']) ? 0 : (int) $_POST['avatar_max_height_external'],
'avatar_action_too_large' => $_POST['avatar_action_too_large'],
));

if (!empty($_POST['custom_avatar_enabled']))
updateSettings(array(
'custom_avatar_dir' => $_POST['custom_avatar_dir'],
'custom_avatar_url' => $_POST['custom_avatar_url'],
));

// Save the adjusted permissions.
save_inline_permissions(array('profile_server_avatar', 'profile_upload_avatar', 'profile_remote_avatar'));
}

init_inline_permissions(array('profile_server_avatar', 'profile_upload_avatar', 'profile_remote_avatar'), array(-1));

$context['valid_avatar_dir'] = is_dir($modSettings['avatar_directory']);
$context['valid_custom_avatar_dir'] = empty($modSettings['custom_avatar_enabled']) || (is_dir($modSettings['custom_avatar_dir']) && is_writable($modSettings['custom_avatar_dir']));
}


have also tried 'saving' in default theme, and chkmoded avs to 755 - no joy - so is back to 777, now 707

i also ran the mysql query suggested in http://www.simplemachines.org/community/index.php?topic=197938.0

UPDATE `smf_attachments` SET `attachmentType` = '1' WHERE `smf_attachments`.`ID_MEMBER` != 0

also tried repair_settings , where it wants me to use /avatars instead of /avs for the avatar url

am also wondering whether tinyportal is involved

have had to revert to an upload via attachments folder scenario, and request members get their own avatars back by choosing their avatars from the list in Profile; i copied the avs folder contents to avatars folder, and then screenshot the 'browse files (avatars)section of the avatar setting area, A-G, H-Q etc, so that they had their filename for their avatar.

posting this for any others who lose their member's avatars :)

i would still welcome any one's ideas btw

thx

Why did you have to revert?

sry-- i reverted back to my original settings, using the default avatar setup using the upload via attachments as opposed to specific directory

Yes, I know what you did. I want to know WHY you did it.

because i couldn't get the avs folder settings to work, or the browse files (avatar) to link correctly - they were //avatar45.png  instead of /avs/avatar45.png, and our members had lost their avatars -- scroll up for full description

thx for your interest

Which usually means you put a / at the end of the URL/folder combination when it didn't need one...

no  / in url
in fact -url won't save (although path in sql table shows correct path)

Quotei find that the upload url is blank, so i retyped and saved;   it accepts the url (which is the same as the avatar url at the top, and my admin avatar appears (so the url is correct), then i go to home page to check the members avatars - no joy  .. no avatars -- so i return to avatar setting, only to find the upload url field empty and admin avatar gone?

you need to scroll up further to see full story i think, and btw this forum here is still v difficult to read because of severe staccato scrolling problems (i am using firefox on a mac and did post on this some months ago)
will check in again with safari and report on that-->safari works fine

I think it has been entered incorrectly, and the 'disappearing' is either because of using an older template in the admin panel or a mod's broken it since it works just fine on a fresh install.

Quote from: stog on February 01, 2011, 12:51:20 PM
btw this forum here is still v difficult to read because of severe staccato scrolling problems (i am using firefox on a mac and did post on this some months ago)

This is usually due to having a zoom level set in Firefox, it causes stuttery scrolling. Go to View->Zoom->Reset in the top menu of Firefox, or alternately the keyboard shortcut Ctrl+0 (number zero, not letter O).

That works , just reset mine

t/y

and me thx, now excellent and fast
np with our own 1.1.12 forums so is this a 2 thing  ;)

re - empty avatar upload url field
the manageattachmentstemplate is the same as a fresh 1.1.12 one, but i agree something to do with many upgrades and mods! thx for looking

Quote from: stog on January 28, 2011, 04:42:16 AM
thx brettflan,
just been back to check after a sleep and find that the upload url is blank, so i retyped and saved;   it accepts the url (which is the same as the avatar url at the top, and my admin avatar appears (so the url is correct), then i go to home page to check the members avatars - no joy  .. no avatars -- so i return to avatar setting, only to find the upload url field empty and admin avatar gone?

thx for your help
also tried repair_settings , where it wants me to use /avatars instead of /avs for the avatar url

am also wondering whether tinyportal is involved

I too am having the exact same problem after upgrading my Tinyportal Mod. Something in this mod is causing the issue.

SMF 1.1.3
TinyPortal 1.0 RC1

Anyone have any ideals, i would rather not revert back to putting avatars in my attachment folder.

Thanks for sharing.

I found this topic very useful. I have followed most of it's suggestions.

I have a few questions about my forum that is being rebuilt. It's using SMF 2.0RC5, heavily modded(mostly template stuff)and has zero user's and traffic ATM. Goal is to have million posts, and around 500 online user's at a time in a year's time. I know that's high, but it's just a goal.

So I have to ask, about at what point do you think "there is too many boards", from a performance stand point. I'm currently trying to keep my boards to a minimum, but there are 38 boards already(only 12 visible from index). I need more boards, but I refuse to add any more at this point.

Also, about at what point should I be worried about too many "queries"? My site has no traffic, only me working on it. It's already using an average of 15 queries. A couple of months or maybe a year from now, if it says "50 queries", I should be concerned right? I do plan to add a portal and I know that will impact it a bit.

I'm just trying to start off on the right foot.

From a pure performance standpoint, too many is any more than you can safely do without. There's no right or wrong answer as to how many, though as I learned recently, one board with half a million topics in it brings its own performance issues.

38 boards isn't that high, and it certainly isn't likely to present a problem with a million posts. But if you don't have a decent proportion of content (say, 30-50 topics per board), you probably have too many anyway.

QuoteAlso, about at what point should I be worried about too many "queries"?

The number of queries per page is not scaled based on content particularly. Last week I had a test forum with 1.5m posts in 500k topics and never went above 16 queries per page. 50 queries is a high number to be using per page, and while a portal will push it up, most portals don't tend to edge it above 25 or so, which is generally fine per page.

Disabling the calendar will save you significant queries if it ever becomes an issue.

Quote from: Arantor on March 10, 2011, 07:23:41 PM
From a pure performance standpoint, too many is any more than you can safely do without. There's no right or wrong answer as to how many, though as I learned recently, one board with half a million topics in it brings its own performance issues.

38 boards isn't that high, and it certainly isn't likely to present a problem with a million posts. But if you don't have a decent proportion of content (say, 30-50 topics per board), you probably have too many anyway.

QuoteAlso, about at what point should I be worried about too many "queries"?

The number of queries per page is not scaled based on content particularly. Last week I had a test forum with 1.5m posts in 500k topics and never went above 16 queries per page. 50 queries is a high number to be using per page, and while a portal will push it up, most portals don't tend to edge it above 25 or so, which is generally fine per page.

Thanks. Good to know.

Quote from: «Mark» on March 10, 2011, 07:26:02 PM
Disabling the calendar will save you significant queries if it ever becomes an issue.

I seen that in the first post. I haven't disabled it as I plan to use it, but if my user's won't use it at least moderately, I will disable it.

I also got another concern, but this is unavoidable as it's needed for my site.

Does excessive membergroups cause any serious performance problems? I have around 105 membergroups, every membergroup will be used.

Only if you have lots of users in lots of different membergroups at the same time. How are you using the groups?

Then it may be a problem.

I have a script that auto-assigns a member by age, to a membergroup. Example, if you are 34 years old, you will be auto-assigned to membergroup "Age 34".

So, how many groups are users likely to be in at once?

It depends on the traffic. My site is under construction and is not yet active. It will build over time, but I'm hoping for million posts, 500 active user's online at once within the first year.

You haven't answered my question. How many different groups is a user likely to be in at once? I CANNOT ANSWER YOUR QUESTION WITHOUT THIS.

Quote from: Arantor on March 13, 2011, 01:16:13 PM
You haven't answered my question. How many different groups is a user likely to be in at once? I CANNOT ANSWER YOUR QUESTION WITHOUT THIS.

Geez

I misread your first post Arantor.

Minimum of 1, maximum of 7, maybe an average of 2 or 3. Not counting postgroup, which I don't care to have.

That shouldn't be a problem. Post group you can't remove without rewriting the table structure anyway.

Ok, thanks Arantor.

Keep up the good work.

Quote from: Xarcell on March 10, 2011, 07:19:37 PM
I found this topic very useful. I have followed most of it's suggestions.

I have a few questions about my forum that is being rebuilt. It's using SMF 2.0RC5, heavily modded(mostly template stuff)and has zero user's and traffic ATM. Goal is to have million posts, and around 500 online user's at a time in a year's time. I know that's high, but it's just a goal.

So I have to ask, about at what point do you think "there is too many boards", from a performance stand point. I'm currently trying to keep my boards to a minimum, but there are 38 boards already(only 12 visible from index). I need more boards, but I refuse to add any more at this point.

The log_mark_read and log_boards tables grow by users * boards. Ideally, these two should be combined, but SMF tends to keep all entries in these tables around, rather than e.g. pruning them for inactive members.

If the size of these two tables for users who are on-line over a short period becomes greater than your server can load into memory, you will have performance issues.

It's mostly a memory limit, however, so e.g. for Elliquiy, where I have eight gigabytes dedicated to the InnoDB buffer, it's not a big issue (though the total InnoDB data size has passed 17GB), and I can handle hundreds of boards and tens of thousands of members without issue. Hopefully some schema improvements in the version after 2.0 can take place, allowing more growth in that department.

Quote
Also, about at what point should I be worried about too many "queries"? My site has no traffic, only me working on it. It's already using an average of 15 queries. A couple of months or maybe a year from now, if it says "50 queries", I should be concerned right? I do plan to add a portal and I know that will impact it a bit.

This really depends on the type of queries. I've passed five thousand write queries per second while doing some updates. These were serial updates that required individualized processing, with little to no blocking involved as it was mostly updating old posts from one forum format to another.

If multiple updates are blocking each other, however, or if the query plan isn't so simple, you are going have a lower limit. Conversely, a high read load with a lot of memory can make reads effectively painless.

Same can be said for writes on a high-quality (SLC) SSD drive.

Right now for Elliquiy I tend to peak around 2,000-3,000 queries per second, with an average load over the course of a given day peaking around 500. This is with APC caching enabled at level 2. Most queries are for the ajax chat.

...ultimately, though? Worry about that point when you get to it, would be my advice. Right now your primary focus should be getting the membership to need hardware like that.

thansk. it is works

Can I move my avatars manually when doing that little avatar trick? I'm having a "can't write to directory" error when trying to apply the hidden option URL. Tried 755, 775 and 777 chmods, to no effect, and my paths are correct.

Is there a way to move them manually and not break the avatar links?

i won't recommand converting smf_log_activity to InnoDB format if innodb_flush_log_at_trx_commit is set to 1 (value of 0 is ok and 2 not tested yet).

Indeed the stat related update query is much slower in this case.
UPDATE smf_log_activity
SET
   hits = hits + 1
WHERE date = '2011-03-24'

0.11303592 seconds with InnoDB
0.00067019 seconds with InnoDB and innodb_flush_log_at_trx_commit = 0
0.00062704 seconds with MyISAM

Quote from: butch2k on March 24, 2011, 07:01:56 AM
i won't recommand converting smf_log_activity to InnoDB format if innodb_flush_log_at_trx_commit is set to 1 (value of 0 is ok and 2 not tested yet).

2 is safer/better than 0. But it doesn't really make a difference with SMF (it doesn't use transactions, so any crash is going to cause issues anyway).

If you have a battery-backed disk/raid controller, or SSD, leave it set to 1.

Yes I have a battery backed raid on my production server, but not on my preproduction server on which I noticed the issue. 
You lose ACID compliance but since the message table is not innodb it does not mater either...

Quote from: butch2k on March 24, 2011, 10:42:23 AM
Yes I have a battery backed raid on my production server, but not on my preproduction server on which I noticed the issue. 
You lose ACID compliance but since the message table is not innodb it does not mater either...

I converted all my tables to InnoDB (I use Sphinx for search, so I don't need fulltext search, which is bad for performance anyway). The only table that will cause issue with InnoDB is smf_boards (at least in SMF 1.1 for sure), because SMF relies on MyISAM's ability to store rows in a specified order (board sort order) where InnoDB always stores in primary key order. That can be fixed by changing the primary key of smf_boards, or just leave that one table as MyISAM.

I was referencing the original recommendation my SMF is full innodb on my production server.

Quote from: Vekseid on February 16, 2009, 06:29:50 AM

Code Select Expand

DROP TABLE smf_log_floodcontrol;
CREATE TABLE smf_log_floodcontrol (
  ip char(16) NOT NULL,
  logTime int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (ip),
  KEY logTime (logTime)
) ENGINE=MEMORY;


For SMF 2.0 the create statements for this is a bit different:

Code Select Expand


CREATE TABLE `smf_log_floodcontrol` (
  `ip` char(16) NOT NULL default '',
  `log_time` int(10) unsigned NOT NULL default '0',
  `log_type` varchar(8) NOT NULL default 'post',
  PRIMARY KEY  (`ip`,`log_type`)
) ENGINE=MEMORY;


Doesn't this table need to be dropped first in SMF 2.0 ?

Yes, it's sort of assumed when it just refers to the 'create statement' being different.

Quote from: Arantor on March 28, 2011, 06:58:45 PM
Yes, it's sort of assumed when it just refers to the 'create statement' being different.

Ok, just wanted to make sure :)

That's probably something that should be updated on the wiki.

QuoteFor most tables this is perfectly reasonable, but for smf_personal_messages and smf_messages, as well as similar "Post and forget" tables like a Shoutbox, this can actually be counterproductive to performance, particularly with a good-sized key_buffer and concurrent_insert = 2 in your my.cnf config.

I actually found that it was a requirement to convert smf_messages to InnoDB because I was getting a lot of table locking going on. Recently upgraded to 2.0 RC5. I would recommend you check your sql commands for locking on this particular table. Not sure why we got into that situation but once I made the change to InnoDB the problems dropped away and we got a slight increase in performance (faster page load times). We run a board with about 7500 members and 850,000 topics.

If your members do a lot of editing, then yes, InnoDB is the only solution.

This tutorial helped me alot..
I have applied changes to sql to change things to innodb as stated in the 1st topic.
It does seem to of made a difference :D

uses about 300mb more ram due to the pool_limit which I set to 128 but that cool I have 4gb

I also wanna convert the rest of my forum to innodb other than those that would NOT benefit.
I do have a large amount of mods and the main ones would obviously be the portal and other mods that possibly would benefit loading times. So please can some one help me in which would improve.. I am looking at ways to improve the speed and performance of my forums when its gets busy.

Here is a list of database tables

smf_admin_info_files
smf_aeva_albums
smf_aeva_comments
smf_aeva_fields
smf_aeva_field_data
smf_aeva_files
smf_aeva_log_media
smf_aeva_log_ratings
smf_aeva_media
smf_aeva_perms
smf_aeva_quotas
smf_aeva_settings
smf_aeva_variables
smf_affiliates
smf_affiliates_banners
smf_affiliates_rating
smf_affiliate_report
smf_ajaxchat_bans
smf_ajaxchat_invitations
smf_ajaxchat_messages
smf_ajaxchat_online
smf_ajaxshout_bans
smf_ajaxshout_invitations
smf_ajaxshout_messages
smf_ajaxshout_online
smf_approval_queue
smf_arcade_categories
smf_arcade_favorite
smf_arcade_games
smf_arcade_personalbest
smf_arcade_rates
smf_arcade_scores
smf_arcade_settings
smf_arcade_shouts
smf_arcade_tournament
smf_arcade_tournament_players
smf_arcade_tournament_rounds
smf_arcade_tournament_scores
smf_arcade_v3temp
smf_attachments
smf_awards
smf_awards_categories
smf_awards_members
smf_ban_groups
smf_ban_items
smf_battle_explore
smf_battle_graveyard
smf_battle_history
smf_battle_monsters
smf_battle_quest
smf_battle_quest_hist
smf_battle_shop
smf_battle_shouts
smf_boards
smf_board_permissions
smf_buddies
smf_calendar
smf_calendar_holidays
smf_categories
smf_cf_fields
smf_cf_forms
smf_collapsed_categories
smf_custom_fields
smf_down_cat
smf_down_catperm
smf_down_comment
smf_down_creport
smf_down_custom_field
smf_down_custom_field_data
smf_down_file
smf_down_groupquota
smf_down_rating
smf_down_report
smf_down_userquota
smf_feedbot
smf_feedbot_log
smf_group_moderators
smf_helpdesk_attachments
smf_helpdesk_log_action
smf_helpdesk_log_read
smf_helpdesk_tickets
smf_helpdesk_ticket_replies
smf_links
smf_links_cat
smf_links_catperm
smf_links_rating
smf_log_actions
smf_log_activity
smf_log_banned
smf_log_boards
smf_log_comments
smf_log_digest
smf_log_enotify_pms
smf_log_enotify_replies
smf_log_errors
smf_log_floodcontrol
smf_log_group_requests
smf_log_karma
smf_log_mark_read
smf_log_member_notices
smf_log_notify
smf_log_online
smf_log_packages
smf_log_polls
smf_log_reported
smf_log_reported_comments
smf_log_rssfeeds
smf_log_scheduled_tasks
smf_log_search_messages
smf_log_search_results
smf_log_search_subjects
smf_log_search_topics
smf_log_spider_hits
smf_log_spider_stats
smf_log_subscribed
smf_log_topics
smf_log_topic_view
smf_mail_queue
smf_membergroups
smf_members
smf_member_notes
smf_messages
smf_message_icons
smf_moderators
smf_openid_assoc
smf_package_servers
smf_permissions
smf_permission_profiles
smf_personal_messages
smf_picture_comments
smf_pm_attachments
smf_pm_recipients
smf_pm_rules
smf_polls
smf_poll_choices
smf_portamx_blocks
smf_portamx_settings
smf_post_drafts
smf_pretty_topic_urls
smf_pretty_urls_cache
smf_profile_albums
smf_profile_comments
smf_profile_pictures
smf_redirect_expire
smf_rssfeeds
smf_scheduled_tasks
smf_sessions
smf_settings
smf_shop_category
smf_shop_item
smf_shop_property
smf_shop_purchHis
smf_smileys
smf_spiders
smf_subscriptions
smf_talkbox
smf_talkbox_archive
smf_talkbox_banned
smf_talkbox_media
smf_team
smf_thank_you_post
smf_themes
smf_topics


I would appreciate and help given. Thank you for this tutorial.

QuoteALTER TABLE smf_members ENGINE = InnoDB
#1214 - The used table type doesn't support FULLTEXT indexes

Any suggestions?

Go to the search options in the admin panel, remove the fulltext index, then do it again. After, switch to a Custom index, or better, to Sphinx.

Quote from: Arantor on April 19, 2011, 05:34:48 PM
Go to the search options in the admin panel, remove the fulltext index, then do it again. After, switch to a Custom index, or better, to Sphinx.

Has been removed, the error remains...

Hang on, I read that as smf_messages not smf_members...

Why is there a fulltext index on the members table? You'll have to manually remove that in phpMyAdmin.

is it safe to convert smf_log_digest to innodb?


I just changed my smf_messages to innodb and my load increased from 1 to 3. Is that normal?

Quote from: Arantor on April 19, 2011, 06:13:56 PM
Why is there a fulltext index on the members table?

Don't know??

When I compare the indexes with another forum I see lots of differences :

This forum:

PRIMARY    PRIMARY    3576     id_member
posts    INDEX    447     posts
dateRegistered    INDEX    3576     date_registered
lastLogin    INDEX    3576     last_login
posts_2    INDEX    447     posts
dateRegistered_2    INDEX    3576     date_registered
lastLogin_2    INDEX    3576     last_login
memberName_2    INDEX    3576     member_name    30
membername    INDEX    3576     member_name    30
ID_GROUP    INDEX    6     id_group
birthdate    INDEX    894     birthdate
ID_POST_GROUP    INDEX    5     id_post_group
lngfile    INDEX    3     lngfile    24
warning    INDEX    6     warning
total_time_logged_in    INDEX    3576     total_time_logged_in
id_theme    INDEX    2     id_theme
real_name    INDEX    3576     real_name
member_name    INDEX    3576     member_name
memberIP    FULLTEXT    1     member_ip

Other forum:

PRIMARY    PRIMARY    1810     id_member
member_name    INDEX    1810     member_name
real_name    INDEX    1810     real_name
date_registered    INDEX    1810     date_registered
id_group    INDEX    4     id_group
birthdate    INDEX    14     birthdate
posts    INDEX    201     posts
last_login    INDEX    1810     last_login
lngfile    INDEX    2     lngfile    30
id_post_group    INDEX    4     id_post_group
warning    INDEX    1     warning
total_time_logged_in    INDEX    258     total_time_logged_in
id_theme    INDEX    2     id_theme

I'll remove the bolded one first. Does the first one have too many indexes?

It's not a case of too many, it's *why*. I see no reason why there should be a fulltext index on the IP column, yet something put it there. Trouble is, removing it might break something...

Quote from: Arantor on April 20, 2011, 02:54:09 AM
Trouble is, removing it might break something...

Nah, this would be a good thing. Would show wtf is stupid enough to place a fulltext index on the ip and then you can delete it as quickly as possible and never go near it again.

I'd be willing to bet it being one of the anti-spam mods that does MATCH() against the IP for comparisons.

Quote from: Arantor on April 20, 2011, 08:44:24 AM
I'd be willing to bet it being one of the anti-spam mods that does MATCH() against the IP for comparisons.

I'm running Stop Forum Spam (as well as your Login Detector) and can't find any DB-changes in the package.

My login mod is a grand total of two lines added to deal with a specific brute force thing. But no, SFS doesn't add it. Something did though...

I Change that is.

My forum is very badly sistem.

restore please restore

Quote from: Forum Labs on April 19, 2011, 07:31:08 PM
is it safe to convert smf_log_digest to innodb?

Yes.

Quote
I just changed my smf_messages to innodb and my load increased from 1 to 3. Is that normal?

If your forum or forums are fairly active and get a lot of posts, this would signify they you are disk-bound for I/O, and InnoDB may be a bit too heavy for you.

my site is crashed  >:( >:( >:( I have a backup.

How can I fix this absurd settings

You'll have to give us more details about your configuration, what you did, and what is going wrong.

smf 2.0 , How to restore settings

Quote from: aljo1985 on April 19, 2011, 04:57:22 PM
This tutorial helped me alot..
I have applied changes to sql to change things to innodb as stated in the 1st topic.
It does seem to of made a difference :D

uses about 300mb more ram due to the pool_limit which I set to 128 but that cool I have 4gb

I also wanna convert the rest of my forum to innodb other than those that would NOT benefit.
I do have a large amount of mods and the main ones would obviously be the portal and other mods that possibly would benefit loading times. So please can some one help me in which would improve.. I am looking at ways to improve the speed and performance of my forums when its gets busy.

Here is a list of database tables

Code Select Expand


smf_admin_info_files
smf_aeva_albums
smf_aeva_comments
smf_aeva_fields
smf_aeva_field_data
smf_aeva_files
smf_aeva_log_media
smf_aeva_log_ratings
smf_aeva_media
smf_aeva_perms
smf_aeva_quotas
smf_aeva_settings
smf_aeva_variables
smf_affiliates
smf_affiliates_banners
smf_affiliates_rating
smf_affiliate_report
smf_ajaxchat_bans
smf_ajaxchat_invitations
smf_ajaxchat_messages
smf_ajaxchat_online
smf_ajaxshout_bans
smf_ajaxshout_invitations
smf_ajaxshout_messages
smf_ajaxshout_online
smf_approval_queue
smf_arcade_categories
smf_arcade_favorite
smf_arcade_games
smf_arcade_personalbest
smf_arcade_rates
smf_arcade_scores
smf_arcade_settings
smf_arcade_shouts
smf_arcade_tournament
smf_arcade_tournament_players
smf_arcade_tournament_rounds
smf_arcade_tournament_scores
smf_arcade_v3temp
smf_attachments
smf_awards
smf_awards_categories
smf_awards_members
smf_ban_groups
smf_ban_items
smf_battle_explore
smf_battle_graveyard
smf_battle_history
smf_battle_monsters
smf_battle_quest
smf_battle_quest_hist
smf_battle_shop
smf_battle_shouts
smf_boards
smf_board_permissions
smf_buddies
smf_calendar
smf_calendar_holidays
smf_categories
smf_cf_fields
smf_cf_forms
smf_collapsed_categories
smf_custom_fields
smf_down_cat
smf_down_catperm
smf_down_comment
smf_down_creport
smf_down_custom_field
smf_down_custom_field_data
smf_down_file
smf_down_groupquota
smf_down_rating
smf_down_report
smf_down_userquota
smf_feedbot
smf_feedbot_log
smf_group_moderators
smf_helpdesk_attachments
smf_helpdesk_log_action
smf_helpdesk_log_read
smf_helpdesk_tickets
smf_helpdesk_ticket_replies
smf_links
smf_links_cat
smf_links_catperm
smf_links_rating
smf_log_actions
smf_log_activity
smf_log_banned
smf_log_boards
smf_log_comments
smf_log_digest
smf_log_enotify_pms
smf_log_enotify_replies
smf_log_errors
smf_log_floodcontrol
smf_log_group_requests
smf_log_karma
smf_log_mark_read
smf_log_member_notices
smf_log_notify
smf_log_online
smf_log_packages
smf_log_polls
smf_log_reported
smf_log_reported_comments
smf_log_rssfeeds
smf_log_scheduled_tasks
smf_log_search_messages
smf_log_search_results
smf_log_search_subjects
smf_log_search_topics
smf_log_spider_hits
smf_log_spider_stats
smf_log_subscribed
smf_log_topics
smf_log_topic_view
smf_mail_queue
smf_membergroups
smf_members
smf_member_notes
smf_messages
smf_message_icons
smf_moderators
smf_openid_assoc
smf_package_servers
smf_permissions
smf_permission_profiles
smf_personal_messages
smf_picture_comments
smf_pm_attachments
smf_pm_recipients
smf_pm_rules
smf_polls
smf_poll_choices
smf_portamx_blocks
smf_portamx_settings
smf_post_drafts
smf_pretty_topic_urls
smf_pretty_urls_cache
smf_profile_albums
smf_profile_comments
smf_profile_pictures
smf_redirect_expire
smf_rssfeeds
smf_scheduled_tasks
smf_sessions
smf_settings
smf_shop_category
smf_shop_item
smf_shop_property
smf_shop_purchHis
smf_smileys
smf_spiders
smf_subscriptions
smf_talkbox
smf_talkbox_archive
smf_talkbox_banned
smf_talkbox_media
smf_team
smf_thank_you_post
smf_themes
smf_topics


I would appreciate and help given. Thank you for this tutorial.

because Porta MX is my home page.. Would converting any of the tables to InnoDB benefit me
If so which ones.

Quote from: xxrapxx on April 20, 2011, 02:15:39 PM
smf 2.0 , How to restore settings

My site is crashed / 70k Alexa ! importand

smf has no control or access to any other forum running the software, if your database has issues and a backup needs to be restored you are best to contact your host to see if they have a backup if you do not, otherwise there is nothing that can be done if you do not have a backup.

Quote from: aljo1985 on April 21, 2011, 12:48:53 PM
because Porta MX is my home page.. Would converting any of the tables to InnoDB benefit me
If so which ones.

All except for four
smf_log_floodcontrol (convert to MEMORY)
smf_personal_messages (best as MyISAM, set concurrent_insert=2, however)
smf_ajaxchat_messages (as with smf_personal_messages - these tables have a lot of inserts, sometimes deletes, but updates are rare)
smf_messages (unless your members edit a lot, in which case you will need to)

Everything else uses a comparatively small amount of memory, or is very update heavy, making table locks rather painful. However, because InnoDB's format is more space heavy, it can mean converting large tables to InnoDB is a problem in and of itself. Table compression helps with this if your members make large posts, however.

I'm thinking that I may even move personal_messages and my ajax_chats back to MyISAM. It would at least take some of the load off of the shared tablespace, which is a problem for me.

Thanks very much, many of your suggestions were missed by me and have now been implemented. Always happy to optimize in any way I can. Also looking forward to the intermediate suggestions when my forum's big enough to really need a vps or dedicated host.

Bookmarked.

Hi,

I'm running a site for small local non-profit organisation with small number of online users (between 15 - 50 at any given time). Since they can only afford basic shared hosting (max memory usage 384M), I have to do all sorts of optimizations to make sure SMF runs smoothly. I have done some on .htaccess level, making all images thumbnail, and now tried the InnoDB conversion as suggested here. I'm really impressed. Although the forum is quite small but the users are very active. It really improved the forum performance. I wonder if there is more to optimize..

Thank you for the optimization tips. :)

Quote from: TechTitan on April 24, 2011, 01:07:02 AM
Hi,

I'm running a site for small local non-profit organisation with small number of online users (between 15 - 50 at any given time). Since they can only afford basic shared hosting (max memory usage 384M), I have to do all sorts of optimizations to make sure SMF runs smoothly. I have done some on .htaccess level, making all images thumbnail, and now tried the InnoDB conversion as suggested here. I'm really impressed. Although the forum is quite small but the users are very active. It really improved the forum performance. I wonder if there is more to optimize..

Thank you for the optimization tips. :)

The biggest thing what you should do is to change the Uploaded Avatars directory. Default is /avatars/ change it to something like /avtrs/ , I mean create a new folder with that name. And thats gonna improve the performance of your forum. :)
BTW, May I know who is your host?  :)

Quote from: 008Rohit on April 24, 2011, 05:39:28 AM

Quote from: TechTitan on April 24, 2011, 01:07:02 AM
Hi,

I'm running a site for small local non-profit organisation with small number of online users (between 15 - 50 at any given time). Since they can only afford basic shared hosting (max memory usage 384M), I have to do all sorts of optimizations to make sure SMF runs smoothly. I have done some on .htaccess level, making all images thumbnail, and now tried the InnoDB conversion as suggested here. I'm really impressed. Although the forum is quite small but the users are very active. It really improved the forum performance. I wonder if there is more to optimize..

Thank you for the optimization tips. :)

The biggest thing what you should do is to change the Uploaded Avatars directory. Default is /avatars/ change it to something like /avtrs/ , I mean create a new folder with that name. And thats gonna improve the performance of your forum. :)
BTW, May I know who is your host?  :)

Many thanks for your reply...
I already did that before I read this thread. Found it somewhere outside SMF Community Forum. Yes, it really improved overall forum performance...

I'm using local provider called Exabytes (for faster local traffic).

I do understand at point or another, as the usage increases, upgrade to VPS can not be avoided. But at least, I'm having fun trying to extract maximum performance out of a simple shared hosting account...  :) (It has been a while since I last touch SMF)

I hope no one here is going to scold me if I list what I did (or wrongly did with no significant performance boost)  8)

a. I use mod_deflate/mod_gzip in .htaccess
b. Enable some browser caching stuff in .htaccess
c. Enable in php.ini output_buffering = On, output_handler = ob_gzhandler, zlib.output_compression = Off
d. Change user avatar directory
e. Disable attachment (don't see the need to have it enabled, use Aeva Media)
f.  Use GD2 (or is it ImageMagick is better?) for Aeva Media
g. Set embedded Aeva Media image in post to display thumbnail
h. In SMF Features and Options -> Layout -> Set maximum width & height of posted image same as Aeva Media thumbnail size (for consistency)
i. Disable SMF compressed output, evalution of templates & hostname lookup (since i do it in .htaccess)
j. Enable database driven session
k. rewrite the URL in .htaccess (i think no boost here)
l. Use optimal number of mods.
m. Convert some recommended tables to InnoDB

At the beginning the forum always reached the maximum memory limit, but after doing above, it now only happens occasionally...(well it is at the end of the day still a shared hosting)

Quote from: TechTitan on April 24, 2011, 07:33:38 AM

Quote from: 008Rohit on April 24, 2011, 05:39:28 AM

Quote from: TechTitan on April 24, 2011, 01:07:02 AM
Hi,

I'm running a site for small local non-profit organisation with small number of online users (between 15 - 50 at any given time). Since they can only afford basic shared hosting (max memory usage 384M), I have to do all sorts of optimizations to make sure SMF runs smoothly. I have done some on .htaccess level, making all images thumbnail, and now tried the InnoDB conversion as suggested here. I'm really impressed. Although the forum is quite small but the users are very active. It really improved the forum performance. I wonder if there is more to optimize..

Thank you for the optimization tips. :)

The biggest thing what you should do is to change the Uploaded Avatars directory. Default is /avatars/ change it to something like /avtrs/ , I mean create a new folder with that name. And thats gonna improve the performance of your forum. :)
BTW, May I know who is your host?  :)

Many thanks for your reply...
I already did that before I read this thread. Found it somewhere outside SMF Community Forum. Yes, it really improved overall forum performance...

I'm using local provider called Exabytes (for faster local traffic).

I do understand at point or another, as the usage increases, upgrade to VPS can not be avoided. But at least, I'm having fun trying to extract maximum performance out of a simple shared hosting account...  :) (It has been a while since I last touch SMF)

I hope no one here is going to scold me if I list what I did (or wrongly did with no significant performance boost)  8)

a. I use mod_deflate/mod_gzip in .htaccess
b. Enable some browser caching stuff in .htaccess
c. Enable in php.ini output_buffering = On, output_handler = ob_gzhandler, zlib.output_compression = Off
d. Change user avatar directory
e. Disable attachment (don't see the need to have it enabled, use Aeva Media)
f.  Use GD2 (or is it ImageMagick is better?) for Aeva Media
g. Set embedded Aeva Media image in post to display thumbnail
h. In SMF Features and Options -> Layout -> Set maximum width & height of posted image same as Aeva Media thumbnail size (for consistency)
i. Disable SMF compressed output, evalution of templates & hostname lookup (since i do it in .htaccess)
j. Enable database driven session
k. rewrite the URL in .htaccess (i think no boost here)
l. Use optimal number of mods.
m. Convert some recommended tables to InnoDB

At the beginning the forum always reached the maximum memory limit, but after doing above, it now only happens occasionally...(well it is at the end of the day still a shared hosting)

Well, if your host provides "unlimited everything" like me host iPage does, then you can waste their resources by enabling attachments and increasing max_uploaded_file_size in php.ini to over 10M. :P
BTW, I'm eager to know how can you enable gzip compression from .htaccess? I enabled some zlib compression from php.ini which was disabled by default. :)
and My host doesnt even support InnoDB tables. :(

Quote from: 008Rohit on April 24, 2011, 09:26:10 AM
Well, if your host provides "unlimited everything" like me host iPage does, then you can waste their resources by enabling attachments and increasing max_uploaded_file_size in php.ini to over 10M. :P
BTW, I'm eager to know how can you enable gzip compression from .htaccess? I enabled some zlib compression from php.ini which was disabled by default. :)
and My host doesnt even support InnoDB tables. :(

My upload_max_filesize is already set at 20MB. There are certain php parameters I can change via local php.ini and upload_max_filesize is not one of it (provider locked). My understanding about SMF isn't that good, but I think the way it handle attachments will create unwanted stress to the hosting account.

I have zlib.output compression=Off in php.ini since I use output_buffering = On, output_handler = ob_gzhandler, this is because i think SMF uses gzhandler in their ob_start(). The gzhandler compresses the php files and mod_deflate in .htaccess the rest.

Did you check what you have through phpinfo() ?

SMF checks if compression is enabled in the ini.

Quote from: Joshua Dickerson on April 24, 2011, 12:20:36 PM
SMF checks if compression is enabled in the ini.

the zlib or gzhandler or both?

@ TechLib

Though I know a little about php and stuffs, Still I think its zlib in case of php.ini :)

zlib and gzhandler are basically the same thing. They both gzip (a compression format) the content as it's sent out.

It's generally better to let the web server (Apache, Nginx, etc) handle the gzipping.

Quote from: «Mark» on April 24, 2011, 02:57:34 PM
zlib and gzhandler are basically the same thing. They both gzip (a compression format) the content as it's sent out.

It's generally better to let the web server (Apache, Nginx, etc) handle the gzipping.

no wonder we can not enable it both simultaneously in php.ini. i have mod_gzip commented in .htaccess as not to double compress the php files...perhaps i should disable it in php.ini and enable the mod_gzip instead...

Yeah, I'd do it with Apache. Less configurations to change.

Hi @all,

I tried to put smf_sessions into MEMORY instead if INNODB ( mysql slowlog tells me that a lot of slow querys arre updates in smd_session ).

I tried :

mysql> CREATE TABLE smf_sessions ( session_id char(32) NOT NULL, last_update int(10) unsigned NOT NULL, data text NOT NULL, PRIMARY KEY (session_id)
    -> ) TYPE=MEMORY;

Unfortunaly I just got :

ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns

Does someone have an idea what kinds of var type I could use instead ?
Btw : MySQL Version : 5.1.41

Thank you very, very much !

Bye Dirk

Try:

CREATE TABLE smf_sessions ( session_id char(32) NOT NULL, last_update int(10) unsigned NOT NULL, data varchar(16384) NOT NULL, PRIMARY KEY (session_id)) TYPE=MEMORY;


A varchar that long has worked fine for me.

Thanks a lot - works perfectly !  :D

I got some other issue I need a little hint for.

I gow a lot of slow querys when SMF is updateing tables.

Especially when increasing the view counter

# Time: 110610 18:37:51
# User@Host: smf[smf] @ localhost []
# Query_time: 7.376958  Lock_time: 0.000066 Rows_sent: 0  Rows_examined: 0
SET timestamp=1307723871;
UPDATE smf_topics
                        SET numViews = numViews + 1
                        WHERE ID_TOPIC = 27515
                        LIMIT 1;


and updating smf_members

# User@Host: XXX[XXX] @ localhost []
# Query_time: 9.971999  Lock_time: 0.000025 Rows_sent: 0  Rows_examined: 0
SET timestamp=1307723790;
UPDATE smf_members
                SET
                        lastLogin = 1307723780,
                        memberIP = 'XXX',
                        memberIP2 = 'XXX',
                        totalTimeLoggedIn = 7534326
                WHERE ID_MEMBER = 2347
                LIMIT 1;

Does someone have a hint how I could speed this up ?


Here's the output of status.php
Connections per second:    0.6846
Kilobytes received per second:    4.6108
Kilobytes sent per second:    26.3944
Queries per second:    20.9057
Percentage of slow queries:    0.004
Opened vs. Open tables:
(table_cache)    3.3363 (should be <= 80)
Key buffer read hit rate:
(key_buffer_size)    0.0065 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.0773 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    44 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.0032 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.5821 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.5032 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)


Thanks s lot in advance !

Quote from: Warlock666 on June 10, 2011, 12:47:49 PM
I got some other issue I need a little hint for.

I gow a lot of slow querys when SMF is updateing tables.

Especially when increasing the view counter

Set innodb_flush_log_at_trx_commit=2 in your MySQL configuration file. Also set innodb_flush_method=O_DIRECT if you are using Linux.

Both smf_members and smf_topics are myisam tables.
I guess I have to convert them to innodb ?

innodb_flush_method=O_DIRECT is already set and innodb_flush_log_at_trx_commit is set to "1" right now.

Quote from: Warlock666 on June 10, 2011, 01:38:26 PM
Both smf_members and smf_topics are myisam tables.
I guess I have to convert them to innodb ?

innodb_flush_method=O_DIRECT is already set and innodb_flush_log_at_trx_commit is set to "1" right now.

Set it to 2.

And converting smf_topics is a fantastic idea. I run all InnoDB myself (except a couple tables as MEMORY).

Hi Mark,

just tried your suggestions and at the first glance it look perfect.

Thanks a whole lot !

It's once again me, sorry guys  :-[

I still have a lot of slow queries when updating smf_log_topics and smf_members.
Both are now InnoDB and I set up the hints of Mark. Are there maybe some more tweaks ?

Thanks a lot !

Paste the output of these SQL queries:


SHOW VARIABLES LIKE  "%innodb%";
SHOW STATUS LIKE  "%innodb%";


Also, how much memory does your machine have? Are you using Apache or Nginx? What tables have you not converted to InnoDB? Are you running anything other than SMF? These will affect my recommendations.

Hi Mark,

SMF is running on a vserver with 2 GB RAM ( temporary use of up to 4 GB possible ) Ubuntu 10.04 64Bit LTS
Webserve is Apache


SHOW VARIABLES LIKE  "%innodb%";
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| have_innodb                             | YES                    |
| ignore_builtin_innodb                   | OFF                    |
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 1048576                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 8388608                |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | OFF                    |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 1048576                |
| innodb_log_file_size                    | 5242880                |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+
37 rows in set (0.00 sec)



mysql> SHOW STATUS LIKE  "%innodb%";
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Innodb_buffer_pool_pages_data     | 455        |
| Innodb_buffer_pool_pages_dirty    | 10         |
| Innodb_buffer_pool_pages_flushed  | 113690     |
| Innodb_buffer_pool_pages_free     | 0          |
| Innodb_buffer_pool_pages_misc     | 57         |
| Innodb_buffer_pool_pages_total    | 512        |
| Innodb_buffer_pool_read_ahead_rnd | 184        |
| Innodb_buffer_pool_read_ahead_seq | 605        |
| Innodb_buffer_pool_read_requests  | 52641258   |
| Innodb_buffer_pool_reads          | 23900      |
| Innodb_buffer_pool_wait_free      | 0          |
| Innodb_buffer_pool_write_requests | 5418980    |
| Innodb_data_fsyncs                | 200234     |
| Innodb_data_pending_fsyncs        | 0          |
| Innodb_data_pending_reads         | 0          |
| Innodb_data_pending_writes        | 0          |
| Innodb_data_read                  | 565006336  |
| Innodb_data_reads                 | 28755      |
| Innodb_data_writes                | 289505     |
| Innodb_data_written               | 3963269632 |
| Innodb_dblwr_pages_written        | 113690     |
| Innodb_dblwr_writes               | 11566      |
| Innodb_log_waits                  | 2          |
| Innodb_log_write_requests         | 321197     |
| Innodb_log_writes                 | 171290     |
| Innodb_os_log_fsyncs              | 177679     |
| Innodb_os_log_pending_fsyncs      | 0          |
| Innodb_os_log_pending_writes      | 0          |
| Innodb_os_log_written             | 234843136  |
| Innodb_page_size                  | 16384      |
| Innodb_pages_created              | 3904       |
| Innodb_pages_read                 | 34352      |
| Innodb_pages_written              | 113690     |
| Innodb_row_lock_current_waits     | 0          |
| Innodb_row_lock_time              | 46013      |
| Innodb_row_lock_time_avg          | 359        |
| Innodb_row_lock_time_max          | 16701      |
| Innodb_row_lock_waits             | 128        |
| Innodb_rows_deleted               | 5224       |
| Innodb_rows_inserted              | 1113868    |
| Innodb_rows_read                  | 20498927   |
| Innodb_rows_updated               | 193718     |
+-----------------------------------+------------+
42 rows in set (0.00 sec)

There is no other Website running.

Other Services

- postfix
- cyrus ( About 10 Mailboxes for the Teammembers )
- clamd
- amavisd

Just in case you'd suggest to move the E-Mailstuff to another machine, no problem, this would be possible.

Here's the first rows of a top, sorted by memory

top - 20:40:50 up 53 days,  6:18,  1 user,  load average: 0.62, 0.69, 0.61
Tasks:  80 total,   2 running,  78 sleeping,   0 stopped,   0 zombie
Cpu(s): 20.4%us,  2.7%sy,  0.0%ni, 76.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2097152k total,  2097152k used,        0k free,        0k buffers
Swap:  2097152k total,        0k used,  2097152k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25656 mysql     15   0 1384m 854m 7868 S  1.3 41.7  10:56.65 mysqld
7509 clamav    18   0  188m 142m 5552 S  0.0  6.9   6:23.78 clamd
9966 amavis    16   0  138m  43m 2544 S  0.0  2.1   0:00.49 amavisd-new
23898 amavis    15   0  136m  42m 2420 S  0.0  2.1   0:05.25 amavisd-new
3755 www-data  15   0  225m  22m 3704 S  6.3  1.1   0:00.21 apache2
4016 www-data  15   0  224m  22m 4268 S  0.0  1.1   0:00.21 apache2
1730 www-data  15   0  225m  21m 3684 S  0.3  1.1   0:00.46 apache2
1965 www-data  15   0  225m  21m 3716 S  0.0  1.1   0:00.32 apache2
3231 www-data  15   0  223m  20m 3684 S  0.0  1.0   0:00.20 apache2
3766 www-data  15   0  223m  20m 3632 S  2.3  1.0   0:00.22 apache2
1865 www-data  15   0  221m  18m 3676 S  0.0  0.9   0:00.38 apache2
1697 www-data  15   0  221m  18m 3692 S  0.0  0.9   0:00.31 apache2
3627 www-data  15   0  221m  18m 3676 S  0.0  0.9   0:00.39 apache2
5397 www-data  18   0  221m  17m 3680 S  4.7  0.9   0:00.14 apache2
5375 www-data  15   0  220m  17m 3708 S  0.0  0.9   0:00.08 apache2
3759 www-data  15   0  220m  17m 3780 S  0.0  0.9   0:00.10 apache2
3232 www-data  15   0  220m  17m 3784 S  0.0  0.9   0:00.07 apache2
5393 www-data  15   0  220m  17m 3604 S  2.7  0.9   0:00.08 apache2
3556 www-data  15   0  220m  17m 3672 S  0.0  0.9   0:00.22 apache2
3237 www-data  15   0  220m  17m 3684 S  0.0  0.9   0:00.32 apache2
3496 root      18   0  217m  17m 6932 S  0.3  0.9   3:10.06 apache2
5146 www-data  15   0  220m  17m 3616 S  0.0  0.9   0:00.04 apache2
3768 www-data  15   0  220m  17m 3632 S  0.0  0.8   0:00.21 apache2
3222 www-data  15   0  220m  17m 3624 S  0.0  0.8   0:00.11 apache2
5399 www-data  17   0  220m  17m 3628 S  2.3  0.8   0:00.07 apache2
3765 www-data  15   0  220m  16m 3376 S  0.0  0.8   0:00.04 apache2
3763 www-data  15   0  220m  16m 3380 S  0.0  0.8   0:00.03 apache2
3748 www-data  15   0  218m  13m 1856 S  0.0  0.6   0:00.00 apache2
3750 www-data  18   0  218m  12m 1568 S  0.0  0.6   0:00.03 apache2
3754 www-data  15   0  218m  12m 1580 S  0.3  0.6   0:00.01 apache2
5140 www-data  15   0  218m  12m 1568 S  0.0  0.6   0:00.00 apache2
5145 www-data  15   0  218m  12m 1580 S  0.3  0.6   0:00.01 apache2
5395 www-data  19   0  217m  12m 1520 S  0.0  0.6   0:00.00 apache2
5396 www-data  18   0  217m  12m 1520 S  0.0  0.6   0:00.00 apache2
5394 www-data  18   0  217m  12m 1520 S  0.0  0.6   0:00.00 apache2
5403 www-data  17   0  217m  12m 1516 S  0.0  0.6   0:00.00 apache2
5404 www-data  17   0  217m  12m 1516 S  0.0  0.6   0:00.00 apache2
5402 www-data  16   0  217m  12m 1516 S  0.0  0.6   0:00.00 apache2

Here are my myisam tables :

`smf_aeva_comments`
`smf_aeva_field_data`
`smf_aeva_fields`
`smf_aeva_files`
`smf_aeva_log_media`
`smf_aeva_log_ratings`
`smf_aeva_media`
`smf_aeva_perms`
`smf_aeva_quotas`
`smf_aeva_settings`
`smf_aeva_variables`
`smf_articles`
`smf_articles_attachments`
`smf_articles_cat`
`smf_articles_catperm`
`smf_articles_comment`
`smf_articles_creport`
`smf_articles_page`
`smf_articles_rating`
`smf_ban_groups`
`smf_ban_items`
`smf_board_permissions`
`smf_boards`
`smf_calendar`
`smf_calendar_holidays`
`smf_categories`
`smf_fc_bans`
`smf_fc_config`
`smf_fc_config_chats`
`smf_fc_config_instances`
`smf_fc_config_main`
`smf_fc_config_values`
`smf_fc_connections`
`smf_fc_ignors`
`smf_fc_messages`
`smf_fc_paypal_log`
`smf_fc_rooms`
`smf_gallery_cat`
`smf_gallery_comment`
`smf_gallery_pic`
`smf_gallery_report`
`smf_log_activity`
`smf_log_banned`
`smf_log_karma`
`smf_log_notify`
`smf_log_polls`
`smf_log_ratings`
`smf_log_search_messages`
`smf_log_search_subjects`
`smf_log_search_topics`
`smf_log_search_words`
`smf_membergroups`
`smf_message_icons`
`smf_messages`
`smf_moderators`
`smf_package_servers`
`smf_permissions`
`smf_personal_messages`
`smf_poll_choices`
`smf_polls`
`smf_smileys`
`smf_tags`
`smf_tags_log`
`smf_themes`


Here are Innodb :

`smf_attachments`
`smf_collapsed_categories`
`smf_log_actions`
`smf_log_boards`
`smf_log_errors`
`smf_log_mark_read`
`smf_log_search_results`
`smf_log_topics`
`smf_members`
`smf_pm_recipients`
`smf_settings`
`smf_topics`

And here's MEMORY tables :

`smf_log_floodcontrol`
`smf_log_online`
`smf_sessions`

status.php looks like this right now :

MySQL Statistics
MySQL 5.1.x
Connections per second:    0.5368
Kilobytes received per second:    13.3048
Kilobytes sent per second:    82.7866
Queries per second:    50.4146
Percentage of slow queries:    0.0029
Opened vs. Open tables:
(table_cache)    8.5057 (should be <= 80)
Key buffer read hit rate:
(key_buffer_size)    0.0392 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.1212 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    528.7283 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.0058 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.537 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.2864 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)

Just in case you need some more information I'll be glad to provide you with all you need.
I just happy that you take some time to help me  :)

Thank you very much in advance !

Bye Dirk

Set/change this in my.cnf:

innodb_buffer_pool_size = 256M
innodb_flush_log_at_trx_commit = 2

And restart mysql.

Very strange, I set innodb_flush_log_at_trx_commit = 2 in my.cnf already some days ago ( after your hint ) but mysql still used innodb_flush_log_at_trx_commit = 1.

Now I checked the line in my.cnf again and even took your line by paste and copy und restartet mysqld.
Mysqld still used innodb_flush_log_at_trx_commit = 1.
Even a stop and start didn't help.

At that points it looks more like a mysql or "human" problem.

I'll try to check this ...

I'll keep you informed how things are going !

Bye Dirk

Well, " set global innodb_flush_log_at_trx_commit = 2;" during runtime worked.

SHOW VARIABLES LIKE  "%innodb%"; show now :

| innodb_flush_log_at_trx_commit          | 2

Are you sure you're editing the right configuration file? Or that you're restarting mysql, not reloading it?

Quote from: «Mark» on June 07, 2011, 02:22:02 PM
Try:

Code Select Expand


CREATE TABLE smf_sessions ( session_id char(32) NOT NULL, last_update int(10) unsigned NOT NULL, data varchar(16384) NOT NULL, PRIMARY KEY (session_id)) TYPE=MEMORY;


A varchar that long has worked fine for me.

Are you sure this is wise? As far as I know memory tables do not support varchars, instead they act like a normal char, so basically for each session you'll be allocating a metric ****** ton of bytes.

If you want sessions in memory then install memcached (or APC?) and use its memory session handler.

If you're on shared hosting or otherwise cannot install memcached (or APC?) then you don't need sessions in memory.

Quote from: Ensiferous on June 14, 2011, 12:39:01 PM

Quote from: «Mark» on June 07, 2011, 02:22:02 PM
Try:

Code Select Expand


CREATE TABLE smf_sessions ( session_id char(32) NOT NULL, last_update int(10) unsigned NOT NULL, data varchar(16384) NOT NULL, PRIMARY KEY (session_id)) TYPE=MEMORY;


A varchar that long has worked fine for me.

Are you sure this is wise? As far as I know memory tables do not support varchars, instead they act like a normal char, so basically for each session you'll be allocating a metric ****** ton of bytes.

If you want sessions in memory then install memcached (or APC?) and use its memory session handler.

If you're on shared hosting or otherwise cannot install memcached (or APC?) then you don't need sessions in memory.

Agreed on all points.

The memory usage isn't all that bad though. For a thousand users, it's 16MB. And the data is hot, as the sessions table should only be holding active users (unless Debian has mucked with PHP's session killing).

Quote from: «Mark» on June 13, 2011, 05:53:56 PM
Are you sure you're editing the right configuration file? Or that you're restarting mysql, not reloading it?

Hi Mark,

sorry for my late answer. I just checked this again on 3 machines with Ubuntu 10.04 LTS 64 Bit.
On the productive Server and the development Server mysql ignores several innodb variables which I set in my.cnf.
On my Workstation everything works fine.

So it HAS to be some sort of stupid problem with the guy who is editing the confgiles   8)

The other thing is : My perfornace problems are solved.
Even with your tuning hints I still had slow writing querys.

The solution (in my case) was :

ALTER TABLE smf_settings TYPE=MyISAM;
ALTER TABLE smf_log_mark_readTYPE=MyISAM;
ALTER TABLE smf_log_boards TYPE=MyISAM;
ALTER TABLE smf_attachments TYPE=MyISAM;

Now SMF runs like a sportscar  :)


Thanks again for all your help !

I would definitely use APC or memcache for sessions. No reason not to IMO.

Quote from: Joshua Dickerson on June 15, 2011, 07:42:46 PM
I would definitely use APC or memcache for sessions. No reason not to IMO.

Unless you're using SMF 1.1. I don't recall it having the ability to use APC or memcached for sessions.

Does that mean that 2.0 can't handle APC/Memcached sessions? Sorry for the ignorance.

Quote from: «Mark» on June 16, 2011, 02:12:09 AM

Quote from: Joshua Dickerson on June 15, 2011, 07:42:46 PM
I would definitely use APC or memcache for sessions. No reason not to IMO.

Unless you're using SMF 1.1. I don't recall it having the ability to use APC or memcached for sessions.

Memcached, at least, has a transparent handler you can set in php.ini. That way SMF just have to use the normal session functions to store in memcached.

Quote from: Ensiferous on June 17, 2011, 08:49:51 AM

Quote from: «Mark» on June 16, 2011, 02:12:09 AM

Quote from: Joshua Dickerson on June 15, 2011, 07:42:46 PM
I would definitely use APC or memcache for sessions. No reason not to IMO.

Unless you're using SMF 1.1. I don't recall it having the ability to use APC or memcached for sessions.

Memcached, at least, has a transparent handler you can set in php.ini. That way SMF just have to use the normal session functions to store in memcached.

Ahh, yeah, I should do that.

That's what I was talking about.

thanks for sharing informative article with everyone. really nice thread and share more interesting news with us.

Excellent post m8 thank you very much for the pointers!

Thank you again for this fantastic thread.
I made a more elaborate backup script based on your suggested backup code, and included a script for backing up attachments.  More info and download here: http://www.donationcoder.com/forum/index.php?topic=27492

Quote from: Ensiferous on June 14, 2011, 12:39:01 PM

Quote from: «Mark» on June 07, 2011, 02:22:02 PM
Try:

Code Select Expand


CREATE TABLE smf_sessions ( session_id char(32) NOT NULL, last_update int(10) unsigned NOT NULL, data varchar(16384) NOT NULL, PRIMARY KEY (session_id)) TYPE=MEMORY;


A varchar that long has worked fine for me.

Are you sure this is wise? As far as I know memory tables do not support varchars, instead they act like a normal char, so basically for each session you'll be allocating a metric ****** ton of bytes.

If you want sessions in memory then install memcached (or APC?) and use its memory session handler.

If you're on shared hosting or otherwise cannot install memcached (or APC?) then you don't need sessions in memory.

It 'wastes' about ~8k per session, on average, though I'm not sure how much more efficient memcached's function would be - sessions can and do reach into the 14k range, and memcached gives stuff an arbitrary amount of space rather than storing it efficiently.

Quote# Turn it on
ExpiresActive On

# Set a default expiry time. One hour is fine, but a day or longer may be appropriate.
ExpiresDefault A3600

# Turn expiry off for dynamic content (or potentially dynamic content).
ExpiresByType application/x-httpd-php A0
ExpiresByType application/x-python-code A0
ExpiresByType text/html A0

<FilesMatch "\.(php|py|pyc|pyo)$">
  ExpiresActive Off
</FilesMatch>

Do I simply add this (as quoted) to my .htacess file in my domain's root file directory?

A lot of this is above my head.... looking to hire someone to make these changes.

Word of warning. Seems like that for certain forums #8 actually decreases performance, not improves it. So if you run that script, observe what happens to your database size and cancel the script if the database becomes bigger.

Quote from: h78509 on September 07, 2011, 12:48:58 PM
Word of warning. Seems like that for certain forums #8 actually decreases performance, not improves it. So if you run that script, observe what happens to your database size and cancel the script if the database becomes bigger.

Actually, it's a tradeoff. You'll get a larger database, but SMF will have to do less work. You're trading a larger table for a performance increase. If you have a highly-active board, it's a good script. If your board has lots of older unused accounts, you'll get a large jump in size and not much performance increase as all the inactive users will be updated as well.

Thanks for posting this list.

#1 is very important, if it's the one where you let browsers cache avatar images.  I recall that SMF out of the box does not let the browser cache the avatars but instead requires php processing... which can be a huge performance hit...

By the way, a great tool for testing overall site performance is webpagetest.org (I can't post it as a link).  It shows you how long it takes for the first byte to be sent, and how long it takes for the JS and CSS files to be sent, as well as all the image files, etc.

Quote from: Edward123 on December 26, 2011, 12:43:56 AM
#1 is very important, if it's the one where you let browsers cache avatar images.  I recall that SMF out of the box does not let the browser cache the avatars but instead requires php processing... which can be a huge performance hit...

That can easily be disabled. The passthrough is somewhat useful as it prevents direct calling of uploaded images which have sometimes been able to cause infections.

Quote from: Edward123 on December 26, 2011, 12:47:55 AM
By the way, a great tool for testing overall site performance is webpagetest.org (I can't post it as a link).  It shows you how long it takes for the first byte to be sent, and how long it takes for the JS and CSS files to be sent, as well as all the image files, etc.

Firebug and the developer tools built into Chrome do the same thing.

Please help.

After using some of the general tips now my bandwidth is increasing day by day.
From 3.6 it has increased to 5.5.

Please help and let me know the cause.

Quote from: pawan2002 on January 11, 2012, 06:25:31 AM
Please help and let me know the cause.

There is more traffic to your site.

Quote from: 青山 素子 on January 11, 2012, 12:35:19 PM

Quote from: pawan2002 on January 11, 2012, 06:25:31 AM
Please help and let me know the cause.

There is more traffic to your site.

May be. But according to Google it is only 5k.

Quote from: pawan2002 on January 11, 2012, 07:19:13 PM
May be. But according to Google it is only 5k.

If you only use Google Analytics, you won't track everybody. The method used will only record page views but won't track images or anything that is loaded without pinging their server. Likewise, people like me that use certain privacy tools won't even show up. Same with things like bots - those don't run the Javascript required for pinging Google.

Quote from: 青山 素子 on January 11, 2012, 10:24:08 PM

Quote from: pawan2002 on January 11, 2012, 07:19:13 PM
May be. But according to Google it is only 5k.

If you only use Google Analytics, you won't track everybody. The method used will only record page views but won't track images or anything that is loaded without pinging their server. Likewise, people like me that use certain privacy tools won't even show up. Same with things like bots - those don't run the Javascript required for pinging Google.

Any suggestion for that tool which can be easily integrated in smf without loading server.

Quote from: pawan2002 on January 12, 2012, 05:30:00 AM
Any suggestion for that tool which can be easily integrated in smf without loading server.

Most hosting providers offer basic statistics that use access logs recorded on the server. These will show the actual traffic on your site.

For categories:

ALTER TABLE `smf_categories` ADD UNIQUE KEY (`id_cat`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `cat_order`, `id_cat` ), ENGINE=InnoDB;

Can someone tell me how many of that 24 are valids for v2.02?

Quote from: cocacolas on June 20, 2012, 04:54:44 PM
Can someone tell me how many of that 24 are valids for v2.02?

Nearly all of them that are different for 2.0 have a note by them stating the difference. Most of them are general tune-up suggestions that are good for any forum software. I highly suggest actually putting your brain in gear and reading (gasp!) the first post or two in the topic.

Quote from: 青山 素子 on June 20, 2012, 05:22:30 PM

Quote from: cocacolas on June 20, 2012, 04:54:44 PM
Can someone tell me how many of that 24 are valids for v2.02?

Nearly all of them that are different for 2.0 have a note by them stating the difference. Most of them are general tune-up suggestions that are good for any forum software. I highly suggest actually putting your brain in gear and reading (gasp!) the first post or two in the topic.

Sorry! i'm not very good with english, i wil try again  :-[

Quote from: cocacolas on June 20, 2012, 05:33:08 PM
Sorry! i'm not very good with english, i wil try again  :-[

You should have mentioned that before, I wouldn't have been so abrupt in my response. Sorry, I'm just used to people not bothering to read.

In general, nearly all will help with the 2.0 series, but some small differences may exist. These have been noted in the posts.

The number one thing that you can do to make sure SMF is as fast as it can be is to use a good hosting provider. A lot of the ones out there are poor quality and they run their servers overloaded, making everyone on their hosting plans suffer.

I had a quick question about this and after reading through the thread I still wasn't sure...so, I'm posting this for clarification.

1) Regarding the recommendation to "move the uploaded avatars directory", does this apply only to avatars that users have uploaded after installation, or to all avatars, including the contents of the /avatars directory?

2) In a brand new install of SMF, if I upload a bunch of avatars for the users to choose from, should they go into the usual /avatars directory or into a new directory, like "/forum/my_avatars" ?

3) Does the original /avatars directory remain, with directories of avatars inside it?

I guess what I'm asking can be put another way: Do I move the original /avatars directory and its contents, or create a new directory and move all the avatars to it?

You'd leave the avatars dir alone, as it's reserved for the av gallery.
1) It's not retroactive.

Quote from: live627 on June 29, 2012, 05:54:49 PMYou'd leave the avatars dir alone, as it's reserved for the av gallery.
1) It's not retroactive.

So ideally this would be done before any users have uploaded their own avatars? In other words, configure this immediately after installation?

If I understand the point of this change, it's only user-uploaded avatars that place the extra burden on the system, is that correct?

Yes.

thank you, needed this tips really

You can also add a subdomain for images in your hosting account and simply point it back at the root domain. Change the image folder location in theme and layout settings to the new subdomain (all other data the same, just change the sub) and voila! parallel loading of image files. Sounds like you could do the same for avatars as well and maybe smileys as well (do subs for each for max impact).

I can confirm that the subdomain trick works - at least on Godaddy.

Add subdomain to your godaddy hosting account, point it at the root domain folder where you have your SMF forum.
Change the upload URL (only) to http://subdomain.example.com/etc/etc/etc and now your avatars load in parallel rather than waiting for connection.

Quote from: Theatre Publicity on December 07, 2012, 11:38:27 AM
I can confirm that the subdomain trick works - at least on Godaddy.

Add subdomain to your godaddy hosting account, point it at the root domain folder where you have your SMF forum.
Change the upload URL (only) to

Code Select Expand

http://subdomain.example.com/etc/etc/etc and now your avatars load in parallel rather than waiting for connection.

Ok, could you say that again, but in small steps?

SMF 2.0.3  default theme  curve    Simple Portal  2.3.5  shout box enabeled ,    Smileys

  Read all the topic from time to time, applied some of them where applicable, avatars etc.

  Was facing performance issues with my site, home page comes very slowly ...

Removed some mods, do some maintenance, no help.

While dealing with smileys, I've noticed that some manualy added smileys to aspecific simeley folder by me, are not in other smiley folders,so  receving warnings.
I didnt pay attention to that for a while, but today I copied the missing smileys to other smiley dirs, that system is reporting as missing.
  Voola  ...  The page loads faster then before ...

I dont know how is applicable to all themes and variations  but can you pls check your systems and report accordingly.

It might be a considance, then I'll delete this post ...
Give it a try ...

( moving these smileys to a subdomain might be an alternative, I haven tried yet)

Hi folks.

Just wanted to doublecheck though I am going to move my avatars right now:)

I'm running SMF 2.0.3

I've created a new directory http://www.mydomain.com/avat

Am I supposed to put the entire URL in the "Specific catalouge" box or just /avat?

After I've done that I just put in the url http://www.mydomain.com/index.php?action=manageattachments;sa=moveAvatars in my browser, and that will move the avatars over?

Thanks

Chris

Did the above but the avatars does not move over to the new directory.

New avatars ends up there but the old ones are still in the old directory, please advice.

That would be because you did not read the instructions properly. The URL you used is for 1.1.x, the URL for 2.0 is index.php?action=admin;area=manageattachments;sa=moveAvatars as per the first post in this thread.

Quote from: Arantor on January 05, 2013, 03:53:36 PM
That would be because you did not read the instructions properly. The URL you used is for 1.1.x, the URL for 2.0 is index.php?action=admin;area=manageattachments;sa=moveAvatars as per the first post in this thread.

Used that one too, says the directory is not writeable.

The new /avat directory is set to 777.

Did you tell it to actually use /avat ? /avat means something different to SMF (and indeed any operating system) than it does to you. What is the normal attachments path that SMF tells you it's using?

Quote from: Arantor on January 05, 2013, 04:02:32 PM
Did you tell it to actually use /avat ? /avat means something different to SMF (and indeed any operating system) than it does to you. What is the normal attachments path that SMF tells you it's using?

Here is what the backoffice looks like, see pics below.

The normal path would be /avatars.

Except the path isn't /avatars at all. The actual path is /hermes/bosweb/web062/b622/nf.adelm...something.../avatars

And as such, the path you need to use for your new avatars folder is also going to be something like /hermes/bosweb/web062/b622/nf.adelm...something... but I don't know what it will be for sure...

I don't really know how to explain the problem, except that the domain is like a shortcut to several layers down in the actual server but you can't skip them.

Ok, so if the newly created directory is /avat then I would use  /hermes/bosweb/web062/b622/nf.adelm...something.../avat as
url to the specific catalouge for uploading (pic 2)?

Here is the original thingy: /hermes/bosweb/web062/b622/nf.adelmetallforumse/public_html/avatars

If the newly created directory is /avat, you will use /hermes/bosweb/web062/b622/nf.adelmetallforumse/public_html/avat as the full path that it needs.

Quote from: Arantor on January 05, 2013, 04:21:12 PM
If the newly created directory is /avat, you will use /hermes/bosweb/web062/b622/nf.adelmetallforumse/public_html/avat as the full path that it needs.

Ok, and what goes where? Do I have to do the change on more places then in the "specific area" and leave the toparea so to speak as is?

You just put that in the one box that was giving you an error just now, and that should be all the changes you need to make.

Quote from: Arantor on January 05, 2013, 04:23:51 PM
You just put that in the one box that was giving you an error just now, and that should be all the changes you need to make.

That worked out pretty well I can tell you.

Huge thanks.

When some of these recmmendations there is code that needs to be entered in. Where is the easiest way to enter this code in? Is there an input box somewhere that SMF has?

Most of the 'code' suggestions are SQL queries, which should be executed from phpMyAdmin. Not all of them are applicable for 2.0.

Though honestly the first step, regarding avatar changes is by far the most important to do.

Hi
I had some big problems lately with this(move of avatars directory) so I decided to return things to what they where before.
The attachment directory.

Now when users upload avatars they are put in the original attachment folder = GOOD;
The only thing I can't mange to do is the current ones. Old Members profile pictures are still directing to the old non existing directory(avs).
I want to move the old avatars back to the attachment folder(attachments).
How can this be achieved ? Cause the index.php?action=manageattachments;sa=moveAvatars is not working.

to make things clear, I want to revert the avatar move explained above.

My appreciate. I follow your guide to make some modification on my forum and it gets much faster now. I haven't done all the modification yet as I'm a newbie. I just did the basic improvement but it does work! Gotta do the intermediate improvement tml. Thanks again for your useful tips.

Will displaying the recent topic using the php code here [ http://www.doncaprio.com/2013/01/how-to-display-rss-feeds-from-a-website-in-php.html ] have any effect on the server ?

This is just awesome, all the basic improvements have helped a lot to improve my site.
I'm yet to move avatars tho (seems technical)

About Pruning inactive members - I kinda need their emails for Newsletters. So even if they never login or post i still have a large reach when sending (HTML) newsletters - this brings some of them back.

Google page speed still suggest improvements to server response time for theme icons and recommend css sprites (which is too complicated)

Thanks

Great thread, I do have some questions though. A little background first:

We've been using a 1.x version of smf for some time now on a couple of online gaming sites we developed and it's worked great. After reviewing the various forum packages that are currently available (especially reviews/comments made by their users), I decided smf 2.x would suit my needs best on my own WordPress-based site, which I host on a VPS that I use for development purposes (that means no other production sites are hosted there).

Anyway, I figured I'd first get things set up in a local copy of WAMP running on Win 7 64-bit, just to check it out. I use WAMP 2.4, which until now, has been okay for everyday stuff (Apache:2.4.4, MySQL: 5.6.12, PHP: 5.4.12). By default, WAMP isn't set up to run INNODB and you have to go through a slew of mods to run INNOB in my local configuration (meaning more overhead of course, more than I've got the time for at the moment).

Now for my question and the reason I'm asking it:

After installing smf 2.0.5 on my local machine, configuring it, and adding categories and boards, I ran into the category ordering issue other folks have been experiencing elsewhere. So I searched Google, and found a thread on this forum (http://www.simplemachines.org/community/index.php?topic=500619.0 (http://www.simplemachines.org/community/index.php?topic=500619.0)) that led me to this thread. Now it could be that my locally installed copy of smf is having problems with WAMP 2.4 the way its developer has it set up out of the box and/or because of the versions of PHP and MySQL that are used in WAMP 2.4, but does anyone have any other idea why the issue is occurring when using smf straight out of the box (that is, before making any changes to the tables using MyISAM)?

Cheers, any additional insight would be appreciated.

Btw, I should also add that MyISAM is the default engine in use with this version of WAMP, and that none of the tables have been converted to INNODB. Also, trying to reset the order by moving the categories up/down in admin doesn't fix the issue in the frontend. Lastly, when hovering over the category links in the frontend, the ids of the categories appear correct.

Edit-added: one more thing: I didn't check the code that sets up the order that the cats are listed in, but I did assign letters to each cat (A, B, C, ... n). Just for the purpose of easily seeing which cats are out of order. The order in the admin panel is as stated,  A, B, C, ... n, while the order in the frontend is L, K, J, G, A, B, C, D, E, F, H. Just an added bit of trivial info.

All that said, I've since read about numerous issues (including this one) that can be experienced when using WAMP on a local machine. I think I'll go ahead and install a copy of smf on my dev VPS and see if the same thing happens.

Try moving an individual board around, not just a whole category.

It should also be noted that your database user really needs to have the ability to issue ALTER TABLE instructions, if this isn't an option it can mean it won't work properly.

Quote from: Arantor on September 18, 2013, 02:24:41 PM
Try moving an individual board around, not just a whole category.

It should also be noted that your database user really needs to have the ability to issue ALTER TABLE instructions, if this isn't an option it can mean it won't work properly.

Moving boards around by themselves doesn't seem to have any impact, nor does moving boards and categories around.

I'm root on my (secured) local WAMP, without a password, and smf is using root for its access to MySQL. I can run ALTER TABLE and other SQL commands locally with no issues.

In which case it sounds like the trick SMF used to rely on for the board index no longer works and that the board index (and anywhere else that uses it) will start to have to be rewritten.

Perhaps. You wouldn't happen to have a link to one or more threads handy that describe this "trick," would you? I'd be happy to take a look at the code associated with that.

There isn't a topic about it.

The trick - and it is a trick - is to use ALTER TABLE to force MySQL to reorder the rows in board order, so that the SELECT can do a table scan and get the rows out without any sorting being required. With classic MyISAM behaviour over the last 10 years, that was possible.

As you saw from the first post in this topic, it doesn't work with InnoDB because InnoDB forces the table content to be ordered by primary key, so changing the schema to force board order to be primary key isn't ideal either but it would solve the problem there.

Seriously, though, the board index code needs to be rewritten, there are two major performance constraints, one of which can be dealt with by splitting the query into two queries, one of which may not even need to be run if the code is written as such.

Ah, I see. You probably have somewhat of a buffer before that work has to be done, as I haven't seen lamp stacks yet (at least those I've been working with) on production servers that are actually using most of the currently available, latest and greatest.

Thanks for the replies, and the info. I probably won't experience the issue on my vps as I'm running CentOS 5.9 there, with PHP 5.3.17 and MySQL 5.1.70, but I'll let you know how that goes.

You shouldn't experience it on 5.1 with MyISAM, it only seems to be 5.6+ with MyISAM where this occurs, but I don't even believe this is being looked at for 2.1...

Just an update after installing smf on my vps: no issues with the category order noted, as was expected.

Anyone here use Percona Server for their forum? If so, have you experienced any performance difference, good or bad?

The reason I want to use it is because they have a tool called "XtraBackup" that can back up the database without locking it or shutting it down.

XtraBackup works just fine with MySQL and MariaDB as well. Please note that the non-blocking backups are only for InnoDB and XtraDB tables. SMF sets up all tables as MyISAM for a new install. Unless you change the engine for the tables, you won't get the benefit of the tool.

Quote from: 青山 素子 on November 11, 2014, 02:06:50 PM
XtraBackup works just fine with MySQL and MariaDB as well. Please note that the non-blocking backups are only for InnoDB and XtraDB tables. SMF sets up all tables as MyISAM for a new install. Unless you change the engine for the tables, you won't get the benefit of the tool.

Thanks for the info. I understand that some tables may be adversely affected by switching them to InnoDB. What can I do to prevent issues there, other than keeping MyISAM around? I would prefer to stick with InnoDB and MEMORY tables.

well, in some cases, MyISAM is the correct type... regardless of what you "would prefer"...

Quote from: Kindred on November 11, 2014, 03:43:48 PM
well, in some cases, MyISAM is the correct type... regardless of what you "would prefer"...

I'm aware of that, and I have not done an en masse switch to InnoDB because of that. However, I'd like to understand why this is the case, because in other web applications that I have worked with, MyISAM is never used. I'd like to know what situations warrant using it, so I can possibly apply that knowledge towards performance improvements on other websites that I am responsible for.

Tables with low write/high read uses, tables that need fulltext indexes will need MyISAM.

Beyond those, it's about observing the actual performance with actual data.

Quote from: rjk on November 11, 2014, 02:30:56 PM
I understand that some tables may be adversely affected by switching them to InnoDB. What can I do to prevent issues there, other than keeping MyISAM around? I would prefer to stick with InnoDB and MEMORY tables.

You'll just have to experiment on a backup of the forum. Some things like the board sorting rely on their position in the MyISAM structure, which is a bit of a side effect of the engine, but useful for making things faster as you don't have to sort the rows on a select. Changing to InnoDB/XtraDB will break board rearranging (I believe this has been corrected in the upcoming 2.1) since they don't have this behavior.

Memory tables are a bad idea for SMF as all tables it creates in the installer are expected to exist and persist data. Memory tables don't do that.

Quote from: Kindred on November 11, 2014, 03:43:48 PM
well, in some cases, MyISAM is the correct type... regardless of what you "would prefer"...

It's worth noting that newer versions of MySQL and MariaDB have changed their default table engine to InnoDB.

Quote from: Arantor on November 11, 2014, 03:57:28 PM
Tables with low write/high read uses, tables that need fulltext indexes will need MyISAM.

My understanding is that InnoDB has improved enough to significantly reduce or remove the speed advantage MyISAM has/had in read operations. Also, it looks like InnoDB has fulltext support. Is the above because of the older MySQL versions SMF claims to support?



Anyway, as long as you don't have a huge database (like this site), even plain old mysqldump should be more than fast enough. At worst, you're looking at about 5-10 seconds downtime during backup. If you can't tolerate that kind of downtime, you should be running a cluster and using one of the nodes for your backup operations.

Thanks for the help guys. I've been googling furiously and have found that fulltext is available in MySQL 5.6+ for the InnoDB engine, but several say that it's not quite "production-ready". I'd only be using MEMORY tables for those specified in the first post of this sticky - just sessions I think. And I know that MEMORY can't easily scale past one server, so any expansion would require a change.

NoSQL is a whole new can of worms, but I don't think I will go there any time soon.  :o

Quote from: 青山 素子 on November 11, 2014, 04:02:23 PMMemory tables are a bad idea for SMF as all tables it creates in the installer are expected to exist and persist data. Memory tables don't do that.

This is true, and memory/heap are also limited in size to the available unallocated system memory. In a decent size forum it's likely that some tables wouldn't fit in memory. When that happens the memory/heap table is converted to on-disk storage (not an on-disk table, though) and this is a hard performance bottleneck.

It basically becomes a pagefile on disk with no table properties, essentially a big blob of disk-cache. Swapping the data back and forth to disk removes all of the performance that would be expected if it was fully in memory.

Quote from: 青山 素子 on November 11, 2014, 04:02:23 PMMy understanding is that InnoDB has improved enough to significantly reduce or remove the speed advantage MyISAM has/had in read operations.

InnoDB has come a long way in terms of speed. In most applications it's often about as fast as MyISAM. The old joke was that "InnoDB gives you yesterday's data tomorrow" but it's pretty close to MyISAM these days in terms of speed.

Hello,

Could I ask, are the steps in this thread still pertinent to the latest 2.0.10 release? And, are there additional steps we can take nowadays not mentioned here?

Thank you for your time,

-Johan

Everything that is valid to 2.0 is valid to any 2.0.x version ;)

Thanks!

If your mysql server is on the same machine change from connecting from localhost to 127.0.0.1 and get a big boost in some cases just saw this on one of my sites and it just had a massive increased in performance.
http://stackoverflow.com/questions/17815604/very-slow-1-second-connections

Quote from: vbgamer45 on September 16, 2015, 11:20:04 PM
If your mysql server is on the same machine change from connecting from localhost to 127.0.0.1 and get a big boost in some cases just saw this on one of my sites and it just had a massive increased in performance.

That's really weird. Normally, localhost resolves internally and should add little to no overhead. If you have control over the server, make sure localhost is an entry in your hosts file.

Yeah was shocked myself on my windows servers I updated it made a huge difference it for all my sites that that had databases on the same server.   The issue was php tries to look for ipv6 localhost first for mysql then ipv4. And mysql by default wasn't listening on ipv6.

Another tip just discovered that the smf_sessions table last_update should have an index to add one run

alter table smf_sessions add index last_update (last_update);


That's only if you're not doing the memcache thing ;)

That server does have memcache with level 3 smf caching I still saw that query hit though.

Then turn off DB sessions and get PHP to use memcache for sessions.

Will give that a shot and test.

Quote8: Run the mark read for inactive users script periodically. The log_topics, log_boards, and log_mark_read tables are not the best designs, and for some unlucky folks end up dominating their database. Pruning them can help.

I think this should be rephrased. The "mark read for inactive users script" does not prune anything. In fact, it makes log_topics, log_boards and log_mark_read tables substantially larger.

What exactly is the benefit of running this script again?

It prunes one set of tables and outs records in the other because if the way SMF works. It just so happens that for you it made it bigger, this is not universally true.

Quote from: vbgamer45 on July 09, 2017, 04:58:44 AM
Another tip just discovered that the smf_sessions table last_update should have an index to add one run

Code Select Expand


alter table smf_sessions add index last_update (last_update);


Don't do this kind of advice based on your wrong setup smf.
index slow down dml actions.

Quote from: Arantor on August 06, 2017, 06:27:28 AM
It prunes one set of tables and outs records in the other because if the way SMF works. It just so happens that for you it made it bigger, this is not universally true.

Ok, after going through the code more carefully, I can see what you're talking about.

In my opinion, this script should ignore users with 0 posts. I've modified my version to do just that. On my forum, I have lots of registered users that just want to access members only content.

This will clear up more than 750,000 entries in the smf_log_mark_read table alone.

In my opinion, the script is fine for normal use, and your use case is atypical, such that changing the script generically will hurt more people than it would help.

The hint to switch from localhost to 127.0.0.1 is only valid for Windows server,
On Linux your hurt yourself because mysql use on localhost socket connection and
On 127.0.0.1 tcp/ip.
Socket >tcp/ip

Please help.

After using some of the general tips now my bandwidth is increasing day by day.
From 3.6 it has increased to 5.5.

Please help and let me know the cause.

What tips did you do and what type of site?

Also, what do 3.6 and 5.5 mean?

Quote from: gaminh on May 12, 2020, 12:12:06 PM
Please help.

After using some of the general tips now my bandwidth is increasing day by day.
From 3.6 it has increased to 5.5.

Please help and let me know the cause.

Quote from: Arantor on May 12, 2020, 01:38:36 PM
Also, what do 3.6 and 5.5 mean?

Linux server load averages?

https://www.tecmint.com/understand-linux-load-averages-and-monitor-performance/

That doesn't really make sense for 'my bandwidth increased' though, and on a multi-core server (common), those numbers might not even be bad. But we don't know without context.

I just read the opening post on this thread, with great interest i might add! Then I noticed it's around 14 years old!! Is it still relevant today?
thanks

Although some of the technical stuff is dated (e.g., I wouldn't suggest running PHP5...), most of this is still valid.  Especially the reminders to turn off various functions you don't need, and try not to overload pages.  Mind your page size, # of boards, etc.

Several of these items are baked into SMF 2.1, e.g., #1 Avatars & #8 Mark Boards as Read.  So you don't need to take any special action, it's already done for you.  (Although you do need to enable the Mark Boards as Read option under Admin | Maintenance | Scheduled Tasks, it's not on by default.)

Note there have been some significant performance improvements in every version of PHP, as well as in the latest MySQL, 8.0.  The more current you are, the better off you are performance-wise.  Note also that queries that span across collations or engines take a performance hit.

So, if you can, at the time of this post, I suggest:
- SMF 2.1
- MySQL 8.0
- PHP 8.2
- Making sure all your collations are consistent, utf8_general_ci
- Making sure all your tables are using InnoDB
- For high traffic, I'd suggest researching your caching options

If performance is of utmost concern, consider postgresql. (Careful, though, you may find some mods do not support pg.)

Finally, keep in mind that the original post was intended for very high volume forums.  For most forums, out-of-the-box is designed to be sufficient...

Thanks very much, I don't understand some of it ("collations") but will go learn.