Convert your tables to InnoDB

[Enc0der]

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.

[Enc0der]

hehe.. sorry

[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]

Thanks Enc0der

[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]

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]

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]

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 Expand


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]

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]

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]

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).