News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

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

iamjanco

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.

Arantor

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.

iamjanco

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.

Arantor

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.

iamjanco

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.

Arantor

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.

iamjanco

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.

Arantor

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

iamjanco

Just an update after installing smf on my vps: no issues with the category order noted, as was expected.

rjk

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

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


rjk

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.

Kindred

well, in some cases, MyISAM is the correct type... regardless of what you "would prefer"...
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

rjk

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.

Arantor

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

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


rjk

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

MrMike

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.


roshaoar

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

margarett

Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

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

Advertisement: