Advertisement:

Author Topic: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)  (Read 565339 times)

Offline iamjanco

  • Semi-Newbie
  • *
  • Posts: 18
  • Gender: Male
    • High Peaks Publishing
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #360 on: September 18, 2013, 02:04:12 PM »
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.
« Last Edit: September 18, 2013, 02:28:07 PM by iamjanco »

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,394
    • StoryBB/StoryBB on GitHub
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #361 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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline iamjanco

  • Semi-Newbie
  • *
  • Posts: 18
  • Gender: Male
    • High Peaks Publishing
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #362 on: September 18, 2013, 02:36:09 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.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,394
    • StoryBB/StoryBB on GitHub
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #363 on: September 18, 2013, 02:41:54 PM »
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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline iamjanco

  • Semi-Newbie
  • *
  • Posts: 18
  • Gender: Male
    • High Peaks Publishing
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #364 on: September 18, 2013, 02:54:22 PM »
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.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,394
    • StoryBB/StoryBB on GitHub
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #365 on: September 18, 2013, 03:01:51 PM »
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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline iamjanco

  • Semi-Newbie
  • *
  • Posts: 18
  • Gender: Male
    • High Peaks Publishing
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #366 on: September 18, 2013, 03:53:31 PM »
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.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,394
    • StoryBB/StoryBB on GitHub
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #367 on: September 18, 2013, 03:59:20 PM »
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...
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline iamjanco

  • Semi-Newbie
  • *
  • Posts: 18
  • Gender: Male
    • High Peaks Publishing
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #368 on: September 19, 2013, 08:44:55 PM »
Just an update after installing smf on my vps: no issues with the category order noted, as was expected.

Offline rjk

  • Semi-Newbie
  • *
  • Posts: 14
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #369 on: November 11, 2014, 01:13:10 PM »
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.

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,068
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #370 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.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Offline rjk

  • Semi-Newbie
  • *
  • Posts: 14
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #371 on: November 11, 2014, 02:30:56 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.

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 58,430
  • Gender: Male
    • Kindred-999 on GitHub
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #372 on: November 11, 2014, 03:43:48 PM »
well, in some cases, MyISAM is the correct type... regardless of what you "would prefer"...
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline rjk

  • Semi-Newbie
  • *
  • Posts: 14
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #373 on: November 11, 2014, 03:52:24 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.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,394
    • StoryBB/StoryBB on GitHub
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #374 on: November 11, 2014, 03:57:28 PM »
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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,068
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #375 on: November 11, 2014, 04:02:23 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.


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.

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.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Offline rjk

  • Semi-Newbie
  • *
  • Posts: 14
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #376 on: November 11, 2014, 04:08:20 PM »
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

Offline MrMike

  • Full Member
  • ***
  • Posts: 649
    • BotScout.com - Bust Those Bots!
Re: Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)
« Reply #377 on: November 11, 2014, 06:54:31 PM »
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.

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.


My 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.


Offline roshaoar

  • Jr. Member
  • **
  • Posts: 205
  • Gender: Male
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

Offline margarett

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,761
  • Gender: Male
Everything that is valid to 2.0 is valid to any 2.0.x version ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

Quote
Over 90% of all computer problems can be traced back to the interface between the keyboard and the chair