Advertisement:

Author Topic: Convert your tables to InnoDB  (Read 224099 times)

Offline H

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 21,662
  • Gender: Male
Re: Convert your tables to InnoDB
« Reply #40 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
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Offline Motorhead

  • Jr. Member
  • **
  • Posts: 220
  • Gender: Male
    • Steve's Offroad and Landrover Site
Re: Convert your tables to InnoDB
« Reply #41 on: February 16, 2006, 06:35:02 PM »
Thanks, worked a treat :)


Offline Joshua Dickerson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,777
  • Gender: Male
    • joshuaadickerson on GitHub
    • joshuaadickerson on LinkedIn
Re: Convert your tables to InnoDB
« Reply #42 on: February 18, 2006, 01:04:11 PM »
You should investigate why it was causing loads like that.
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?

Offline Kjell H.

  • Jr. Member
  • **
  • Posts: 111
  • Gender: Male
Re: Convert your tables to InnoDB
« Reply #43 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.


Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Convert your tables to InnoDB
« Reply #44 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.

Offline rosey

  • Jr. Member
  • **
  • Posts: 178
Re: Convert your tables to InnoDB
« Reply #45 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?

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Convert your tables to InnoDB
« Reply #46 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)


Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #47 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?
Liverpool FC Forum with 14 million+ posts.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Convert your tables to InnoDB
« Reply #48 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.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Convert your tables to InnoDB
« Reply #49 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.

Offline minskog

  • Jr. Member
  • **
  • Posts: 109
  • minskog inside
Re: Convert your tables to InnoDB
« Reply #50 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?

Offline WhoIsShe

  • Semi-Newbie
  • *
  • Posts: 43
Re: Convert your tables to InnoDB
« Reply #51 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!

Offline WhoIsShe

  • Semi-Newbie
  • *
  • Posts: 43
Re: Convert your tables to InnoDB
« Reply #52 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.

Offline Triangle

  • Semi-Newbie
  • *
  • Posts: 48
  • Gender: Male
Re: Convert your tables to InnoDB
« Reply #53 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??
« Last Edit: May 19, 2006, 02:10:15 PM by Triangle »

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #54 on: May 20, 2006, 12:10:05 PM »
Sounds like you have skip-innodb in your my.cnf file.
Liverpool FC Forum with 14 million+ posts.

Offline Triangle

  • Semi-Newbie
  • *
  • Posts: 48
  • Gender: Male
Re: Convert your tables to InnoDB
« Reply #55 on: May 20, 2006, 12:55:46 PM »
Thanks, I will check that out.

Offline WhoIsShe

  • Semi-Newbie
  • *
  • Posts: 43
Re: Convert your tables to InnoDB
« Reply #56 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?  :'(

Offline ivo2296

  • Full Member
  • ***
  • Posts: 514
  • Gender: Male
  • SMF Bulgarian Translator
Re: Convert your tables to InnoDB
« Reply #57 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?

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #58 on: July 26, 2006, 04:30:41 PM »
Yes, InnoDB tables take up more space.
Liverpool FC Forum with 14 million+ posts.

Offline ivo2296

  • Full Member
  • ***
  • Posts: 514
  • Gender: Male
  • SMF Bulgarian Translator
Re: Convert your tables to InnoDB
« Reply #59 on: July 26, 2006, 04:55:37 PM »
Yes, InnoDB tables take up more space.

Thanks :) that makes me sleep better :)