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

Started by Vekseid, February 16, 2009, 06:29:50 AM

Previous topic - Next topic

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

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.

exxocet

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

Something like that

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.

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


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:


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 : )
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Vekseid

Finally put together the bit about converting away from text/tinytext >_>
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Something like that

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.

ElvenCat

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

amlucent

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

Something like that

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

Advertisement: