Advertisement:

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

Offline Enc0der

  • Jr. Member
  • **
  • Posts: 358
  • Gender: Male
  • I'm a llama!
Re: Convert your tables to InnoDB
« Reply #180 on: September 24, 2008, 02:22:16 PM »
Here is a great attitude I really suggest you to adopt:  :)

**removed**
« Last Edit: September 24, 2008, 02:23:50 PM by Motoko-chan »

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #181 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.
Motoko-chan
Director, Simple Machines

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


Offline Enc0der

  • Jr. Member
  • **
  • Posts: 358
  • Gender: Male
  • I'm a llama!
Re: Convert your tables to InnoDB
« Reply #182 on: September 24, 2008, 04:25:21 PM »
hehe.. sorry :P

Offline toy9b

  • Semi-Newbie
  • *
  • Posts: 11
Re: Convert your tables to InnoDB
« Reply #183 on: September 25, 2008, 07:44:17 AM »
Hi There ...

Should I convert smf_messages into InnoDB?

Thanks

Offline Enc0der

  • Jr. Member
  • **
  • Posts: 358
  • Gender: Male
  • I'm a llama!
Re: Convert your tables to InnoDB
« Reply #184 on: September 25, 2008, 08:12:52 AM »
most likely no.
(unless you have many many posted messages per second, that nobody actually reads...)

Offline toy9b

  • Semi-Newbie
  • *
  • Posts: 11
Re: Convert your tables to InnoDB
« Reply #185 on: September 25, 2008, 09:38:43 AM »
Thanks Enc0der

Offline toy9b

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

Offline master2oo8

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

Offline Leeloo5E

  • Semi-Newbie
  • *
  • Posts: 62
  • Gender: Female
Re: Convert your tables to InnoDB
« Reply #188 on: February 17, 2009, 06:39:20 AM »
Should I make the DROP before the CREATE or just a ALTER TABLE is ok?

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #189 on: February 17, 2009, 08:35:24 AM »
Use ALTER TABLE if you don't want to lose any information.

Offline Leeloo5E

  • Semi-Newbie
  • *
  • Posts: 62
  • Gender: Female
Re: Convert your tables to InnoDB
« Reply #190 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?

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #191 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?

Offline Leeloo5E

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


Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #193 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.

Offline Leeloo5E

  • Semi-Newbie
  • *
  • Posts: 62
  • Gender: Female
Re: Convert your tables to InnoDB
« Reply #194 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?
« Last Edit: February 18, 2009, 04:26:17 AM by Leeloo5E »

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #195 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.
« Last Edit: February 18, 2009, 06:18:05 PM by Phalloidium »

Offline Turk Navy

  • Jr. Member
  • **
  • Posts: 156
  • Gender: Male
  • J. Statham
Re: Convert your tables to InnoDB
« Reply #196 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?

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #197 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.

Offline Turk Navy

  • Jr. Member
  • **
  • Posts: 156
  • Gender: Male
  • J. Statham
Re: Convert your tables to InnoDB
« Reply #198 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?

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #199 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).