Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)

[exxocet]

  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.

[Something like that]

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.

[exxocet]

  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?

[Something like that]

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.

[exxocet]

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

[Something like that]

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.

[exxocet]

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

[exxocet]

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 .

[Something like that]

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

[tojo]

thanks krab

[n1cotine]

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 ?

[Something like that]

Do it like this then:

Code Select Expand


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.

[n1cotine]

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

[Something like that]

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

[Vekseid]

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 : )

[Vekseid]

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

[Something like that]

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.

[ElvenCat]

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 

And thanks Vekseid for the great info all in one spot

[amlucent]

Were would one find these options to disable them?

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.

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

[Something like that]

Were would one find these options to disable them?

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.

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