Simple Machines Community Forum

SMF Support => Server Performance and Configuration => Topic started by: [Unknown] on September 20, 2005, 04:31:53 PM

Title: Convert your tables to InnoDB
Post by: [Unknown] on September 20, 2005, 04:31:53 PM
MySQL has support for two major database storage engines: MyISAM and InnoDB.  MyISAM has, at least in past releases, been the default, and is the default for SMF installations.  And, this is for good reason: MyISAM reads tables much faster than InnoDB.

However, InnoDB has some major benefits over MyISAM.  First, it's important to have a basic understanding of how a query works.  Let's say I run a query like this:

SELECT ID_MSG
FROM smf_messages
WHERE subject = 'Convert your tables to InnoDB'
LIMIT 1;

While MySQL is looking for the specific topic (which can take some time, since the subject column has no index) no one can write to the table.  That means, no one can post to the table.  For the messages table, this isn't a huge problem - and it has to be MyISAM for a FULLTEXT index anyway.

However, it matters more for tables like topics, log_topics, and friends.  For example, every time you view a topic, this happens:

UPDATE smf_topics
SET numViews = numViews + 1
WHERE ID_TOPIC = ###
LIMIT 1;

If the table is locked, it will have to wait for... whatever is happening to finish.  The same goes for multiple people viewing multiple topics at once.  They have to wait in line for the topics table to become available.

This is not true for InnoDB.  If two people view the same topic, yes... they will have to wait for the numViews update.  But if they view different topics, it's immediate.  This is a huge difference, and can make more difference than you'd expect, depending on what's going on on your server.  But, remember... there is a penalty.  MyISAM may lock badly, but it still reads faster.  For tables that don't change much, like categories, there's usually no reason to use InnoDB.

For example, if you look at status.php, and see:

95 (87 sleeping, 2 running, 6 locked)

That means that the running queries are locking other queries, making them wait.  This usually means you should be using InnoDB for some table which you aren't, but doesn't always mean that.  With SMF, when your forum reaches a point of activity where it's benefical, you can run the following queries to change your tables to InnoDB:

ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
TYPE=InnoDB;
ALTER TABLE smf_log_actions
TYPE=InnoDB;
ALTER TABLE smf_log_boards
TYPE=InnoDB;
ALTER TABLE smf_log_errors
TYPE=InnoDB;
ALTER TABLE smf_log_karma
TYPE=InnoDB;
ALTER TABLE smf_log_mark_read
TYPE=InnoDB;
ALTER TABLE smf_log_online
TYPE=InnoDB;
ALTER TABLE smf_log_search
TYPE=InnoDB;
ALTER TABLE smf_log_topics
TYPE=InnoDB;
ALTER TABLE smf_members
TYPE=InnoDB;
ALTER TABLE smf_pm_recipients
TYPE=InnoDB;
ALTER TABLE smf_sessions
TYPE=InnoDB;
ALTER TABLE smf_settings
TYPE=InnoDB;
ALTER TABLE smf_topics
TYPE=InnoDB;

Additionally, you'll probably want log_floodcontrol 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)
) TYPE=HEAP;

What is phpMyAdmin? (http://www.simplemachines.org/community/index.php?topic=21919.0)  Before running these queries, it's a good idea to put your forum in maintenance mode.  They will take, for large forums, quite some time (10 minutes, in cases.)  What is repair_settings.php? (http://www.simplemachines.org/community/index.php?topic=18096.0)

-[Unknown]
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on September 20, 2005, 09:31:51 PM
Should really use ENGINE instead of TYPE.

Probably would make log_online MEMORY/HEAP too (with some slight modifications)
Title: Re: Convert your tables to InnoDB
Post by: [Unknown] on September 20, 2005, 10:19:13 PM
Should really use ENGINE instead of TYPE.

Probably would make log_online MEMORY/HEAP too (with some slight modifications)

TYPE works on basically every version of MySQL.  ENGINE works, iirc, only on MySQL 4.1 and above.  Since most hosts are still using 4.0, that would be very silly.

And log_online cannot be HEAP/MEMORY because of the text column, except in MySQL 5.0 (which no one runs, I don't care how much you think it's production ready.)

-[Unknown]
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on September 20, 2005, 11:03:57 PM
Yeah, it should be ENGINE. Because people should be using the GA release.

You can convert to (var)char. And I am willing to bet some people run 5 (besides me)
Title: Re: Convert your tables to InnoDB
Post by: Mike Bobbitt on September 25, 2005, 10:58:47 AM
Now that smf_log_search doesn't exist in 1.1, are there other tables we should be converting?
Title: Re: Convert your tables to InnoDB
Post by: Gargoyle on October 02, 2005, 08:05:00 PM
WOW!!!

I don't have that big of a forum and this made my site turn into a speed freak!!

Very cool !! ;D
Title: Re: Convert your tables to InnoDB
Post by: xtremecruiser on October 03, 2005, 06:39:15 PM
If we have 4.1 or over we use engine.  If we are using 1.05 and have not updated to the RC is that ok for a later update ? Also can you explain the process a little better for us Microsoft guys ::)
Thanks
Title: Re: Convert your tables to InnoDB
Post by: Oldiesmann on October 05, 2005, 11:53:37 PM
Good question. You should ask him to give you more details about that...
Title: Re: Convert your tables to InnoDB
Post by: Bonk on October 11, 2005, 12:30:11 PM
I asked my host about InnoDB and he apparently has it disabled and said "it causes problems"... What's up with that?

Probably the dramatically increased memory use and detailed configuration necessary in my.ini (my.cnf).
Title: Re: Convert your tables to InnoDB
Post by: Gargoyle on October 11, 2005, 06:46:46 PM
Thats why I love my host... They are very accomidating... But I also pay more than 3 dollars a month... ;D
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on October 11, 2005, 08:36:57 PM
Probably the dramatically increased memory use and detailed configuration necessary in my.ini (my.cnf).

The default settings are fine for the vast majority of people and the increased memory usage is a whole 50MB.

Given that InnoDB will vastly help SMF boards, enabling it will probbaly save resources in the end.
Title: Re: Convert your tables to InnoDB
Post by: Webrunner on October 14, 2005, 07:48:07 PM
Can i change it back when i am not happy with the results?
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on October 14, 2005, 07:51:26 PM
yes
Title: Re: Convert your tables to InnoDB
Post by: Elmacik on October 16, 2005, 03:27:15 AM
how?
we the noobs and greedies and  :P :D
Title: Re: Convert your tables to InnoDB
Post by: Enc0der on October 16, 2005, 04:06:51 AM
lol...  :P
use the same code, and just replace InnoDB with MyISAM..

for example:
Code: [Select]
ALTER TABLE smf_attachments
TYPE=MyISAM;
ALTER TABLE smf_collapsed_categories
TYPE=MyISAM;
ALTER TABLE smf_log_actions
TYPE=MyISAM;
ALTER TABLE smf_log_boards
TYPE=MyISAM;
etc etc..
Title: Re: Convert your tables to InnoDB
Post by: Elmacik on October 16, 2005, 04:08:56 AM
i knew it... i asked for the second thingy that [unknown] posted :)
Code: [Select]
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)
) TYPE=HEAP;
Title: Re: Convert your tables to InnoDB
Post by: Webrunner on October 16, 2005, 06:35:41 AM
Thge same query but Type - MyIsam
Title: Re: Convert your tables to InnoDB
Post by: Elmacik on October 16, 2005, 06:36:48 AM
Thge same query but Type - MyIsam
i think you didnt read one post above yours
Title: Re: Convert your tables to InnoDB
Post by: Webrunner on October 16, 2005, 06:53:56 AM
Ooops.. sorry
 :o
Title: Re: Convert your tables to InnoDB
Post by: Douglas on December 14, 2005, 01:05:35 AM
Sorry to bump this up, can someone update this for 1.0.5 and 1.1.1 RC*
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on December 14, 2005, 06:01:06 AM
Should be fine with 1.0x, obviously you will need to replace the pm_recipients with im_recipients, as for 1.1, most of it is fine, although log_search probably no longer exists.
Title: Re: Convert your tables to InnoDB
Post by: sirwoogie on January 15, 2006, 11:18:57 AM
Should be fine ... as for 1.1, most of it is fine, although log_search probably no longer exists.

Yes, it's split now into 5. My question is which table is most appropriate to convert now in the 1.1 area?

Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on January 17, 2006, 09:57:17 PM
All
Title: Re: Convert your tables to InnoDB
Post by: Simplemachines Cowboy on January 17, 2006, 10:13:05 PM
This smoked my forum. Wicked fast now.
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on January 22, 2006, 12:47:49 PM
Just as a note, there are tables that shouldn't be changed to InnoDB because they would be faster using MyISAM. For instance the 'boards' table. Unless you have 10,000 boards that are getting new posts 3 times a second. In which case, you are going to need a lot more than software changes.
Title: Re: Convert your tables to InnoDB
Post by: Webrunner on January 23, 2006, 11:15:32 AM
how about 100 boards getting new messages 3 times a second?
Title: Re: Convert your tables to InnoDB
Post by: akalaze on January 23, 2006, 05:48:08 PM
Thanks for this advice I did do the changes today and it made
a very big difference. I run a rather large adult board with an
average of 80 to 120 new members per day and about 2000
people constantly online.

The forum started to slow down a lot so I was looking for
improvements and found that thread ;)

Well it took a bit longer than 10 minutes (about 1 1/2 hours
but it was well worth it. Can't thank you enough!!!

akalaze
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on January 23, 2006, 08:21:43 PM
how about 100 boards getting new messages 3 times a second?
It might.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on January 23, 2006, 08:26:23 PM
Although thats one busy forum, would love to see it.
Title: Re: Convert your tables to InnoDB
Post by: ldk on January 31, 2006, 03:28:56 PM
Can someone just confirm how to "undo" this mySql command below?! I converted to InnoDB tables today it's killing my server for some reason. :'( So I know how to change the tables back to MyISAM but I don't know how to undo this command which I also ran:

i knew it... i asked for the second thingy that [unknown] posted :)
Code: [Select]
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)
) TYPE=HEAP;
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on January 31, 2006, 06:52:11 PM
Just change the table type to whatever you want.
Title: Re: Convert your tables to InnoDB
Post by: jerm on February 02, 2006, 04:18:52 PM
do you think it would be possible for the list to be updated?
i have converted a bunch of my tables to InnoDB, but i want to make sure i hvae the correct ones done..
Title: Re: Convert your tables to InnoDB
Post by: kezayah on February 07, 2006, 11:06:30 AM
Why convert my tables to InnoDB ???
I tried that but it was very very slow, it's better with MyIsam
Title: Re: Convert your tables to InnoDB
Post by: H on February 07, 2006, 11:31:42 AM
It would be nice if a dev or support member could update the first post with instructions that definitely work for 1.1 and 1.0.X
Title: Re: Convert your tables to InnoDB
Post by: Webrunner on February 07, 2006, 05:27:08 PM
i am noticing a lot more database errors and "strange bugs" since i switched them tables to innodb.
I am considering changing them back. :(
Title: Re: Convert your tables to InnoDB
Post by: Mike Bobbitt on February 07, 2006, 06:06:40 PM
Check your database consistency:

Code: [Select]
mysqlcheck --password=[password] --auto-repair --compress --medium-check --optimize --all-databases
Might save you problems down the road if there are issues...
Title: Re: Convert your tables to InnoDB
Post by: Webrunner on February 08, 2006, 03:48:38 AM
I just converted them back and did a myisamchk -r.
I see thst some indices aren't correctly set but those are from the standard SMF config.
Should i change those?
Title: Re: Convert your tables to InnoDB
Post by: Motorhead on February 16, 2006, 04:30:44 AM
Would changing the tables like suggested above, put any greater load on a server..

I'm on a VPS and I've noticed my memory useage seems to have gone up and my server loads seem to have increased...this didn't happen straight after the change maybe a couple of days after, but I'm just trying to eliminate possible causes for my server loads

Title: Re: Convert your tables to InnoDB
Post by: minskog on February 16, 2006, 11:35:34 AM
Houston we have a problem:

I have a very large forum (over 1.400.000 posts/ 1.5 gb database on disk ), and I convert database to innodb to try o give it more perfomance.

Forums Version: 1.0.6.
Machine: P4 2.8 ht, hd 80 gb SATA 150, 1 Gb ram ECC. (Dell 1750)
Mysql version: 4.1.11a
S.O.: Debian sarge

But now the server are overload all time: Load average: 54.92 54.98 42.79


Any tip to tune the innodb ? I need to make it myisam?

I think that the problem es hard disk access.

Thanks in advance.
Title: Re: Convert your tables to InnoDB
Post by: Motorhead on February 16, 2006, 04:04:06 PM
MMmm looks like InnoDB was casuing me higher server loads I've switched back to MyIsam and they seem to be lower

this bit though

Quote
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)
) TYPE=HEAP;

How do I lose it or revert back to whatever it changed ???



Title: Re: Convert your tables to InnoDB
Post by: H on February 16, 2006, 04:30:58 PM
Delete that table and then restore the original one from the SMF SQL file. For the 1.1 series the query to recreate it for MyISAM is:

Code: [Select]
CREATE TABLE {$db_prefix}log_floodcontrol (
  ip char(16) NOT NULL default '                ',
  logTime int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (ip(16))
) TYPE=MyISAM;

Replace {$db_prefix} with your database prefix
Title: Re: Convert your tables to InnoDB
Post by: Motorhead on February 16, 2006, 06:35:02 PM
Thanks, worked a treat :)

Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on February 18, 2006, 01:04:11 PM
You should investigate why it was causing loads like that.
Title: Re: Convert your tables to InnoDB
Post by: Kjell H. on February 19, 2006, 09:35:18 PM

I converted my tables to InnoDB when I used the 1.1RC1 version.

After the upgrade to RC1, the tables went naturally back to MyISAM.

When I try to alter the tables back to InnoDB, query says it is successful, but the tables are still MyISAM.

Title: Re: Convert your tables to InnoDB
Post by: minskog on February 25, 2006, 07:47:49 AM
You should investigate why it was causing loads like that.

It is the mysqld process, and before to make the change to innodb it have load but not this crazy load, it was over 3 or 5.
Title: Re: Convert your tables to InnoDB
Post by: rosey on March 03, 2006, 02:04:42 PM
Quote
ALTER TABLE smf_log_search
TYPE=InnoDB;

it says table smf_log_search doesn't exist.  I see several tables that start with that:

log_search_messages
log_search_results
log_search_subjects
log_search_topics
log_search_words

is this because I created a fulltext search, and if so do I just run one of those TYPE=InnoDB queries for each of those tables?
Title: Re: Convert your tables to InnoDB
Post by: minskog on April 03, 2006, 01:02:27 PM
You should investigate why it was causing loads like that.

It is the mysqld process, and before to make the change to innodb it have load but not this crazy load, it was over 3 or 5.

I migrate the forum to new dedicated server, and the same. Have a lot of load either myisam or innodb. If I change the forum to maintenance mode, the load downs to 0.1. Then the problem is the mysql writes, i think. But now the forum are in a dedicated server with 1 gb ram, pentium 4 ht 3.0, and SATA disk.

Forum's Version: 1.0.7.
Diskspace: over 700 mb
Topics: 23.840
Posts: 1.253.355
Users:  1.405
Max users online: 235 (average: 120.65)

Title: Re: Convert your tables to InnoDB
Post by: Ben_S on April 03, 2006, 02:03:57 PM
You will probably want to tweak the InnoDB settings in my.cnf. This can be a pain though if you are already running InnoDB tables, do a google search for optimizing InnoDB settings.

Just a thought, what tables did you covert, it wasn't all of them was it?
Title: Re: Convert your tables to InnoDB
Post by: minskog on April 04, 2006, 06:19:44 AM
You will probably want to tweak the InnoDB settings in my.cnf. This can be a pain though if you are already running InnoDB tables, do a google search for optimizing InnoDB settings.

Just a thought, what tables did you covert, it wasn't all of them was it?

The problem is that fails with innodb and MyIsam, i tune innodb and the same (two ibdata 100mb for example), and the load goes crazy randomly.

Sorry by my poor english.
Title: Re: Convert your tables to InnoDB
Post by: minskog on April 05, 2006, 05:48:17 PM
seems that this type of query could be the problem, it takes a lot of time to execute it:

INSERT INTO smf_matches
SELECT DISTINCT t.ID_TOPIC, t.ID_FIRST_MSG, t.numReplies, 0 AS is_subject
FROM smf_topics AS t, smf_messages AS m
WHERE t.ID_TOPIC = m.ID_TOPIC
   AND m.ID_BOARD IN (28, 2, 3, 4, 5, 6, 7, 32, 30, 9, 10, 11, 12, 14, 15, 16, 17, 18, 35, 20, 21, 22, 23, 34, 26, 33, 31, 36, 37, 40, 42, 43, 44)
   AND m.body LIKE '%carla%' AND m.body LIKE '%bruni%'

There are a lot of querys like this, and they take a lot of time to execute them.

The database user have create temporary tables privileges.
Title: Re: Convert your tables to InnoDB
Post by: minskog on April 10, 2006, 04:52:53 AM
I migrate fron 1.0.7 to 1.1-rc2, what tables must be changed to innodb in 1.1-rc2?
Title: Re: Convert your tables to InnoDB
Post by: WhoIsShe on April 10, 2006, 05:57:13 PM
Today I discovered I was having locking issues on my forum "average of 16 processes, 2 running and 14 sleeping".  After some experimenting, I changed just smf_topics to innodb and the locking issues have gone away.  Yipee!
Title: Re: Convert your tables to InnoDB
Post by: WhoIsShe on April 10, 2006, 06:07:16 PM
The table smf_topics in particular was a bottleneck for me because it's updated on every pageview and I happen to get a lot of read-only guest traffic.  I'm glad that I didn't have to convert any other tables.  Now when I run status.php there are still 2 running processes on average but 0 locked processes.  Someone else might benefit from this tip: first convert smf_topics alone.
Title: Re: Convert your tables to InnoDB
Post by: Triangle on May 19, 2006, 02:05:08 PM
I just changed these to Innodb:

log_search_messages
log_search_results
log_search_subjects
log_search_topics
log_search_words

log_search_words at 475MB took 2 hours! Anyway, all those tables still show as MYISAM even though the query claimed to be successful. I changed flood_control to HEAP and it shows up correctly.

Now what??
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on May 20, 2006, 12:10:05 PM
Sounds like you have skip-innodb in your my.cnf file.
Title: Re: Convert your tables to InnoDB
Post by: Triangle on May 20, 2006, 12:55:46 PM
Thanks, I will check that out.
Title: Re: Convert your tables to InnoDB
Post by: WhoIsShe on June 21, 2006, 09:12:51 AM
I converted my tables to Innodb, but when I restarted the database it got corrupted and I had to drop and reconstruct those tables.  Any idea what may have happened?  :'(
Title: Re: Convert your tables to InnoDB
Post by: ivo2296 on July 26, 2006, 03:59:25 PM
Hi all,

I converted smf_log_topics to InnoDB and from 5MB the table became 16MB?!?

Is that normal with the bigger size?
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on July 26, 2006, 04:30:41 PM
Yes, InnoDB tables take up more space.
Title: Re: Convert your tables to InnoDB
Post by: ivo2296 on July 26, 2006, 04:55:37 PM
Yes, InnoDB tables take up more space.

Thanks :) that makes me sleep better :)
Title: Re: Convert your tables to InnoDB
Post by: !Hachi! on August 01, 2006, 12:40:28 AM
my forum mysql is around 60 mb and we are on stable vps.should i convert to innodb.
Total Posts: 75397
Total Topics: 6236
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on August 01, 2006, 05:57:38 AM
Entirely upto you, you should do the search ones at the very least though.
Title: Re: Convert your tables to InnoDB
Post by: !Hachi! on August 01, 2006, 06:48:02 AM
well googled it and didn't get any EASY answer.
      actually on my forum user online a tone time are more than 30 now.and spiders are always on site.and we hade trouble in openeing who's online list.every page including stats come up in 0.04 seconds "approx." but when we open it goes to 20+ seconds every time at everytime.asked host he said may be tables are corrputed restart the mysql service etc.
    errors like
Database Error:Can't create/write to file '/tmp/#sql_54f4_0.MYI' (Errcode: 122)
                  comes so i am thinking if innodb is solution i will like to do.let me know sorry i am not sql guru.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on August 01, 2006, 07:48:18 AM
root@shankly [~]# perror 122
System error: 122 = Disk quota exceeded

You need to take that up with your host.
Title: Re: Convert your tables to InnoDB
Post by: !Hachi! on August 01, 2006, 08:02:14 AM
i have shell access.Ben will you explain it bit more please.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on August 01, 2006, 08:54:11 AM
Database Error:Can't create/write to file '/tmp/#sql_54f4_0.MYI' (Errcode: 122) simply means that mysql couldnt write to the file because the user was over their disk space limit.
Title: Re: Convert your tables to InnoDB
Post by: !Hachi! on August 01, 2006, 08:56:24 AM
ok so should i give more space to /tmp folder or change some settings in mysql?i have vps so i am littel hesitate to ask host as i have full controll over server.
                  and what about dead who's online list?
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on August 01, 2006, 02:23:28 PM
Start be resolving your quota issues and then you can work on the rest.
Title: Re: Convert your tables to InnoDB
Post by: qtime on January 02, 2007, 02:09:36 PM
What is the smf_log_topics ??
It's 838 Mb with over 42.500.000 records.
I convert them to innoDB, after 24 hours it was ready, but the forum don't run after it, so I recovered the backup file.

Can I expect real speed improvement? And what about innoDB settings? Because the log_topics was 3,2 gig big after converting.

Any help will be much appreciated.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on January 02, 2007, 06:49:07 PM
log_topics keeps track of what topics a user has read and upto what posts to control the "new" indicator.

How large is your forum, 42.500.000 records sounds like quite a lot, although if you have a lot of posts and members then it probably isn't really
Title: Re: Convert your tables to InnoDB
Post by: qtime on January 03, 2007, 02:42:12 AM
 494834 Posts in 89291 Topics by 61881 Members.

Is it possible to "clean" this one to speed up, or is the large table not important for the speed?
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on January 03, 2007, 11:24:56 AM
Mines ~12,000,000 records and I've noticed no real difference in speed from a fresh install, although you do have a fair few more than me, will bring it up with the devs though, could be handy to prune it a bit every now and then.
Title: Re: Convert your tables to InnoDB
Post by: ivo2296 on February 08, 2007, 08:00:15 AM
Any news about latest SMF 1.1.1 ?
I mean - should be edited the first post?
Title: Re: Convert your tables to InnoDB
Post by: qtime on February 08, 2007, 12:13:40 PM
How do I prune a table?
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on February 08, 2007, 11:36:28 PM
Why do you want to prune a table?
Title: Re: Convert your tables to InnoDB
Post by: Simplemachines Cowboy on February 09, 2007, 12:36:58 AM
I presume he wants to prune a db table because of what Ben_S said a few posts before:

Mines ~12,000,000 records and I've noticed no real difference in speed from a fresh install, although you do have a fair few more than me, will bring it up with the devs though, could be handy to prune it a bit every now and then.
Title: Re: Convert your tables to InnoDB
Post by: qtime on February 09, 2007, 01:40:01 AM
Correctly Cowboy, but how to do that? Because it's 1 gig and I want to convert it to innodb, this will cost 3 days.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on February 09, 2007, 12:25:20 PM
It would have to be done with a script that only deletes older data, at this stage such a script doesn't exist. I've suggested it to the devs so it may appear at some stage in a future version.
Title: Re: Convert your tables to InnoDB
Post by: qtime on February 09, 2007, 04:52:17 PM
What will happen if I replaced the table with an empty one from a fresh install?
Title: Re: Convert your tables to InnoDB
Post by: webvision on February 10, 2007, 05:36:53 AM
thanks for sharing nice info
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on February 10, 2007, 07:48:53 AM
What will happen if I replaced the table with an empty one from a fresh install?

All topics will be marked as unread.
Title: Re: Convert your tables to InnoDB
Post by: qtime on February 10, 2007, 11:13:12 AM
ok, that's what I was hoping.
Is there a command to empty the table in php myadmin?
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on February 10, 2007, 09:31:52 PM
TRUNCATE TABLE tablenamehere;
Title: Re: Convert your tables to InnoDB
Post by: qtime on February 10, 2007, 09:34:16 PM
ok, I will try, thanks for answering.
Title: Re: Convert your tables to InnoDB
Post by: Saku on February 22, 2007, 08:41:22 AM
I just changed these to Innodb:

log_search_messages
log_search_results
log_search_subjects
log_search_topics
log_search_words

log_search_words at 475MB took 2 hours! Anyway, all those tables still show as MYISAM even though the query claimed to be successful. I changed flood_control to HEAP and it shows up correctly.

Now what??

I have the same problem, If I remove skip-innodb from my.cnf Mysql wont restrat
here is my.cnf file : 

Code: [Select]
[mysqld]
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
log-bin
server-id=1

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

Any one know how to enable INNODB support in MySQL 4.1.21 ?
Title: Re: Convert your tables to InnoDB
Post by: emrys01 on March 10, 2007, 12:55:10 AM
Why do you want to prune a table?

you can prune a table, but you can't prune a fish.

(oh, i'm so sorry.  honestly.)
Title: Re: Convert your tables to InnoDB
Post by: etdwh on March 10, 2007, 05:18:07 AM
HI all,

I will be migrating our forum from phpBB to SMF soon.
We have about 160,000++ posts, and 2000+ members.

Should i convert the tables to InnoDB BEFORE the migration or AFTER the migration?
Any pro's/con's of both ???
Title: Re: Convert your tables to InnoDB
Post by: qtime on March 10, 2007, 05:21:44 AM
I think after, because it will be set back to MyISAM. But I am not a smf developer :).
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on March 10, 2007, 11:18:16 AM
You can set the destination SMF tables to innodb if you need, but you don't need to touch the phpBB db since the converters don't change the source database at all.
Title: Re: Convert your tables to InnoDB
Post by: qtime on August 08, 2007, 07:07:23 PM
What should be faster
setting to heap or InnoDB for the table log online?
Now I have setup to heap.
Is it possible for mysql to read more fields at once in heap mode or can it be locked?
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on August 08, 2007, 07:20:18 PM
As per the suggestion, heap since it's all in ram.
Title: Re: Convert your tables to InnoDB
Post by: qtime on August 08, 2007, 07:25:46 PM
ok, thanks for the fast replay, I assume that, but I did not know for sure if there could be a locking problem.
Title: Re: Convert your tables to InnoDB
Post by: FTL_error on August 24, 2007, 02:30:51 AM
This is absolutely powerful stuff. My forum (http://bbs.freetalklive.com/index.php) is now running a whole lot faster, with 265840 Posts in 14664 Topics by 1679 Members. Maybe SMF should use InnoDB for these tables by default, or at least offer InnoDB as an installation option.
Title: Re: Convert your tables to InnoDB
Post by: heavyccasey on September 01, 2007, 01:26:44 AM
It's supposedly really slow and unnecessary for small forums.
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on September 01, 2007, 11:53:41 AM
It isn't that InnoDB is slow, but just that it has some disadvantages. Once you get big enough to need it, then the advantages outweigh the disadvantages.
Title: Re: Convert your tables to InnoDB
Post by: tmdg on September 01, 2007, 12:19:46 PM
It's supposedly really slow and unnecessary for small forums.

But how big is a small forum.     I'm new to administering smf and investigating what can be done  to improve performance.    I've not seen any guide lines (Havn't been looking long though.) as to what size things have to et to before they teh forum is deemed to be large enough to gain advantage from using InnoDB.

t.
Tom.
Title: Re: Convert your tables to InnoDB
Post by: SleePy on September 02, 2007, 12:47:27 AM
Did you read some of the stickies at the top of the board?
I believe some of them explain about InnoDB.

How large is your forum?
How many online in a time period?
Title: Re: Convert your tables to InnoDB
Post by: tmdg on September 02, 2007, 04:52:16 PM
Did you read some of the stickies at the top of the board?
I believe some of them explain about InnoDB.
Well I read the starter post in
http://www.simplemachines.org/community/index.php?topic=50217.0  wich is of course this thread.

But not every post in the thread.

How large is your forum?

Well I don't think our forum is that big.   
Uncompressed the backup of the database is less than 10 Meg.
Just under 100,000 posts and about 7,000 topics.
in July we have 200,000 page views and 150,000 in August give or take.
How many on-line in a time period?
Typically there are not more then say 30 people on lone at once  in a 15 minute period.
Though I think it it typically much less than that.

I'm not expecting a real answer to the question.    I was really trying to point out  the problem of knowing. what is big and what is small.  It's a bit like asking how tall do you have to be to be tall.

I guess it's really all  down to transaction rate really. 

Perhaps the real quetion to ask here is.

Does converting the tables indicated have the same effect as optimizing?
I haven't seen a post the says "I tried the optimization but that made no difference, So I converted to InnoDB and now it goes like the wind".  Or words to that effect.

I did read teh post teh say something like "I only have a small DB but after converting to InnoDB it goes like you wouldn't belive".

Regatrds.
Tom.
Title: Re: Convert your tables to InnoDB
Post by: Webrunner on September 03, 2007, 04:31:02 AM
You should first do a optimize and repair before you transfer them. ;)

And yes, innodb takes up more space, but the performance gain ism worth it (that is, if you are having locking problems)
Title: Re: Convert your tables to InnoDB
Post by: Something like that on September 28, 2007, 01:44:23 AM
It's supposedly really slow and unnecessary for small forums.

But how big is a small forum.     I'm new to administering smf and investigating what can be done  to improve performance.    I've not seen any guide lines (Havn't been looking long though.) as to what size things have to et to before they teh forum is deemed to be large enough to gain advantage from using InnoDB.

t.
Tom.

That will depend on your server hardware. On my forums, I found the severe slowness started at around 100 simultaneous users. I'd recommend the changes above if you have 50+ simultaneous users.
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 02, 2007, 10:44:43 PM
If you are running MySQL 5.0.3 or later, you can also convert smf_log_online to type MEMORY/HEAP. First, you need to convert the "url" field to type varchar, and put a length of 512 or so. I chose 1024 for good measure (so what if i waste 512 bytes of RAM per user: even with 1000 logged in users, that's only 512 KB of RAM wasted).

Before MySQL 5.0.3, a varchar was limited to 256 bytes, which isn't long enough for the url field in all cases.

You will get (temporary) forum errors if the length of the value of the url field ever exceeds the length of the url field, so convert url at your own risk.
Title: Re: Convert your tables to InnoDB
Post by: knibal on October 03, 2007, 11:16:37 AM
Hi,

This is my server status:

http://www.hastalamuerte.net/foros/status.php?mysql_info=1

Should I make this changues? I´m usually over 50 simultaneous users
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on October 03, 2007, 11:56:37 AM
If you are running MySQL 5.0.3 or later, you can also convert smf_log_online to type MEMORY/HEAP. First, you need to convert the "url" field to type varchar, and put a length of 512 or so. I chose 1024 for good measure (so what if i waste 512 bytes of RAM per user: even with 1000 logged in users, that's only 512 KB of RAM wasted).

Before MySQL 5.0.3, a varchar was limited to 256 bytes, which isn't long enough for the url field in all cases.

You will get (temporary) forum errors if the length of the value of the url field ever exceeds the length of the url field, so convert url at your own risk.

Good tip, also since you are using varchar, you aren't actually wasting as much ram as you think since it allocates whatever the length is plus 2 bytes to store the actual length (1 if it's less than 256).

Same would probably work for session too, would just have to set the data field to a very long varchar.
Title: Re: Convert your tables to InnoDB
Post by: nitins60 on October 04, 2007, 08:03:24 PM
MySQL has support for two major database storage engines: MyISAM and InnoDB.  MyISAM has, at least in past releases, been the default, and is the default for SMF installations.  And, this is for good reason: MyISAM reads tables much faster than InnoDB.

However, InnoDB has some major benefits over MyISAM.  First, it's important to have a basic understanding of how a query works.  Let's say I run a query like this:

SELECT ID_MSG
FROM smf_messages
WHERE subject = 'Convert your tables to InnoDB'
LIMIT 1;

While MySQL is looking for the specific topic (which can take some time, since the subject column has no index) no one can write to the table.  That means, no one can post to the table.  For the messages table, this isn't a huge problem - and it has to be MyISAM for a FULLTEXT index anyway.

However, it matters more for tables like topics, log_topics, and friends.  For example, every time you view a topic, this happens:

UPDATE smf_topics
SET numViews = numViews + 1
WHERE ID_TOPIC = ###
LIMIT 1;

If the table is locked, it will have to wait for... whatever is happening to finish.  The same goes for multiple people viewing multiple topics at once.  They have to wait in line for the topics table to become available.

This is not true for InnoDB.  If two people view the same topic, yes... they will have to wait for the numViews update.  But if they view different topics, it's immediate.  This is a huge difference, and can make more difference than you'd expect, depending on what's going on on your server.  But, remember... there is a penalty.  MyISAM may lock badly, but it still reads faster.  For tables that don't change much, like categories, there's usually no reason to use InnoDB.

For example, if you look at status.php, and see:

95 (87 sleeping, 2 running, 6 locked)

That means that the running queries are locking other queries, making them wait.  This usually means you should be using InnoDB for some table which you aren't, but doesn't always mean that.  With SMF, when your forum reaches a point of activity where it's benefical, you can run the following queries to change your tables to InnoDB:

ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
TYPE=InnoDB;
ALTER TABLE smf_log_actions
TYPE=InnoDB;
ALTER TABLE smf_log_boards
TYPE=InnoDB;
ALTER TABLE smf_log_errors
TYPE=InnoDB;
ALTER TABLE smf_log_karma
TYPE=InnoDB;
ALTER TABLE smf_log_mark_read
TYPE=InnoDB;
ALTER TABLE smf_log_online
TYPE=InnoDB;
ALTER TABLE smf_log_search
TYPE=InnoDB;
ALTER TABLE smf_log_topics
TYPE=InnoDB;
ALTER TABLE smf_members
TYPE=InnoDB;
ALTER TABLE smf_pm_recipients
TYPE=InnoDB;
ALTER TABLE smf_sessions
TYPE=InnoDB;
ALTER TABLE smf_settings
TYPE=InnoDB;
ALTER TABLE smf_topics
TYPE=InnoDB;

Additionally, you'll probably want log_floodcontrol 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)
) TYPE=HEAP;

What is phpMyAdmin? (http://www.simplemachines.org/community/index.php?topic=21919.0)  Before running these queries, it's a good idea to put your forum in maintenance mode.  They will take, for large forums, quite some time (10 minutes, in cases.)  What is repair_settings.php? (http://www.simplemachines.org/community/index.php?topic=18096.0)

-[Unknown]

i am newbie n using phpmyadmin/dealing with Mysql. However my forum got 3000+ members and 5000+ posts and 2000+ attachments. Should i convert to innodb to get better performance?
Can any1 make simple web based conversion script (to innodb)? It'll be useful for newbies also.

Some of the factors are exceeding in status.php. Pleas check my status.php (http://www.s60-forum.org/status.php) and give me suggestions to run it faster, how do i do them? :)
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 04, 2007, 10:04:24 PM
Good tip, also since you are using varchar, you aren't actually wasting as much ram as you think since it allocates whatever the length is plus 2 bytes to store the actual length (1 if it's less than 256).

That's generally true, but not for MEMORY tables. Memory tables use a fixed-length row storage format, which is one of the reasons why they don't support text or blob column types.

Quote
Same would probably work for session too, would just have to set the data field to a very long varchar.

Yeah, I took at look at the 440 elements currently in my session table, and some entries in the data column were 12k characters long (when they're long, it's full of SQL statements). If I assign the full 64k length for margin, that becomes a large chunk of RAM used.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on October 05, 2007, 07:33:34 AM
Ah, that kinda puts the dampers on that idea ;D
Title: Re: Convert your tables to InnoDB
Post by: moofa on October 11, 2007, 12:26:42 AM
And a more complte InnoDB Conversion

Code: [Select]
ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
TYPE=InnoDB;
ALTER TABLE smf_members
TYPE=InnoDB;
ALTER TABLE smf_pm_recipients
TYPE=InnoDB;
ALTER TABLE smf_sessions
TYPE=InnoDB;
ALTER TABLE smf_settings
TYPE=InnoDB;
ALTER TABLE smf_topics
TYPE=InnoDB;
ALTER TABLE smf_ads
TYPE=InnoDB;
ALTER TABLE smf_ads_settings
TYPE=InnoDB;
ALTER TABLE smf_awards
TYPE=InnoDB;
ALTER TABLE smf_awards_members
TYPE=InnoDB;
ALTER TABLE smf_ban_groups
TYPE=InnoDB;
ALTER TABLE smf_ban_items
TYPE=InnoDB;
ALTER TABLE smf_boards
TYPE=InnoDB;
ALTER TABLE smf_board_permissions
TYPE=InnoDB;
ALTER TABLE smf_calendar
TYPE=InnoDB;
ALTER TABLE smf_calendar_holidays
TYPE=InnoDB;
ALTER TABLE smf_categories
TYPE=InnoDB;
ALTER TABLE smf_gallery_cat
TYPE=InnoDB;
ALTER TABLE smf_gallery_comment
TYPE=InnoDB;
ALTER TABLE smf_gallery_pic
TYPE=InnoDB;
ALTER TABLE smf_gallery_report
TYPE=InnoDB;
ALTER TABLE smf_googletagged
TYPE=InnoDB;
ALTER TABLE smf_membergroups
TYPE=InnoDB;
ALTER TABLE smf_messages
TYPE=InnoDB;
ALTER TABLE smf_message_icons
TYPE=InnoDB;
ALTER TABLE smf_moderators
TYPE=InnoDB;
ALTER TABLE smf_ob_googlebot_stats
TYPE=InnoDB;
ALTER TABLE smf_package_servers
TYPE=InnoDB;
ALTER TABLE smf_permissions
TYPE=InnoDB;
ALTER TABLE smf_personal_messages
TYPE=InnoDB;
ALTER TABLE smf_poll_choices
TYPE=InnoDB;
ALTER TABLE smf_subscriptions
TYPE=InnoDB;
ALTER TABLE smf_themes
TYPE=InnoDB;
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on October 11, 2007, 04:35:47 AM
Not all tables benefit from being InnoDB, in fact doing it on messages means you loose some advantages such as the ability to use full text search.
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 11, 2007, 09:31:26 PM
And a more complte InnoDB Conversion

Code: [Select]
ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
TYPE=InnoDB;
ALTER TABLE smf_members
TYPE=InnoDB;
ALTER TABLE smf_pm_recipients
TYPE=InnoDB;
ALTER TABLE smf_sessions
TYPE=InnoDB;
ALTER TABLE smf_settings
TYPE=InnoDB;
ALTER TABLE smf_topics
TYPE=InnoDB;
ALTER TABLE smf_ads
TYPE=InnoDB;
ALTER TABLE smf_ads_settings
TYPE=InnoDB;
ALTER TABLE smf_awards
TYPE=InnoDB;
ALTER TABLE smf_awards_members
TYPE=InnoDB;
ALTER TABLE smf_ban_groups
TYPE=InnoDB;
ALTER TABLE smf_ban_items
TYPE=InnoDB;
ALTER TABLE smf_boards
TYPE=InnoDB;
ALTER TABLE smf_board_permissions
TYPE=InnoDB;
ALTER TABLE smf_calendar
TYPE=InnoDB;
ALTER TABLE smf_calendar_holidays
TYPE=InnoDB;
ALTER TABLE smf_categories
TYPE=InnoDB;
ALTER TABLE smf_gallery_cat
TYPE=InnoDB;
ALTER TABLE smf_gallery_comment
TYPE=InnoDB;
ALTER TABLE smf_gallery_pic
TYPE=InnoDB;
ALTER TABLE smf_gallery_report
TYPE=InnoDB;
ALTER TABLE smf_googletagged
TYPE=InnoDB;
ALTER TABLE smf_membergroups
TYPE=InnoDB;
ALTER TABLE smf_messages
TYPE=InnoDB;
ALTER TABLE smf_message_icons
TYPE=InnoDB;
ALTER TABLE smf_moderators
TYPE=InnoDB;
ALTER TABLE smf_ob_googlebot_stats
TYPE=InnoDB;
ALTER TABLE smf_package_servers
TYPE=InnoDB;
ALTER TABLE smf_permissions
TYPE=InnoDB;
ALTER TABLE smf_personal_messages
TYPE=InnoDB;
ALTER TABLE smf_poll_choices
TYPE=InnoDB;
ALTER TABLE smf_subscriptions
TYPE=InnoDB;
ALTER TABLE smf_themes
TYPE=InnoDB;

You're better off to leave small tables that are infrequently updated as MyISAM. Read performance is very fast for MyISAM. InnoDB is used because it allows concurrent actions while MyISAM doesn't.
Title: Re: Convert your tables to InnoDB
Post by: mlsred on October 12, 2007, 10:04:31 AM
Hi All

I have converted all the tables as recommended in the first post but still have some large tables with lots of records set to MyISAM (see attached). My forum is still preforming intermittently i.e. one thread will appear in less than a second and the next takes 10 seconds.

Should I convert any of these other larger tables?

Cheers
M
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 13, 2007, 12:08:37 AM
Hi All

I have converted all the tables as recommended in the first post but still have some large tables with lots of records set to MyISAM (see attached). My forum is still preforming intermittently i.e. one thread will appear in less than a second and the next takes 10 seconds.

Should I convert any of these other larger tables?

Cheers
M

I'd convert them all except perhaps phosmf_themes. InnoDB is almost always a better choice (the exception being tables that are rarely changed).
Title: Re: Convert your tables to InnoDB
Post by: mlsred on October 24, 2007, 07:52:19 AM
Hi All

I have converted all the tables as recommended in the first post but still have some large tables with lots of records set to MyISAM (see attached). My forum is still preforming intermittently i.e. one thread will appear in less than a second and the next takes 10 seconds.

Should I convert any of these other larger tables?

Cheers
M

I'd convert them all except perhaps phosmf_themes. InnoDB is almost always a better choice (the exception being tables that are rarely changed).

Thanks for the reply.. I thought, from reading elswhere on the board, that there isn't really a benefit to changing the Messages & PM table and I will loose the ability to use fulltext search on them?
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 25, 2007, 11:05:38 PM
Hi All

I have converted all the tables as recommended in the first post but still have some large tables with lots of records set to MyISAM (see attached). My forum is still preforming intermittently i.e. one thread will appear in less than a second and the next takes 10 seconds.

Should I convert any of these other larger tables?

Cheers
M

I'd convert them all except perhaps phosmf_themes. InnoDB is almost always a better choice (the exception being tables that are rarely changed).

Thanks for the reply.. I thought, from reading elswhere on the board, that there isn't really a benefit to changing the Messages & PM table and I will loose the ability to use fulltext search on them?

How frequently are posts made? If you only have a few posts per hour, it's no big deal. If there are several posts a minute, it's an issue. Every time someone is making a new post when the messages table is MyISAM, it stops all queries from using the messages table until the update is done: that includes the forum index, the thread listings, and the thread displays (essentially every page outside of PM's and profiles). You do lose the fulltext searching, but if you're suffering from a lagging forum, it's one of the first tables I'd convert.
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on October 26, 2007, 01:50:50 AM
Little secret. There is work being done to get Sphinx (http://www.sphinxsearch.com/) support working in SMF (probably as a mod). A few places are using it already for testing.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on October 26, 2007, 04:28:27 AM
And it works very well :).
Title: Re: Convert your tables to InnoDB
Post by: mlsred on October 26, 2007, 07:32:07 AM
How frequently are posts made? If you only have a few posts per hour, it's no big deal. If there are several posts a minute, it's an issue. Every time someone is making a new post when the messages table is MyISAM, it stops all queries from using the messages table until the update is done: that includes the forum index, the thread listings, and the thread displays (essentially every page outside of PM's and profiles). You do lose the fulltext searching, but if you're suffering from a lagging forum, it's one of the first tables I'd convert.

Thanks, converted PM but when I went to convert messages i got this:

Error

SQL query:

ALTER TABLE phosmf_messages TYPE = InnoDB

MySQL said: Documentation
#1214 - The used table type doesn't support FULLTEXT indexes

how do I get around this?

Cheers
M
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on October 26, 2007, 08:16:16 AM
You would need to drop the FULL TEXT index and change to a different search method, although the reality is unless you have numerous posts a second it isn't going to really help all that much.
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 26, 2007, 01:55:03 PM
You would need to drop the FULL TEXT index and change to a different search method, although the reality is unless you have numerous posts a second it isn't going to really help all that much.

Even if your forum has just a high read load like mine, posting with MyISAM has to wait for all reads to finish, and also causes all those reads to block until the post has been entered. That's with a daily average of 1 hit per second, but during peak times of 5-10+ hits per second, the forum would crawl brutally. MyISAM is very poorly suited for use by a forum, mainly because it has almost no support for parallel read and write tasks.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on October 27, 2007, 01:42:36 PM
I've not seen any issues whatsoever with my messages table, no evidence of any locking going on, but then most locking would probably be caused by searches and using Sphinx, search is no issue.
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 27, 2007, 10:22:36 PM
I've not seen any issues whatsoever with my messages table, no evidence of any locking going on, but then most locking would probably be caused by searches and using Sphinx, search is no issue.

Sphinx looks like a lot of fun. It'll be neat when it's implemented and tested :)
Title: Re: Convert your tables to InnoDB
Post by: pcigre on November 24, 2007, 11:40:20 AM
Little secret. There is work being done to get Sphinx (http://www.sphinxsearch.com/) support working in SMF (probably as a mod). A few places are using it already for testing.

That would be great... We all have big problems with search.
Title: Re: Convert your tables to InnoDB
Post by: Douglas on November 29, 2007, 11:15:56 PM
Little secret. There is work being done to get Sphinx (http://www.sphinxsearch.com/) support working in SMF (probably as a mod). A few places are using it already for testing.

And it works very well :)
AMEN TO THAT!

I've not seen any issues whatsoever with my messages table, no evidence of any locking going on,
I have, and I am, unfortunately.  Havin' to change the Ville's _messages DB over to InnoDB.  ::grumbles::
Title: Re: Convert your tables to InnoDB
Post by: pcigre on November 30, 2007, 05:23:29 AM
Any1 to shere seacret about making Spinix to work with others?
Title: Re: Convert your tables to InnoDB
Post by: IchBin™ on December 15, 2007, 12:02:19 AM
No secrets at this point. :)
Title: Re: Convert your tables to InnoDB
Post by: Something like that on December 24, 2007, 07:36:29 PM
I ran into a bug with converting smf_boards and smf_categories to InnoDB where the main forum page doesn't show boards and categories in the right order. The bug and a fix is here: http://www.simplemachines.org/community/index.php?topic=212877.0
Title: Re: Convert your tables to InnoDB
Post by: Something like that on January 15, 2008, 04:46:20 PM
I've since converted all my tables to InnoDB (except the MEMORY ones).

One thing I haven't seen mentioned that really helps performance is to increase InnoDB's buffers. You can do this by editing my.cnf and changing innodb_buffer_pool_size.

On a pure InnoDB server (not doing web or anything else), people recommend setting it to as much as 80% of available RAM. As I'm using a mixed server with 1 GB or RAM, I set it to 256 MB: innodb_buffer_pool_size=256M . If you are on a 1 GB machine, you might want to set it a little lower if you're using Apache, say 128M, if you find Apache processes are making your system swap.

I also recommend setting innodb_flush_method=O_DIRECT on Linux systems. This prevents Linux from caching the database files itself. It's pointless for Linux to cache them as MySQL already has them cached if you have increased innodb_buffer_pool_size.

After changing these settings, SHOW STATUS shows that MySQL almost never has to touch the disks when doing reads for SMF. Over 99.95% of my reads come straight from RAM!
Title: Re: Convert your tables to InnoDB
Post by: Stüldt Håjt on January 15, 2008, 06:16:50 PM
If you are running MySQL 5.0.3 or later, you can also convert smf_log_online to type MEMORY/HEAP. First, you need to convert the "url" field to type varchar, and put a length of 512 or so. I chose 1024 for good measure (so what if i waste 512 bytes of RAM per user: even with 1000 logged in users, that's only 512 KB of RAM wasted).

Before MySQL 5.0.3, a varchar was limited to 256 bytes, which isn't long enough for the url field in all cases.

You will get (temporary) forum errors if the length of the value of the url field ever exceeds the length of the url field, so convert url at your own risk.

Can you give specific instructions how to do this.
Title: Re: Convert your tables to InnoDB
Post by: Something like that on January 15, 2008, 06:52:41 PM
If you are running MySQL 5.0.3 or later, you can also convert smf_log_online to type MEMORY/HEAP. First, you need to convert the "url" field to type varchar, and put a length of 512 or so. I chose 1024 for good measure (so what if i waste 512 bytes of RAM per user: even with 1000 logged in users, that's only 512 KB of RAM wasted).

Before MySQL 5.0.3, a varchar was limited to 256 bytes, which isn't long enough for the url field in all cases.

You will get (temporary) forum errors if the length of the value of the url field ever exceeds the length of the url field, so convert url at your own risk.

Can you give specific instructions how to do this.

I did it with phpMyAdmin myself. It's easier to not screw up that way.
Title: Re: Convert your tables to InnoDB
Post by: Webrunner on January 22, 2008, 04:29:37 PM
I use sphinx and it works very well :)
Title: Re: Convert your tables to InnoDB
Post by: zukdj on January 22, 2008, 08:09:28 PM
My forum is very slow, I'm on a shared server on PowWeb but I know very little about MySQL. Is there an easy way to tune up my forum to make it fast? I looked in the MySQL database and I'm completely lost.
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on January 22, 2008, 11:58:00 PM
I used to manage a forum running on PowWeb. There isn't much you can do, their service has gone down in quality significantly since they were bought out. (They've been packing their clusters much heavier since that time.)

About the only thing you can try to do is make sure hostname lookups are off and you've tried the tips in the stickies. Beyond that, I encourage leaving PowWeb.
Title: Re: Convert your tables to InnoDB
Post by: slackerpunk on January 23, 2008, 05:04:38 AM
hi not sure if this is the right place to ask this question but how do you repair InnoDB tables
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on January 23, 2008, 05:07:23 AM
Same way as MyISAM tables,

REPAIR TABLE table_name
Title: Re: Convert your tables to InnoDB
Post by: Chriss Cohn on January 26, 2008, 06:27:15 PM
So to have a summarizing (im missing a sticky about that), which tables should converted to innoDB and what additional "tuning" for innoDB should we set into our my.cnf ???

Regards, Christian
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on January 26, 2008, 06:57:42 PM
It all depends on your board access patterns and your traffic. Heavy posting needs different optimization than heavy viewing.

In general, INNODB works best for tables that have a lot of insertions, MyISAM is faster for tables that get a lot of SELECTs. Also, once you get heavier traffic, you need to increase the buffers for INNODB or you'll lose the advantage it gives.
Title: Re: Convert your tables to InnoDB
Post by: Chriss Cohn on February 02, 2008, 12:51:48 PM
What i mean is that the first post is outdated and i doubt that all the tables have the same name as in the 1.0.x branch.....
So something summarizing for the 1.1.x version would be great (the tables you shoud AT LEAST convert)....

Regards, Christian
Title: Re: Convert your tables to InnoDB
Post by: Something like that on February 03, 2008, 05:12:13 PM
What i mean is that the first post is outdated and i doubt that all the tables have the same name as in the 1.0.x branch.....
So something summarizing for the 1.1.x version would be great (the tables you shoud AT LEAST convert)....

Regards, Christian


You can safely convert all of them, except smf_boards. SMF relies on smf_boards being kept sorted on boardOrder by MyISAM, but InnoDB will not keep the sorted order.

You'll also want to increase innodb_buffer_pool_size in my.cnf. If you have 1 GB or less, I'd set it to quarter of your RAM, and to half if you have more (this is for machines that are also doing webserving, etc). It'd also set the option innodb_flush_method=O_DIRECT if you increase innodb_buffer_pool_size.
Title: Re: Convert your tables to InnoDB
Post by: Chriss Cohn on February 03, 2008, 07:04:19 PM
All except of one?  :o
No i mean, thats just YOUR oppinion.... i want to hear some others first....
also the most important:
What is at a upgrade to SMF 2.0 ??? DO i then first need to make all tables back to MyIsam first?

Regards, Christian
Title: Re: Convert your tables to InnoDB
Post by: AleXit on April 17, 2008, 03:22:19 PM
Hello,
I converted all my tables to InnoDB... everything goes ok except one bug:
The order of my board are completely reverted, and I can NOT change it anymore.
Moreover, some subforum of some boards are not visibile in the board index:
http://www.studenti.fi.it/forum/index.php?action=forum
Look, in the category "corsi di laurea" forums are not in alphabetical order.

Changes from admin panel does not have effects :(

I think some tables are corrupted or similar.. (smf_boards ?)

What should I do?  :-\
Title: Re: Convert your tables to InnoDB
Post by: Overseer on April 17, 2008, 04:38:27 PM
you shouldnt convert all.. didnt you read the post?
Title: Re: Convert your tables to InnoDB
Post by: AleXit on April 17, 2008, 04:51:13 PM
you shouldnt convert all.. didnt you read the post?
Yes I read them but I would like to have better performances ...
No one report corruption table issue, at most slowness...

So, what it could be gone wrong? Tables seem ok, not corrupted and well optimized, but I cannot change order of board and categories !

Please help me...  :(
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on April 17, 2008, 04:52:12 PM
I converted all my tables to InnoDB... everything goes ok except one bug:
The order of my board are completely reverted, and I can NOT change it anymore.

You shouldn't convert all tables to InnoDB, problems like this will surface.

The quickest fix is to go into the admin and change the order of one board and one category. Switch them to a different position, save, then switch them back. The reason is that the board re-order changes the order the rows are stored in the database (to speed up display, I believe). When you convert, they go to numerical order, not display order.


you shouldnt convert all.. didnt you read the post?
Yes I read them but I would like to have better performances ...
No one report corruption table issue, at most slowness...

InnoDB isn't always the faster table type. If it was, it would be used by default. InnoDB is good for tables with lots of inserts happening along with selects. The board table doesn't change often enough to make a switch worthwhile for most forums.

Note that switching certain tables to InnoDB also disables the ability to use a fulltext search index. You need to use a custom index instead.
Title: Re: Convert your tables to InnoDB
Post by: AleXit on April 17, 2008, 05:02:49 PM
Ok.. I know, I made a big mistake :(

I have already tried to change board ordering as you suggests, but nothing happen! In the admin board order looks ok, but in the index board not.
I created also a new test forum and set it at the top of a category: in the index of the board it went to the bottom!

Re-covert some tables to MyISAM, could help?
Title: Re: Convert your tables to InnoDB
Post by: IchBin™ on April 17, 2008, 05:34:04 PM
Why not just restore to a backup before you made the changes? You did back up right?
Title: Re: Convert your tables to InnoDB
Post by: AleXit on April 17, 2008, 05:45:59 PM
Why not just restore to a backup before you made the changes? You did back up right?
Ehm..  :-[
Yes I have a backup but it is not made exactly before the conversion...

I could restore only the boards table... it's "smf_boards", right?

There is not a way to reorder it correctly?
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on April 17, 2008, 06:17:15 PM
You can try converting back to MyISAM and doing what I suggested previously.
Title: Re: Convert your tables to InnoDB
Post by: AleXit on April 17, 2008, 06:30:06 PM
You can try converting back to MyISAM and doing what I suggested previously.
I solved before reading this post: i converted back "smf_boards" to MyISAM, and order it by "boardOrder" 
This has solved the issue...:)

Now.. which table you suggest it is best to reconvert to MyISAM ?
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on April 17, 2008, 06:47:51 PM
Pretty much any not in the suggested list for InnoDB.
Title: Re: Convert your tables to InnoDB
Post by: AleXit on April 17, 2008, 06:55:18 PM
Pretty much any not in the suggested list for InnoDB.
Yes but I think the list in first post of this thread is not updated for smf 1.1.4 (some tables are changed...)
Anyone can confirm that?
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on April 17, 2008, 06:57:48 PM
The only changes are additional log_search, they can probably be InnoDB.
Title: Re: Convert your tables to InnoDB
Post by: Skuzzy on April 18, 2008, 08:31:10 AM
I should think anything not using FULLTEXT should be able to convert to InnoDB.  It is just a trade-off.  MyISAM is faster than InnoDB, but at the expense of low granularity locking which can kill your forum performance on tables that have a high number of concurrent UPDATES and SELECTS.

For instance, it really makes no sense to make the 'membergroups' table InnoDB as it is mostly read and not updated.  In this case, it is faster to leave it MyISAM.

Of course, InnoDB is usually more resilient than MyISAM, so take that into consideration as well.

By the way, if you have a large forum, you probably will want to make sure you turn on the use of separate files for the tables in InnoDB, as the default single file can get really large, really fast and can slow down accesses.
Title: Re: Convert your tables to InnoDB
Post by: AleXit on April 18, 2008, 09:00:39 AM
Ok, I reverted the wrong-converted tables to MyISAM, following the list in first post.

Thank you for you help guys ;)
Title: Re: Convert your tables to InnoDB
Post by: Sverre on April 25, 2008, 07:03:28 AM
Should really use ENGINE instead of TYPE.

If you are on a MySQL version which supports it, should all tables, whether you've converted them to InnoDB or not, be changed to use ENGINE?

It all depends on your board access patterns and your traffic. Heavy posting needs different optimization than heavy viewing.

In general, INNODB works best for tables that have a lot of insertions, MyISAM is faster for tables that get a lot of SELECTs. Also, once you get heavier traffic, you need to increase the buffers for INNODB or you'll lose the advantage it gives.

I think I speak for a lot of SMF admins out there, or at least I hope I'm not the only one, when I say that a more detailed guide to help us identify the need to convert tables to InnoDB would be greatly appreciated.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on April 28, 2008, 06:01:49 AM
If you are on a MySQL version which supports it, should all tables, whether you've converted them to InnoDB or not, be changed to use ENGINE?

Engine & type are the same thing, just the usage of type is depreciated and removed from MySQL 6. Once the tables have been converted it makes no difference what command you used.

Quote
I think I speak for a lot of SMF admins out there, or at least I hope I'm not the only one, when I say that a more detailed guide to help us identify the need to convert tables to InnoDB would be greatly appreciated.

I'd only convert the tables recommended in the first post in this topic and the log_search_* tables that are new to 1.1.

Converting _messages I definitely would not recommend, regardless of whether you use FULLTEXT search or not, in my experience the additional overhead of InnoDB on this table does not justify the benefits it brings.
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on July 09, 2008, 07:37:10 AM
Entirely upto you, you should do the search ones at the very least though.
Why?
Title: Re: Convert your tables to InnoDB
Post by: evgenydeep on September 05, 2008, 02:59:23 PM
Hello "community" ;-), can dear ALL advise, which tables is better to make InooDB?
My forum description is here: http://www.simplemachines.org/community/index.php?topic=259843.0
Title: Re: Convert your tables to InnoDB
Post by: evgenydeep on September 06, 2008, 02:40:27 AM
After readind this topic from first to last message, i decided to convert following tables:

ALTER TABLE smf_attachments TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_collapsed_categories TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_actions TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_boards TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_errors TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_karma TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_mark_read TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_online TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_messages TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_results TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_subjects TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_topics TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_topics TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_members TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_pm_recipients TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_sessions TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_settings TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_topics TYPE=InnoDB;
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on September 06, 2008, 06:55:39 PM
Noticed any improvements?
Title: Re: Convert your tables to InnoDB
Post by: evgenydeep on September 07, 2008, 06:10:58 AM
Honestly saying, not too much. If there are any improvements... may be they are hided.
But looking after requests I noticed that some previously "stacked" situations began solving much faster.

My decision: converting to innoDB is one of step to make forum better and for big forums this is MUST HAVE.
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on September 09, 2008, 07:05:54 PM
and for big forums this is MUST HAVE.
What is considered BIG?

My forum has been around since July 2005 and has 361,587 posts.
Title: Re: Convert your tables to InnoDB
Post by: Ben_S on September 09, 2008, 07:28:32 PM
Then do it.
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on September 10, 2008, 05:38:11 AM
Are you sure?

I don't have lots of posts happening at a time but I often get loads of people refreshing particular topics as they get updated (like a tennis match).. so is it beneficial for that?

Also, what's the benefit for converting the search tables to InnoDB?
Title: Re: Convert your tables to InnoDB
Post by: mrhope on September 11, 2008, 06:55:11 AM
Would converting tables to InnoDB resolve connection problems? Getting a lot of reports of users getting the below error randomly.

Connection Problems
Sorry, SMF was unable to connect to the database. This may be caused by the server being busy. Please try again later.

Running on a shared MySQL 5. 0 server, getting an average of 400-600 connected users and currently have over 60,000 topics and over 400,000 posts.

Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on September 11, 2008, 11:06:27 AM
No, it probably wouldn't. You might want to try persistent connections if you are on a dedicated solution (VPS or true dedicated). Do not enable it on shared hosting, it causes more problems than it solves on that type.
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on September 11, 2008, 11:18:50 AM
I'm on a VPS but I thought persistent connections was a feature that generally everyone suggests not doing?
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on September 12, 2008, 08:24:11 PM
I converted the tables outlined in the first post to InnoDB and on average it added 500ms to each page load, so certainly wasn't beneficial for me.
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on September 12, 2008, 09:18:36 PM
How big is your board? InnoDB isn't as fast as MyISAM on SELECT statements, but is much better for high-transaction environments. If you run a board that doesn't get a lot of traffic, you won't get any benefit from switching.
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on September 13, 2008, 07:21:59 AM
My board only gets a lot of traffic at match times. So most of the time it has around 80 users (based on 30mins) on but when a match is on it can peak too 600+ users.

So my situation is quite tricky - do I optimise for the general browsing which lasts most of the time or just for the peak times? I think the latter is the one I want to focus on though.
Title: Re: Convert your tables to InnoDB
Post by: poolhall on September 13, 2008, 03:10:35 PM
...but when a match is on it can peak too 600+ users....

Do you mean a soccer, football, boxing match, etc? If so, I would make an educated guess that the vast majority of visitors during the peak are viewing the same topic. Is it correct assumption? If it's the case, then InnoDB would not be beneficial for your because per the  [Unknown]'s explanation given in the first post visitors will still have to wait for the topic table to become available.
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on September 13, 2008, 04:47:43 PM
...but when a match is on it can peak too 600+ users....

Do you mean a soccer, football, boxing match, etc? If so, I would make an educated guess that the vast majority of visitors during the peak are viewing the same topic. Is it correct assumption? If it's the case, then InnoDB would not be beneficial for your because per the  [Unknown]'s explanation given in the first post visitors will still have to wait for the topic table to become available.
Your exactly right, it's a tennis match and they all refresh the same topic. So thank you for explaining why InnoDB is not useful to me :)

Although, I assume changing some of the tables to InnoDB is good? Like doing search so that it doesn't freeze the server.
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on September 13, 2008, 10:01:02 PM
You might benefit a lot from getting caching set up. You'll have a lot less hits on the database then, and much less falling under load. In addition, you can always move up to a higher cache level if you see you need it during a match.
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on September 14, 2008, 06:32:05 AM
I already have APC set up with SMF and I have query cache for MySQL... I've also never seen less queries being used when upping the cache level in SMF so it seems useless to me.
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on September 14, 2008, 01:20:20 PM
I've also never seen less queries being used when upping the cache level in SMF so it seems useless to me.

Did you make sure to set a cache level and make sure SMF is detecting the engine? It should give about an 80% reduction in queries with the stock SMF.

Also, if this goes a bit further off-topic, I'll probably split this out to a new topic.
Title: Re: Convert your tables to InnoDB
Post by: mark7144 on September 14, 2008, 02:02:02 PM
The cache works but I'm saying I don't see a difference in queries used between level 1 and level 3 caching.
Title: Re: Convert your tables to InnoDB
Post by: toy9b on September 18, 2008, 10:01:02 PM
from my understanding convert some table into innoDB will not related to 1.1.6 that I just upgraded, right. So it is mean "it is save to convert to innoDB on 1.1.6"???

Please advice. I got 152 (143 sleeping, 7 running, 2 zombie) and experienced several db crashes for a week now.

Thanks
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on September 18, 2008, 10:46:53 PM
It is safe to convert to InnoDB on any SMF version. It will not help prevent tables from crashing. Crashed tables are almost always a server issue.
Title: Re: Convert your tables to InnoDB
Post by: toy9b on September 18, 2008, 10:51:45 PM
thanks Motoko-chan
Title: Re: Convert your tables to InnoDB
Post by: Mai Pen Rai on September 24, 2008, 12:57:19 PM
I have a forum (only 3 months old) with 28.000 posts by 1800 members and around 1.400.000 pageviews per month.

Should I change to InnoDB?
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on September 24, 2008, 01:07:02 PM
Should I change to InnoDB?

Are you having issues with the speed of operations involving things like database posts? Are you noticing lots of locked queries (if you have checked)?

If yes: You might want to look at converting some of your tables to InnoDB.
If no: Leave things alone.
Title: Re: Convert your tables to InnoDB
Post by: Mai Pen Rai on September 24, 2008, 01:19:06 PM
I think its a no then.

I am running the site on a dedicated server with loads of power, does this explain the "no loss of speed"?
But then again I don't know if the site would be even faster with innoDB unless I try it.

Because I did have some complaints at first, but after upgrading the RAM the complaints stopped.
Title: Re: Convert your tables to InnoDB
Post by: Enc0der on September 24, 2008, 02:22:16 PM
Here is a great attitude I really suggest you to adopt:  :)

**removed**
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on September 24, 2008, 02:25:28 PM
Enc0der, while I appreciated the humor in the flowchart, I had to remove it because of the language. Feel free to put up one without that.
Title: Re: Convert your tables to InnoDB
Post by: Enc0der on September 24, 2008, 04:25:21 PM
hehe.. sorry :P
Title: Re: Convert your tables to InnoDB
Post by: toy9b on September 25, 2008, 07:44:17 AM
Hi There ...

Should I convert smf_messages into InnoDB?

Thanks
Title: Re: Convert your tables to InnoDB
Post by: Enc0der on September 25, 2008, 08:12:52 AM
most likely no.
(unless you have many many posted messages per second, that nobody actually reads...)
Title: Re: Convert your tables to InnoDB
Post by: toy9b on September 25, 2008, 09:38:43 AM
Thanks Enc0der
Title: Re: Convert your tables to InnoDB
Post by: toy9b on September 25, 2008, 10:21:33 AM
One more thing,

What would be recommended table that should be converted into InnoDB on 2.x

Thanks again
Title: Re: Convert your tables to InnoDB
Post by: master2oo8 on October 24, 2008, 11:20:38 PM
Thanks a lot for that great tip!
My forum works now about 50% faster (i have over 300k posts).

i have installed a thank you mod with about 50k entries. should i convert that table too?

thanks again
Title: Re: Convert your tables to InnoDB
Post by: Leeloo5E on February 17, 2009, 06:39:20 AM
Should I make the DROP before the CREATE or just a ALTER TABLE is ok?
Title: Re: Convert your tables to InnoDB
Post by: Something like that on February 17, 2009, 08:35:24 AM
Use ALTER TABLE if you don't want to lose any information.
Title: Re: Convert your tables to InnoDB
Post by: Leeloo5E on February 17, 2009, 10:21:53 AM
Yes, only 4 entries were in the table, but now i have a lot of OPTIMIZE TABLE processes in the processlist. Was the ALTER TABLE query causing this?
Title: Re: Convert your tables to InnoDB
Post by: Something like that on February 17, 2009, 10:25:55 AM
Yes, only 4 entries were in the table, but now i have a lot of OPTIMIZE TABLE processes in the processlist. Was the ALTER TABLE query causing this?

No. Though SMF has an option to run optimize tables. Did you click it?
Title: Re: Convert your tables to InnoDB
Post by: Leeloo5E on February 17, 2009, 10:33:41 AM
No, I didn't click this. I'm wondering seeing this in the processlist. Maybe this weekly OPTIMIZE process started automaticly ... I don't know.
We should talk about this in the other thread. To set the floodcontrol table to heap/memory, I know everything.

Title: Re: Convert your tables to InnoDB
Post by: Something like that on February 17, 2009, 10:38:50 AM
You may also want to set smf_log_online to memory, too. You have to convert the url column to varchar (1024) first though.
Title: Re: Convert your tables to InnoDB
Post by: Leeloo5E on February 17, 2009, 11:06:04 AM
Ok, thanks.
I will try.

What about smf_sessions? There are also a lot of UPDATEs DELETEs and INSERTS. It only holds the sessions. And if this table is lost, you have to log in again. Or is there another function I can't see?
Title: Re: Convert your tables to InnoDB
Post by: Something like that on February 18, 2009, 06:14:21 PM
I know this site is using memcached for sessions. Personally, I'd hate to have to login again if I was writing a nice long post and the sessions got dropped. That's an issue if you ever restart mecached, it's also an issue if you convert smf_sessions to MEMORY (every time you restart MySQL, everyone has to login again).

If you want to go ahead with converting the table to MEMORY, be aware it will eat up a decent amount of RAM. The amount of information stored in the session is rather large, somewhere around up to 16KB when I looked. Unfortunately, MEMORY tables only support fixed row lengths, so that means you have to use the maximum necessary space for each user. So if you had 1600 users online, and each took 16KB of space, that's 25 MB of space used. You'll have to convert the data column to a varchar(16384) first, but you probably want some margin for safety, so go with varchar(32768). Of course, you'll need to use MySQL 5.0.3 or later to use varchars that long. And you'll never want to restart MySQL for fear of annoying your users ;)

A much better way to speed up those queries when using an InnoDB table is to set these two variables in your my.cnf:
Code: [Select]
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT

InnoDB will then be just as fast or even faster than MyISAM for updates.
Title: Re: Convert your tables to InnoDB
Post by: Turk Navy on May 06, 2009, 06:42:59 AM
I can see my InnoDb tables in phpmyadmin but i can't see them in SSH. What should i do?

And before converting innodb, my db is about 1.1 gigabytes. After converting it became 2 gb. Is this normal?
Title: Re: Convert your tables to InnoDB
Post by: Something like that on May 06, 2009, 11:25:54 AM
I can see my InnoDb tables in phpmyadmin but i can't see them in SSH. What should i do?

And before converting innodb, my db is about 1.1 gigabytes. After converting it became 2 gb. Is this normal?

Yes, it is.

It may grow a little larger as time goes on, too, due to the way InnoDB stores things on disk.
Title: Re: Convert your tables to InnoDB
Post by: Turk Navy on May 06, 2009, 02:51:45 PM
I can see my InnoDb tables in phpmyadmin but i can't see them in SSH. What should i do?

And before converting innodb, my db is about 1.1 gigabytes. After converting it became 2 gb. Is this normal?

Yes, it is.

It may grow a little larger as time goes on, too, due to the way InnoDB stores things on disk.
Hmm, okay.

In SSH, database folder is 500mb but in phpmyadmin same database is 2 GB which is normal. Why I cant see InnoDB's in SSH?
Title: Re: Convert your tables to InnoDB
Post by: Something like that on May 06, 2009, 02:56:45 PM
In SSH, database folder is 500mb but in phpmyadmin same database is 2 GB which is normal. Why I cant see InnoDB's in SSH?

Unless you specified innodb_file_per_table=1 in your my.cnf file before you converted the tables (or make new InnoDB tables), all the InnoDB tables will normally be kept inside files named like ibdata1, ibdata2, ibdata3, etc. (unless configured otherwise in my.cnf).
Title: Re: Convert your tables to InnoDB
Post by: Turk Navy on May 08, 2009, 02:09:28 PM
In SSH, database folder is 500mb but in phpmyadmin same database is 2 GB which is normal. Why I cant see InnoDB's in SSH?

Unless you specified innodb_file_per_table=1 in your my.cnf file before you converted the tables (or make new InnoDB tables), all the InnoDB tables will normally be kept inside files named like ibdata1, ibdata2, ibdata3, etc. (unless configured otherwise in my.cnf).
So, here's what should i do?

* Convert all tables to myisam
* add "innodb_file_per_table=1" to my.cnf
* Convert tables to innodb

Am I right?
Title: Re: Convert your tables to InnoDB
Post by: Something like that on May 08, 2009, 02:25:18 PM
In SSH, database folder is 500mb but in phpmyadmin same database is 2 GB which is normal. Why I cant see InnoDB's in SSH?

Unless you specified innodb_file_per_table=1 in your my.cnf file before you converted the tables (or make new InnoDB tables), all the InnoDB tables will normally be kept inside files named like ibdata1, ibdata2, ibdata3, etc. (unless configured otherwise in my.cnf).
So, here's what should i do?

* Convert all tables to myisam
* add "innodb_file_per_table=1" to my.cnf
* Convert tables to innodb

Am I right?

There's really not much need to do that (beyond a slight performance increase).
Title: Re: Convert your tables to InnoDB
Post by: Turk Navy on May 08, 2009, 02:38:37 PM
In SSH, database folder is 500mb but in phpmyadmin same database is 2 GB which is normal. Why I cant see InnoDB's in SSH?

Unless you specified innodb_file_per_table=1 in your my.cnf file before you converted the tables (or make new InnoDB tables), all the InnoDB tables will normally be kept inside files named like ibdata1, ibdata2, ibdata3, etc. (unless configured otherwise in my.cnf).
So, here's what should i do?

* Convert all tables to myisam
* add "innodb_file_per_table=1" to my.cnf
* Convert tables to innodb

Am I right?

There's really not much need to do that (beyond a slight performance increase).
I am not doing this for the performance. As I said before, I just want to see my whole database in one folder while using SSH.

Aren't these things going to make that happen?
Title: Re: Convert your tables to InnoDB
Post by: Something like that on May 08, 2009, 02:48:38 PM
I am not doing this for the performance. As I said before, I just want to see my whole database in one folder while using SSH.

Aren't these things going to make that happen?

Yes, that would be the effect. Keep in mind that your ibdata* files won't shrink (even if they're mostly empty). Also be aware that some information about the structure of the database is still stored in those ibdata files (so backing up the directory with the tables in it won't be sufficient).
Title: Re: Convert your tables to InnoDB
Post by: Turk Navy on May 08, 2009, 03:13:33 PM
I am not doing this for the performance. As I said before, I just want to see my whole database in one folder while using SSH.

Aren't these things going to make that happen?

Yes, that would be the effect. Keep in mind that your ibdata* files won't shrink (even if they're mostly empty). Also be aware that some information about the structure of the database is still stored in those ibdata files (so backing up the directory with the tables in it won't be sufficient).

Hmm, I was going to make that changes just for the backup. Thanks for letting me know. So, what should I do for getting full backup of my database while using SSH?
Title: Re: Convert your tables to InnoDB
Post by: Something like that on May 08, 2009, 03:36:07 PM
I am not doing this for the performance. As I said before, I just want to see my whole database in one folder while using SSH.

Aren't these things going to make that happen?

Yes, that would be the effect. Keep in mind that your ibdata* files won't shrink (even if they're mostly empty). Also be aware that some information about the structure of the database is still stored in those ibdata files (so backing up the directory with the tables in it won't be sufficient).

Hmm, I was going to make that changes just for the backup. Thanks for letting me know. So, what should I do for getting full backup of my database while using SSH?

This is how I do it:

mysqldump --all-databases -u root -pPASSWORD | gzip > ~/mysql.backup.sql.gz

Note the lack of a space between -p and the password.

This, however, will take significant time (minutes) on a large forum during which time your site will be down.

If you wish to backup live, one of the usual ways is to keep your database files on an LVM volume. You then make a snapshot, and copy the database files. If you just start copying files on a live InnoDB database, the ib_logfile* files may have played through and over-written up-coming changes by the time you have copied all the data. You also have to be careful to copy them last. If you use a snapshot, you can just copy without worry.
Title: Re: Convert your tables to InnoDB
Post by: Stüldt Håjt on May 08, 2009, 04:40:26 PM
Using -q option in mysqldump will do the backup much faster. My ~4GB forum database it will take 2-3 minutes to make a full backup.
Title: Re: Convert your tables to InnoDB
Post by: Something like that on May 08, 2009, 04:49:54 PM
Using -q option in mysqldump will do the backup much faster. My ~4GB forum database it will take 2-3 minutes to make a full backup.

Thanks!
Title: Re: Convert your tables to InnoDB
Post by: Something like that on May 08, 2009, 04:51:25 PM
I just read the man page for mysqldump. Looks like -q is enabled by default -- it's enabled by --opt, which is itself enabled by default.
Title: Re: Convert your tables to InnoDB
Post by: Stüldt Håjt on May 08, 2009, 07:30:57 PM
Didn't work by default on my debian mysql 5.0.x installation. I was very astonished when I first time tested the -q option.
Title: Re: Convert your tables to InnoDB
Post by: ForumIntegration on October 21, 2009, 09:05:15 PM
My host is using InnoDB and they do not allow some tables in MyISAM and some in InnoDB. I suppose by converted all the tables to InnoDB will not affect the forum functionality but will have some effect on the performance. Is that true?

Several posts in this thread mention that converted all the tables to InnoDB causes problems. How could that happen?

Thanks!
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 21, 2009, 09:13:12 PM
My host is using InnoDB and they do not allow some tables in MyISAM and some in InnoDB. I suppose by converted all the tables to InnoDB will not affect the forum functionality but will have some effect on the performance. Is that true?

Several posts in this thread mention that converted all the tables to InnoDB causes problems. How could that happen?

Thanks!

The big one is the board ordering. You have to adjust the primary key on smf_boards. I believe it talks about this in the first post.

I run all InnoDB without any other issues. Do note that if MySQL isn't tuned properly, InnoDB can run very slowly. But properly tuned, it's just as fast as MyISAM (and in many cases faster).
Title: Re: Convert your tables to InnoDB
Post by: ForumIntegration on October 21, 2009, 09:35:06 PM
If the board order changes after converting to InnoDB, is it easy to re-order it in the admin page? Besides this, any other problems?

Does SMF has some fulltext search functionality that will be jeopardized by switching to InnoDB.
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on October 21, 2009, 10:03:26 PM
To be blunt, your host sucks.
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 21, 2009, 10:30:53 PM
If the board order changes after converting to InnoDB, is it easy to re-order it in the admin page? Besides this, any other problems?

No, that doesn't fix it. It's a bug in SMF 1.1. It may be fixed in 2.0. I'm not sure.

Quote
Does SMF has some fulltext search functionality that will be jeopardized by switching to InnoDB.

Yes. You'll need to build a custom search index.
Title: Re: Convert your tables to InnoDB
Post by: ForumIntegration on October 22, 2009, 09:50:11 AM
Thanks a lot for your help!

If the board order changes after converting to InnoDB, is it easy to re-order it in the admin page? Besides this, any other problems?

No, that doesn't fix it. It's a bug in SMF 1.1. It may be fixed in 2.0. I'm not sure.
Looks SMF2.0 still has this problem.  The following query can fix the order
    ALTER TABLE `smf_boards` ADD UNIQUE KEY (`id_board`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `board_order`, `id_board` ), ENGINE=InnoDB;

Quote
Quote
Does SMF has some fulltext search functionality that will be jeopardized by switching to InnoDB.

Yes. You'll need to build a custom search index.
Could you please give more details on this?

Another problem is when I try to convert the table smf_openid_assc to InnoDB, it complains about "BLOB/TEXT column "handle" used in key specification without a key length". I must change the datatype of handle to varchar(255). Will that cause any trouble?

Thanks!
Title: Re: Convert your tables to InnoDB
Post by: ForumIntegration on October 22, 2009, 08:17:23 PM
Mark Rose, is there any specific reason that the smf_log_floodcontrol table need to be MEMORY? What is that table for? Will a InnoDB smf_log_floodcontrol table hit the performance badly?

Thanks!
Title: Re: Convert your tables to InnoDB
Post by: Portable on October 23, 2009, 08:32:13 AM
Very nice information. Thanks for this. I look forward to reading more information from you.
Title: Re: Convert your tables to InnoDB
Post by: Something like that on October 28, 2009, 12:36:41 AM
Could you please give more details on this?

Another problem is when I try to convert the table smf_openid_assc to InnoDB, it complains about "BLOB/TEXT column "handle" used in key specification without a key length". I must change the datatype of handle to varchar(255). Will that cause any trouble?

Thanks!

It's in the Admin area of the forum. You'll need to build a custom search index, because InnoDB doesn't support fulltext searches.

I haven't played with converting that table. From what I understand, OpenID support is a bit broken at the moment anyway.
Title: Boards locking up - very large site...
Post by: Flavious on January 05, 2010, 03:42:46 PM
Our site is getting in excess of 80,000 unique visitors and 4 million page views per day. About once a week, someone will post a message and whatever board they posted to will lock up. Any user after that clicking on the board will cause their browser to just sit and spin... it won't go into that board.

The only way I've found to fix this is to look up the ID_Board for that board, then look at all the messages, and start deleting posts backwards till I find the one that caused the issue. It's usually the first one or two messages, sometimes the third. But once it's deleted, it works fine.

I am on a shared hosting solution, but the other sites on the server are very small, most are static and not using MySQL.

Any ideas? I'm to replicate the problem by posting. It seems to be rather random.
Title: Re: Convert your tables to InnoDB
Post by: Something like that on January 05, 2010, 03:58:59 PM
That seems very odd.

What can happen with all large boards, is that the log tables aren't cached in RAM, especially log_mark_read. The solution to this is increasing your innodb_buffer_pool_size. I don't know how big your forums are, nor what kind of hardware you're serving them with, but your innodb_buffer_pool_size should be at least 128M (guessing based on your traffic), but ideally large enough to contain the entirety of your InnoDB tables.

Can you post the output of status.php, including the variables or link to it?
Title: Re: Convert your tables to InnoDB
Post by: Flavious on January 05, 2010, 05:16:47 PM
Where do I find said Status.php? Not seeing it on the server...
Title: Re: Convert your tables to InnoDB
Post by: IchBin™ on January 05, 2010, 05:41:43 PM
It's on this page.
http://download.simplemachines.org/?tools
Title: Re: Convert your tables to InnoDB
Post by: Flavious on January 05, 2010, 06:11:16 PM
Here's the output:

January 05, 2010, 03:09:37 PM
Operating System:    CentOS release 5.4 (Final)
Processor:    Intel® Xeon® CPU E5420 @ 2.50GHz (2500.131MHz)
Load averages:    6.21, 5.32, 5.28
Current processes:    11 (5 sleeping, 1 running, 5 zombie)
Processes by CPU:    php (6) 7.0%, (other) (5) 0.0%
Memory usage:    88.98% (3688796k / 4145640k)
Swap: 0.015% (620k / 4192944k)

Connections per second:      2.9521
Kilobytes received per second:    1.0906
Kilobytes sent per second:    0.8367
Queries per second:    99.5376
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    119.9295 (should be <= 80)
Table cache usage:
(table_cache)    1 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0035 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.4614 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    160990.7313 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.5 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.4547 (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.1745 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0.2034 (should be <= 0.05)

Aborted_clients:     20000
Aborted_connects:    38823
Binlog_cache_disk_use:    0
Binlog_cache_use:    0
Bytes_received:    4080355597
Bytes_sent:    3130605643
Com_admin_commands:    2187812
Com_alter_db:    0
Com_alter_table:    1513
Com_analyze:    1
Com_backup_table:    0
Com_begin:    530693
Com_call_procedure:    0
Com_change_db:    10641731
Com_change_master:    0
Com_check:    301
Com_checksum:    0
Com_commit:    322457
Com_create_db:    57
Com_create_function:    0
Com_create_index:    2
Com_create_table:    309701
Com_create_user:    0
Com_dealloc_sql:    324539
Com_delete:    6842578
Com_delete_multi:    43
Com_do:    0
Com_drop_db:    41
Com_drop_function:    0
Com_drop_index:    0
Com_drop_table:    23609
Com_drop_user:    0
Com_execute_sql:    324539
Com_flush:    13719
Com_grant:    19570
Com_ha_close:    0
Com_ha_open:    0
Com_ha_read:    0
Com_help:    0
Com_insert:    6939635
Com_insert_select:    66246
Com_kill:    157
Com_load:    0
Com_load_master_data:    0
Com_load_master_table:    0
Com_lock_tables:    1093180
Com_optimize:    14131
Com_preload_keys:    0
Com_prepare_sql:    324539
Com_purge:    0
Com_purge_before_date:    0
Com_rename_table:    0
Com_repair:    2282
Com_replace:    819765
Com_replace_select:    41
Com_reset:    0
Com_restore_table:    0
Com_revoke:    3
Com_revoke_all:    0
Com_rollback:    571
Com_savepoint:    0
Com_select:    52149270
Com_set_option:    5036216
Com_show_binlog_events:    0
Com_show_binlogs:    119
Com_show_charsets:    1013
Com_show_collations:    1118
Com_show_column_types:    0
Com_show_create_db:    3
Com_show_create_table:    563311
Com_show_databases:    12882
Com_show_errors:    0
Com_show_fields:    853523
Com_show_grants:    40198
Com_show_innodb_status:    0
Com_show_keys:    1912
Com_show_logs:    0
Com_show_master_status:    0
Com_show_ndb_status:    0
Com_show_new_master:    0
Com_show_open_tables:    0
Com_show_privileges:    0
Com_show_processlist:    1113
Com_show_slave_hosts:    0
Com_show_slave_status:    0
Com_show_status:    196
Com_show_storage_engines:    15
Com_show_tables:    235417
Com_show_triggers:    617656
Com_show_variables:    28595
Com_show_warnings:    29
Com_slave_start:    0
Com_slave_stop:    0
Com_stmt_close:    324539
Com_stmt_execute:    324539
Com_stmt_fetch:    0
Com_stmt_prepare:    324539
Com_stmt_reset:    0
Com_stmt_send_long_data:    0
Com_truncate:    5330
Com_unlock_tables:    1093941
Com_update:    16524177
Com_update_multi:    35488
Com_xa_commit:    0
Com_xa_end:    0
Com_xa_prepare:    0
Com_xa_recover:    0
Com_xa_rollback:    0
Com_xa_start:    0
Compression:    OFF
Connections:    10786379
Created_tmp_disk_tables:    2593859
Created_tmp_files:    11
Created_tmp_tables:    5704089
Delayed_errors:    0
Delayed_insert_threads:    0
Delayed_writes:    0
Flush_commands:    1
Handler_commit:    538278
Handler_delete:    7522227
Handler_discover:    0
Handler_prepare:    0
Handler_read_first:    8845989
Handler_read_key:    2762765006
Handler_read_next:    3171941631
Handler_read_prev:    19614552
Handler_read_rnd:    86125298
Handler_read_rnd_next:    3395420968
Handler_rollback:    132449
Handler_savepoint:    0
Handler_savepoint_rollback:    0
Handler_update:    965488131
Handler_write:    684472009
Innodb_buffer_pool_pages_data:    492
Innodb_buffer_pool_pages_dirty:    0
Innodb_buffer_pool_pages_flushed:    213105
Innodb_buffer_pool_pages_free:    0
Innodb_buffer_pool_pages_misc:    20
Innodb_buffer_pool_pages_total:    512
Innodb_buffer_pool_read_ahead_rnd:    8131
Innodb_buffer_pool_read_ahead_seq:    479
Innodb_buffer_pool_read_requests:    71558805
Innodb_buffer_pool_reads:    147163
Innodb_buffer_pool_wait_free:    0
Innodb_buffer_pool_write_requests:    2107710
Innodb_data_fsyncs:    286850
Innodb_data_pending_fsyncs:    0
Innodb_data_pending_reads:    0
Innodb_data_pending_writes:    0
Innodb_data_read:    3681439744
Innodb_data_reads:    168287
Innodb_data_writes:    428975
Innodb_data_written:    2901644800
Innodb_dblwr_pages_written:    213105
Innodb_dblwr_writes:    37915
Innodb_log_waits:    0
Innodb_log_write_requests:    228243
Innodb_log_writes:    183938
Innodb_os_log_fsyncs:    212736
Innodb_os_log_pending_fsyncs:    0
Innodb_os_log_pending_writes:    0
Innodb_os_log_written:    199624704
Innodb_page_size:    16384
Innodb_pages_created:    6876
Innodb_pages_read:    224564
Innodb_pages_written:    213105
Innodb_row_lock_current_waits:    0
Innodb_row_lock_time:    32834
Innodb_row_lock_time_avg:    35
Innodb_row_lock_time_max:    3520
Innodb_row_lock_waits:    931
Innodb_rows_deleted:    1483
Innodb_rows_inserted:    108093
Innodb_rows_read:    29642994
Innodb_rows_updated:    161402
Key_blocks_not_flushed:    0
Key_blocks_unused:    0
Key_blocks_used:    14497
Key_read_requests:    4340076250
Key_reads:    15117512
Key_write_requests:    68887765
Key_writes:    31787254
Last_query_cost:    0.000000
Max_used_connections:    67
Ndb_cluster_node_id:    0
Ndb_config_from_host:    
Ndb_config_from_port:    0
Ndb_number_of_data_nodes:    0
Not_flushed_delayed_rows:    0
Open_files:    15185
Open_streams:    0
Open_tables:    10240
Opened_tables:    1228078
Prepared_stmt_count:    0
Qcache_free_blocks:    5988
Qcache_free_memory:    20268704
Qcache_hits:    246696684
Qcache_inserts:    43697548
Qcache_lowmem_prunes:    10607347
Qcache_not_cached:    8330228
Qcache_queries_in_cache:    16982
Qcache_total_blocks:    42045
Queries:    363693592
Questions:    363693592
Rpl_status:    NULL
Select_full_join:    1689630
Select_full_range_join:    8862
Select_range:    5368394
Select_range_check:    1049
Select_scan:    12862189
Slave_open_temp_tables:    0
Slave_retried_transactions:    0
Slave_running:    OFF
Slow_launch_threads:    0
Slow_queries:    226
Sort_merge_passes:    0
Sort_range:    1619125
Sort_rows:    632158553
Sort_scan:    3162107
Table_locks_immediate:    122577015
Table_locks_waited:    236329
Tc_log_max_pages_used:    0
Tc_log_page_size:    0
Tc_log_page_waits:    0
Threads_cached:    64
Threads_connected:    3
Threads_created:    67
Threads_running:    1
Uptime:    3653831

MySQL variables
auto_increment_increment:    1
auto_increment_offset:    1
automatic_sp_privileges:    ON
back_log:    50
basedir:    /
binlog_cache_size:    32768
bulk_insert_buffer_size:    8388608
character_set_client:    latin1
character_set_connection:    latin1
character_set_database:    latin1
character_set_filesystem:    binary
character_set_results:    latin1
character_set_server:    latin1
character_set_system:    utf8
character_sets_dir:    /usr/share/mysql/charsets/
collation_connection:    latin1_swedish_ci
collation_database:    latin1_swedish_ci
collation_server:    latin1_swedish_ci
completion_type:    0
concurrent_insert:    1
connect_timeout:    10
datadir:    /var/lib/mysql/
date_format:    %Y-%m-%d
datetime_format:    %Y-%m-%d %H:%i:%s
default_week_format:    0
delay_key_write:    ON
delayed_insert_limit:    100
delayed_insert_timeout:    300
delayed_queue_size:    1000
div_precision_increment:    4
keep_files_on_create:    OFF
engine_condition_pushdown:    OFF
expire_logs_days:    0
flush:    OFF
flush_time:    0
ft_boolean_syntax:    + -><()~*:""&|
ft_max_word_len:    84
ft_min_word_len:    4
ft_query_expansion_limit:    20
ft_stopword_file:    (built-in)
group_concat_max_len:    1024
have_archive:    YES
have_bdb:    NO
have_blackhole_engine:    YES
have_compress:    YES
have_community_features:    NO
have_profiling:    NO
have_crypt:    YES
have_csv:    YES
have_dynamic_loading:    YES
have_example_engine:    YES
have_federated_engine:    YES
have_geometry:    YES
have_innodb:    YES
have_isam:    NO
have_merge_engine:    YES
have_ndbcluster:    DISABLED
have_openssl:    NO
have_ssl:    NO
have_query_cache:    YES
have_raid:    NO
have_rtree_keys:    YES
have_symlink:    YES
hostname:    olympic.aqhostdns.com
init_connect:    
init_file:    
init_slave:    
innodb_additional_mem_pool_size:    1048576
innodb_autoextend_increment:    8
innodb_buffer_pool_awe_mem_mb:    0
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_adaptive_hash_index:    ON
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_arch_dir:    
innodb_log_archive:    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_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
interactive_timeout:    300
join_buffer_size:    16777216
key_buffer_size:    16777216
key_cache_age_threshold:    300
key_cache_block_size:    1024
key_cache_division_limit:    100
language:    /usr/share/mysql/english/
large_files_support:    ON
large_page_size:    0
large_pages:    OFF
lc_time_names:    en_US
license:    GPL
local_infile:    ON
locked_in_memory:    OFF
log:    OFF
log_bin:    OFF
log_bin_trust_function_creators:    OFF
log_error:    
log_queries_not_using_indexes:    OFF
log_slave_updates:    OFF
log_slow_queries:    OFF
log_warnings:    1
long_query_time:    10
low_priority_updates:    OFF
lower_case_file_system:    OFF
lower_case_table_names:    0
max_allowed_packet:    16777216
max_binlog_cache_size:    4294963200
max_binlog_size:    1073741824
max_connect_errors:    100
max_connections:    750
max_delayed_threads:    20
max_error_count:    64
max_heap_table_size:    16777216
max_insert_delayed_threads:    20
max_join_size:    18446744073709551615
max_length_for_sort_data:    1024
max_prepared_stmt_count:    16382
max_relay_log_size:    0
max_seeks_for_key:    4294967295
max_sort_length:    1024
max_sp_recursion_depth:    0
max_tmp_tables:    32
max_user_connections:    50
max_write_lock_count:    4294967295
multi_range_count:    256
myisam_data_pointer_size:    6
myisam_max_sort_file_size:    2146435072
myisam_recover_options:    OFF
myisam_repair_threads:    1
myisam_sort_buffer_size:    67108864
myisam_stats_method:    nulls_unequal
ndb_autoincrement_prefetch_sz:    1
ndb_force_send:    ON
ndb_use_exact_count:    ON
ndb_use_transactions:    ON
ndb_cache_check_time:    0
ndb_connectstring:    
net_buffer_length:    16384
net_read_timeout:    30
net_retry_count:    10
net_write_timeout:    60
new:    OFF
old_passwords:    OFF
open_files_limit:    21240
optimizer_prune_level:    1
optimizer_search_depth:    62
pid_file:    /var/lib/mysql/olympic.aqhostdns.com.pid
plugin_dir:    
port:    3306
preload_buffer_size:    32768
protocol_version:    10
query_alloc_block_size:    8192
query_cache_limit:    1048576
query_cache_min_res_unit:    4096
query_cache_size:    67108864
query_cache_type:    ON
query_cache_wlock_invalidate:    OFF
query_prealloc_size:    8192
range_alloc_block_size:    4096
read_buffer_size:    16777216
read_only:    OFF
read_rnd_buffer_size:    262144
relay_log:    
relay_log_index:    
relay_log_info_file:    relay-log.info
relay_log_purge:    ON
relay_log_space_limit:    0
rpl_recovery_rank:    0
secure_auth:    OFF
secure_file_priv:    
server_id:    0
skip_external_locking:    ON
skip_networking:    OFF
skip_show_database:    OFF
slave_compressed_protocol:    OFF
slave_load_tmpdir:    /tmp/
slave_net_timeout:    3600
slave_skip_errors:    OFF
slave_transaction_retries:    10
slow_launch_time:    2
socket:    /var/lib/mysql/mysql.sock
sort_buffer_size:    33554432
sql_big_selects:    ON
sql_mode:    
sql_notes:    ON
sql_warnings:    OFF
ssl_ca:    
ssl_capath:    
ssl_cert:    
ssl_cipher:    
ssl_key:    
storage_engine:    MyISAM
sync_binlog:    0
sync_frm:    ON
system_time_zone:    PST
table_cache:    10240
table_lock_wait_timeout:    50
table_type:    MyISAM
thread_cache_size:    128
thread_stack:    196608
time_format:    %H:%i:%s
time_zone:    SYSTEM
timed_mutexes:    OFF
tmp_table_size:    33554432
tmpdir:    /tmp/
transaction_alloc_block_size:    8192
transaction_prealloc_size:    4096
tx_isolation:    REPEATABLE-READ
updatable_views_with_limit:    YES
version:    5.0.85-community
version_comment:    MySQL Community Edition (GPL)
version_compile_machine:    i686
version_compile_os:    pc-linux-gnu
wait_timeout:    300
Title: Re: Convert your tables to InnoDB
Post by: Something like that on January 05, 2010, 06:20:08 PM
As I suspected, your innodb_buffer_pool_size is extremely small. It's set to 8M right now. Set it to 384M.

Also, your key_buffer_size for MyISAM is also too small (16M). Set key_buffer_size to 128M.

You can also set innodb_flush_log_at_trx_commit to 2 for a big improvement in InnoDB writes.

That should fix your slow downs and make your forum about twice as fast. :)
Title: Re: Convert your tables to InnoDB
Post by: Flavious on January 05, 2010, 06:37:11 PM
God bless Mark. We will do that right now and see how she goes!
Title: Re: Convert your tables to InnoDB
Post by: Something like that on January 05, 2010, 07:15:47 PM
Do you have over 500k posts? If so, you should apply for the Big Boards group (http://www.simplemachines.org/community/index.php?action=profile;area=groupmembership). It'll give you access to another forum with advice for people with big boards.

If you don't have that many posts yet, your traffic certainly warrants it, just tell them I sent you and point here. I don't make the decisions, but I think you should have access.
Title: Re: Convert your tables to InnoDB
Post by: Flavious on January 12, 2010, 05:38:56 PM
I have signed up for it. We continue to be plagued with all sorts of issues... internal server errors, lock ups, and now.. our most viewed thread has disappeared. My hosting provider is providing vague answers like "your scripts are using too much cpu". Is there anyone with huge Simple Machines experience available for paid consultation as well? Just in case - we may need it.
Title: Re: Convert your tables to InnoDB
Post by: forumite on December 05, 2010, 08:25:51 AM
Apologies for posting in an old topic, but most of the discussion appears relevant.

Having procrastinated for a long time, I was getting ready to convert defined tables from MyISAM to InnoDB, and stumbled on this warning in the MySQL docs:

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

Did something change, or is it advisable to continue with the conversion, ignoring the warning?

FWIW the conversion would be limited to the tables defined in earlier messages, and my.cnf will have the changes suggested.

TIA.
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on December 05, 2010, 12:16:40 PM
None of the size restrictions should apply to a default SMF table. If you do find an SMF table with over 1000 columns, please do share!

The red warnings are specifically for the "mysql" database that contains user, password, and grant information and if you are using InnDB over NFS due to the way that system handles locking.
Title: Re: Convert your tables to InnoDB
Post by: forumite on December 05, 2010, 12:23:47 PM
Quote
The red warnings are specifically for the "mysql" database that contains user, password, and grant information .....

Ah so, thanks Motoko. I misunderstood the warnings, probably because I missed the significance of the term system tables.
Title: Re: Convert your tables to InnoDB
Post by: forumite on December 09, 2010, 05:44:23 AM
My forum has been suffering from sporadic chronic delays for some time. A few days ago I finally got around to converting the defined tables to InnoDB and made the requisite tweaks in my.cnf. Because of the prior sporadic nature of the behavior, I've been watching and testing to be sure the problem is fixed, and all I can say is - wow! what a difference.

One of Unkown's recommendations I haven't yet implemented is to change the log_floodcontrol table to be MEMORY/HEAP (ENGINE=MEMORY). But, my question is how important is this, and what will be the downside of not making this change? I just looked at the existing MyISAM log_floodcontrol table, and it's quite small (2.3KB).

TIA.
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on December 09, 2010, 04:51:57 PM
I'd do it. It is worth it. Nothing is lost from doing it. Be sure to do the other changes in the sticky in this board as well.
Title: Re: Convert your tables to InnoDB
Post by: Thrilling on January 10, 2011, 09:27:08 AM
What are the phpmyadmin queries to be  run on SMF 2.0RC4?

These don't work:

Code: [Select]
ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
TYPE=InnoDB;
ALTER TABLE smf_log_actions
TYPE=InnoDB;
ALTER TABLE smf_log_boards
TYPE=InnoDB;
ALTER TABLE smf_log_errors
TYPE=InnoDB;
ALTER TABLE smf_log_karma
TYPE=InnoDB;
ALTER TABLE smf_log_mark_read
TYPE=InnoDB;
ALTER TABLE smf_log_online
TYPE=InnoDB;
ALTER TABLE smf_log_search
TYPE=InnoDB;
ALTER TABLE smf_log_topics
TYPE=InnoDB;
ALTER TABLE smf_members
TYPE=InnoDB;
ALTER TABLE smf_pm_recipients
TYPE=InnoDB;
ALTER TABLE smf_sessions
TYPE=InnoDB;
ALTER TABLE smf_settings
TYPE=InnoDB;
ALTER TABLE smf_topics
TYPE=InnoDB;

Quote
MySQL said: Documentation
#1146 - Table 'geeksoup_forum.smf_log_search' doesn't exist
Title: Re: Convert your tables to InnoDB
Post by: 青山 素子 on January 10, 2011, 11:58:11 AM
You need to adjust the queries for your table names. You might be using a different prefix, for example.
Title: Re: Convert your tables to InnoDB
Post by: Masterd on January 11, 2011, 06:55:25 AM
I tried this on my forum and nothing happened.
Title: Re: Convert your tables to InnoDB
Post by: Paracelsus on January 11, 2011, 07:07:11 AM
I tried this on my forum and nothing happened.

What did you expect to happen?
Title: Re: Convert your tables to InnoDB
Post by: 008Rohit on April 24, 2011, 07:57:57 AM
My Host iPage doesnt even support InnoDB tables. I tried to run this query "ALTER TABLE smf_attachments
TYPE=InnoDB;" and it said successful but smf_attachments was still MyIsam.
Title: Re: Convert your tables to InnoDB
Post by: Masterd on April 24, 2011, 08:04:24 AM
If your host doesn't support InnoDB then you can do nothing.
Title: Re: Convert your tables to InnoDB
Post by: 008Rohit on April 24, 2011, 08:59:52 AM
If your host doesn't support InnoDB then you can do nothing.
I am disgusted with iPage and planning to Move to Aspiration Hosting. :)
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on April 24, 2011, 12:14:48 PM
You could ask your host to enable InnoDB. Or, like you said, move hosts.
Title: Re: Convert your tables to InnoDB
Post by: 008Rohit on April 24, 2011, 01:19:48 PM
@ Joshua

Normal support staffs of iPage know nothing about MySQL, They say "Our advanced technical support person will contact you" everytime. :(
I wanted someone's VPS, as I cant afford to buy one myself atm. but noone is willing to share their VPS with me.
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on April 24, 2011, 03:58:34 PM
That's sad. I definitely would not be using a host that didn't know how to enable InnoDB.
Title: Re: Convert your tables to InnoDB
Post by: naikvarda on April 25, 2011, 01:09:53 AM
This is really nice information related to the conversion.
Title: Re: Convert your tables to InnoDB
Post by: 008Rohit on April 25, 2011, 07:42:53 AM
That's sad. I definitely would not be using a host that didn't know how to enable InnoDB.
I asked them today to enable InnoDB tables. and their advanced technical support person replied after an hour saying that InnoDB is not supported on their server...
Even status.php cant track their MySQL processes or Processor information. :(
Title: Re: Convert your tables to InnoDB
Post by: Joshua Dickerson on April 26, 2011, 06:45:34 PM
I would definitely change hosts.
Title: Re: Convert your tables to InnoDB
Post by: Realinfo on October 14, 2012, 01:06:45 AM
"You have to choose at least one column to display"

I got this message........
Title: Re: Convert your tables to InnoDB
Post by: Realinfo on December 15, 2012, 09:48:34 PM
"You have to choose at least one column to display"

I got this message........

Bump
Title: Re: Convert your tables to InnoDB
Post by: madfiddler on August 21, 2013, 06:36:05 PM
Hi,

Having made these database changes to InnoDB - I now get this error whenever trying to post

Unknown column 'log_time' in 'where clause'
File: /xxx/xxx/public_html/fiddleforum/Sources/Subs.php
Line: 3110
Title: Re: Convert your tables to InnoDB
Post by: madfiddler on August 21, 2013, 07:02:03 PM
Fixed it by running upgrade.php again
Title: Re: Convert your tables to InnoDB
Post by: madfiddler on August 21, 2013, 07:53:45 PM
Ok, these errors go away when running upgrade, however if I do a database optimise, it comes back.

I tried converting the settings table back to MyIASM and get this

ALTER TABLE x_settings
ENGINE=MyIASM;# MySQL returned an empty result set (i.e. zero rows).
Title: Re: Convert your tables to InnoDB
Post by: Biology Forums on June 04, 2014, 02:48:58 PM
I just did this conversion on my website, and I've noticed a speed difference. Thank you for the suggestion.
Title: Re: Convert your tables to InnoDB
Post by: Arantor on June 04, 2014, 02:53:19 PM
Good job using 9 year old advice without checking if it's still relevant...
Title: Re: Convert your tables to InnoDB
Post by: Biology Forums on June 04, 2014, 04:45:03 PM
Well, it's worth a try. If you make the appropriate backups prior to trying, and if things go wrong, you can always go back... So why the smite? No one gave me an instructional manual on how to run a forum, I'm learning as I go. If you don't have any constructive criticism for us, then get lost, otherwise play nice.

- http://www.simplemachines.org/community/index.php?topic=496086.0
Title: Re: Convert your tables to InnoDB
Post by: Arantor on June 04, 2014, 05:01:40 PM
Because this is lacking a number of tables, lacking a good number of enhancements and taking advice from a 9 year old topic as gospel is a bad idea.

Just because it doesn't have any consequences *now* doesn't mean there won't be consequences in the future. Also, I *really* hope you didn't touch smf_boards or smf_categories in your conversion.

Also, 'smite', really? You really don't know me very well by now, do you? I treat everything in black and white as it is presented to me. 9 year old advice, written for SMF years ago, for a totally different version of MySQL... it could have all kinds of consequences, which you might not find out until too late.

Fine, you don't want my advice, I can manage that.
Title: Re: Convert your tables to InnoDB
Post by: Biology Forums on June 04, 2014, 05:11:05 PM
Just because it doesn't have any consequences *now* doesn't mean there won't be consequences in the future. Also, I *really* hope you didn't touch smf_boards or smf_categories in your conversion.

Those boards weren't designed for innoDB, you'd need to alter some table attributes. I didn't change those.

I get your mannerism, but sometimes it comes across as smug, especially with your vast amount of knowledge in this field. From an educators standpoint, your way of approaching questions (from students, like myself) would never work in a classroom - not that it matters here.