Urgent: IPB with MyISAM, will it run ?

[Andy Millne]

I'm using the 5.1.65-cll version. (I guess).

Dumb question: If I drop the indexes of a table(Ex: ibf_posts) to convert to InnoDB, it won't damage the table right ?

The only thing that will change is that I'll have to use sphinx to search ?

That's correct. Take a backup regardless as there's always the possibility of data corruption when altering large tables.

[blair]

Back to the OP, I disagree with the second part of his hosts recommendation (bolded)

Talking with my webhost(Knownhost) and after doing a lot of tweaks in the server to minimize the problems, they told me to talk with IPB to enable the "innodb_file_per_table" option in MySQL configuration and switch ibf_content_cache_posts from MyISAM to InnoDB, to increase performance.

I've run InnoDB (Percona) for a couple of years. I currently have an 18GB database, but it was even larger when using exclusively InnoDB. I now run content_cache_posts, and core_item_markers as MyISAM (ibf_sessions in memory). There's no reason you can't run both, It reduces the size of the database. Has better read performance. Greatly reduces chances of corruption of your InnoDB tablespace.

My server has 32GB of RAM so the reduced database size allows InnoDB tables to run from memory. content_cache_posts can be a huge table. Also, it's a cache that can easily be rebuilt, so there's no need for additional data integrity. Page generation times, page load times, and server load loads were all lower after the conversion to MyISAM. core_item_markers was added to MyISAM because when I was experiencing a hardware issue, it was a table consistently marked as corrupt. Simply the path of least resistance. Your results may vary.

One last word about InnoDB, it's very robust and far less likely to suffer corruption than MyISAM. Data integrity, and no row locking are probably the best reasons to switch. However, If you're accustomed to running repairs on MyISAM tables, at first it's a bit jarring. Data corruption can and does occur with InnoDB. When it does, "recovery" usually means lost data in the corrupt table. Standard cPanel backups also won't backup InnoDB (recommend XtraDB). A good backup plan is a must.

Looking forward to testing 5.6 and fulltext search on a 3.4.x dev server.

[PatrickRQ]

When I try to convert posts, topics, message_posts, etc... I got this error: ERROR 1214 (HY000) at line 1: The used table type doesn't support FULLTEXT indexes

Any ideas ?

Thanks

You do not have to drop whole table.. just do.. show create table table_name;

then look at the key_name of fulltext then do alter table table_name drop index key_name

then convert it to InnoDB..

List of fulltext keys..

blog_entries            -> FULLTEXT KEY `entry_name` (`entry_name`)


ccs_custom_database_1   -> FULLTEXT KEY `field_1` (`field_1`)
                        -> FULLTEXT KEY `field_3` (`field_3`)


ccs_custom_database_3   -> FULLTEXT KEY `field_15` (`field_15`)
                        -> FULLTEXT KEY `field_18` (`field_18`)

nexus_packages          -> FULLTEXT KEY `p_name` (`p_name`)
                        -> FULLTEXT KEY `p_desc` (`p_desc`)

nexus_support_replies   -> FULLTEXT KEY `reply_post` (`reply_post`)

nexus_support_requests  -> FULLTEXT KEY `r_title` (`r_title`)

posts                   -> FULLTEXT KEY `post` (`post`)

topics                  -> FULLTEXT KEY `title` (`title`)

[Ohio Riders]

bfarber may have had a much different experience with it than I did at least.

In truth, MyISAM offers almost no tangible benefits other than full text indexing, especially with MySQL 5.6.. the generally available 5.5 is much better with InnoDB even. Any benefits that you could state will most likely come from resources that are years old. The performance is worse with MyISAM, the lack of row-level locking causes performance problems with tables that require frequent updates (sessions).. scaling is even worse. Having a transaction log as well makes it super easy to restore a corrupted database from the last backup with InnoDB.

Here is your best bet for a db right now: http://www.percona.com/software/percona-server

Utilize the Xtradb storage engine for your tables.

Agreed. There's no way I'd go back to MyISAM for anything.

Not sure about Percona though. I'd rather use MariaDB than Percona if I weren't using plain Jane MySQL.

For scaling, MySQL 5.6 has some excellent improvements in replication and failover. Look on YouTube for "mysql 5.6 replication" if you haven't already seen the demo video. Great stuff.

[Ohio Riders]

Sorry, I simply don't know about databases. Its not clear for me hehe.

I'm searching on how to do that. Thank you!

Back up everything before you do anything else. If you're unsure what you're doing, it's VERY easy to shoot yourself in the foot.

[teacher]

A question (or two):

If I decide to change engine of some tables to Innodb and rest of the tables leave it to Myisam, what I should write to conf_global as a table engine: Inno or myisam?

And what do that memory type of engine? Write all to RAM or something on the disk?

[GreenLinks]

Agreed. There's no way I'd go back to MyISAM for anything.

Not sure about Percona though. I'd rather use MariaDB than Percona if I weren't using plain Jane MySQL.

For scaling, MySQL 5.6 has some excellent improvements in replication and failover. Look on YouTube for "mysql 5.6 replication" if you haven't already seen the demo video. Great stuff.

I don't deny the fact that MySQL 5.6 has nice improvements but still in my own tests Persona always performed faster compared to MySQL 5.6 and MariaDB. However your results may vary and i totally understand that as each databases/website requirements are different

[BigStamp]

A question (or two):

If I decide to change engine of some tables to Innodb and rest of the tables leave it to Myisam, what I should write to conf_global as a table engine: Inno or myisam?

And what do that memory type of engine? Write all to RAM or something on the disk?

Did you ever find the answer to this?

[Grumpy]

Did you ever find the answer to this?

There is no real answer when you go splitting some into innodb and another into myisam. At least not with this much generalization.

[Marcher Technologies]

There is no real answer when you go splitting some into innodb and another into myisam. At least not with this much generalization.

INNODB.

otherwise, IPB will attempt to make fulltext queries on INNODB tables.

[sobrenome]

Now that MySQL 5.6 is out there, is it recommend for performance to change all tables from Myisam to innoDB?

[GreenLinks]

Now that MySQL 5.6 is out there, is it recommend for performance to change all tables from Myisam to innoDB?

I will suggest waiting IPB 4 to do that also instead of default MySQL 5.6 you shall choose either MariaDB or Percona 5.6

[sobrenome]

I would like to use MariaDB, but I am stuck in Cpanel and they are still considering support for MariaDB in a future release.

[Dmacleo]

I would like to use MariaDB, but I am stuck in Cpanel and they are still considering support for MariaDB in a future release.

I ran maria on cpanel

http://blog.cpanel.net/mysql-mariadb/

[sobrenome]

What about the performance difference? Is it really that amazing? Even if compared to MySQL 5.6?

[Dmacleo]

was better for me with no tuning done.

going to probably convert my directadmin mysql over this week.