Convert your tables to InnoDB

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

Previous topic - Next topic

Enc0der

#180
Here is a great attitude I really suggest you to adopt:  :)

**removed**

青山 素子

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.


Enc0der


toy9b

Hi There ...

Should I convert smf_messages into InnoDB?

Thanks

Enc0der

most likely no.
(unless you have many many posted messages per second, that nobody actually reads...)

toy9b


toy9b

One more thing,

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

Thanks again

master2oo8

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

Leeloo5E

Should I make the DROP before the CREATE or just a ALTER TABLE is ok?

Something like that

Use ALTER TABLE if you don't want to lose any information.

Leeloo5E

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?

Something like that

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

No. Though SMF has an option to run optimize tables. Did you click it?

Leeloo5E

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.


Something like that

You may also want to set smf_log_online to memory, too. You have to convert the url column to varchar (1024) first though.

Leeloo5E

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

Something like that

#195
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:

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.

Turk Navy

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?

Something like that

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

Yes, it is.

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

Turk Navy

Quote from: Phalloidium on May 06, 2009, 11:25:54 AM
Quote from: 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?

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?

Something like that

Quote from: Turk Navy on May 06, 2009, 02:51: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).

Advertisement: