Convert your tables to InnoDB

[Turk Navy]

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

So, here's what should i do?

* Convert all tables to myisam
* add "innodb_file_per_table=1" to my.cnf
* Convert tables to innodb

Am I right?

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

So, here's what should i do?

* Convert all tables to myisam
* add "innodb_file_per_table=1" to my.cnf
* Convert tables to innodb

Am I right?

There's really not much need to do that (beyond a slight performance increase).

[Turk Navy]

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

So, here's what should i do?

* Convert all tables to myisam
* add "innodb_file_per_table=1" to my.cnf
* Convert tables to innodb

Am I right?

There's really not much need to do that (beyond a slight performance increase).

I am not doing this for the performance. As I said before, I just want to see my whole database in one folder while using SSH.

Aren't these things going to make that happen?

[Something like that]

I am not doing this for the performance. As I said before, I just want to see my whole database in one folder while using SSH.

Aren't these things going to make that happen?

Yes, that would be the effect. Keep in mind that your ibdata* files won't shrink (even if they're mostly empty). Also be aware that some information about the structure of the database is still stored in those ibdata files (so backing up the directory with the tables in it won't be sufficient).

[Turk Navy]

I am not doing this for the performance. As I said before, I just want to see my whole database in one folder while using SSH.

Aren't these things going to make that happen?

Yes, that would be the effect. Keep in mind that your ibdata* files won't shrink (even if they're mostly empty). Also be aware that some information about the structure of the database is still stored in those ibdata files (so backing up the directory with the tables in it won't be sufficient).

Hmm, I was going to make that changes just for the backup. Thanks for letting me know. So, what should I do for getting full backup of my database while using SSH?

[Something like that]

I am not doing this for the performance. As I said before, I just want to see my whole database in one folder while using SSH.

Aren't these things going to make that happen?

Yes, that would be the effect. Keep in mind that your ibdata* files won't shrink (even if they're mostly empty). Also be aware that some information about the structure of the database is still stored in those ibdata files (so backing up the directory with the tables in it won't be sufficient).

Hmm, I was going to make that changes just for the backup. Thanks for letting me know. So, what should I do for getting full backup of my database while using SSH?

This is how I do it:

mysqldump --all-databases -u root -pPASSWORD | gzip > ~/mysql.backup.sql.gz

Note the lack of a space between -p and the password.

This, however, will take significant time (minutes) on a large forum during which time your site will be down.

If you wish to backup live, one of the usual ways is to keep your database files on an LVM volume. You then make a snapshot, and copy the database files. If you just start copying files on a live InnoDB database, the ib_logfile* files may have played through and over-written up-coming changes by the time you have copied all the data. You also have to be careful to copy them last. If you use a snapshot, you can just copy without worry.

[Stüldt Håjt]

Using -q option in mysqldump will do the backup much faster. My ~4GB forum database it will take 2-3 minutes to make a full backup.

[Something like that]

Using -q option in mysqldump will do the backup much faster. My ~4GB forum database it will take 2-3 minutes to make a full backup.

Thanks!

[Something like that]

I just read the man page for mysqldump. Looks like -q is enabled by default -- it's enabled by --opt, which is itself enabled by default.

[Stüldt Håjt]

Didn't work by default on my debian mysql 5.0.x installation. I was very astonished when I first time tested the -q option.

[ForumIntegration]

My host is using InnoDB and they do not allow some tables in MyISAM and some in InnoDB. I suppose by converted all the tables to InnoDB will not affect the forum functionality but will have some effect on the performance. Is that true?

Several posts in this thread mention that converted all the tables to InnoDB causes problems. How could that happen?

Thanks!

[Something like that]

My host is using InnoDB and they do not allow some tables in MyISAM and some in InnoDB. I suppose by converted all the tables to InnoDB will not affect the forum functionality but will have some effect on the performance. Is that true?

Several posts in this thread mention that converted all the tables to InnoDB causes problems. How could that happen?

Thanks!

The big one is the board ordering. You have to adjust the primary key on smf_boards. I believe it talks about this in the first post.

I run all InnoDB without any other issues. Do note that if MySQL isn't tuned properly, InnoDB can run very slowly. But properly tuned, it's just as fast as MyISAM (and in many cases faster).

[ForumIntegration]

If the board order changes after converting to InnoDB, is it easy to re-order it in the admin page? Besides this, any other problems?

Does SMF has some fulltext search functionality that will be jeopardized by switching to InnoDB.

[Joshua Dickerson]

To be blunt, your host sucks.

[Something like that]

If the board order changes after converting to InnoDB, is it easy to re-order it in the admin page? Besides this, any other problems?

No, that doesn't fix it. It's a bug in SMF 1.1. It may be fixed in 2.0. I'm not sure.

Does SMF has some fulltext search functionality that will be jeopardized by switching to InnoDB.

Yes. You'll need to build a custom search index.

[ForumIntegration]

Thanks a lot for your help!

If the board order changes after converting to InnoDB, is it easy to re-order it in the admin page? Besides this, any other problems?

No, that doesn't fix it. It's a bug in SMF 1.1. It may be fixed in 2.0. I'm not sure.

Looks SMF2.0 still has this problem.  The following query can fix the order
    ALTER TABLE `smf_boards` ADD UNIQUE KEY (`id_board`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `board_order`, `id_board` ), ENGINE=InnoDB;

Does SMF has some fulltext search functionality that will be jeopardized by switching to InnoDB.

Yes. You'll need to build a custom search index.

Could you please give more details on this?

Another problem is when I try to convert the table smf_openid_assc to InnoDB, it complains about "BLOB/TEXT column "handle" used in key specification without a key length". I must change the datatype of handle to varchar(255). Will that cause any trouble?

Thanks!

[ForumIntegration]

Mark Rose, is there any specific reason that the smf_log_floodcontrol table need to be MEMORY? What is that table for? Will a InnoDB smf_log_floodcontrol table hit the performance badly?

Thanks!

[Portable]

Very nice information. Thanks for this. I look forward to reading more information from you.

[Something like that]

Could you please give more details on this?

Another problem is when I try to convert the table smf_openid_assc to InnoDB, it complains about "BLOB/TEXT column "handle" used in key specification without a key length". I must change the datatype of handle to varchar(255). Will that cause any trouble?

Thanks!

It's in the Admin area of the forum. You'll need to build a custom search index, because InnoDB doesn't support fulltext searches.

I haven't played with converting that table. From what I understand, OpenID support is a bit broken at the moment anyway.

[Flavious]

Our site is getting in excess of 80,000 unique visitors and 4 million page views per day. About once a week, someone will post a message and whatever board they posted to will lock up. Any user after that clicking on the board will cause their browser to just sit and spin... it won't go into that board.

The only way I've found to fix this is to look up the ID_Board for that board, then look at all the messages, and start deleting posts backwards till I find the one that caused the issue. It's usually the first one or two messages, sometimes the third. But once it's deleted, it works fine.

I am on a shared hosting solution, but the other sites on the server are very small, most are static and not using MySQL.

Any ideas? I'm to replicate the problem by posting. It seems to be rather random.