News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Convert your tables to InnoDB

Started by [Unknown], September 20, 2005, 04:31:53 PM

Previous topic - Next topic

H

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:

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
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Motorhead


Joshua Dickerson

You should investigate why it was causing loads like that.
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Kjell H.


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.


minskog

Quote from: groundup on February 18, 2006, 01:04:11 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.

rosey

QuoteALTER 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?

minskog

Quote from: minskog on February 25, 2006, 07:47:49 AM
Quote from: groundup on February 18, 2006, 01:04:11 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)


Ben_S

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?
Liverpool FC Forum with 14 million+ posts.

minskog

Quote from: 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?

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.

minskog

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.

minskog

I migrate fron 1.0.7 to 1.1-rc2, what tables must be changed to innodb in 1.1-rc2?

WhoIsShe

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!

WhoIsShe

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.

Triangle

#53
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??

Ben_S

Sounds like you have skip-innodb in your my.cnf file.
Liverpool FC Forum with 14 million+ posts.

Triangle


WhoIsShe

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?  :'(

ivo2296

Hi all,

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

Is that normal with the bigger size?

Ben_S

Yes, InnoDB tables take up more space.
Liverpool FC Forum with 14 million+ posts.

ivo2296

Quote from: Ben_S on July 26, 2006, 04:30:41 PM
Yes, InnoDB tables take up more space.

Thanks :) that makes me sleep better :)

Advertisement: