Advertisement:

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

Offline Turk Navy

  • Jr. Member
  • **
  • Posts: 156
  • Gender: Male
  • J. Statham
Re: Convert your tables to InnoDB
« Reply #200 on: May 08, 2009, 02:09:28 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).
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?

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #201 on: May 08, 2009, 02:25:18 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).
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).

Offline Turk Navy

  • Jr. Member
  • **
  • Posts: 156
  • Gender: Male
  • J. Statham
Re: Convert your tables to InnoDB
« Reply #202 on: May 08, 2009, 02:38:37 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).
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?

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #203 on: May 08, 2009, 02:48:38 PM »
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).

Offline Turk Navy

  • Jr. Member
  • **
  • Posts: 156
  • Gender: Male
  • J. Statham
Re: Convert your tables to InnoDB
« Reply #204 on: May 08, 2009, 03:13:33 PM »
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?

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #205 on: May 08, 2009, 03:36:07 PM »
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.
« Last Edit: May 08, 2009, 04:51:44 PM by Phalloidium »

Offline Stüldt Håjt

  • Jr. Member
  • **
  • Posts: 326
Re: Convert your tables to InnoDB
« Reply #206 on: May 08, 2009, 04:40:26 PM »
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.

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #207 on: May 08, 2009, 04:49:54 PM »
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!

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #208 on: May 08, 2009, 04:51:25 PM »
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.

Offline Stüldt Håjt

  • Jr. Member
  • **
  • Posts: 326
Re: Convert your tables to InnoDB
« Reply #209 on: May 08, 2009, 07:30:57 PM »
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.

Offline ForumIntegration

  • Semi-Newbie
  • *
  • Posts: 37
Re: Convert your tables to InnoDB
« Reply #210 on: October 21, 2009, 09:05:15 PM »
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!

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #211 on: October 21, 2009, 09:13:12 PM »
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).

Offline ForumIntegration

  • Semi-Newbie
  • *
  • Posts: 37
Re: Convert your tables to InnoDB
« Reply #212 on: October 21, 2009, 09:35:06 PM »
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.

Offline Joshua Dickerson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,778
  • Gender: Male
    • joshuaadickerson on GitHub
    • joshuaadickerson on LinkedIn
Re: Convert your tables to InnoDB
« Reply #213 on: October 21, 2009, 10:03:26 PM »
To be blunt, your host sucks.
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 Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #214 on: October 21, 2009, 10:30:53 PM »
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.

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

Offline ForumIntegration

  • Semi-Newbie
  • *
  • Posts: 37
Re: Convert your tables to InnoDB
« Reply #215 on: October 22, 2009, 09:50:11 AM »
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;

Quote
Quote
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!
« Last Edit: October 22, 2009, 05:16:05 PM by ForumIntegration »

Offline ForumIntegration

  • Semi-Newbie
  • *
  • Posts: 37
Re: Convert your tables to InnoDB
« Reply #216 on: October 22, 2009, 08:17:23 PM »
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!

Offline Portable

  • Newbie
  • *
  • Posts: 3
Re: Convert your tables to InnoDB
« Reply #217 on: October 23, 2009, 08:32:13 AM »
Very nice information. Thanks for this. I look forward to reading more information from you.

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: Convert your tables to InnoDB
« Reply #218 on: October 28, 2009, 12:36:41 AM »
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.

Offline Flavious

  • Semi-Newbie
  • *
  • Posts: 62
Boards locking up - very large site...
« Reply #219 on: January 05, 2010, 03:42:46 PM »
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.